CodersTea - Sip & Learn
Java

Steps to Achieve Greatness in JDBC

 0

 0

 26

By CodersTea-Team

 On Feb 8, 2020 at 06:50 pm

 

Introduction

Hey tea lovers, this tea break we will make a healthy one. What about green tea? Just kidding. I don’t like it either. Let us jump to the post, shall we?

We will talk about the best practices as we talked in “What’s in a Name: Java naming conventions” but this time about the JDBC. There are many small tweaks you can do to not only make it much faster but also make 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.

One Place to Rule them All

What makes people irritating about JDBC? The redundancy. No matter how small queries you have to run you always have the same code redundantly such as Driver registration, connection creation, connection closing. If you can make the driver registration and connection creation code or getting a connection from the pool (explained in the next block) in one static function it will greatly improve the maintainability as well as the robustness and less redundant code.

The Pooling

If your application’s communication with the database it frequent, then you should consider using JDBC connection pooling. 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 “Make your life easy by HikariCP: Connection Pooling”. 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 return given connection back to the pool or in simple word close the connection. Which takes us to our next part, the try-catch block with a twist.

try-with-resources

What you hate the most in the JDBC connection? For me, closing every damn JDBC object. Be it Connection or PreparedStatement or ResultSet etc. And this thing is redundant but still, we need to write it over and over again for every connection object. Wouldnt it be nicer if we Java compiler does it for us? It just has to call the close(), doesn’t it? try-with-resources to the rescue. Introduced in Java 7, it automatically closes the connection for you in a simpler world, If the object implements the ‘AutoCloseable it calls the overridden method close() in the finally block. And all the code is generated at the compile time. No need to remember to close the connection ever.

try (Connection con  = DriverManager.getConnection("url", "user", "pass");        PreparedStatement pstmt = con.prepareStatement("SELECT COUNT(*) FROM employee");        ResultSet rs = pstmt.executeQuery()) {       if (rs.next()){           return rs.getInt(1);       }   } catch (Exception e) {       e.printStackTrace();   }   return 0;}

Kick It Out of the House

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 not only more readable and robust but a lot more maintainable. Typically they are called DAO or Data Access Object. This 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, CodersTeaDao, TeaLoverDao etc.

Using the PreparedStatement

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

The TL;DR of this is that it comes with, Caching: DB side caching. Batching: You can read or write from/to DB in batches. Fetch size: How many data should be fetched in one go. No String concatenations: You have to put ‘?’ where you need to replace the value and set it with the setXXX(index, value).

String insertQuery = " SELECT id FROM employee WHERE name  = ? ";// '?' will be replaced with the valuestry (Connection con = JdbcBestPractices.getConnection();    PreparedStatement pstmt = con.prepareStatement(insertQuery)    ) {   //fetch only 1000 data at a time   pstmt.setFetchSize(1000);   //setting name value   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 body to a cheap car, 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 engine is its query. Focus on the query first. Like, not using ‘*’ in a select statement instead, being specific about columns you needed, resulting in fewer data and less time. Using indexes. Use capital letters when using SQL keywords and using aliases. It will not increase the speed but will increase readability.

Moral of the Story

So the moral of the story is to reduce redundant connection creation code by creating separate global functions and use a connection pool if possible. Write database communication code in DAO. Use try-with-resources and PreaparedStatement. And yes optimizing the SQL Query should be our first objective. 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 find the code on GitHub here or the full project here

   javabestpracticesconcurrencyconnectionpoolingcuttingfunctionalprogrammingfunctionalprogramminginstallationjavajava8jdbclatestjdkmultithreadingnamingconventionreflectionapistreamapi

About Contributer

Questions / Comments


Comments

Be the first to comment on this post.

Questions / Comments

×