How can Prepared Statements be used with Apache DBUtils?

Question

It seems that most of the methods for org.apache.commons.dbutils.* expect string arguments. It's surprising that there isn't a method that accepts PreparedStatements.

Look at the [source code of QueryRunner](http://svn.apache.org/viewvc/commons/proper/dbutils/trunk/src/main/java/org/apache/commons/dbutils/QueryRunner.java?view=markup) and you will notice they do (look at the main implementations of `query`, `update` and `insert`). Or would you like to feed these methods existing instances of `PreparedStatement` ? — fvu, Apr 10 '13 at 00:54
Yes, I did notice that. It's a little much for me too absorb right now, so I'm working on that. I was using Prepared Statements before, but not with these QueryRunner methods. That's what I would like to do , so that when I process my form input, it doesn't crash with apostrophes. — CodeFinity, Apr 10 '13 at 12:55

score 5 · Answer 1 · answered Jan 19 '14 at 04:32

5

Prepared Statements is used inside DbUtils BUT, the point of prepared statement is not to prepare a statement each time you do an update, and reuse it changing only the params. Suppose you have to insert 1000 records, you'd like to reuse the same prepared statement changing only the params. To do so, use QueryRunner.batch instead of QueryRunner.update.

answered Jan 19 '14 at 04:32

chairam

335
5
12

2

However, if a million records are inserted, I highly doubt that dbutils would be the solution since QueryRunner.batch initializes prepared statement. I recommend writing a custom implementation instead of resorting to dbutils. I am not sure whether there are any other tools that caches the prepared statements. – dmachop Jun 28 '16 at 16:13

score 2 · Accepted Answer · answered Apr 10 '13 at 13:11

From the examples page

// Execute the query and get the results back from the handler
Object[] result = run.query(
    "SELECT * FROM Person WHERE name=?", h, "John Doe");

which indicates that there must be a PreparedStatement being used. And in the source for the query method we see

private <T> T query(Connection conn, boolean closeConn, String sql, 
                     ResultSetHandler<T> rsh, Object... params)
...
PreparedStatement stmt = null;
ResultSet rs = null;
T result = null;

try {
    stmt = this.prepareStatement(conn, sql);
    this.fillStatement(stmt, params);
    rs = this.wrap(stmt.executeQuery());
    result = rsh.handle(rs);
} catch (SQLException e) {
...

Conclusion? PreparedStatements are being used, no need to be worried at all.

How can Prepared Statements be used with Apache DBUtils?

2 Answers2