Execute multiple SQL statements (CRUD) with JDBC

Question

I am using Java (JDBC) to create a command line utility for SQL statement execution. A script is defined as a text file, having many queries. Each query is separated by a query separator (";"). The output is routed to stdout.

SELECT * FROM table1;
UPDATE table1 SET field1='' WHERE field2='';
SELECT * FROM table1;
INSERT INTO table1 VALUES(...)
SELECT * FROM table1;

Since JDBC can execute statements batchwise, only if they don't return a ResultSet, I need another approach.

As of now, I would read the script file with the queries, split them by the separator, and analyze each query, whether it's a "SELECT" query, or an "INSERT", "UPDATE", "DELETE" query. After that, I would execute each query in it's own statement. The ones that return something are written to stdout, the queries that manipulate the database are executed. And, of course I would keep the order of the queries from the file.

My problem is: If one of the queries in the file is wrong, I can't rollback, because each query is executed separately. How could I handle this issue?

Currently looking for an approach to implement this. I think this code is what I'm looking for: http://stackoverflow.com/a/1497614/1188357 — Kiril, Oct 21 '14 at 11:09
in the end statement is executing by a string, make a separate method for executing query, and set a flag for valid line — Keshav Sharma, Oct 21 '14 at 12:03

score 1 · Answer 1 · answered Oct 21 '14 at 09:59

1

For your database connection, just call connection.setAutoCommit(false) then execute your statements and call connection.commit() when you're finished, or connection.rollback() if you encounter an error.

answered Oct 21 '14 at 09:59

BarrySW19

3,759
12
26

For details see the Java Tutorial on [Transactions](http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html). – Gord Thompson Oct 21 '14 at 10:00
The main problem is the differentiation between `SELECT` queries and `UPDATE, DELETE, INSERT...`. This answer has it covered, i think: http://stackoverflow.com/a/1497614/1188357 – Kiril Oct 21 '14 at 11:10

Madhavi Talla · Answer 2 · 2014-10-21T11:00:29.813

1

This is the code for for adding queries to batch.

Statement stmt = conn.createStatement();
conn.setAutoCommit(false);

String SQL = "INSERT INTO table1 VALUES(...)";
stmt.addBatch(SQL);

String SQL = "INSERT INTO Employees (id, first, last, age) " +
         "VALUES(201,'Raj', 'Kumar', 35)";
stmt.addBatch(SQL);

String SQL = "UPDATE Employees SET age = 35 " +
         "WHERE id = 100";
stmt.addBatch(SQL);


 int[] count = stmt.executeBatch();

//Explicitly commit statements to apply changes

 conn.commit();

Try this.....

edited Oct 21 '14 at 11:00

answered Oct 21 '14 at 10:54

Madhavi Talla

205
2
5
12

What about `SELECT` queries? – Kiril Oct 21 '14 at 10:56
What I meant to say is, if I have `String SQL = "SELECT * FROM emploeyees"`, how can I get the result when adding this query to the batch? – Kiril Oct 21 '14 at 11:07

score 0 · Answer 3 · answered May 12 '16 at 12:11

This code might helpful to you.

 String Query1 = "SELECT * FROM Tablename1";
        stmt1 = con.createStatement();
        ResultSet rs = stmt1.executeQuery(Query1);
        rs.next();
        int totalQuery1Count = rs.getInt("TOTAL_Item");

    String Query2 = "SELECT * FROM Tablename2";
        rs = stmt1.executeQuery(Query2);
        rs.next();
        int totalQuery2Count = rs.getInt("TOTAL_Item2");

Execute multiple SQL statements (CRUD) with JDBC

3 Answers3