android -> sqlite 快速大批量插入

结果：

1W 数据 658 MS

10W 数据 3482 MS

 

 

 

import android.app.Activity;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.os.Bundle;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

public class MainActivity extends Activity {
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        //-------------

        long start = System.currentTimeMillis();

        List<RemoteAppInfo> l1 = new ArrayList<RemoteAppInfo>();
        RemoteAppInfo r1 = new RemoteAppInfo();
        r1.setParam1("ppp1");
        r1.setParam2("pppp2");
        l1.add(r1);
        for (int x =0;x<100000;x++){
            l1.add(r1);
        }

        MySQLiteHelper sqlHelper = new MySQLiteHelper(this, "test.db", null, 1);

        insertBySql(sqlHelper,l1);

        long end = System.currentTimeMillis();

        Log.d("mft", "插入完了, 执行时间 : " + (end - start));


    }
    public static boolean insertBySql(SQLiteOpenHelper openHelper,
                                      List<RemoteAppInfo> list) {
        if (null == openHelper || null == list || list.size() <= 0) {
            return false;
        }
        SQLiteDatabase db = null;
        try {
            db = openHelper.getWritableDatabase();
            String sql = "insert into " + "person" + "("
                    + "name" + ","// 包名
                    + "age"
                    + ") " + "values(?,?)";
            SQLiteStatement stat = db.compileStatement(sql);
            db.beginTransaction();
            for (RemoteAppInfo remoteAppInfo : list) {
                stat.bindString(1, remoteAppInfo.getParam1());
                stat.bindString(2, remoteAppInfo.getParam2());
                long result = stat.executeInsert();
                if (result < 0) {
                    return false;
                }
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (null != db) {
                    db.endTransaction();
                    db.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

}


class MySQLiteHelper extends SQLiteOpenHelper{
    //调用父类构造器
    public MySQLiteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,
                          int version) {
        super(context, name, factory, version);
    }

    /**
     * 当数据库首次创建时执行该方法，一般将创建表等初始化操作放在该方法中执行.
     * 重写onCreate方法，调用execSQL方法创建表
     * */
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS person (_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR, age SMALLINT)");

    }

    //当打开数据库时传入的版本号与当前的版本号不同时会调用该方法
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

}

class RemoteAppInfo {

    private String param1;
    private String param2;

    public String getParam1() {
        return param1;
    }

    public void setParam1(String param1) {
        this.param1 = param1;
    }

    public String getParam2() {
        return param2;
    }

    public void setParam2(String param2) {
        this.param2 = param2;
    }



}