Java efficent strategy to access a DB

Question

A simple question: what is the more efficient way to access a db in Java/JDBC? I'm a web developper and I'd like to write some reusable and scalable code.
What is interesting for me is the use of tools like the ResultSupport: is it too expansive in terms of resource usage?
What can you suggest?

Answer 1

Not just JDBC specific, just general SQL stuff

1. If you have to rerun a query multiple times, use PreparedStatement. Use stored procedure if it is available. This is obviously not portable so YMMV.

2. Always close your ResultSet or Statement if you are not using it. Closing a Statement will auto close all ResultSet associated with the Statement. Still it is a good habit to close the ResultSet.

3. Try to restrict what can be queried eg. select * from orders where order_date between XXX and yyy. In MySQL the query may either be a full table scan or 'range' depending on how much data is returned. So deciding a how 'flexible' you want your queries to be

4. If you are using MySQL, use explain to optimize your query. If you are using JPA, then you don't get to see the SQL generated. (This is not strictly JDBC) You might want to enable logging on the ORM manager to display the SQL statement used. Then use explain to optimize that. You may want to use @NamedNativeQuery if the ORM generates a really convoluted query

5. If your JDBC driver supports batch update then use that. Batch updates is supported in PreparedStatement and ResultSet.

6. You can also control the commit. Good idea to turn it off if you are performing lots of updates. The call commit() yourself.