JDBC - Statement, PreparedStatement, CallableStatement and caching

Question

I am wondering what are the differences and when to use Statement, PreparedStatement, and CallableStatement.

What is the best practice and typical scenario of using each of these?

Answer 1

Statement vs PreparedStatement

1. Performance can be better with PreparedStatement but is database dependent.

2. With PreparedStatement you avoid SQL injection. How does a PreparedStatement avoid or prevent SQL injection?

3. Better type check with preparedStatement by setInt, setString where as statement you just keep appending to the main SQL.

Similar Post:

Difference between Statement and PreparedStatement

CallableStatement - Java answer to access StoredProcedures across all databases.

Similar post

CallableStatement vs Statement

With PreparedStatement and Callable you already have caching, also caching is a big topic in its own, you wouldn't like to do all of that instead look at ehcache

You should almost always prefer PreparedStatement over Statement

If you have to operate over StoredProcedure you have just one option CallableStatement.

Answer 2

I'd recommend using PreparedStatement pretty much any time you pass parameters, whether or not you'll be re-using the statement. In practice I use PreparedStatement for everything except procedure calls and let the DB and JDBC driver decide what to cache and how. Procedure calls should use CallableStatement to handle the lack of consistent cross-database procedure call syntax.

On PostgreSQL, the JDBC driver caches prepared statements client-side until a certain threshold of re-use is reached. At that point a server-side PREPARE is issued and future executions use the server-side prepared statement and its cached plan. This can have some ... interesting ... and unexpected effects because of PostgreSQL's statistics-based query planner. If your table has certain value distributions (or bad statistics due to lack of ANALYZE, wrong random_page_cost or too-low stats threshold) the planner might choose a different and slower query plan when it has an unknown parameter to what it would've chosen if it'd known the actual value you were searching for. If you encounter a sudden and massive slowdown in queries after the 5th (by default) repetition of a particular statement you may be being bitten by this, and can work around it by turning off server-side PREPARE in PgJDBC. There's ongoing work to detect these problem cases in the server by checking whether a particular parameter has very different stats to the unknown-value case, but AFAIK it hasn't hit HEAD yet. See also this question. Search the pgsql-general mailing list and stackOverflow for more info.