JDBC prepared statement vs hibernate session transaction readonly operations

Question

I'm working on a project where there are a lot of readonly operations (like in select) written with the help of prepared statements but other operations are with hibernate transactions. I know the reason of a transaction is because is somesort of failproof if you make multiple modifications to the database. So if one fails, there is a transaction rollback. But I don't know what is the best to use for readonly operations: when should i use a prepared statement over a hibernate transactions and vice-versa ?

Answer 1

Prepared statements and transactions have different scopes.

You should use prepared statements to prevent SQL Injection and make your code more readable, secure and reusable.

You use transactions to controll transactionality (commit and rollbacks), set timeouts, isolation levels and lock modes.

Of course you should use both when necessary.

Maybe you find useful this links:

Transactions for read-only DB access?

Advantages of using prepared statements over normal mysqli statements?

Answer 2

I'm not exactly sure I agree with using raw JDBC PreparedStatement for read-only SELECT operations and delegate to Hibernate for everything else. Hibernate is capable of supporting both native SQL queries along with read-only operations.

One of the biggest reasons to use Hibernate over raw JDBC is that you can take part in using the out-of-the-box first-level cache support. This is great even for read-only operations where you may issue multiple select-clauses which may need to load the same relation or entity.

Additionally, you can also then take part in introducing a second-level cache provider. Ehcache or Infinispan are great cache providers which allow Hibernate to store the query-cache and entity caches locally, allowing for faster result-set processing.

Beyond that, it also allows all code which generates queries to be a bit more consistent. You always interact with Hibernate and choose whether to use raw native SQL or not. You may even be able to move away from native SQL and just use HQL/JPQL too allowing the application to become less database dependent and more agnostic.