IT, Scala, Java, Web, Ubuntu

Month: September 2011

I can still see many tutorials of JDBC that are not really talking about how JDBC programming can be done easily and more practically & efficiently. I can understand that those tutorials are to give a very basic level of knowledge of JDBC. Besides, we do often not use JDBC directly any more these days. There are easier and simpler solutions like Object Relational Mapping (ORM) and other persistence frameworks. I use Java Persistence API (JPA) with Hibernate and QueryDSL (Personally, I think QueryDSL is great. I cannot imagine life without QueryDSL anymore when using JPA. ). There are also other choices. For instance, EclipseLink and OpenJPA both of which are JPA implementations, Java Data Objects (JDO), MyBatis, the successor of iBATIS, and so on.

However, there may come a time when you need to directly use JDBC. It can be developing an application which requires accessing and using database or for developing your own Java persistence framework. One way or another it is good to learn this very fundamental Java persistence technology to get some good things from it as well as its drawback in order to avoid it. I am not going to talk about what JDBC is or how to use it as there are plenty of other tutorials explaining these on the Internet. What I am going to do is talking about better and easier way to use JDBC. I will not cover every single detail about it though, as my point here is giving some idea to use it better and there are already known solutions that provide what I am going to explain.

There are four steps to take to explain the better way of using JDBC. First of all, I will show a typical way to use JDBC then point out problems in it. After that analyse it to find a better way. Finally, I will implement it.

Preparation

Database and JDBC Driver

In my examples, I’ve used MySQL 5.1 and MySQL Connection/J 5.1.17 downloaded from the Connector/J download page on the MySQL website.

mysql-connector-java-5.1.17.zip

If you don’t want to become a user of the site, you can click the “No thanks, just take me to the downloads!” link at the bottom.
What you need is only one jar file that is mysql-connector-java-5.1.17-bin.jar in the zip file.

Java Beans

There are three JavaBeans used in the example to contain the data from the database.

Just the same… OK, now I will compare just one method in each DAO and try to find if there are any similarities in these methods. One method from PersonDaoImpl is findPeopleByState(String) and the other one from BookDaoImpl is findBooksByPublisher(String).

Let’s see what are different.

Method Comparison Between PersonDao and BookDao

As you can see, except for a few parts highlighted, I’ve just repeated the same code. That means if the user of the DAO can dynamically give the code for these few differences when using it, I do not need to repeat the same code over and over again.

There is another problem in the catch block that catches the SQLException which can be thrown in the try block containing the main part of the code snippet.

It just prints the StackTrace and ignores the exception, and this may cause a more serious problem as the exception is simply ignored so the programmers cannot easily find what went wrong. I need to deal with it too.

3. Finding Better Way

I will create a JdbcManager which takes care of selection, insertion, update and deletion of data.

I will try something very easy to do first. In the typical DAO code, every method has a finally block having really ugly code snippet to close ResultSet, Statement and Connection. I am sick of it so want to get rid of it first, but I cannot simply remove that code as it’s absolutely necessary therefore it’s there. Then I can make a method which closes those objects quietly without throwing any exception, for it’s already in the finally block and there might be already an exception thrown. So I don’t want the finally block to throw another exception which interrupts the fist exception.

My JdbcManager class will have the following method to close ResultSet, Statement and Connection.

Now think about the highlighted lines that are different depending on what type of object should be returned.
The first one is the method signature. I will talk about the parameters later and now focus on only the return type.

public List<Person> findPeopleByState(final String state)

It returns a List of Person but what if I want to get a List of Book? So the return type is of type List, but the type of the element should be contained in the List is undecided. It should be pending until the user of the method specifies it. In this kind of situation, what do we normally use? When we design an API and want to defer specifying what type to use until the user of the API decides it with providing compile-time type safety, we use Generics in Java.
So I can just easily make the method a generic method so that the return type would be depending on how the user of the method uses it.

public <T> T findPeopleByState( *don't worry about params for a while* )

The second part is the same, I don’t know what type of object the user wants to have in the List, so I just use a generic type parameter T.
So I’ve changed this

final List<Person> result = new ArrayList<Person>();

to

final List<T> result = new ArrayList<T>();

The third one is easy. It can easily become a parameter of the method.

So it seems like it will possibly have many parameters from the user of my method. So I think I should use some kind of Collection type containing the parameters or an array of Object containing these because the types of the parameters can vary. Wait. More conveniently I can just use varargs so that the user of my method doesn’t need to create any extra instance of collection or array to pass the parameters although using varargs results in creation of an array by compiler, yet in the user’s point of view, it’s hidden so can be very convenient.

The types of the parameters can vary so I put Object type varargs. Now I need to set the parameters to the PreparedStatement. Since the varargs is actually an array, I can iterate over it to get each parameter then I use an index to get a parameter and index + 1 to set the parameterIndex in the PreparedStatement as it starts from 1. I will cover here only primitive types (including boxed ones), String and other commonly used reference types in db programming which are Date, Calendar and BigDecimal. All the other types will cause an IllegalArgumentException. It can of course improve but I am just giving an idea to use it better so am trying to make it just simple enough to understand with providing the idea. So the code would be

The highlighted part should be done by the user of the API. The JdbcManager does’t know what should be there nor can it guess what data the user of it wants to retrieve from the database. It looks like I can use Callback here. Java does not have First-Class Function yet (Java will have First-Class Function in the form of lambda expression in the Java SE 8. Click here to get more details about Project Lambda), but I can define an interface which maps data in a ResultSet row to a Java object and let the user pass an instance of class implementing the interface or an anonymous class instance of the interface can be used too.
What I need to consider is the return type and the ResultSet as I’m now trying to map the data in the ResultSet to a Java object (return type). So the mapper interface should look like.

Notice that I’ve also added the throws clause having SQLException as using ResultSet requires it and that’s why we put try-catch block in our code using JDBC. The SQLException thrown in the map() method will be handled by the try-catch block in the main part of the JdbcManager select() method.

There is one last issue left. Now I need to take care of ignoring SQLException in the catch block.

}
catch (final SQLException e)
{
e.printStackTrace();
}
finally

I can properly log it instead of just calling the printStackTrace() method in the SQLException, but it’s not enough. I don’t want to just ignore it as it may cause some serious issue in runtime. However, I don’t want to re-throw it either because then the caller of this method or “the caller of the caller” of the method or in the worst case, all the methods in the method call hierarchy have to deal with the SQLException. Thus I want to create my own unchecked exception (an exception that extends RuntimeException) which wraps the SQLException. It is beneficial not only because the caller of the method doesn’t need to catch or re-throw it but because it is also understandable by the caller of the method even after I decide not to use JDBC at all so it doesn’t have any SQLException any more. In this case, if I used SQLException, I would have to change the code in the caller-side as the caller would directly deal with it.

Wow! It’s really simple now! Just passing an SQL query and parameters with a RowMapper.

OK, it’s good, but what happens if I want to insert or update or delete data from the database? The JdbcManager interface has the method signature for these but how can I implement it?
It would be simple too.

Turn off auto commit so the changes can be rolled back if any Exception is thrown.

There is still room for improvement in JdbcManagerImpl. Instead of having the setParameters() method to set parameter values to the Statement, Strategy pattern can be used so that the user of JdbcManager can later inject an object that sets the parameters so how to handle the parameters can be decided by the user of the API.

I think it’s enough to learn the better way to use JDBC. What I’ve explained here can be useful for solving other problems too (e.g. reading and writing files).
The code I’ve posted here is not production-ready. It’s solely for educational purpose. If you want to use it in practice, I recommend already existing libraries and frameworks which provide what I’ve explained here. These are well tested so are likely to be better than my in-house JdbcManager. If you use or are willing to use the Spring Framework, you can use its JdbcTemplate. If you don’t or cannot use the Spring Framework, you can choose Apache CommonsDbUtils.

=================================================================
The source code of “Easier and Better Way to Use JDBC” is available ->HERE<-.