How to Achieve Greatness in JDBC Performance

Hey! tea lovers. Today, we will talk about the JDBC, the code which connects Java code to the realm of the database, and the best practices of JDBC to achieve greatness in the performance of database operations. These are not the huge changes or codes instead, they are little tricks to your way of writing JDBC code or tweaks to existing code.

We will talk about the best practices as we talked in “What’s In A Name: Java Naming Conventions” but this time about it is about the JDBC. These are the small tweaks you can do to your code that, not only makes it much faster but also makes your code less horrific. It will be like a list to improve the JDBC or we can say, handling database like a pro. So let us start the adventure and yeah, make your tea to Sip and Learn.

JDBC Factory: One Place to Rule them All

How does JDBC irritate Java developers? Via redundancy. No matter how many queries you have to run, you always have to write the code for Driver registration, connection creation, connection closing when creating a JDBC connection.

But what If you can make the driver registration, connection creation, or getting a connection from the pool in one static function and call only that when needed? It will greatly improve the maintainability as well as the robustness and less redundant code.

The idea behind this is that, you create static function in lest say, getConnection() in ‘JdbcUtil’ class. Now all the connection creation will be in getConection() method.

public class JdbcUtil {
	public static Connection getConnection() {
	    try {
	        Class.forName("classname.of.driver");  
	        return  DriverManager.getConnection("url", "user", "pass");
	    } catch () {
	        throw new RuntimeException("Can not create connection", ex);
	    }
	}
}

Use JDBC Connection Pool

If your application’s communication with the database it frequent, then you should consider using the JDBC connection pool. It creates the pool of connection to the database and hands you the one when needed instead of creating a new one at every connection request which is a very expensive part in JDBC. This topic is explained in the easiest way possible in “JDBC Connection Pooling Explained with HikariCP”. Please do check it out for more in-depth knowledge of connection pooling in the database by HikariCp. You can see an example of HikariCP as shown below

public Connection getConnectionFromDataSource() throws SQLException {
	HikariConfig hikariConfig = new HikariConfig();
	//config hikari with the url, user, password etc.
	HikariDataSource hikariDataSource = new 
	HikariDataSource(hikariConfig);
	return hikariDataSource.getConnection();
}

You are reusing the same connection again and again which saves a lot of time and greatly improves your database communication efficiency. And yeah, do close the connection to give the connection back to the pool. Which takes us to our next part, the try-catch block with a twist.

Use try-with-resources In Java

Closing every JDBC object manually is cumbersome. Be it Connection  or  PreparedStatement or  ResultSet. And this thing, we need to write it over and over again for every connection object. Wouldn’t it be nicer if Java compiler does it for us? It just has to call the close() method of the object, pretty easy right? 

Well, Java has a solution just for that, and that is try-with-resources. Introduced in Java 7, it automatically closes the connection for you. The syntax for it is try(<? implements AutoCloseable>). It automatically adds the finally block and calls the close() of each object in the try(<objects>). And all the code is generated at the compile time. No need to remember to close the connection ever. One thing to remember is that object must implement AutoCloseable.

try (Connection con  = JdbcUtil.getConnection();
        PreparedStatement pstmt = con.prepareStatement(COUNT_QUERY);
        ResultSet rs = pstmt.executeQuery()) {
       if (rs.next()){
           return rs.getInt(1);
       }
   } catch (Exception e) {
       e.printStackTrace();
   }
   return 0;
}

Separate Database Code from Business Logic

The database communication code in your application should be separated from your business logic code. You can separate them via package or classes. The package is a better choice. This way, your code is not only more readable and robust but also a lot more maintainable. Typically they are called DAO or Data Access Object. DAO object’s sole responsibility is to pull/push data from/to database and nothing else. You can create a DAO class per table or database, depending on your requirements and usage.

Following the naming conventions, you can put Dao at the end of the class name. For example,  CusotmerDao,  OrderDao,  CodersTeaDaoTeaLoverDao  etc.

Use the JDBC PreparedStatement

Using PreparedStatement instead of Statement comes with many advantages. You can read more about it in detail in this answer on StackOverflow.

The TL;DR of this is that PreparedStatement comes with,

  • Caching: Does Db side caching for faster response
  • Batching: You can read or write from/to DB in batches. Increases throughput of the transactions.
  • Fetch size: How many data should be fetched in one single network call.
  • No more string concatenations: You can to put ‘?’ in the where condition to replace the value and set it with the setXXX(index, value) where XXX is the object type.
String insertQuery = " SELECT id FROM employee WHERE name  = ? ";

try (Connection con = JdbcUtil.getConnection();
    PreparedStatement pstmt = con.prepareStatement(insertQuery)
    ) {
   
    //fetch only 1000 data at a time
    pstmt.setFetchSize(1000);

    //replace first ? with the "name".
    // it will put single qoutes (') itself for strings or varchars. 
    pstmt.setString(1,"name");

    ResultSet rs = pstmt.executeQuery();
    while(rs.next()){
       //todo
    }
  rs.close();
} catch (Exception e) {
   e.printStackTrace();
}

We will be exploring the wonders of PreparedStament in detail on another post.

Those are Just Little Hacks

Put a Ferrari’s car body to a cheap car engine, will it run like a Ferrari? Of course not. Ferrari’s real power, the core, is its engine. Get a better engine get a better speed. That’s what happens with JDBC.

Optimizing the JDBC code, with the tricks above, is like optimizing the aerodynamics of the car. It will improve the speed, but its real engine is its query. Focus on the query first. For example, do not use ‘*’ in a select statement instead, be specific about the columns you need, resulting in required and fewer data, and less time. Use UPPERCASE for SQL keywords and aliases. It will not increase the speed but it will increase readability.

Moral of the Story

So the moral of the story is to reduce boilerplate connection creation code by creating separate global functions and use a connection pool if possible. The in-depth details about how, why, when to use JDBC connection can be found in “JDBC Connection Pooling Explained with HikariCP“. Write database communication code in DAO classes (packages). Use try-with-resources and PreaparedStatement. And yes optimizing the SQL Query should be the first priority.

That’s it for this post. I will be writing a post about “Wonders of PreparedStatement” so that we can leverage the optimality of JDBC to highest. You can You can find the code on GitHub here or the full project here