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

I think the easiest and most common way is to use Spring and their JDBCTemplate.

The best approach likely depends on the stack you are using to create your web app. If you're starting afresh then Spring is a good way to go.

Answer 2

It is very important to use always a connection pool DataSource such as c3p0.

Answer 3

There is a project that maps java objects to mysql databases. Azirt.

Answer 4

Use connection pooling (either the one from your container or a standalone connection pool like c3p0 or DBCP) and something like DBUtils or Spring's JdbcTemplate.

Answer 5

Best answer to this simple question is: "it depends".

There are many API's you can use for database access. Nearly all of them will use the JDBC API as their means to communicate with the database. So in theory, nothing can beat raw low level JDBC (just as machine code is in theory always faster than higherlevel programming languages).

But as you also like to write reusable code, I suggest you look into JPA. It's the Java standard for object persistence to relational databases. It’s performance is quite good and it’s very portable.

As JPA is just a specification, you can choose you’re own implementation: Hibernate, OpenJPA or any compliant Java EE server.

Answer 6

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.