JDBC Best Practices Tutorial
Hello, in this tutorial we will learn some Java Database Connectivity (JDBC) best practices that Java programmer should follow while writing JDBC code.
You can also check our Java Programming Database Tutorial in the following video:
JDBC API is used to connect and interact with the relational databases to perform CREATE, READ, UPDATE, 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 anyone should follow when using JDBC.
Table Of Contents
- 1. Introduction
- 2. JDBC Best Practices
- 2.1 Use Prepared Statement
- 2.2 Use Connection Pool
- 2.3 Disable Auto Commit Mode
- 2.4 Use JDBC Batch Statements
- 2.5 Accessing Result Set by Column Names
- 2.6 Use Bind variables instead of String concatenation
- 2.7 Always close Statement, PreparedStatement, CallableStatement, ResultSet & Connection Object
- 2.8 Statement Caching
- 2.9 Use correct getXXX() method
- 2.10 Use standard SQL statement
- 2.11 Choose suitable JDBC Driver
- 3. Conclusion
- 4. Download the Eclipse Project
1. JDBC Components
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. Developers can obtain a connection to a database only after the JDBC driver for that database is loaded and initialized in JVM memory.
- Statement: A statement is used to execute the CRUD operations.
- Result Set: After developers have executed a query using the JDBC API, the result of the query is returned 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
2. JDBC Best Practices
In this section, we will explore the strategies that can be adopted to improve JDBC performance.
2.1 Use Prepared Statement
It is very important JDBC best practice. Prepared Statement is used for executing a precompiled SQL statement. java.sql.PreparedStatement
is suitable for executing DML commands: SELECT, INSERT, UPDATE and DELETE. Prepared Statement is faster as compared to Statement because it is used for executing pre-compiled SQL statements. Hence, same SQL query can be executed repeatedly in Prepared Statement.
Here is an example of how to use PreparedStatement
in Java:
PreparedStmtExample.java
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | package com.jcg.jdbc.bestpractices; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class PreparedStmtExample { // JDBC Driver Name & Database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver" ; static final String JDBC_DB_URL = "jdbc:mysql://localhost:3306/tutorialDb" ; // JDBC Database Credentials static final String JDBC_USER = "root" ; static final String JDBC_PASS = "admin@123" ; public static void main(String[] args) { try { Class.forName(JDBC_DRIVER); Connection connObj = DriverManager.getConnection(JDBC_DB_URL, JDBC_USER, JDBC_PASS); PreparedStatement prepStatement = connObj.prepareStatement( "SELECT DISTINCT loan_type FROM bank_loans WHERE bank_name=?" ); prepStatement.setString( 1 , "Citibank" ); ResultSet resObj = prepStatement.executeQuery(); while (resObj.next()) { System.out.println( "Loan Type?= " + resObj.getString( "loan_type" )); } } catch (Exception sqlException) { sqlException.printStackTrace(); } } } |
2.2 Use Connection Pool
It is a very common JDBC best practice to use Connection pooling in Java. Connection pooling is the process where we maintain a cache of database connections. Database connections maintained in a cache can be reused whenever a request comes to connect with the database. So, Connection pooling reduces database hits and improves the application performance significantly.
Application servers allow configuration of JDBC connection pools where developers can define the minimum and a maximum number of database connections that could be created within the application server. The application server manages the creation and deletion of database connections. JNDI Lookup is used in the application to obtain the database connections from the pool.
There are a few choices when using the JDBC connection pool:
- Developers can depend on application server if it supports this feature, generally, all the application servers support connection pools. Application server creates the connection pool on behalf of developers when it starts. Developers need to give properties like min, max and incremental sizes to the application server.
- Developers can use JDBC 2.0 interfaces, for e.g.
ConnectionPoolDataSource
andPooledConnection
if the driver implements these interfaces. - Developers can even create their own connection pool if they are not using any application server or JDBC 2.0 compatible driver.
By using any of these options, one can increase the JDBC performance significantly.
2.3 Disable Auto Commit Mode
This is one of those JDBC best practices which provide a substantial performance gain. It’s recommended to run SQL query with auto commit mode disable. The reason behind this JDBC best practice is that with auto commit mode disabled we can group SQL Statement in one transaction, while in the case of auto commit mode enabled every SQL statement runs on its own transaction and committed as soon as it finishes. So, always execute SQL queries with auto commit mode disabled.
- Developers can set auto commit mode of connection to false using
connObj.setAutoCommit(false)
and then accordingly useconnObj.commit()
orconnObj.rollback()
. - If any transaction fails in between, then rollback the transaction by calling
connObj.rollback()
, and commit the transaction by usingconnObj.commit()
only if it went successfully.
For e.g.:
Let’s say we have to update salary of two employees, and salary of both employees must be updated simultaneously in a database. And let’s say the salary of the first employee is updated successfully. But, if anything goes wrong in updating salary of the second employee then any modifications done to first employee’s salary will be rolled back.
The following example illustrates the use of a commit and rollback object:
AutoCommitExample.java
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | package com.jcg.jdbc.bestpractices; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class AutoCommitExample { // JDBC Driver Name & Database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver" ; static final String JDBC_DB_URL = "jdbc:mysql://localhost:3306/tutorialDb" ; // JDBC Database Credentials static final String JDBC_USER = "root" ; static final String JDBC_PASS = "admin@123" ; public static void main(String[] args) { try { Class.forName(JDBC_DRIVER); Connection connObj = DriverManager.getConnection(JDBC_DB_URL, JDBC_USER, JDBC_PASS); // Assuming A Valid Connection Object connObj.setAutoCommit( false ); Statement stmtObj = connObj.createStatement(); String correctQuery = "INSERT INTO employee VALUES (001, 20, 'Java', 'Geek')" ; stmtObj.executeUpdate(correctQuery); // Submitting A Malformed SQL Statement That Breaks String incorrectQuery = "INSERTED IN employee VALUES (002, 22, 'Harry', 'Potter')" ; stmtObj.executeUpdate(incorrectQuery); // If There Is No Error. connObj.commit(); // If There Is Error connObj.rollback(); } catch (Exception sqlException) { sqlException.printStackTrace(); } } } |
2.4 Use JDBC Batch Statements
This is another JDBC best practice which is very popular among developers. JDBC API provides addBatch()
method to add SQL queries into a batch and executeBatch()
to send batch queries for execution. The reason behind this JDBC best practices is that JDBC batch update potentially reduces the number of database roundtrip which again results in significant performance gain. So always use JDBC batch update for insertion and update queries.
In simple words, Batch statement sends multiple requests from Java to the database in just one call. Without Batch statements multiple requests will be sent in multiple (one by one) calls to the database.
About addBatch()
method:
PreparedStatement
extendsStatement
and inherits all methods fromStatement
and additionally addsaddBatch()
method.addBatch()
method adds a set of parameters to thePreparedStatement
object’s batch of commands.
The following example illustrates the use of batch statements:
BatchStatementsExample.java
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | package com.jcg.jdbc.bestpractices; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class BatchStatementsExample { // JDBC Driver Name & Database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver" ; static final String JDBC_DB_URL = "jdbc:mysql://localhost:3306/tutorialDb" ; // JDBC Database Credentials static final String JDBC_USER = "root" ; static final String JDBC_PASS = "admin@123" ; public static void main(String[] args) { try { Class.forName(JDBC_DRIVER); Connection connObj = DriverManager.getConnection(JDBC_DB_URL, JDBC_USER, JDBC_PASS); connObj.setAutoCommit( false ); Statement stmtObj = connObj.createStatement(); stmtObj.addBatch( "INSERT INTO student VALUES(101, 'JavaGeek', 20)" ); stmtObj.addBatch( "INSERT INTO student VALUES(102, 'Lucifer', 19)" ); stmtObj.addBatch( "UPDATE employee SET age = 05 WHERE id = 001" ); // Execute Batch int [] recordsAffected = stmtObj.executeBatch(); connObj.commit(); } catch (Exception sqlException) { sqlException.printStackTrace(); } } } |
2.5 Accessing Result Set by Column Names
JDBC API allows accessing the returned data by SELECT query using ResultSet
, which can further be accessed using either column name or the column index. This JDBC best practice suggests using column name over column index in order to avoid InvalidColumnIndexException
which comes if an index of the column is incorrect (most common of them is 0) since ResultSet
column index starts from 1 and 0 is invalid.
Some Java programmers may argue that accessing a database column using index is faster than a name, which is true. But if we look in terms of maintenance, robustness, and readability, I prefer accessing the database column using the name in ResultSet
iterator.
The following example illustrates the use:
InvalidColumnIndexExample.java
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | package com.jcg.jdbc.bestpractices; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class InvalidColumnIndexExample { // JDBC Driver Name & Database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver" ; static final String JDBC_DB_URL = "jdbc:mysql://localhost:3306/tutorialDb" ; // JDBC Database Credentials static final String JDBC_USER = "root" ; static final String JDBC_PASS = "admin@123" ; public static void main(String[] args) { try { Class.forName(JDBC_DRIVER); Connection connObj = DriverManager.getConnection(JDBC_DB_URL, JDBC_USER, JDBC_PASS); PreparedStatement prepStmtObj = connObj.prepareStatement( "SELECT DISTINCT item FROM order where order_id=?" ); prepStmtObj.setString( 0 , "101" ); // This Will Throw "java.sql.SQLException: Invalid Column Index" Because "0" Is Not Valid Colum Index ResultSet resultSetObj = prepStmtObj.executeQuery(); while (resultSetObj.next()) { System.out.println( "Item: " + resultSetObj.getString( 2 )); // This Will Throw "java.sql.SQLException: Invalid column index" Because ResultSet Has Only One Column } } catch (Exception sqlException) { sqlException.printStackTrace(); } } } |
2.6 Use Bind variables instead of String Concatenation
In JDBC best practices, we have suggested using PreparedStatement
in Java because of better performance. But performance can only be improved if developer use bind variables
denoted by ?
or place holders
which allow the database to run the same query with a different parameter. This JDBC best practices results in a better performance and provides protection against SQL injection as a text for all the parameter values is escaped.
Sample Code 1
1 2 | prepStmt = con.prepareStatement( "select * from EMPLOYEE where ID=? " ); prepStmt.setInt( 1 , 8 ); |
While Statement enforces SQL injection because we end up using query formed using concatenated SQL strings.
Sample Code 2
1 2 3 | String query = "select * from EMPLOYEE where id = " ; int i = 2 ; stmt.executeQuery(query + String.valueOf(i)); |
Here comes one very important question, are Prepared Statement vulnerable to SQL injections? The answer is yes when we use concatenated SQL strings rather than using input as a parameter for the prepared statement.
2.7 Always close Statement, PreparedStatement, CallableStatement, ResultSet & Connection Object
It’s a common Java coding practice to close any resource in finally
block as soon as we are done using the resource. JDBC connection and classes are a costly resource and should be closed in finally
block to ensure the release of connection even in the case of any SQLException
. This even helps to avoid ora-01000 - java.sql.SQLException
errors in Java.
The following example illustrates the use:
CloseJdbcObjects.java
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | package com.jcg.jdbc.bestpractices; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class CloseJdbcObjects { public static void main(String[] args) throws ClassNotFoundException, SQLException { Connection connObj = null ; PreparedStatement prepStmtObj = null ; ResultSet resultSetObj = null ; try { // Business Logic! } finally { try { // Close Result Set Object if (resultSetObj!= null ) { resultSetObj.close(); } // Close Prepared Statement Object if (prepStmtObj!= null ) { prepStmtObj.close(); } // Close Connection Object if (connObj!= null ) { connObj.close(); } } catch (SQLException sqlException) { sqlException.printStackTrace(); } } } } |
From Java 7 onwards, developers can use the Automatic Resource Management (ARM) block to close resources automatically.
2.8 Statement Caching
Statement caching improves performance by caching executable statements that are used repeatedly, such as in a loop or in a method that is called repeatedly. Starting from JDBC 3.0, JDBC API defines a statement-caching interface. Setting the MaxPooledStatements
connection option enables the statement pooling. Enabling statement pooling allows the JDBC driver to re-use Prepared Statement objects and are returned to the pool instead of being freed and the next Prepared Statement with the same SQL statement is retrieved from the pool rather than being instantiated and prepared against the server.
Statement caching can:
- Prevent the overhead of repeated cursor creation.
- Prevent repeated statement parsing and creation.
- Allows the JDBC driver to use the Prepared Statement objects.
The following code snippet illustrates how Statement pooling can be enabled:
Sample Code 1
1 2 3 4 5 6 | Properties propObj = new Properties(); propObj.setProperty( "user" , "root" ); propObj.setProperty( "password" , "admin@123" ); propObj.setProperty( "MaxPooledStatements" , "250" ); Connection connObj = DriverManager.getConnection( "jdbc:mysql://localhost:3306/tutorialDb" , propObj); |
Read more about statement pooling here: JDBC Statement Pooling
2.9 Use correct getXXX() method
ResultSet
interface provides a lot of getXXX()
methods to get and convert database data types to Java data types and is flexible in converting non-feasible data types. For e.g.:
getString(String columnName)
returns Java String object.- Column Name is recommended to be a
VARCHAR
orCHAR
type of database but it can also be aNUMERIC
,DATE
etc.
If you give not recommended parameters, it needs to cast it to proper Java data type that is expensive. For e.g., Consider that we want to select a product’s id from a huge database which returns millions of records from search functionality. It needs to convert all these records that are very expensive.
So always use proper getXXX()
methods according to JDBC recommendations.
2.10 Use standard SQL Statements
This is another JDBC best practice in Java which ensures writing portable code. Since most of JDBC code is filled up with SQL query it’s easy to start using database specific feature which may present in MySQL but not in Oracle etc. By using ANSI SQL or by not using DB specific SQL we ensure minimal change in the DAO layer in case developers switch to another database.
Also, it is a good JDBC practice to write as much business logic as much as possible in Stored Procedure or Functions as compared to writing it down in Java class. Because this approach reduces the database hits and improves application performance significantly.
2.11 Choose suitable JDBC Driver
There are 4 types of JDBC drivers in Java and it can directly affect the performance of DAO layer. It is recommended to always use latest JDBC drivers if available and prefer type 4 native JDBC Drivers.
That’s all for this post. Happy Learning!!
3. Conclusion
The main goal of this article is to discuss important and best JDBC (Java Database Connectivity) practices in Java with examples.
4. Download the Eclipse Project
This was an example of JDBC Best Practices.
You can download the full source code of this example here: JDBC Best Practices Tutorial
There are lots of people out here who really benefit form your great works. Very much appreciate.