Android利用SQLiteStatement提高数据库插入数据的效率

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