JDBC Best Practices


In this guide, I would like to explain JDBC Best Practices. We can follow these best practices in the day to day project work.
Check out JDBC complete tutorial at https://www.javaguides.net/p/jdbc-tutorial.html.

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.

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. Use Prepared Statement

PreparedStatement 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, the 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();
      }
  }
}
Check out below article to know more about PreparedStatement and Statement interfaces:
  • JDBC Statement Interface - In this article, we will learn commonly used methods of Statement interface with examples.

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

4. Disable Auto-Commit Mode

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.
For example: In JDBC API, the Connection interface  provides the setAutoCommit() , commit() and rollback() methods to perform transaction management.
The below diagram shows all the JDBC Connection interface transaction management methods:
Learn how to use JDBC transactions with examples at JDBC Transactions with Example.

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.

Here is a complete JDBC PreparedStatement Batch Insert Example:
package com.javaguides.jdbc.batch;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;

/**
 * Insert Batch operation using PreparedStatement Interface
 * @author Ramesh Fadatare
 *
 */
public class BatchInsertExample {

    public static void main(String[] args) {
        parameterizedBatchUpdate();
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }

    private static void parameterizedBatchUpdate() {

        String INSERT_USERS_SQL = "INSERT INTO users" + "  (id, name, email, country, password) VALUES " +
            " (?, ?, ?, ?, ?);";

        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
            connection.setAutoCommit(false);

            preparedStatement.setInt(1, 20);
            preparedStatement.setString(2, "a");
            preparedStatement.setString(3, "a@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 21);
            preparedStatement.setString(2, "b");
            preparedStatement.setString(3, "b@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 22);
            preparedStatement.setString(2, "c");
            preparedStatement.setString(3, "c@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            preparedStatement.setInt(1, 23);
            preparedStatement.setString(2, "d");
            preparedStatement.setString(3, "d@gmail.com");
            preparedStatement.setString(4, "India");
            preparedStatement.setString(5, "secret");
            preparedStatement.addBatch();

            int[] updateCounts = preparedStatement.executeBatch();
            System.out.println(Arrays.toString(updateCounts));
            connection.commit();
            connection.setAutoCommit(true);
        } catch (BatchUpdateException batchUpdateException) {
            printBatchUpdateException(batchUpdateException);
        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    public static void printBatchUpdateException(BatchUpdateException b) {

        System.err.println("----BatchUpdateException----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();

        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
    }
}
Output:
[1, 1, 1, 1]
Related JDBC 4.2 API - Batch Processing Posts:

         Example to update records in a batch process using Statement and PreparedStatement interfaces.
         Example to insert records in a batch process using Statement and PreparedStatement interfaces.
          Example to insert records in a batch process via Statement interface.
         Example to update records in a batch process via Statement interface.
         Example to insert records in a batch process via PreparedStatement interface.
          Example to update records in a batch process via PreparedStatement interface.

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
} 

7. Use Placeholders for Parameterization

In JDBC best practices, I would like to suggest using PreparedStatement in Java because of better performance. But performance can only be improved if the developer use bind variables denoted by ? or placeholders which allow the database to run the same query with a different parameter.

Below example demonstrates the use of placeholders with PreparedStatement:
prepStmt = con.prepareStatement("select * from EMPLOYEE where ID=? ");
prepStmt.setInt(1, 8);

8. Always Close Statement, PreparedStatement, and Connection

Its common Java coding practice to close any resource in finally block or Java 7 try-with-resources as soon as you are done with that.

JDBC Connection and other JDBC classes are a costly resource and should be closed in finally block or Java 7 try-with-resources to ensure the release of connection even in case of any SQLException.

Let's demonstrate closing resources using Java 7 try-with-resources with an example:
        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_USERS_SQL)) {
            preparedStatement.setString(1, "Ram");
            preparedStatement.setInt(2, 1);

            // Step 3: Execute the query or update query
            preparedStatement.executeUpdate();
        } catch (SQLException e) {

            // print SQL exception information
            printSQLException(e);
        }

        // Step 4: try-with-resource statement will auto close the connection.
    }

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 the DAO layer. always use latest JDBC Driver if available and prefer type 4 native JDBC Driver.

Read more about JDBC Drivers at Java JDBC API Overview.

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().
I hope these few JDBC best practices useful. Happy Coding !!!!.
Check out JDBC complete tutorial at https://www.javaguides.net/p/jdbc-tutorial.html.

References

Related Best Practices Posts

Comments