Execute more query before close connection using sql2o

Question

I'm using function which execute more than one query. I want to make it faster and don't open connection after single one.

I'm using sql2o library to connect my java program with postgresql. ConnectionClass is based on Singleton pattern.

In this code connecton.open() function is call number of execute queries time.

public class ConnectionClass{

        private static final ConnectionClass db = new ConnectionClass();
        private final Sql2o sql2o;
        private Connection connection;

private InfraTramDAO() {
        sql2o = new Sql2o("jdbc:postgresql:...............");
    }

private List<Map<String, Object>> queryToList(String query) {

        List<Map<String, Object>> returnList = connection.createQuery(complexSql).executeAndFetchTable().asList();

        return returnList;
    }
}

public void makeObject(FileMapper fileMapper ) {
        ConnectionClass db = ConnectionClass.getInstance();
        db.setConnection(db.getSql2o().open());
        universalCreator(fileMapper );      //function execute few queries
        db.getConnection().close();

    }

Answer 1

Did you try Batch? Look example here

public void insertABunchOfRows(){
    final String sql = "INSERT INTO SomeTable(id, value) VALUES (:id, :value)";

    try (Connection con = sql2o.beginTransaction()) {
        Query query = con.createQuery(sql);

        for (int i = 0; i < 100; i++){
            query.addParameter("id", i).addParameter("value", "foo" + i)
                    .addToBatch();
        }

        query.executeBatch(); // executes entire batch
        con.commit();         // remember to call commit(), else sql2o will automatically rollback.
    }
}