在java中运行许多sqlLite语句 [英] run many sqlLite sentences in java

问题描述

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

I am programming in java with jdbc I need to run a script that create 2 tables and insert into around 30 records in each one but When I run the entire sql instructions execute only the firt create statement , this is my code

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 条记录

I tried to do this but for 100 record take a long time I want to insert 12000 records

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 中有效地做到这一点的方法是什么?

What is the way to do this efficently in Java?

推荐答案

一些提示:

1. 在单个事务中使用所有语句；

1. Use all statements inside a single transaction;

编译语句 INSERT INTO tblclientes VALUES (?, ?, ?, ?, ?, ...) 一次，对于每个数据行，只需绑定参数并执行.

Compile statement INSERT INTO tblclientes VALUES (?, ?, ?, ?, ?, ...) ONCE and for each data row, just bind parameters and execute.

这通常是使用 SQL 插入大(和小)数据的最佳方式.

This is generally the best way to insert large (and smaller also) data with SQL.

这更快，因为(1)数据写入存储一次，而不是每行插入，(2)插入语句编译一次，而不是编译每个插入语句(只有数据在变化，操作相同).

This is faster as (1) data is written to storage once, not for every row insert, and (2) insert statement is compiled once, instead of compiling each insert statement (only data is changing, operation is same).

这样更安全，因为字符串数据被绑定为参数，因此无效字符不会破坏您的语句，也不允许运行恶意内容.

This is safer, as string data is bind as parameter, so invalid characters don't break your statements neither malicious content is allowed to run.

查看准备好的语句.

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

I'm not a Java programmer, but your code should look something like this:

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

这篇关于在java中运行许多sqlLite语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！