Guide to JDBC Best Practices

1. Overview

In this guide, I would like to explain JDBC Best Practices. We can follow these best practices in the day to day project work.

2. JDBC Best Practices

As I know, we basically prefer ORM framework to develop DAO layer like Hibernate ORM Framework but If you still using JDBC in your projects or if you are learning about JDBC then this post will guide you the best practices of JDBC.

2.1. Check ResultSet

Always check the return values of navigation methods (next, previous, first, last) of a ResultSet. If the value return is ‘false’, it should be handled properly. Example:
Statement stat = conn.createStatement();
ResultSet rst = stat.executeQuery("SELECT name FROM person");
rst.next();     // what if it returns false? bad form
String firstName = rst.getString(1);

Statement stat = conn.createStatement();
ResultSet rst = stat.executeQuery("SELECT name FROM person");
if (rst.next()) {    // result is properly examined and used
    String firstName = rst.getString(1);
    } else  {
        // handle missing data
}

2.2. Use Prepared Statement

Prepare Statement gives more benefits over using Statement interface like
  • 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 which improves the performance.
  • PreparedStatement allows you to write a dynamic and parametric query.
  • PreparedStatement prevents SQL Injection attacks in Java.
Here is an example of how to use PreparedStatement in Java:
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.3. Use ConnectionPool

ConnectionPool as JDBC best practice has already gained recognition and it even becomes standard nowadays. 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 and PooledConnection 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.4. 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 the auto-commit mode of connection to false using connObj.setAutoCommit(false)and then accordingly use connObj.commit() or connObj.rollback(). If any transaction fails in between, then rollback the transaction by calling connObj.rollbak(), and commit the transaction by using connObj.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.
Sample Source Code:
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.5. 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 extends Statement and inherits all methods from Statement and additionally adds addBatch() method. addBatch() method adds a set of parameters to the PreparedStatement object’s batch of commands. 
The following example illustrates the use of batch statements:
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.6. Access ResultSet Using Column Name Instead of Column Index

JDBC API allows accessing data returned by a SELECT query using ResultSet, which can further be accessed using either column name or column index
This JDBC best practice suggests using column name over column index in order to avoid InvalidColumnIndexException which comes if the index of the column is incorrect, most common of them is 0, since ResultSet column Index starts from 1, zero is invalid.
Example:
ResultSet resultSetObj = prepStmtObj.executeQuery();
while(resultSetObj.next()) {
    // Avoid
    String result resultSetObj.getString(2); // passed index
     // Better
    String result resultSetObj.getString("columnName"); // passed columnName
} 

2.7. Use Placeholders for Parameterization

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 placeholders 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. 
Example:
prepStmt = con.prepareStatement("select * from EMPLOYEE where ID=? ");
prepStmt.setInt(1, 8);

2.8. Always Close Statement, Preparedstatement and Connection

Nothing new on this JDBC Best practice. Its common Java coding practice to close any resource in finally block as soon as you are done with that.
JDBC Connection and other JDBC classes are costly resource and should be closed in finally block to ensure release of connection even in case of any SQLException. 
From Java 7 onwards you can use Automatic Resource Management (ARM) Block to close resources automatically. 
I suggest using Java 7 try-with-resources block for automatic resource closing.
Example:
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();
   }
  }
 }
}

2.9. Choose Suitable JDBC Driver for Your Application

There are 4 types of JDBC driver in Java and it can directly affect the performance of DAO layer. always use latest JDBC Driver if available and prefer type 4 native JDBC Driver.

2.10. Use Correct getxxx() Method

This is the last JDBC best practice in this article which suggests using correct getter while getting data from ResultSet to avoid data conversion even though JDBC allows to get any data type using getString() or getObject().

3. Conclusion

In this post, we have seen the possible JDBC best practices. There can be other JDBC best practices all well so check out the reference section of this post.We will continue posting best practices related to Java Technologies.
Happy Coding !!!!.

4. References :

5. Related Best Practices Posts

Comments