Java - MySQL --> SQLite - unable to insert in SQLite database using java

Question

iv created a program which works really well with MySQL. However, when I convert it to SQLlite, everything works such as Creating tables, getConnection() to the database and such EXCEPT inserting values to the table?? I am unable to insert value to the table using Java (Netbeans) to SQLite database. I can only insert ONE row but not multiple rows? In MySQL, i could insert multiple rows at once, however, I cant in SQLite?

The code is (This works for only ONE row):

       Connection con;
       Statement s= con.CreateStatement();
       String st = "Insert into table1 (10,'abc')";
       s.executeUpdate(st);
       s.close();

If I do something like this (DOES NOT work for more than one row - have no idea why- SQLite):

         Connection con;
         Statement s= con.CreateStatement();
         String st = "Insert into table1 (10,'abc'), (5,'vfvdv')"; //now it doesnt    work since Im inserting more than one row. I can't figure out what Im doing wrong - 
         //Iv also tried Insert into table1(ID, Name) values (10,'abc'), (5,'afb'); but it dont work.
        s.executeUpdate(st);
        s.close();

Could any java expert or anyone help me on this. I cant figure out what Im doing wrong or anything because when I type my commands in the 'SQLite' command line it works fine for ALL. But, In JAVA with SQLite, I can only update one row for some reason? However, Java with MySQL works fine but not SQLlite.

Anyone could clarify what Im doing wrong would be brillant. Thanks alot for reading this and your time.

Thanks alot for the quick replies :).

Aaqib
– Aaqib

2012-05-15 01:51:29 +00:00
Commented May 15, 2012 at 1:51 — Aaqib
– Aaqib, Commented May 15, 2012 at 1:51

joev · Accepted Answer · 2012-05-15 02:02:45Z

2

It's possible that the SQLite JDBC driver doesn't support the multi-insert syntax. This syntax is also not standard, though many databases do support it.

Another option for doing multiple inserts like this is to use the batching mechanism of PreparedStatement.

Connection conn = ....;
PreparedStatement stmt = 
  conn.prepareStatement("INSERT INTO table1(id, name) VALUES (?, ?)");

stmt.setInt(1, 10);
stmt.setString(2, "abc");
stmt.addBatch();

stmt.setInt(1, 5);
stmt.setString(2, "vfvdv");
stmt.addBatch();

stmt.executeBatch();

Typically, you'd use the setInt, setString, addBatch sequence in a loop, instead of unrolled as I've shown here, and call executeBatch after the loop. This will work with all JDBC compliant databases.

edited May 15, 2012 at 2:02

answered May 15, 2012 at 1:44

joev

1,3841 gold badge9 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aaqib Over a year ago

Thanks im going to give this try tommorow and hopefully all goes well thanks :)

Aaqib Over a year ago

I forgot to add - I did add a 'values', however, it didnt work. Im going with the prepared statement as stated by 'joev'. Thanks alot for the help

tibo · Accepted Answer · 2012-05-15 01:43:18Z

0

Multi row insert is not standard according to the SQL92 standard.

SQLite suports it since version 3.7.11.

If you have a version under this one, you need to make one insert for each row...

answered May 15, 2012 at 1:43

tibo

5,5044 gold badges40 silver badges54 bronze badges

1 Comment

Aaqib Over a year ago

Oh k and Im sure I downloaded the lateat version of sqlite but anyways thanks alot for your quick reply

Collectives™ on Stack Overflow

Java - MySQL --> SQLite - unable to insert in SQLite database using java

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related