Insert multiple rows in Sql Server Tables using java

Question

Hi i want to achieve following

insert into t1 (c1,c2) values (v1,v2)

above transaction will create a unique identifier for the row created. lets say UIDT1

now i want to execute multiple inserts in another table like

insert into t2 (c1,c2,c3) values (UIDT1,v2,v3)
insert into t2 (c1,c2,c3) values (UIDT1,v2,v3)
insert into t2 (c1,c2,c3) values (UIDT1,v2,v3)
.
.
.

using Java, i can write all queries in a text file and read one by one and create all transactions but i wanted to know if there is any more efficient way of doing the same. Need your inputs..

Note : I am using Spring JDBC

Purpose : Unit testing, creating User(t1) and UserDetails(t2) considering tests run with a fresh DB i am creating users and user details first and then using that user i will test other scenarios.

For executing multiple inserts you could look into using a PreparedStatement with batches. A little more info here: http://stackoverflow.com/questions/6892105/bulk-insert-in-java-using-prepared-statements-batch-update — tim, Aug 01 '14 at 22:36

score 0 · Answer 1 · answered Aug 02 '14 at 04:15

You can do somthing like this

String [] queries = {
    "insert into t2 (c1,c2,c3) values (UIDT1,v2,v3)",
    "insert into t2 (c1,c2,c3) values (UIDT1,v2,v3)",
    "insert into t2 (c1,c2,c3) values (UIDT1,v2,v3)"
};
try{
connectionObject.setAutoCommit(false);
Statement statement = connectionObject.createStatement();

for (String query : queries) {
    statement.addBatch(query);
}
statement.executeBatch();
connectionObject.commit();
}
catch(SQLException e){
e.printStackTrace(); 
}
finally{
//closing statements 
}

Insert multiple rows in Sql Server Tables using java

1 Answers1