android insert方法,android中SQLite数据库中用insert同时插入多条记录的方法和效率比较...

在Android开发中我们常会遇到这样一种情况：在建立一个SQLite数据库时，想同时插入大量数据。那么应该怎么做呢？

下面笔者以插入20条记录为例：

将同时插入大量的数据写成一条SQL语句

最笨的方法用insert语句一条一条的输入

使用事务

代码如下：

public class DateBaseOpenHelper extends SQLiteOpenHelper {

public static final String DBNAME="radiomap";

public static final int VERSION=1;

public DateBaseOpenHelper(Context context){

super(context,DBNAME,null,VERSION);

}

@Override

public void onCreate(SQLiteDatabase db) {

// TODO Auto-generated method stub

db.execSQL("create table radiomap(location varchar(20),ap1 int,ap2 int)");

long a=System.currentTimeMillis();

db.execSQL("insert into radiomap(location,ap1,ap2) select 'x=1,y=1',-80,-73 " +

"union all select 'x=2,y=3',80,40 union all select 'x=3,y=5',30,20 "+

"union all select 'x=4,y=5',3,2 union all select 'x=30,y=50',30,20 union all select 'x=3,y=5',40,20"

+" union all select 'x=3,y=5',6,20 union all select 'x=3,y=5',6,7 union all select 'x=3,y=5',7,8 union all select 'x=3,y=5',8,9 union all select 'x=3,y=5',9,9" +

" union all select 'x=3,y=5',3,5 union all select 'x=3,y=5',7,20 union all select 'x=3,y=5',4,20 union all select 'x=3,y=5',5,20 union all select 'x=3,y=5',6,20" +

" union all select 'x=3,y=5',3,6 union all select 'x=3,y=5',7,7 union all select 'x=3,y=5',3,8 union all select 'x=3,y=5',8,2");

long b=System.currentTimeMillis();

long c=b-a;

Log.i("LocationActivity", String.valueOf(c));

a=System.currentTimeMillis();

db.beginTransaction();

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',2,3)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',4,5)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',6,7)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',2,3)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',4,5)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',6,7)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',1,3)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',1,5)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',1,7)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',2,3)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',2,5)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',2,7)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',3,3)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',3,5)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',3,7)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',4,3)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=7',5,5)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',4,7)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',5,3)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',5,5)");

db.setTransactionSuccessful();

db.endTransaction();

b=System.currentTimeMillis();

Log.i("LocationActivity", String.valueOf(b-a));

a=System.currentTimeMillis();

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=7,y=8',7,8)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=8,y=9',8,9)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=9,y=10',9,10)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',2,3)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',4,5)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',6,7)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',2,3)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',2,5)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',2,7)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',3,3)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',3,5)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',3,7)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',4,3)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',4,4)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',4,7)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',5,3)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=7',6,5)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',5,7)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',6,3)");

db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',6,5)");

b=System.currentTimeMillis();

Log.i("LocationActivity", String.valueOf(b-a));

}

第一种方式及：

insert into 表名(列名1,列名2)

select  值1,值2

union all

select 值1,值2

union all

select 值1,值2

以上三种方式测试结果，及运行效率：

第一种方式为9ms

用事务的为：86ms

第三种直接用insert插入的为：29ms

这是因为本次测试用了20条数据，所以用事务的反而比不用的开销大时间长。若1000条以上则明显快于直接用insert插入的。