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>