JDBC batch query for high performance

Question

I want to do batch query DB for high performance, example sql to query based on different customer_id:

select order_id, 
       cost 
from customer c 
  join order o using(id) 
where c.id = ... 
order by

I'm not sure how to do it using JDBC statement. I know I can use stored procedure for this purpose, but it's much better if I can just write sql in Java app instead of SP.
I'm using DBCP for my Java client and MySQL DB.

What exactly do you mean with "batch query"? You can use `where c.id in (...)` to get more than one customer. — , Mar 24 '12 at 16:18
I have no idea what you mean with "multiple result sets for customers". — , Mar 24 '12 at 16:38
@a_horse_with_no_name I mean one result set for one customer, don't mix the return rows. — superche, Mar 24 '12 at 16:45

score 10 · Accepted Answer · answered Mar 24 '12 at 18:31

The JDBC Specification 4.0 describes a mechanism for batch updates. As such, the batch features in JDBC can be used for insert or update purposes. This is described in chapter 14 of the specification.

AFAIK there is not a mechanism for select batches, probably because there is no apparent need for that since, as others have recommended, you can simply retrieve all the rows that you want at once by properly constructing your query.

int[] ids = { 1, 2, 3, 4 };
StringBuilder sql = new StringBuilder();
sql.append("select jedi_name from jedi where id in(");
for (int i = 0; i < ids.length; i++) {
    sql.append("?");
    if(i+1 < ids.length){
        sql.append(",");
    }
}
sql.append(")");
System.out.println(sql.toString());

try (Connection con = DriverManager.getConnection(...)) {

    PreparedStatement stm = con.prepareStatement(sql.toString());
    for(int i=0; i < ids.length; i++){
        stm.setInt(i+1, ids[i]);
    }

    ResultSet rs = stm.executeQuery();
    while (rs.next()) {
        System.out.println(rs.getString("jedi_name"));
    }

} catch (SQLException e) {
    e.printStackTrace();
}

Output

select jedi_name from jedi where id in(?,?,?,?)
Luke, Obiwan, Yoda, Mace Windu

Is there any reason why you would consider that you need a thing like a batch-select statement?

My current theory is that for selects (with ResultSet's for instance) they kind of "auto buffer" the next few rows as you go, so they're "batched" for free for selects. FWIW — rogerdpack, Jan 21 '15 at 23:14
@rogerdpack Do you mean the [fetchSize](http://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#setFetchSize-int-) property? That probably works, although the documentation states that may be just a hint for the database. That is not all drivers necessarily implement it. — Edwin Dalorzo, Jan 21 '15 at 23:34
Hey that's it, thanks for the link! For followers, with Postgres appears you also need autocommit off (weird): http://stackoverflow.com/questions/1468036/java-jdbc-ignores-setfetchsize — rogerdpack, Jan 22 '15 at 00:16

score 0 · Answer 2 · answered Mar 24 '12 at 16:37

It is really does not matter what is your SQL statement (you can use as many nested joins as your DB can handle). Below is basic Java example (not DBCP). For DBCP example which is pretty similar you can check out their example.

Connection connect = DriverManager.getConnection(YOUR_CONNECTION_STRING);
// Statements allow to issue SQL queries to the database
Statement statement = connect.createStatement();

ResultSet resultSet = statement.executeQuery("select order_id, cost 
                                                from customer c 
                                                join order o using(id) 
                                                where c.id = ... 
                                            order by");

I want to do batch query which means getting multiple results for one DB query, sql statement like: select order_id, cost from customer c join order o using(id) where c.id = 123 order by;select order_id, cost from customer c join order o using(id) where c.id = 456 order by; — superche, Mar 24 '12 at 16:43
you can feed any sql statement that compiles as an argument to `executeQuery()` BTW: in your example 2 queries can be consolidated in one — aviad, Mar 24 '12 at 16:46

JDBC batch query for high performance

2 Answers2