Open source JDBC Tool class Dbutils

　　This article will describe in detail the dbutils of the Apache JDBC Help tool class and how to use it. In the previous article, we have made a simple encapsulation of the DAO layer's redundant code using the JDBC Operation database to form its own jdbcutils, and in the process many of them are reference to Dbutils code, so through the previous study, Will give us a faster and simpler understanding of the dbutils.

As the saying goes, the best way to learn an open source tool is to look at its official documents, yes, in the Apache official website to dbutils detailed introduction: http://commons.apache.org/proper/commons-dbutils/. (or there is a document in the jar that downloaded the dbutils)

Dbutils is a library of classes that simplify the development of JDBC, solving most of the redundancy we've written previously based on "template" code, simplifying our code to the greatest extent, leaving us only interested in querying and updating (including adding, modifying, and deleting Data It's like the query and update method we focused on in the previous blog post.

What can dbutils do? Although the JDBC code is not difficult, it is time-consuming and laborious to use the "template" code we previously wrote, and it is very easy to lose links and find it difficult to track down, while using dbutils basically does not happen with resource leaks (such as link loss). Of course, as in the previous blog post, Dbutils also provides an interface Resultsethandler for result set processing, which we can implement to process the result set, or use the implementation class provided by Dbutils. For example Beanhandler or Beanlisthandler and so on, there are many useful result set processor classes. If some companies do not use Hibernate, the general will choose Dbutils.

Dbutils is the main two,Queryrunner class and Resultsethandler interface.

The Queryrunner class is primarily used to provide batch methods in various overloaded forms, the Query method, and the Update method, where the Update method contains additions, deletions, and modifications.

Queryrunner is not a static tool class, so we want to use an object that must first create a Queryrunner class, and there are two constructors, one with no parameters and one with parameters, where the constructor receiving a connection pool object datasource.

　　

　　

　　As you can see from the manual, if a connection pool object is given when the Queryrunner object is created, the connection is automatically fetched from the connection pool when some method that does not require the connection object as a parameter is called, and the connection is returned back to the connection pool when the call is finished For a parameterless constructor, the connection object must be specified when the method is called, and the processing of the connection object is itself responsible after the method is called.

Here's a look at the query method and the Update method for various overloaded forms of Queryrunner objects that have no parameters and parameters:

　　

　　

You can see that these methods are mainly divided into two kinds, one is connection object parameters, one is no connection object parameters. As mentioned above, the method of no connection object parameter is to get the link from the connection pool with the Queryrunner, and the connection is automatically returned to the connection pool. The method that has the connection parameter is responsible for closing the connection by the caller, which may seem cumbersome, but it is necessary to perform the overall execution of a transaction consisting of multiple SQL, since we do not need to give the connection Pool a single SQL.

In addition to the Query method and the Update method, the Queryrunner class has a batch method that performs the SQL batch processing:

　　

This method is used to execute multiple times on a single SQL, and the parameter we provide to this method is a two-dimensional array, so how do we use it?

such as "INSERT into user (Id,name,age) VALUES (?,?,?)" Such SQL statements, the two-dimensional array parameters in the batch method can be [[1, "Ding", 25],[2, "LRR", 24]] such a form, that is, batch execution two times, and each time the parameters are different.

The above introduction is basically queryrunner all, of course queryrunner is only part of Dbutils, and the other part is queryrunner in the query method parameter Resultsethandler interface, for processing the result set, The previous blog post has been very clear. And in Dbutils we've already written more of this interface's implementation class:

　　

In addition to the Beanhandler and beanlisthandler that we've done in our last blog, we can use the convenient result set processor in Dbutils and

Arrayhandler: Encapsulates the first row of data in the result set into an array of objects.

Arraylisthandler: Encapsulates each row of data in the result set into an array, and then saves the arrays into the list collection.

Columnlisthandler: Saves all data from a column in the result set into the list collection.

Keyedhandler: Encapsulates each row of data in the result set into a map, and then saves the map to a map with the key specified as key.

Maphandler: Encapsulates the first row of data in the result set into a map, key is the column name, and value is the corresponding value.

Maplisthandler: Encapsulates each row of data in the result set into a map, and then stores the map in the list.

Remember the custom Update method and the query method we focused on in the JDBC tool class jdbcutils in the previous blog (4th and 5th of the previous blog), just to encapsulate the redundant code In this article we can delete the two methods in the Jdbcutils, and directly in the DAO layer using Dbutils for the low-level additions and deletions.

Example 1 :

Then in the following code in the last blog Userdao in the final additions and deletions to reconstruct:

1  Public classUserdao {2     //Add User3      Public voidAdd (user user)throwsSQLException {4Queryrunner QR =NewQueryrunner (Jdbcutils.getdatasource ());5String sql = "INSERT into user (Id,name,age) VALUES (?,?,?)";6Object[] params ={User.getid (), User.getname (), User.getage ()};7 qr.update (SQL, params);8     }9 Ten     //Delete User One      Public voidDeleteintIdthrowsSQLException { AQueryrunner QR =NewQueryrunner (Jdbcutils.getdatasource ()); -String sql = "Delete from user where id=?"; -Object params =ID; the qr.update (SQL, params); -     } -  -     //Modify User +      Public voidUpdate (user user)throwsSQLException { -Queryrunner QR =NewQueryrunner (Jdbcutils.getdatasource ()); +String sql = "Update user set name=?,age=?" where id=? "; AObject[] params ={user.getname (), User.getage (), User.getid ()}; at qr.update (SQL, params); -     } -  -     //Find a user -      PublicUser Find (intIdthrowsSQLException { -Queryrunner QR =NewQueryrunner (Jdbcutils.getdatasource ()); inString sql = "SELECT * from user where id=?"; -Object params =ID; toUser user = Qr.query (sql,NewBeanhandler<> (User.class), params); +         returnuser; -     } the  *     //List all users $      PublicList<user> Getalluser ()throwsSQLException {Panax NotoginsengQueryrunner QR =NewQueryrunner (Jdbcutils.getdatasource ()); -String sql = "SELECT * from User"; thelist<user> list = qr.query (sql,NewBeanlisthandler<> (User.class)); +         returnlist; A     } the}

View Code

We can see that using Queryrunner to change our additions and deletions is very simple, while we in the tool class Jdbcutils also do not have to write the Update method and the Query method, in Jdbcutils is mainly to create a connection pool. Since we used DataSource as a parameter when we created the Queryrunner object, we can use the Update method and the Query method without the Connection object to specify the connection, and these methods will automatically put our connection back into the connection pool. So we don't even have to write code to release resources in Jdbcutils (of course, specific analysis).

Example 2 :

Add a demo of batch batch method using Dbutils:

  void  batchinserttest () throws   SQLException {queryrunner qr  = new   Query        Runner (Jdbcutils.getdatasource ()); String SQL  = "INSERT into user (Id,name,age) VALUES (?,?,?)"         = new  object[5][3];  for  (int  i=0;i<params.length ; I++ = new  object[]{i+1,        "AA" +i,25+i};    } qr.batch (sql, params); }

View Code

The results are as follows:

　

About Dbutils in the Queryrunner class is almost finished, mainly is batch, query and Update method.

For the result set processor class, Dbutils provides us with many implementation classes for the Resultsethandler interface, and if none of these implementation classes meet our needs, we can implement the Resultsethandler interface to make the implementation class of the functionality we need.

Example 3 :

The following demo is briefly demonstrated using the Arrayhandler class of one of the result set processor classes in Dbutils (for example, the data in the database after the batch method call above):

1  Public voidArrayhandlertest ()throwsSQLException {2Queryrunner QR =NewQueryrunner (Jdbcutils.getdatasource ());3String sql = "SELECT * from User";4object[] Firstuser = qr.query (sql,NewArrayhandler ());5          for(Object o:firstuser) {6 System.out.println (o);7         }8}

View Code

The Arrayhandler class encapsulates only the first row of data in the result set into an array of objects, which is observed in the console:

　　

Example 4:

Sometimes we need to calculate the total number of records in a table, for example, when we use the database for paging, we need to know how much data, then return the query results as a result set can also use arrayhandler simple processing, because the first element of the array is the query results, but the following code seems to be no problem, It throws an exception at run time:

1  Public voidCounttest ()throwsSQLException {2Queryrunner QR =NewQueryrunner (Jdbcutils.getdatasource ());3String sql = "SELECT COUNT (*) from user";4Object[] result = qr.query (sql,NewArrayhandler ());5         intCount = (int) result[0];6 System.out.println (count);7}

View Code

Exception Reason:

　　

That is, the Count method in MySQL returns the value of the query as long in JDBC, so we cannot use only the integer type. Note that you can cast a long type to an integer type when the total number of records is not large:

1  Public voidCounttest ()throwsSQLException {2Queryrunner QR =NewQueryrunner (Jdbcutils.getdatasource ());3String sql = "SELECT COUNT (*) from user";4Object[] result = qr.query (sql,NewArrayhandler ());5         intCount = ((Long) result[0]). Intvalue ();6 System.out.println (count);7}

View Code

Of course there are other very useful result set processor classes, which are not introduced here. This article on the dbutils of the end, for the use of dbutils is very simple, as long as the Master Queryrunner class and Resultsethandler interface and its implementation class, you can have a good grasp of dbutils.

Open source JDBC Tool class Dbutils