MainActivity如下:
package cc.testdb;
import cc.database.DBUtils;
import cc.domain.Person;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.Window;
import android.view.WindowManager;
import android.widget.Button;
import android.app.Activity;
/**
* Demo描述:
* 利用SQLiteStatement提高数据库插入数据的效率
*
* 测试说明:
* 我们分别采用两种方式来插入大量数据(此处为2000条数据)
* 方式一:
* 利用execSQL插入数据
* 方式二:
* 利用SQLiteStatement插入数据
*
* 我们分别计算两种方式的耗时进行比较,可以发现在插入2000条数据时候
* 方式二比方式一大概可节约5秒钟以上的时间.
*
* 参考资料:
* http://liuzhichao.com/p/1664.html
* Thank you very much
*
*/
public class MainActivity extends Activity {
private DBUtils mDBUtils;
private Button mFirstInsertButton;
private Button mSecondInsertButton;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
requestWindowFeature(Window.FEATURE_NO_TITLE);
getWindow().setFlags(WindowManager.LayoutParams.FLAG_FULLSCREEN,
WindowManager.LayoutParams.FLAG_FULLSCREEN);
setContentView(R.layout.main);
init();
}
private void init(){
mFirstInsertButton=(Button) findViewById(R.id.firstInsertButton);
mFirstInsertButton.setOnClickListener(new ClickListenerImpl());
mSecondInsertButton=(Button) findViewById(R.id.secondInsertButton);
mSecondInsertButton.setOnClickListener(new ClickListenerImpl());
}
private class ClickListenerImpl implements OnClickListener {
Person person=null;
@Override
public void onClick(View v) {
mDBUtils=new DBUtils(MainActivity.this);
switch (v.getId()) {
case R.id.firstInsertButton:
new Thread(){
public void run() {
long startTime=System.currentTimeMillis();
for (int i = 0; i < 2000; i++) {
person=new Person("xiaoming"+i, "9527"+i);
mDBUtils.addDataByExecSQL(person);
}
long endTime=System.currentTimeMillis();
System.out.println("第一种方式耗时:"+(endTime-startTime)/1000);
};
}.start();
break;
case R.id.secondInsertButton:
new Thread(){
public void run() {
long startTime=System.currentTimeMillis();
for (int i = 0; i < 2000; i++) {
person=new Person("xiaoming"+i, "9527"+i);
mDBUtils.addDataBySQLiteStatement(person);
}
long endTime=System.currentTimeMillis();
System.out.println("第二种方式耗时:"+(endTime-startTime)/1000);
};
}.start();
break;
default:
break;
}
}
}
}
DBUtils如下:
package cc.database;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import cc.domain.Person;
public class DBUtils {
private DataBaseOpenHelper openHelper;
public DBUtils(Context context) {
openHelper=new DataBaseOpenHelper(context);
}
public void initDataBase(Context context){
openHelper=new DataBaseOpenHelper(context);
openHelper.getWritableDatabase();
}
public void addDataByExecSQL(Person person){
SQLiteDatabase db=openHelper.getWritableDatabase();
db.execSQL("insert into person (name,phone) values(?,?)",
new Object[]{person.getName(),person.getPhone()});
}
public void addDataBySQLiteStatement(Person person){
SQLiteDatabase db=openHelper.getWritableDatabase();
SQLiteStatement sqLiteStatement=
db.compileStatement("insert into person (name,phone) values(?,?)");
sqLiteStatement.bindString(1, person.getName());
sqLiteStatement.bindString(2, person.getPhone());
sqLiteStatement.executeInsert();
}
}
DataBaseOpenHelper如下:
package cc.database;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DataBaseOpenHelper extends SQLiteOpenHelper {
private final static String DATABASE_NAME="test.db";
public DataBaseOpenHelper(Context context) {
super(context, DATABASE_NAME, null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table person(personid integer primary key autoincrement,name varchar(20),phone VARCHAR(12))");
}
//为person增加一个address字段,默认值为null
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("ALTER TABLE person ADD address VARCHAR(12) NULL");
}
}
Person如下:
package cc.domain;
public class Person {
private Integer id;
private String name;
private String phone;
public Person(String name, String phone) {
this.name = name;
this.phone = phone;
}
public Person(Integer id, String name, String phone) {
this.id = id;
this.name = name;
this.phone = phone;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", phone=" + phone + "]";
}
}
main.xml如下:
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
>
<TextView
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="使用SQLiteStatement提高数据库插入数据的效率"
android:layout_marginTop="50dip"
android:gravity="center"
/>
<Button
android:id="@+id/firstInsertButton"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="The First Insert Button"
android:layout_marginTop="50dip"
/>
<Button
android:id="@+id/secondInsertButton"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="The Second Insert Button"
android:layout_marginTop="50dip"
/>
</LinearLayout>
参考资料:
https://developer.android.com/reference/android/database/sqlite/SQLiteStatement.html
http://blog.csdn.net/vurtne_ye/article/details/21947177
http://blog.csdn.net/efeics/article/details/18995433
Thank you very much