Java data transaction performance

Question

I know how to open data transaction with JDBC. But i think I can/must do something to increase data transaction performance. For example:

public class F_Koneksi {
private static final String JDBC_DRIVER;
private static final String DB_URL;
private static final String USER;
private static final String PASS;
static {
    JDBC_DRIVER = "org.postgresql.Driver";
    DB_URL = "jdbc:postgresql://localhost:5432/MyDatabase";
    USER = "Username";
    PASS = "Password";
}
private final Connection con;
private ResultSet rs;
private Statement stmt;

public F_Koneksi() {
    Connection connect;
    try {
        Properties props = new Properties();
        props.setProperty("user", USER);
        props.setProperty("password",PASS);
        props.setProperty("sslfactory", "org.postgresql.ssl.NonValidatingFactory");
        props.setProperty("ssl", "true");
        forName(JDBC_DRIVER);
        connect = getConnection(DB_URL, props);
    } catch (SQLException|ClassNotFoundException se) {
        connect = null;
    }
    con = connect;
}
public boolean Update(String Query) {
    try {
        Query = Query.replaceAll("`", "\"");
        System.out.println(Query);
        Statement stmt = con.createStatement();
        stmt.executeUpdate(Query);
        return true;
    } catch (SQLException ex) {
        ex.printStackTrace();
        return false;
    } 
}

And when i must close my connection or turning auto commit off?

What can I do to improve my app data transaction performance? How is the proper way to make data transaction? Or any tips to do it better?

To increase your insert performance use batch – Marged Dec 03 '15 at 00:02 — Marged, Dec 03 '15 at 00:02

score 1 · Accepted Answer · edited May 23 '17 at 12:33

When i must close my connection?

If you are running in a Java EE environment (i.e. on an app server) then you can get and close connections as you wish, since most Java EE environments will pool JDBC connections for you unless you explicitly disable connection pooling.

If you are running in a Java SE environment, this depends on how you are getting the connection. For this example, it looks like you are doing a bunch of static imports (which is bad practice by the way) and you are but from waht I can tell you are using DriverManager to get your connection. If this is true and you are using DriverManager, then getting connections is very expensive! Especially once you start using a remote database. You will want to try to cache your connections. Alternatively, you could use a javax.sql.ConnectionPoolDataSource and use getPooledConnection() which will have much higher performance for get/close scenarios and take care of the connection caching for you.

When should I turn auto-commit on/off?

Auto commit on or off isn't a huge deal. I always like to leave auto-commit on, since it is less error prone by leaving the commit responsibility up to the JDBC driver.

What will help out your performance a lot is if you batch your Statements.

For example:

try(Statement statement = conn.createStatement()){
    statement.addBatch("update people set firstname='Alice' where id=1");
    statement.addBatch("update people set firstname='Bob'   where id=2");
    statement.addBatch("update people set firstname='Chuck' where id=3");
    statement.executeBatch();
}

Java data transaction performance

1 Answers1