在java中运行许多sqlLite语句

Question

我是用jdbc用java编程的，我需要运行一个脚本，创建两个表，并在每个表中插入大约30个记录，但是当我运行整个sql指令时，只执行firt语句，这是我的代码。

public Statement qry;
//... set connection and others for sqlite3
qry.execute(strSql); //strSql contains the set of sql sentences

句子看上去像这样

CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT "en_US");INSERT INTO android_metadata VALUES ("es_ES");CREATE TABLE "tblclientes" ("_id" INTEGER,"Nombres" varchar(25) DEFAULT NULL,"Apellidos" varchar(25) DEFAULT NULL,"RazSocial" varchar(20) DEFAULT NULL,"Direccion" varchar(50) DEFAULT NULL,"Ciudad" varchar(15) DEFAULT "Arequipa","Fono" varchar(12) DEFAULT NULL,"Fax" varchar(12) DEFAULT NULL,"Email" varchar(35) DEFAULT NULL,"Ruc" varchar(12) DEFAULT NULL,"latitud" decimal(20,14) DEFAULT NULL,"longitud" decimal(20,14) DEFAULT NULL,"ruta" varchar(10) DEFAULT NULL,"sincro" CHAR(10),"copiar" BOOL DEFAULT 1);INSERT INTO tblclientes VALUES (6, "Julia", "Lea Barrios", "", "Guillermo Mercado Mz-R Lte-22","Arequipa", "", "", "", "", "-16,34930944346466", "-71,56028982646531", "521", "", "1" ),(7, "Reyna ", "Mamani", "", "Villa Fontana Mz-18 Lte-1","Arequipa", "", "", "", "", "-16,37338828616529", "-71,49954834718501", "333", "", "1" ),(8, "Elizabeth", "Paco Toclla", "", "Villa Fontana Mz-7 Lte-7","Arequipa", "", "", "", "", "-16,37327397121976", "-71,49942636965591", "333", "", "1" ),(9, "Faustina ", "Mayhua", "", "Villa Paraiso Mz-B Cte-3 Lte-9","Arequipa", "", "", "", "", "0", "0", "0", "", "1" ),(10, "Marcelina", "Vilca", "", "Villa Cerrilos Mz-I Lte-1","Arequipa", "", "", "", "", "0", "0", "0", "", "1" ),(11, "Patricia", "Hinojosa Chirinos", "", "Villa Santa Maria Mz-B Lte-6","Arequipa", "", "", "", "", "-16,3233752620739", "-71,55582815082221", "544", "", "1" ),(12, "Lorenzo", "Mayta", "", "Urb. Nazareno Mz-C Lte-1 Zona B","Arequipa", "", "", "", "", "0", "0", "0", "", "1" ),(13, "Balbina ", "Sivincha", "", "Urb. Nazareno Mz-M Lte-20","Arequipa", "", "", "", "", "0", "0", "0", "", "1" ) ... and so on

我试过这样做，但是对于100张唱片，我需要很长时间才能插入12000条记录。

consulta = conexion.createStatement();
            consulta.addBatch("CREATE TABLE 'tblclientes' ('_id' INTEGER,'Nombres' varchar(25) DEFAULT NULL,'Apellidos' varchar(25) DEFAULT NULL,'RazSocial' varchar(20) DEFAULT NULL,'Direccion' varchar(50) DEFAULT NULL,'Ciudad' varchar(15) DEFAULT 'Arequipa','Fono' varchar(12) DEFAULT NULL,'Fax' varchar(12) DEFAULT NULL,'Email' varchar(35) DEFAULT NULL,'Ruc' varchar(12) DEFAULT NULL,'latitud' decimal(20,14) DEFAULT NULL,'longitud' decimal(20,14) DEFAULT NULL,'ruta' varchar(10) DEFAULT NULL,'sincro' CHAR(10),'copiar' BOOL DEFAULT 1)");
            consulta.addBatch("INSERT INTO tblclientes VALUES (6, 'Julia', 'Lea Barrios', '', 'Guillermo Mercado Mz-R Lte-22','Arequipa', '', '', '', '', '-16,34930944346466', '-71,56028982646531', '521', '', '1' )");
            consulta.addBatch("INSERT INTO tblclientes VALUES (6, 'Julia', 'Lea Barrios', '', 'Guillermo Mercado Mz-R Lte-22','Arequipa', '', '', '', '', '-16,34930944346466', '-71,56028982646531', '521', '', '1' )");

            consulta.executeBatch();

如何在Java中有效地做到这一点呢？

Answer 1

一些建议：

1. 使用单个事务中的所有语句；
2. 编译语句INSERT INTO tblclientes VALUES (?, ?, ?, ?, ?, ...)，对于每个数据行，只需绑定参数并执行。

这通常是使用SQL插入大(也小)数据的最佳方法。

这样做的速度更快，因为(1)数据被写入存储一次，而不是每一行插入一次，(2) insert语句编译一次，而不是编译每个insert语句(只有数据正在更改，操作是相同的)。

这更安全，因为字符串数据被绑定为参数，因此无效字符不会中断语句，也不允许运行恶意内容。

看看准备好的陈述。

我不是Java程序员，但是您的代码应该如下所示：

Statement stmt = connection.createStatement();
stmt.executeUpdate("CREATE TABLE tblclientes (_id INTEGER, Nombres TEXT, Apellidos TEXT, RazSocial TEXT, Direccion TEXT, Ciudad TEXT DEFAULT 'Arequipa', Fono TEXT, Fax TEXT, Email TEXT, Ruc TEXT, latitud FLOAT, longitud FLOAT, ruta TEXT, sincro TEXT, copiar INT DEFAULT 1)");
PreparedStatement ins = connection.prepareStatement("INSERT INTO tblclientes VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
connection.setAutoCommit(false);
for(...i...) {
    ins.clearParameters();
    ins.setInt(1, id[i]); //1, 2, ...
    ins.setString(2, nombre[i]); //"Julia", ...
    ins.setString(3, apellidos[i]); //"Lea Barrios", ...
    ...
    ins.setDouble(11, latitud[i]); //-16,34930944346466, ...
    ins.setDouble(12, longitud[i]); //-71,56028982646531, ...
    ...
    ins.setInt(15, copiar[i]); //1, ...
    ins.execute();
}
connection.commit(); //All inserts are done now, at once!
connection.setAutoCommit(true); //restore autocommit