SQL performance on PreparedStatement vs keeping an open connection

Question

I'm writing a program that reads lines from a csv file, for each of these lines it checks against an different database for some additional data and it finally inserts the newly constructed data in a mysql DB.

        BufferedReader br = new BufferedReader(new FileReader(file));
        for(String line; (line = br.readLine()) != null; ) { //Read each file line
            try{
                processLine(line);
            } catch(ProcessLineException e){
                logger.warn("Something happened");
            }
        }
        br.close();

processLine being

private void processLine(String line) throws ProcessLineException{
    ...
    insertData(foo, data);
}

private void insertData(String foo, String data) {
    Connection connection = null;
    PreparedStatement pStatement = null;
    try{
        connection = dataSource.getConnection();
        pStatement = connection.prepareStatement("INSERT INTO table VALUES(?,?)");
        pStatement.setString(1, foo);
        pStatement.setString(2, data);
    } catch(SQLException e){
        logger.error("Error when inserting data");
    } finally {
        try {
            pStatement.close();
            connection.close();
        } catch (SQLException e) {
            logger.warn("Couldn't close resources");
        }
    }
}

I've learnt some things about PreparedStatements when I was looking for a better way to handle SQLException (could also get some help on that, code above) and, the way i see it, I could benefit from using a PreparedStatement to store the mysql insert query and just modify parameters on each iteration of the loop. But shouldn't that imply that I should keep an open connection with the database during the whole process? Would that be negative in any way?

`dataSource.getConnection();` Are you not already keeping a single open connection? This call looks like a singleton or DIC. — bassxzero, Oct 19 '17 at 14:55

score 1 · Accepted Answer · answered Oct 19 '17 at 15:11

You are executing each query separately. This hits the database for each insert statement.Instead you should use addBatch() method of Statement, instead of directly executing the query one after another like above and after adding all the queries should execute them in one go using statement.executeBatch() method.e.g

import java.sql.Connection;
import java.sql.Statement;

//...

Connection connection = new getConnection();
Statement statement = connection.createStatement();

for (Employee employee: employees) {
    String query = "insert into employee (name, city) values('"
            + employee.getName() + "','" + employee.getCity + "')";
    statement.addBatch(query);
}
statement.executeBatch();
statement.close();
connection.close();

SQL performance on PreparedStatement vs keeping an open connection

1 Answers1