0

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

The sentences looks like this

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

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();

What is the way to do this efficently in Java?

tshepang
  • 12,111
  • 21
  • 91
  • 136
David
  • 95
  • 1
  • 8
  • 1
    I think you are going to have to break your command into multiple commands and pass each through `qry.execute()`. – mah Dec 05 '13 at 23:58
  • Do you have a sql file which contains those sentences and want to load him for execution? – Bosko Mijin Dec 05 '13 at 23:58
  • Have a look here for a general approach (not SQLite specific): http://stackoverflow.com/questions/10929369/how-to-execute-muliple-sql-statements-from-java – PM 77-1 Dec 06 '13 at 00:04
  • yes I have this is the file http://daviduh.awardspace.info/Test.sql but I get the entire content in a string variable to run in java, but dont work – David Dec 06 '13 at 00:08
  • I finally resolver it the script take long time but when I sorround the entire script with begin transaction and end transactio the run speed increments dramaticaly – David Dec 06 '13 at 00:40

1 Answers1

0

Some pointers:

  1. Use all statements inside a single transaction;

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

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

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.

Take a look at Prepared Statements.

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
LS_ᴅᴇᴠ
  • 10,823
  • 1
  • 23
  • 46