execute batch of queries in one time using java and JDBC

Let say we have something like below.

String [] queries = {

    "insert into employee (name, city, phone) values ('A', 'X', '123')",

    "insert into employee (name, city, phone) values ('B', 'Y', '234')",

    "insert into employee (name, city, phone) values ('C', 'Z', '345')",

};

             

Connection connection = new getConnection();

Statement statemenet = connection.createStatement();

             

for (String query : queries) {

    statemenet.execute(query); //execute query one by one

}

statemenet.close();

connection.close();

 

Execute all the queries one by one.This is not a good practice.We can use something like below.Execute all the queries in once.

 

 

 

Connection connection = new getConnection();

Statement statemenet = connection.createStatement();

 

for (String query : queries) {

    statemenet.addBatch(query);

}

statemenet.executeBatch();

statemenet.close();

connection.close();

 

 

 

 

As well as we can avoid sql injection by using something like below.

 

String sql = "insert into employee (name, city, phone) values (?, ?, ?)";

Connection connection = new getConnection();

PreparedStatement ps = connection.prepareStatement(sql);

 

final int batchSize = 1000;

int count = 0;

 

for (Employee employee: employees) {

 

    ps.setString(1, employee.getName());

    ps.setString(2, employee.getCity());

    ps.setString(3, employee.getPhone());

    ps.addBatch();

     

    if(++count % batchSize == 0) {

        ps.executeBatch();

    }

}

ps.executeBatch(); // insert remaining records

ps.close();

connection.close();