sql 数据库批量操作数据-开始事件

 

 

 

具体参考：http://www.blogjava.net/qileilove/archive/2014/05/23/414005.html

http://liuzhichao.com/p/1664.html

实例：

1.普通执行语句插入    37000ms +

2.SQLiteStatement 插入 34000 ms+

3.开启事物插入 trasaction 300+

db.beginTransaction();
xxxx….
db.setTransactionSuccessful();
db.endTransaction();

package com.example.testbatchinsertdata.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {

	private static final String DB_NAME = "userdb";
	private static final int DB_VERSION = 1;

	public DBHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		StringBuffer sql = new StringBuffer();
		sql.append("create table users");
		sql.append("(_id int PRIMARY KEY,name varchar,gender int,age int,phoneNumber varchar,address varchar)");

		db.execSQL(sql.toString());
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

}

　　

package com.example.testbatchinsertdata.db;

public class User {
	private int id;
	private String name;
	private int gender;
	private int age;
	private String phoneNumber;
	private String address;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getGender() {
		return gender;
	}

	public void setGender(int gender) {
		this.gender = gender;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public String getPhoneNumber() {
		return phoneNumber;
	}

	public void setPhoneNumber(String phoneNumber) {
		this.phoneNumber = phoneNumber;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

}

　　

package com.example.testbatchinsertdata.db;

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

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

public class UserDao {

	private DBHelper dbHelper;
	private SQLiteDatabase db;

	private StringBuffer sql_insert;
	private List<User> users;

	public UserDao(Context context){
		this.dbHelper = new DBHelper(context);
		this.db = dbHelper.getWritableDatabase();

		sql_insert = new StringBuffer();
		sql_insert.append("INSERT INTO users(name,gender,age,phoneNumber,address) ");
		sql_insert.append(" VALUES( ?, ?, ?, ?, ?)");

		users = new ArrayList<User>();

		//测试数据
		for(int i = 0;i<1000;i++){
			User user = new User();
			user.setId(i);
			user.setName("name"+i);
			user.setGender(0);
			user.setAge(0);
			user.setPhoneNumber("13800138000");
			user.setAddress("GuangDong ShenZhen No."+i);

			users.add(user);
		}

	}

	/**
	 * 使用SQLiteDatabase 的execSQL方法插入数据 
	 * @return 返回执行所需要的时间
	 */
	public long insertexecSQL()
	{

		long start=System.currentTimeMillis();
		for(User user:users){
			Object[] bindArgs = {user.getName(),user.getGender(),user.getAge(),user.getPhoneNumber(),user.getAddress()};
			db.execSQL(sql_insert.toString(),bindArgs);
		}
		long end = System.currentTimeMillis();

		return end - start;
	}

	/**
	 * 使用SQLiteStatement的executeInsert方法插入数据
	 * @return 返回执行所需要的时间
	 */
	public long insertStatement()
	{
		long start = System.currentTimeMillis();
		for(User user:users){
		SQLiteStatement statement=	db.compileStatement(sql_insert.toString());
		statement.bindString(1, user.getName());
		statement.bindLong(2, user.getGender());
		statement.bindLong(3, user.getAge());
		statement.bindString(4, user.getPhoneNumber());
		statement.bindString(5, user.getAddress());
		statement.executeInsert();
		}
		long end = System.currentTimeMillis();
		return end - start;
	}
	/**
	 * 使用Transaction的executeInsert方法插入数据
	 * @return 返回执行所需要的时间
	 */
	public long insertTransaction()
	{

		long start=System.currentTimeMillis();
		db.beginTransaction();
		for(User user:users){
			Object[] bindArgs = {user.getName(),user.getGender(),user.getAge(),user.getPhoneNumber(),user.getAddress()};
			db.execSQL(sql_insert.toString(),bindArgs);
		}
		db.setTransactionSuccessful();
		db.endTransaction();
		long end = System.currentTimeMillis();

		return end - start;
	}
}

　　

/**
     * 使用SQLiteDatabase 的execSQL方法插入数据 
     * @return 返回执行所需要的时间
     */
    public long insertexecSQL()
    {

        long start=System.currentTimeMillis();
        for(User user:users){
            Object[] bindArgs = {user.getName(),user.getGender(),user.getAge(),user.getPhoneNumber(),user.getAddress()};
            db.execSQL(sql_insert.toString(),bindArgs);
        }
        long end = System.currentTimeMillis();

        return end - start;
    }

    /**
     * 使用SQLiteStatement的executeInsert方法插入数据
     * @return 返回执行所需要的时间
     */
    public long insertStatement()
    {
        long start = System.currentTimeMillis();
        for(User user:users){
        SQLiteStatement statement=    db.compileStatement(sql_insert.toString());
        statement.bindString(1, user.getName());
        statement.bindLong(2, user.getGender());
        statement.bindLong(3, user.getAge());
        statement.bindString(4, user.getPhoneNumber());
        statement.bindString(5, user.getAddress());
        statement.executeInsert();
        }
        long end = System.currentTimeMillis();
        return end - start;
    }
    /**
     * 使用Transaction的executeInsert方法插入数据
     * @return 返回执行所需要的时间
     */
    public long insertTransaction()
    {

        long start=System.currentTimeMillis();
        db.beginTransaction();
        for(User user:users){
            Object[] bindArgs = {user.getName(),user.getGender(),user.getAge(),user.getPhoneNumber(),user.getAddress()};
            db.execSQL(sql_insert.toString(),bindArgs);
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        long end = System.currentTimeMillis();

        return end - start;
    }

 

 

 

ContentProvider  中 需要了解

1.索引操作

2.ContentProvider ContentProviderOperation

3.ContentProvider bulkInsert