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 make it much faster but also makes your code less horrific. It will be like a list to improve the JDBC or we can say, handle 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 post on CodersTea, 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 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, and connection closing when creating a JDBC connection.

But what If you can make the abstract driver registration, connection creation, or get 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);
	    }
	}
}

The 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 connections 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 of JDBC. This topic is explained in the easiest way possible in “JDBC Connection Pooling Explained with HikariCP”. Please 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. This 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 the 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 the 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 much data should be fetched in one single network call?
  • No more string concatenations: You can put ‘?’ in the where condition to replace the value and set it with  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  PreparedStament in detail in another post.

Batching is Underrated JDBC Best Practices

Whenever you have multiple data to insert or 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 in 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 into 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 less 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 using a connection pool if possible. The in-depth details about how, why, and when to use a 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 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 the highest. 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 post on CodersTea, just share your thought on Contact Us or let us know in the comments.

2 comments

Sergei C. January 30, 2022 - 12:34 am

Sorry to say this, but many of advices are bad.

public Connection getConnectionFromDataSource() throws SQLException {

The spec of this method is really confusing. DataSource is assumed to be reused and have lifecycle, but not kept as local variable in a method. Don’t do it like this.

public static Connection getConnection() {

Avoid using static methods to obtain Connections. It is not possible to write unit tests for such code, it’s not friendly to DI frameworks like spring.

ResultSet rs = pstmt.executeQuery();

should be

try (ResultSet rs = pstmt.executeQuery()) {
pstmt.setFetchSize(1000);

I strongly recommend not to do it like this. It looks innocent, but actually e.g. for Oracle JDBC driver it leads to memory leaks (when it’s repeatable actions and many connections have fetch size that big). At least you should reset the fetch size in a final section.

} catch (Exception e) {
       e.printStackTrace();
   }

you database query fails and you just hide it. Don’t do it like this.

Reply
Imran Shaikh
Imran Shaikh February 5, 2022 - 2:00 pm

Thanks for your suggestions. But most of the things I avoided were because of simplicity. For example, to get the connection from Datasource, As mentioned in the post, I have another post that explains this in detail. Maybe you can go through it and if you think something needed to be updated please let me know. You Have given quite a good suggestion. thank you.

Reply

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

TWITTER FEED

INSTRAGRAM FEED

Newsletter

Subscribe my Newsletter for new blog posts, tips & new photos. Let's stay updated!

coderstea-logo-invert
@2022 All Right Reserved. Designed and Developed by CodersTea

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