JDBC Best Practices
The JDBC API in Java allows you to connect to relational databases to perform Create, Read, Update, and Delete (commonly known as CRUD) operations. It is a database-independent API that you can use to execute your queries against a database. In this article, I will present the best practices that we should follow when using JDBC.
The Core JDBC components are comprised of the following:
- JDBC Driver: This is a collection of classes that enables you to connect to a database and perform CRUD operations against it.
- Connection: This class is used to connect to a database using the JDBC API. You can obtain a Connection to a database only after the JDBC driver for that database is loaded and initialized in memory.
- Statement: A Statement is used to execute your CRUD operations.
- ResultSet: After you execute a query using the JDBC API, the result of execution of the query is returned or made available to you in the form of a ResultSet.
The following is a list of the possible use cases in JDBC:
- Query database
- Query database metadata
- Update database
- Perform database transactions
JDBC Performance Tuning
In this section, we will explore the strategies that can be adopted to improve JDBC performance.
Database connections are expensive; there is an overhead in establishing a network connection and then opening a connection to a database. Connection Pooling can be used to improve JDBC connectivity; the pool manager stores a pool of ready to be used connections. These connection instances in the connection pool are stored as soon as a connection is closed. When the application requests for a new connection, the request is served from the connection pool, thus eliminating the need to creating a new connection to the database and the overhead involved.
Handle Data Efficiently
You should cache data appropriately to avoid unnecessary, expensive, and redundant hits to the database. Data that is relatively static over a period of time should be cached. You should cache data belonging to read-only and read-mostly databases tables. On a different note, you should always retrieve only the amount of data that is needed; you should return data iteratively to improve performance. For this, you can either use stored procedures or cache the search data at the server and return data iteratively.
You can turn on statement pooling; you can do this by setting the MaxPooledStatements connection option. If statement pooling is enabled, the JDBC driver would re-use the Prepared Statement objects. When statement caching is turned on, the overhead of creating and parsing statements is eliminated. Moreover, when statement pooling is enabled, the JDBC driver reuses the Prepared Statement objects. The following code snippet illustrates how Statement pooling can be enabled.
Properties p = new Properties(); p.setProperty("user", "admin"); p.setProperty("password", "joydip1@3"); p.setProperty("MaxPooledStatements", "250");
Reduce Network Traffic
There are many other points that you can consider to reduce the network traffic when working with JDBC. You should use addBatch() instead of a Prepared Statement when inserting data in a batch. This ensures that multiple insert requests are sent in one single network packet; hence, network bandwidth is saved. You should ensure in the database that the application is connected to for performing CRUD operations using the JDBC API and is optimized to use the maximum packet size to reduce the number of packets that are transmitted between the driver and the server.
Handle Transactions Efficiently
A transaction is a unit of work (a collection of statements in a batch) that are guaranteed to be executed in totality if any one of them is executed. If any one of the statements in a transaction fails, none of the statements in the transaction unit are executed. If any one or more statements in a transaction unit has executed preceding a statement in the transaction unit that has failed, then those statement(s) are rolled back.
Transactions should be executed in a batch. You should choose the correct isolation level based on your requirement. Note that TRANSACTION_READ_UNCOMMITED is a good choice for applications that have concurrent transactions. On the contrary, TRANSACTION_NONE is a good choice for non-concurrent transaction based applications.
The Java Database Connectivity (JDBC) API is a standard API to connect to a relational database and perform CRUD operations against it. To improve the performance of your application that uses the JDBC API, you should profile your application to find the bottlenecks and then prepare a strategy that should be followed to mitigate the performance issues. In this article, I presented the most important points that you should consider when using the JDBC API to maximize the application performance.