JDBC—Best Practices

Most Web applications include some sort of a database access—thus making JDBC one of the most crucial components in an application. Therefore, it's important to take several factors into account while working with JDBC to improve performance of the overall application. In this section, we discuss some of the aspects that you must remember while programming with JDBC.

Use Smart Queries

Database queries can often be the most time-consuming aspect of your application. Make sure that you optimize your queries as much as possible. Never query more data than you require. This applies for columns, too. It's preferable to SELECT the fields that you need rather than performing a SELECT *. Avoid using database clauses such as count(*) and sort by because they might be resource intensive.

Combine Database Updates into a Transaction

Every database operation is treated as a transaction unless you explicitly specify the start and end of the transaction. Avoid the use of auto-commit feature while performing multiple database updates because it consumes time to commit each and every operation. Instead, try to combine database updates, inserts, and deletes into logical user transactions.

Don't Use Transactions That Span User Input

Transactions normally signify a single unit of work. Therefore, it's a very good practice to collect all user inputs prior to the beginning of the transaction. If transactions encompass user input, there's always the possibility that the user won't provide the input for a long time, thereby keeping the relevant locks in the database for that duration. This will obviously hamper other transactions, which might not start until these locks are released.

Design Your Application for Parallel Data Processing

Databases are much more than dumb file storage mechanisms. Most databases are tuned to perform better when several parallel tasks are running simultaneously, rather than a single process. Utilize this feature by "parallelizing" the data processing in your application.

Select an Optimal Isolation Level

The choice of isolation level will depend on the type of your application and the accuracy of the data it can work with in case of concurrent data access. An application is guaranteed the latest data in the database by using the isolation level TRANSACTION_SERIALIZABLE, but that's also the slowest in terms of performance. In most cases, TRANSACTION_READ_COMMITTED will work just fine, although this won't prevent nonrepeatable and phantom reads.

Use a Connection Pool

Always try to use a connection pool rather than attempting to create connections every time you need data access. Connection creation can be the most expensive operation in your application. Attempt to create connections at initialization time rather than at usage time. Using connection pools also uses fewer resources, and can make economic sense because it can make several application threads work with a limited number of connections.

Use the Appropriate Statement Object

While performing multiple database operations, such as inserting many rows at once, you should prefer the use of a PreparedStatement over a Statement object because the former supports precompiled SQL statements. You can simply change the input data every time you want to use this SQL and re-execute it. If your task involves the use of multiple different database operations, a PreparedStatement might not be very helpful. In such cases, consider the use of a stored procedure and localize the operations within the database. You can then invoke the stored procedure by using a CallableStatement object. Databases are often optimized to work very well with stored procedures.

Use Batch Processing

You might often want to perform a bunch of operations to the database at one time. In such cases, choose to use batch processing. The advantage of batch processing is that all the operations in the batch are sent to the database at once, thus making the performance better.

Use Data Caching

If you know some data won't change very often, and if you also know that you use this data quite often in your application, it's a very good idea to cache that data in memory. This is often very useful for dealing with lookup tables. Caching large amounts of data can obviously prove to be resource intensive. So, a decision of what to cache and what not to cache must be based on the type of data and the frequency of its use.

Use In-Place Updates Where Possible

Inserting and deleting records in a database can be very expensive, especially if several table indexes have to be updated because of this operation. Therefore, you must try to minimize such operations. Choose to use updates, rather than deletes and inserts.

Free Up JDBC Resources

JDBC objects such as Connection, Statement, and ResultSet utilize resources, which must be released appropriately. You can release these resources by simply invoking the close method in these objects. It's usually a good idea to invoke the close method in the finally block of your try-catch statement block so that you can ensure that the resources are freed whether or not an exception is thrown.

Release Connections When Done

Database connections that are obtained from a connection pool should be released as and when you are done with them. This is very important because you'll normally be working with a limited number of connections in the connection pool. By holding on to a connection, your application will not scale.