Best Practices

Some of the best practices are already built-in the JDBC API itself, such as connection pooling, PreparedStatement, and batch updates. Still some of the widely known practices help when developing large and enterprise J2EE applications.

Some factors affecting the performance of your application with respect to database are independent of JDBC or any other access method. Examples of such factors are writing optimized queries, and tuning the database using caching of often-used resources. Both database tuning and query optimization are beyond the scope of this book. A simple example such as using the query "SELECT * from Student” rather than "SELECT id from Student" when you need only the student IDs, increases network traffic and the allocated resources to handle such ResultSet. Performing an UPDATE row operation rather than a DELETE on the row followed by an INSERT of new row, is another type of good practice.

With respect to JDBC, performance gain can be established by obtaining a Context to the DataSource in the initialization code. Use PreparedStatement to performing the same query repeatedly. CallableStatement also gives better performance than PreparedStatement and Statement when there is a requirement for single request to process multiple complex statements. It parses and stores the stored procedures in the database and optimizes the work at the database itself to improve its performance. Use stored procedure and triggers for large operations, but use them with care. Stored procedures are handled differently by database products. This has a negative affect on application portability and makes you rely on vendor-specific stored procedures.

Use batch updates to perform batches of SQL statements. Release all resources appropriately; that is, in the reverse order of how they were allocated. If possible, avoid transaction auto-commit, and control your transaction behavior.

Tuning your connection pool is a prime factor in the overall system performance. Both minimum and maximum size should be selected based on the expected user load. Another factor is that the Connection must be returned to the pool as soon as you are done working with it. Delaying the release of the connection has negative impact on an application's scalability. Disabling auto-commit to control batch operations through a single transaction also helps improve application performance.