Using reflection and ResultSetMetaData to realize the basic functions of dbutils

Dbutils greatly simplifies the writing of JDBC, greatly improves development efficiency, and simplifies the process of JDBC development with database connection pooling. A simple custom database connection pool can be easily implemented by Decorator design mode and dynamic proxy mode, so how should dbutils be implemented? In order to understand the process of Dbutils's internal work, I implemented a Dbutils tool class to implement some simple update and query operations.

ResultSetMetaData is an object that can get the column type and property information of a ResultSet object. There are many methods in this class, in this case, only two: getColumnCount (): Gets the number of columns in the resultset result set. getcolumnname (int column): Gets the column name based on the specified number of columns. With these two methods you can do it yourself to achieve a simple version of the Dbutils-the following is my implementation of the steps:

1. Write the Myqueryrunner executeupdate method.

This method is very simple to write, because the connection can be obtained through DataSource, which is a simple JDBC operation inside the method. Note that you need to manually set the incoming parameters into PreparedStatement. The code is as follows:

 Public intUpdate (String sql, Object ... params) {Connection Connection=NULL; PreparedStatement PreparedStatement=NULL; Try{Connection=datasource.getconnection (); PreparedStatement=connection.preparestatement (SQL);  for(inti=0;i<params.length;i++) {Preparedstatement.setobject ((i+1), params[i]);//set the parameters.            }            intX=preparedstatement.executeupdate ();//perform the update operation.            returnx; } Catch(SQLException e) {Throw NewRuntimeException (e); }        finally {            if(preparedstatement!=NULL) {                Try{preparedstatement.close (); } Catch(SQLException e) {e.printstacktrace (); }            }            if(connection!=NULL) {                Try{connection.close (); } Catch(SQLException e) {e.printstacktrace (); }            }        }    }

　2. Write the Myqueryrunner ExecuteQuery method.

The implementation of this method is not difficult, because we give the important code to the incoming Resultsethandler implementation object to handle. The code is as follows:

//queries can be tricky. //make three implementations of Beanhandler Beanlisthandler Maplisthandler      Public<T> T query (String SQL, resultsethandler<t>rsh, Object ... params) {Connection Connection=NULL; PreparedStatement PreparedStatement=NULL; ResultSet RS=NULL; Try{Connection=datasource.getconnection (); PreparedStatement=connection.preparestatement (SQL); if(params!=NULL) {                     for(inti=0;i<params.length;i++) {Preparedstatement.setobject ((i+1), params[i]); }} RS=Preparedstatement.executequery (); returnRsh.handle (RS);//handing over to processor processing}Catch(SQLException e) {Throw NewRuntimeException (e); }            finally {                if(rs!=NULL) {                    Try{rs.close (); } Catch(SQLException e) {e.printstacktrace (); }                }                if(preparedstatement!=NULL) {                    Try{preparedstatement.close (); } Catch(SQLException e) {e.printstacktrace (); }                }                if(connection!=NULL) {                    Try{connection.close (); } Catch(SQLException e) {e.printstacktrace (); }                }            }     }

　　3.3 processors are written.

In the implementation of the Myqueryrunner implementation of the custom query, I implemented three processors, respectively, using the Beanhandler,beanlisthandler,maplisthandler implementation idea, and gave the most simple to understand ( It's actually because I don't have enough = =) implementation. The following is the implementation of the Beanhandler process, the implementation of handler is basically the same, nothing more than to use reflection to obtain the element object, and the data encapsulated in.

There are two member variables in Beanhandler, a T-T used as the JavaBean to be returned, declared outside for encapsulation data use, a class type object, used to get class, to create JavaBean object to assign to T, Create a Field object to assign a value to a member variable of T. In the implementation of the method, first get the number of columns by getColumnCount method, traverse each column, get the column name through getColumnName (int columnCount) method, Use the Class object's GetField method to get the Field object (here the JavaBean value must be the same as in the database!), and then use the Field object's set method to assign a value. Of course, the member variable corresponding to the Field object must be private ( Characteristics of the JavaBean.) Therefore, the Setaccessable method needs to be called first. The specific code is as follows:

 Public classMybeanhandler<t>Implementsresultsethandler{PrivateClass<t>type; T T;//JavaBean required for encapsulation     PublicMybeanhandler (class<t>type) {         This. type=type; } @Override PublicObject handle (ResultSet RS)throwsSQLException {Try{T=type.newinstance (); ResultSetMetaData MetaData=Rs.getmetadata (); intCount=metadata.getcolumncount ();//gets the number of columns of data in the ResultSetRs.next ();//Move Pointer//iterate through the column names of each column and set the value using the reflection mechanism             for(inti=1;i<=count;i++) {String name=Metadata.getcolumnname (i); Object obj=Rs.getobject (i); Field field=Type.getdeclaredfield (name); Field.setaccessible (true); Field.set (t, obj);//encapsulating data into JavaBean            }        } Catch(Exception e) {e.printstacktrace (); }        returnT; }}

　　Mybeanlisthandler implementation steps, basic and Beanhandler consistent. The only difference is that the next method of the result set is used to traverse each record, and the creation of the object is moved into the loop. The list object returned here is considered to be fewer additions and deletions, so the ArrayList is used. The implementation is as follows:

 Public classMybeanlisthandler<t>ImplementsResultsethandler<list<t>>{    PrivateClass<t>type; List<T> list=NewArraylist<t> ();//may fetch more. Therefore , the use of ArrayList         PublicMybeanlisthandler (class<t>type) {         This. Type =type; } @Override PublicList<t> handle (ResultSet RS)throwsSQLException {Try{resultsetmetadata MetaData=Rs.getmetadata (); intCount=Metadata.getcolumncount ();  while(Rs.next ()) {T T=type.newinstance ();  for(inti=1;i<=count;i++) {String name=metadata.getcolumnname (i);//This method gets the column name. Gets a list of field names. For example Name,age ...Object Obj=rs.getobject (i);//Get field valuesField field = Type.getdeclaredfield (name);//Get Field ObjectField.setaccessible (true); Field.set (t, obj);//Setting the value} list.add (t); }            returnlist; } Catch(Exception e) {Throw NewRuntimeException (e); }    }}

　　Maplist seems the most complex, but in reality it is most convenient to implement without generics and reflection, and the code is as follows:

 Public classMymaplisthandlerImplementsResultsethandler<list<map<string, object>>> {    PrivateList<map<string,object>> Data=NewArraylist<>(); @Override PublicList<map<string, object>> handle (ResultSet RS)throwsSQLException {resultsetmetadata metaData=Rs.getmetadata (); intCount=Metadata.getcolumncount ();  while(Rs.next ()) {Map<String,Object> map=NewHashmap<>();  for(inti=1;i<=count;i++) {Object value=Rs.getobject (i); String name=Metadata.getcolumnname (i);            Map.put (name, value);        } data.add (map); }        returndata; }}

Using reflection and ResultSetMetaData to realize the basic functions of dbutils