Home » How to Achieve Greatness in JDBC Performance

How to Achieve Greatness in JDBC Performance

by Imran Shaikh
1254 views
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 databases, and the best practices of JDBC to achieve greatness in the performance of database operations.

We will talk about the best practices as we talked about in “What’s In A Name: Java Naming Conventions” but this time, it is about the JDBC best practices. 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 the database like a pro. So let us start the adventure.



You can follow me on social media via @coderstea on TwitterLinkedinFacebook, or Instagram. We also share high-quality videos about programming on our Youtube channel. You can also publish your own post on CodersTea.com, just share your thought on Contact Us or let us know in the comments.



Separate Database Code from Business Logic

The first thing you have to do is the separation of concern. 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 will be 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.

JDBC Factory: One Place to Rule them All

How does JDBC irritate Java developers? Via redundant code. 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 abstract 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. It is one of the major JDBC best practices.

The idea behind this is that you create a static function, something like, getConnection() in JdbcUtilclass. 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);
	    }
	}
}

Best Practice is to Use JDBC Connection Pool

If your application’s communication with the database is frequent, then you should consider using the JDBC connection pool. It is one of the main JDBC best practices. 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.

Using try-with-resources to Auto Close

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. 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 this 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 Class 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;
}

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.

Batching is Underrated JDBC Best Practices

Whenever you have multiple data to insert or to update, always use batching. With batching you are saving the additional trips to the databases over the network. You have to use the batch methods of PreparedStatement. There is a process of how to use batching in JDBC, which I have explained in detail on my another post “How to Use Batch in JDBC to Insert huge data

JDBC Best Practices 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.

Conclusion for JDBC Best Practices

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.

See you in the next post. HAKUNA MATATA!!!


You can follow me on social media via @coderstea on TwitterLinkedinFacebook, or Instagram. We also share high-quality videos about programming on our Youtube channel. You can also publish your own post on CodersTea.com, just share your thought on Contact Us or let us know in the comments.


Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More

Privacy & Cookies Policy
0
Would love your thoughts, please comment.x
()
x