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, handle the database like a pro. So let us start the adventure.
I would be happy to connect with you guys on social media. It’s @coderstea on Twitter, Linkedin, Facebook, Instagram, and YouTube.
Please Subscribe to the newsletter to know about the latest posts from CodersTea.
Separate Database Code from Business Logic
The first thing you have to do is separate concerns. 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 Objects. DAO object’s sole responsibility is to pull/push data from/to the 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, CodersTeaDao, TeaLoverDao 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, and 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);
}
}
}Code language: JavaScript (javascript)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();
}Code language: PHP (php)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;
}Code language: PHP (php)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();
}Code language: JavaScript (javascript)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 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!!!
I would be happy to connect with you guys on social media. It’s @coderstea on Twitter, Linkedin, Facebook, Instagram, and YouTube.
Please Subscribe to the newsletter to know about the latest posts from CodersTea.
