Use JakartaCommons components beanutils and dbutils to simplify JDBC database operations

Although many ORM frameworks have emerged, many friends may still be using JDBC. Just like me, apart from learning these excellent frameworks such as Hibernate and Spring, JDBC is always used at work. This article briefly introduces how to use beanutils and dbutils of JakartaCommons to simplify JDBC database operations,

Although many ORM frameworks have emerged, many friends may still be using JDBC. Just like me, apart from learning these excellent frameworks such as Hibernate and Spring, JDBC is always used at work. This article briefly introduces how to use beanutils and dbutils of Jakarta Commons to simplify JDBC database operations,

Although many ORM frameworks have emerged, many friends may still be using JDBC. Just like me, apart from learning these excellent frameworks such as Hibernate and Spring, JDBC is always used at work. This article briefly introduces how to use beanutils and dbutils of Jakarta Commons to simplify JDBC database operations, so as to help others who are using JDBC like me.

The following describes how to use beanutils and dbutils in JDBC-based API database access operations. The first part introduces the application of beanutils in JDBC database access operations, the second part introduces the application of dbutils in JDBC database access operations, and finally looks at their advantages and disadvantages, I will talk about some of my experiences in the application of the project, which is for reference only. I hope you can give me some advice if there is something wrong with it.

I. Jakarta Commons beanutils

Beanutils is a sharp weapon for operating beans. The BeanUtils tool class mentioned by Beanutils can be used to easily read or set Bean attributes. Using the LS-DYNA series, you can also create beans at runtime, it's just like LazyDynaBean and LazyDynaClass. These usages have been mentioned in many articles. You can also refer to the official apache documentation.

When you access the database directly using the jdbc api (this is the query select of the ResultSet returned result set), most of them adopt two methods, one is to retrieve the returned result set data and store it in Map, and the other is Bean. For the second method, Beanutils provides ResultSetDynaClass combined with DynaBean and RowSetDynaClass combined with DynaBean to simplify the operation. The following is a simple example to illustrate the use of beanutils in JDBC database operations.

Create a database publish on the local machine. I use MySQL to create a table book in the publish database. The script is as follows:

Create table book (

Id int (11) not null auto_increment,

Title varchar (50) character set latin1 not null,

Authors varchar (50) character set latin1 default NULL,

Primary key (id)

)
Then, create a class BeanutilsJDBCTest in your favorite editor. We first use ResultSetDynaClass for processing, and then use RowSetDynaClass to implement the same class. Then we can see what is the difference between them, the source code processed with ResultSetDynaClass is as follows:

Then, create a class BeanutilsJDBCTest in your favorite editor. We first use ResultSetDynaClass for processing, and then use RowSetDynaClass to implement the same class. Then we can see what is the difference between them, the source code processed with ResultSetDynaClass is as follows:

Package cn. qtone. test;

Import java. SQL. Connection;

Import java. SQL. DriverManager;

Import java. SQL. ResultSet;

Import java. SQL. Statement;

Import java. util. Iterator;

Import org. apache. commons. beanutils. DynaBean;
Import org. apache. commons. beanutils. PropertyUtils;
Import org. apache. commons. beanutils. ResultSetDynaClass;
Public class BeanutilsJDBCTest {
Public static void main (String [] args ){
Connection con = null;
Statement st = null;
ResultSet rs = null;
Try {
Class. forName ("com. mysql. jdbc. Driver ");
String url = "jdbc: mysql: // 127.0.0.1: 3306/publish? UseUnicode = true & characterEncoding = GBK ";
Con = DriverManager. getConnection (url, "root", "hyys ");

St = con. createStatement ();
Rs = st.exe cuteQuery ("select * from book ");

ResultSetDynaClass rsDynaClass = new ResultSetDynaClass (rs );

Iterator itr = rsDynaClass. iterator ();

System. out. println ("title ------------- authors ");

While (itr. hasNext ()){

DynaBean dBean = (DynaBean) itr. next ();

System. out. println (PropertyUtils. getSimpleProperty (dBean, "title ")

+ "-------------" + PropertyUtils. getSimpleProperty (dBean, "authors "));

}

} Catch (Exception e ){
E. printStackTrace ();

} Finally {

Try {

If (rs! = Null ){

Rs. close ();

}

If (st! = Null ){

St. close ();

}
If (con! = Null ){

Con. close ();

}
} Catch (Exception e ){

E. printStackTrace ();

}

}
}

}

The source code processed with RowSetDynaClass is as follows:

Package cn. qtone. test;

Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. ResultSet;
Import java. SQL. Statement;

Import java. util. Iterator;
Import java. util. List;
Import org. apache. commons. beanutils. DynaBean;

Import org. apache. commons. beanutils. PropertyUtils;
Import org. apache. commons. beanutils. RowSetDynaClass;

Public class BeanutilsJDBCTest {
Public static void main (String [] args ){
List rsDynaClass = rsTest ();
System. out. println ("title ------------- authors ");
Iterator itr = rsDynaClass. iterator ();

While (itr. hasNext ()){

DynaBean dBean = (DynaBean) itr. next ();

Try {
System. out. println (PropertyUtils. getSimpleProperty (dBean, "name ")

+ "-------------" + PropertyUtils. getSimpleProperty (dBean, "mobile "));

} Catch (Exception e ){

// TODO automatically generates catch Blocks

E. printStackTrace ();

}
}

}
Private static List rsTest (){

Connection con = null;

Statement st = null;

ResultSet rs = null;

Try {

Class. forName ("com. mysql. jdbc. Driver ");

String url = "jdbc: mysql: // 127.0.0.1: 3306/publish? UseUnicode = true & characterEncoding = GBK ";

Con = DriverManager. getConnection (url, "root", "hyys ");

St = con. createStatement ();

Rs = st.exe cuteQuery ("select * from book ");

RowSetDynaClass rsdc = new RowSetDynaClass (rs );
Return rsdc. getRows ();

} Catch (Exception e ){
E. printStackTrace ();
} Finally {

Try {
If (rs! = Null ){
Rs. close ();

}

If (st! = Null ){

St. close ();

}

If (con! = Null ){
Con. close ();
}
} Catch (Exception e ){

E. printStackTrace ();

}

}

Return null;

}

}

The output results of these two methods should be the same. However, it is obvious that the second method is better than the first method. It extracts the data access part and puts it in a method, which is simple and clear.

In fact, when using ResultSetDynaClass, you must process the data before the database resources such as ResultSet are closed. You cannot use DynaBean after the resource is closed; otherwise, an exception is thrown, exception means you cannot access the data after the ResultSet (I forgot the specific exception name). Of course, you can also put the data in the Map one by one in the previous method, if you must do that, we recommend that you do not use Beanutils because it does not bring you any benefits. In short, the use of ResultSetDynaClass is very good in scalability of your program.

The second method shows that RowSetDynaClass can effectively solve the problems encountered by the ResultSetDynaClass. The getRows () method of RowSetDynaClass encapsulates each row in a DynaBean object. Then, put some rows in a List, and then you can process every DynaBean in the returned List. In addition, you can use the standard get/set Method for DynaBean, of course, you can also use PropertyUtils. getSimpleProperty (Object bean, String name.

From the above analysis, you should be able to decide whether you should use ResultSetDynaClass or RowSetDynaClass.

Ii. Jakarta Commons dbutils:
When using JDBC APIs, the most annoying thing is exception handling, which is also cumbersome and error-prone. I have considered using templates for handling, and then I saw dbutils, later, we used the dbutils and used the template. You can refer to Spring. Spring's JdbcTemplate is not flexible and powerful, rod Johnson is really very respectable.
The QueryRunner of Dbutils encapsulates most of the resources related to resource closure. In addition, you can also use DbUtils to disable the functions provided by DbUtils, it provides several common static methods, in addition to disabling resources, DbUtils. commitAndClose (Connection conn) also provides operations such as transaction mentions.
Let's talk about it as an example. After all, I am not engaged in business, but I can't beat it up, huh, huh.
In order to make a better comparison with the use of Beanutils, this example implements the same function, and the database also uses the publish mentioned in the previous article.
Similarly, use your favorite editor to create a class DbutilsJDBCTest. The sample code is as follows:
Package cn. qtone. test;
Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. SQLException;
Import java. util. List;
Import java. util. Map;
Import org. apache. commons. dbutils. DbUtils;
Import org. apache. commons. dbutils. QueryRunner;
Import org. apache. commons. dbutils. handlers. MapListHandler;
Public class DbutilsJDBCTest {
Public static void main (String [] args ){
Connection conn = null;
String jdbcURL = "jdbc: mysql: // 127.0.0.1: 3306/publish? UseUnicode = true & characterEncoding = GBK ";
String jdbcDriver = "com. mysql. jdbc. Driver ";
Try {
DbUtils. loadDriver (jdbcDriver );
// Username "root". Password "root"
Conn = DriverManager. getConnection (jdbcURL, "root", "root ");
QueryRunner qRunner = new QueryRunner ();
System. out. println ("*** Using MapListHandler ***");
// The following code uses Map storage instead of Bean for processing.
List lMap = (List) qRunner. query (conn,
"Select title, authors from books", new MapListHandler ());
// The following code can be extracted:
System. out. println ("title ------------- authors ");
For (int I = 0; I <lMap. size (); I ++ ){
Map vals = (Map) lMap. get (I );
System. out. println (vals. get ("title") + "-------------" + vals. get ("authors "));
}
} Catch (SQLException ex ){
Ex. printStackTrace ();
} Finally {
DbUtils. closeQuietly (conn );
}
}
}

How is it? Is it much better than ResultSetDynaTrial and RowSetDynaClass using Beanutils? What makes it difficult to use Beanutils is to close those resources and handle those exceptions. Here, Dbutils is used to obviously reduce the amount of code.
In the previous example, when processing the result set, it maps each row in the database into a Map, where the column name is used as the Key and the Value corresponding to the column is stored as the Value, all the data to be queried is put together in a List and then processed. Of course, a more wise way is to directly return the List and then process it separately.
In fact, each row in the result set returned in the previous example does not need to be placed in a Map. You can put it in a Bean. If you are really LazyDynaClass and LazyDynaBean of Beanutils, however, it may not be necessary to do so. For the reason, see the following.

If you use Bean instead of Map, you may need to create a Bean as follows:
Package cn. qtone. test;
Public class Book {
Public int id;
Public String title;
Public String authors;
Public StudentBean (){
}
Public String getAuthors (){
Return authors;
}
Public void setAuthors (String authors ){
This. authors = authors;
}
Public int getId (){
Return id;
}
Public void setId (int id ){
This. id = id;
}
Public String getTitle (){
Return title;
}
Public void setTitle (String title ){
This. title = title;
}
}

Then, simply modify some of the Code in DbutilsJDBCTest. The replaced source code is as follows:

Package cn. qtone. test;

Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. SQLException;
Import java. util. List;
Import java. util. Map;
Import org. apache. commons. dbutils. DbUtils;
Import org. apache. commons. dbutils. QueryRunner;
Import org. apache. commons. dbutils. handlers. BeanListHandler;

Public class DbutilsJDBCTest {
Public static void main (String [] args ){
Connection conn = null;
String jdbcURL = "jdbc: mysql: // 127.0.0.1: 3306/publish? UseUnicode = true & characterEncoding = GBK ";
String jdbcDriver = "com. mysql. jdbc. Driver ";
Try {
DbUtils. loadDriver (jdbcDriver );
// Username "root". Password "root"
Conn = DriverManager. getConnection (jdbcURL, "root", "root ");
QueryRunner qRunner = new QueryRunner ();
System. out. println ("*** Using BeanListHandler ***");
// The following code uses Map storage instead of Bean for processing.
List lBeans = (List) qRunner. query (conn, "select title, authors from books", new BeanListHandler (Book. class ));
// The following code can be extracted:
System. out. println ("title ------------- authors ");
For (int I = 0; I <lBeans. size (); I ++ ){
Book vals = (Book) lBeans. get (I );
System. out. println (vals. getTitle () + "-------------" + vals. getAuthors ());
}
} Catch (SQLException ex ){
Ex. printStackTrace ();
} Finally {
DbUtils. closeQuietly (conn );
}
}
}
The output results of these two methods should be the same. Both methods are similar, but I prefer the first one, because the first one writes less beans, and I have tested the method of using Map, that is, the first method has better performance, the performance of Bean is relatively low, probably because reflection is used. There is still a gap between the performance of reflection-based Dongdong and that of reflection-free. This is also the reason. LazyDynaClass and LazyDynaBean are not recommended, because they are used to dynamically create Bean classes and Bean properties at runtime, and then create Bean objects, the performance can be imagined (but I have never tested it, so I can say that it is not correct. If you are interested, test it by yourself and tell me the result ), in addition to MapListHandler and BeanListHandler, DButils also provides other Handler. If these cannot meet your needs, you can implement a Handler by yourself.
Finally, it is also the biggest experience. Maybe it is the biggest achievement, that is: for each project, when obtaining the corresponding solution based on each requirement, first look for open source components, check whether there are open-source components that meet certain functional requirements. If not, consider independent development or purchase from a third party. Otherwise, try to use open-source components.
Please try to enjoy the charm of open source and embrace open source.
Now, I have finished writing. Please contact me if you have any questions.