JDBC - Statement, PreparedStatement, CallableStatement and Caching

Question

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

What is the best practice and typical use case for each of them?

Answer 1

Operation vs PreparedStatement

• Performance may be better with PreparedStatement, but depends on the database.

• With PreparedStatement, you avoid SQL injection. How does PreparedStatement prevent or prevent SQL injection?

• It is better to check the type with the preparedStatement by setInt, setString, where as an instruction you just continue to add to the main SQL.

Related posts:

Difference Between Statement and PreparedStatement

CallableStatement is a Java response for accessing StoredProcedures in all databases.

Related posts

CallableStatement vs Statement

With PreparedStatement and Callable, you already have caching, also caching is a big topic in your own, you would not want to do all this, instead look at ehcache

You should almost always prefer PreparedStatement over Statement.

If you need to work on StoredProcedure, you have only one CallableStatement option.

Answer 2

I would recommend using PreparedStatement almost during parameter passing, regardless of whether you will reuse 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. Procedural calls should use CallableStatement to handle the lack of a consistent syntax for calling a cross-domain database procedure.

In PostgreSQL, the JDBC driver caches prepared statements on the client side until a certain reuse threshold is reached. At this moment, the PREPARE server is issued on the server side, and in future versions, the operator prepared on the server side and its cached plan are used. This may have some ... interesting ... and unexpected effects due to the query planner based on PostgreSQL statistics. If your table has certain distribution of values ​​(or bad statistics due to lack of ANALYZE, incorrect random_page_cost value or too low value), the scheduler can choose a different and slower query plan if it has an unknown parameter that it would choose if he knew the real value you were looking for. If you are faced with a sudden and massive slowdown in queries after the 5th (default) repetition of a particular statement, you can be bitten by this and you can bypass it by turning off server-side PREPARE in PgJDBC. Work continues on detecting these problematic situations on the server, checking whether a particular parameter has very different statistics for the case with an unknown value, but AFAIK it has not yet hit HEAD. See also this question . Find more information on the pgsql-general and stackOverflow mailing lists.