Notes-jdbc and Commons-dbutils

1. Preface

People who have played the Java web should have been exposed to JDBC, and it is the Java program that makes it easy to access the database. JDBC is a lot of people, but why do I have to write about it? I used to suck jdbc at one point, I thought JDBC was all that, and later, I got into the object-oriented understanding and slowly learned how to abstract the JDBC code, and then I met Commons-dbutils, a lightweight toolkit, The discovery of this toolkit is also an abstraction of the JDBC code, and is more optimized than the code I wrote. In this process, I realized the abstract charm, I also hope that through this article, my experience to share.

The article is roughly based on some logic: how does JDBC use-----So what's the problem------how to improve the principle of-----analysis Commons-dbutils

2. How JDBC Uses

This section shows an example of how JDBC is used.

We can roughly say that the entire operational process of JDBC is divided into 4 steps:

1. Get a database connection

2. Create statement

3. Execute the SQL statement and process the returned results

4. Releasing resources that are not needed

Here is a small example (omitting the Try-catch code):

String username= "root"; String password= "123"; String url= "Jdbc:mysql://localhost/test"; Connection Con=null; Statement St=null; ResultSet RS=NULL;//1, obtain connection Class.forName ("Com.mysql.jdbc.Driver");         
Con=drivermanager.getconnection (Url,username,password);//2, create statementstring sql= "SELECT * from Test_user"; st= Con.createstatement ();//3, executes the SQL statement and processes the returned result rs=st.executequery (SQL); while (Rs.next ()) {    //processes The result  }//4, Release resources Rs.close (); St.close (); Con.close ();

The above example is a use of the query, in addition to statement, can also be used PreparedStatement, the latter is the former subclass, on the basis of the former added pre-compilation and prevent SQL injection function. In addition, the query and additions and deletions are different usages, the query will return resultset and additions and deletions will not be changed.

3. What's wrong with writing code like this

3.1, so write code will cause a lot of repetitive work, such as getting a connection, if each method of executing SQL to write the same code again, then such duplicate code will flood the entire DAO layer.

3.2, such code is poor readability, dozens of lines of code really and business-related actually a few lines

3.3, a lot of duplication of code will cause a problem, that is, maintainability is poor, once a constant changes, then you need to change each method.

3.4, database connection is a heavyweight resource, each call method to create a connection, performance bottlenecks

4. How to Improve

For the 1, 2, and 3 of the previous questions, the improved approach is abstraction, abstracting reusable code, and forming a single module, decoupling the module from the module. Since the entire JDBC operation process is divided into 4 steps, it is possible to abstract from these 4 steps.

4.1. Get a database connection

My solution at the time was to initialize a lot of connections into the list, and then use the time to take, now the common method is to connect the pool, such as DBCP, c3p0 and so on. Interested people can go to see their source code and see how it's implemented

4.2. Create statement

I was working with PreparedStatement because PreparedStatement would cache the compiled SQL

4.3. Execute the SQL statement and process the returned results

This block can use reflection to encapsulate the resulting results into Java bean objects

4.4. Releasing Resources

Use dynamic proxies to change the behavior of the connection Close method and put connection back into the connection pool

5, the principle of commons-dbutils

While I've made improvements, it's far from true decoupling, and commons-dbutils as a member of the Commons Open source project team is doing pretty well, and by reading its source code, you can learn how to abstract and decouple JDBC operations.

5.1. Overall structure

Let's take a look at what the classes are:

There are 27 classes, but what is really common is the three major components more than 10 classes: The façade component, the result processing component, and the row processing component, where the façade component provides the entrance to the program, and some parameter checking, and so on, the result processing component is the core, because the returned result can be a map, Can be a list can be JavaBean, this piece of change is very large, so the abstraction of a component to deal with these changes, the row processing component is separated from the result processing component, it is the basis of the result processing component, no matter what kind of processor, ultimately to deal with a row of data, so, This component is abstracted out individually.

Class name	Describe
Façade components
Queryrunner	Perform additions and deletions to the entrance
Result processing component
Resultsethandler	Interface for handling resultset
Abstractkeyedhandler	Abstract class that processes the returned results into key-value pairs
Keyedhandler	Processing the database return results, encapsulated into a map, a database table column named key, usually can use the primary key, a row of the database in the form of a map as value
Beanmaphandler	Processing the database returns results, encapsulated as a map, and keyedhandler The only difference is that each row results in the form of JavaBean as value
Abstractlisthandler	Abstract class that processes the returned results into a linked list
Arraylisthandler	The returned results are processed into a linked list, each of which element is an object array that holds the corresponding row of data in the database
Columnlisthandler	If you want to take a single column of data, you can use this handler, the user specifies the column name, which returns the A list of columns
Maplisthandler	Unlike Arraylisthandler, each element of the list is a map, which represents a row of data in the database
Arrayhandler	Data processing of a row into an object array
Beanhandler	Processing a row into a Java bean
Beanlisthandler	When all data is processed into an list,list element, the Java bean
Maphandler	Process a row of results into a map
Maplisthandler	When all the results are processed into an list,list element, map
Scalarhandler	This class is often used to take a single piece of data, such as the total number of a data set, etc.
Row processing Components
Rowprocessor	Interface for processing a row of data in a database
Basicrowprocessor	Basic line Processor Implementation class
Beanprocessor	Convert database data to JavaBean through reflection
Tool class
Dbutils	Contains many JDBC tool methods

5.2 Execution Process

Both additions and deletions are required to call the Queryrunner method, so Queryrunner is the entrance to the execution. Each of its methods requires the user to provide connection, handler, SQL, and SQL parameters, and returns the result that the user wants, which may be a list, a javabean, or just an integer.

1, the query as an example, Queryrunner internal Query method will call the private method, first to create a PreparedStatement, then execute SQL to get resultset, and then use handler to process the results, and finally release the connection, the code is as follows:

 1 Private <T> T query (Connection conn, boolean closeconn, String sql, resultsethandler<t> rsh, Object ... par AMS) 2 throws SQLException {3 if (conn = = null) {4 throw new SQLException ("Null Connecti On "); 5} 6 7 if (sql = = null) {8 if (Closeconn) {9 close (conn); 10}1 1 throw new SQLException ("Null SQL statement"),}13 if (rsh = = Null) {if ( Closeconn) {close (conn);}18 throw new SQLException ("Null resultsethandler");         }20 PreparedStatement stmt = null;22 ResultSet rs = null;23 T result = null;24 25 try {stmt = this.preparestatement (conn, SQL);//Create Statement27 this.fillstatement (stmt, PA  Rams); Fill parameter: rs = This.wrap (Stmt.executequery ());  The RS is packaged with a result = Rsh.handle (RS);      Working with the resulting processor 30 31   } catch (SQLException e) {this.rethrow (E, SQL, params);                     Close (RS), PNS} finally {stmt (closeconn) {40 Close (conn);}42}43}44 return result;46}

2, each handler implementation class is based on an abstract class, see the code (take Abstractlisthandler as an example):

1     @Override 2 public     list<t> handle (ResultSet Rs) throws SQLException {3         list<t> rows = new Array List<t> (); 4         while (Rs.next ()) {5             Rows.Add (This.handlerow (RS)); 6         } 7         return rows; 8     } 9     /**11      * Row handler. Method converts current row into some Java object.12      *13      * @param rs <code>ResultSet</code> to Proce ss.14      * @return Row processing result15      * @throws SQLException error OCCURS16      */17     protected Abstract T Handlerow (ResultSet rs) throws SQLException;

Handle methods are the same, this method is also queryrunner internal execution method, but not the same in the implementation of Handlerow this method. The design pattern of the template method is used here,
The constant abstraction to the upper layer, variable to the lower level.

  3, each Handlerow implementation is different, but eventually will use the line processor components, the line processor is basicrowprocessor, there are toarray,tobean,tobeanlist,tomap these methods
ToArray and Tomap are implemented through the metadata of the database, and Tobean and tobeanlist are realized by reflection, and can be seen from the source code implementation, it should be better understood.

5.3. Integration with data sources
As can be seen from the above, Dbutils Abstracts 2, 3, 4 (JDBC 4 steps), without abstracting the connection, in fact, the acquisition and maintenance of the connection itself is provided by other components, that is, datasource
Data source, dbutils only responsible for 2, 3, 4, it should not be the tube, so as to achieve decoupling. When constructing the Queryrunner, you can choose to pass in a data source, so that when the method is called,
There is no need to pass in connection.


5.4. Summary
With Dbutils plus dbcp data sources, you can greatly simplify repetitive code, improve code readability and maintainability, and here's a small example of using dbutils:

1/** 2      * Get common Address 3      * */4 public     list<commonaddr> getcommaddrlist (int memid) {5         String sql = "Selec T ' Addrid ', ' addr ', ' phone ', ' receiver ', ' usedtime ', ' 6                 + ' from ' usr_cm_address ' WHERE ' memid ' =? ORDER BY usedtime Desc "; 7          8         try {9             return runner.query (SQL, New beanlisthandler<commonaddr> (Commonaddr.class), memid); 10         } catch (SQLException E1) {One             logger.error ("Getcommaddrlist error,e={}", E1);         }13         return null;14     }

If you write with the most primitive jdbc, it takes more than 10 lines of code to convert the database results into a list estimate.

6. Epilogue

From JDBC to dbutils, the functionality does not change, but the code is concise, and the relationship between the program and the program is clearer, which may be the essence of object-oriented.

Article derived from: http://www.cnblogs.com/biakia/p/4300275.html

Notes-jdbc and Commons-dbutils