Java Web Learning Summary (23)-------------------Writing your own JDBC framework

Introduction of meta-data

Metadata refers to the definition of "database", "table", and "column".

1.1. DatabaseMetaData Meta-data

Connection.getdatabasemetadata () Gets the DatabaseMetaData object that represents the DatabaseMetaData metadata.
Common methods for DatabaseMetaData objects:

• GetURL (): Returns a String class object that represents the URL of the database.
• GetUserName (): Returns the user name that is connected to the current database management system.
• Getdatabaseproductname (): Returns the product name of the database.
• Getdatabaseproductversion (): Returns the version number of the database.
• Getdrivername (): Returns the name of the driver driver.
• Getdriverversion (): Returns the version number of the driver.
• IsReadOnly (): Returns a Boolean value that indicates whether the database allows only read operations.

 1/** 2 * @Method: Testdatabasemetadata 3 * @Description: Get meta information for the database 4 * @Anthor: Aloof Wolf 5 * 6 * @thr  OWS SQLException 7 */8 @Test 9 public void Testdatabasemetadata () throws SQLException {Ten Connection conn = Jdbcutils.getconnection (); DatabaseMetaData metadata = Conn.getmetadata ();//geturl (): Returns a Stri Ng class object representing the URL13 System.out.println (Metadata.geturl ()) of the database;//getusername (): Returns the user name that is connected to the current database management system SYS Tem.out.println (Metadata.getusername ());//getdatabaseproductname (): Returns the product name of the database System.out.println (meta Data.getdatabaseproductname ());//getdatabaseproductversion (): Returns the version number of the database System.out.println (metadata.ge Tdatabaseproductversion ());//getdrivername (): Returns the name of the driver driver System.out.println (Metadata.getdrivername ()) //getdriverversion (): Returns the driver version number System.out.println (Metadata.getdriverversion ());//isreado Nly (): Returns a Boolean value indicating that the databaseWhether to allow only read operations System.out.println (metadata.isreadonly ()); Jdbcutils.release (conn, NULL, NULL); 27} 

The results of the operation are as follows:

　　

1.2. Parametermetadata Meta-data

Preparedstatement.getparametermetadata () Gets the Parametermetadata object that represents the PreparedStatement metadata.
Select * from user where name=? and password=?
Common methods for Parametermetadata objects:

• GetParameterCount (): Gets the number of specified arguments
• Getparametertype (int param): Gets the SQL type of the specified parameter, MySQL database driver does not support

1     /** 2     * @Method: Testparametermetadata 3     * @Description: Get parameter meta information 4     * @Anthor: Aloof Wolf 5     * 6     * @thro WS SQLException 7     *     /8 @Test 9 public     void Testparametermetadata () throws SQLException {Ten         Connection conn = Jdbcutils.getconnection ();         String sql = "SELECT * from user wherer name=?" and password=? ";         //pre-compile SQL         PreparedStatement st = conn.preparestatement (SQL);         parametermetadata PM = St.getparametermetadata ();         //getparametercount () Gets the number of specified arguments,         System.out.println ( Pm.getparametercount ());         //getparametertype (int param): Gets the SQL type of the specified argument, MySQL database driver does not support         System.out.println (Pm.getparametertype (1));         jdbcutils.release (conn, NULL, NULL);     

1.3. ResultSetMetaData meta-data

ResultSet. GetMetaData () Gets the ResultSetMetaData object that represents the metadata for the ResultSet object.
Common methods for ResultSetMetaData objects:

• getColumnCount () returns the number of columns of the ResultSet object
• getColumnName (int column) Gets the name of the specified column
• Getcolumntypename (int column) Gets the type of the specified column

1     /** 2     * @Method: Testresultsetmetadata 3     * @Description: Meta data for result set 4     * @Anthor: Aloof Wolf 5     * 6     * @thro WS Exception 7     *     /8 @Test 9 public     void Testresultsetmetadata () throws Exception {Ten         Connection conn = Jdb Cutils.getconnection (); one         String of sql = "SELECT * from Account";         PreparedStatement st  = Conn.preparestatement (SQL);         ResultSet rs = St.executequery ();         //resultset.getmetadata () Get ResultSetMetaData object representing ResultSet object metadata         ResultSetMetaData metadata = Rs.getmetadata ();         getColumnCount () returns the number of columns of the ResultSet object,         System.out.println (Metadata.getcolumncount ());         //getcolumnname ( int column) Gets the name of the specified column         System.out.println (metadata.getcolumnname (1));         //getcolumntypename (int column) Gets the type of the specified column         System.out.println (metadata.getcolumntypename (1));         Jdbcutils.release (Conn, St, RS); 23     }

Ii. encapsulating a simple JDBC framework using metadata

All entity objects in the system involve basic CRUD operations
The cud operation code for all entities is basically the same, and the SQL statements sent to the database are different, so you can extract all the same code for the cud operation into an update method of the tool class and define the SQL statement that the parameter receives the change.
The r operation of an entity, in addition to the SQL statements, differs from the entity in which the ResultSet is mapped, so it is possible to have a query method, divided by the Parameter form, to receive the changed SQL statement. The policy mode can be used by the caller of the Qurey method to determine how the data in the resultset is mapped to the entity object.

2.1. Package general Update method and Qurey method

Defines a jdbcutils tool class, which is responsible for obtaining database connections, freeing resources, and performing SQL update and query operations, with the following code:

  1 package me.gacl.util;  2 3 Import Java.io.InputStream;  4 Import java.sql.Connection;  5 Import Java.sql.DriverManager;  6 Import java.sql.PreparedStatement;  7 Import Java.sql.ResultSet;  8 Import java.sql.SQLException; 9 Import java.sql.Statement; Ten import java.util.Properties; public class Jdbcutils {$ private static string driver = NULL, and the private static string url = null; 1 6 private static String username = null; $ private static String password = NULL; static{try{21//Read the database connection information in the Db.properties file at InputStream in = Jdbcuti Ls.class.getClassLoader (). getResourceAsStream ("db.properties"); Properties prop = new properties (); Prop.load (in); 25 26//Get database connection Driver Driver = prop.getproperty ("Driver"); 28//Get database connection URL Address url = prop.getproperty ("url"); 30//Get database connection user name username = PROP.GEtproperty ("username"); 32//Get database connection Password Password = prop.getproperty ("password"); 34 35//LOAD Database drive Class.forName (driver);      Panax Notoginseng}catch (Exception e) {$ throw new Exceptionininitializererror (e); 40} 41 /** * @Method: getconnection * @Description: Get database Connection Object * @Anthor: Aloof Wolf 47 * * @return Connection Database Connection Object * @throws SQLException * */public static Connection getconnection ( ) throws sqlexception{drivermanager.getconnection return (URL, username,password); 53} 54 55/** * @Method: Release * @Description: Free resources, 58 * The resources to be freed include the connection database connection object, which is responsible for executing the statement object of the SQL command, storing the query results ResultSet Object * @Anthor: Lonely Wolf * @param conn * @param st @param RS * * * ublic static void release (Connection conn,statement St,resultset rs) {IF (rs!=null) {try{68//Close ResultSet object that stores query results rs.close (); 70} catch (Exception e) {e.printstacktrace ();                 St!=null {try{77//Close the statement object responsible for executing the SQL command 78 St.close ();          }catch (Exception e) {e.printstacktrace (); 81} 82} 83 84             if (conn!=null) {try{86//Close Connection Database Connection object Conn.close (); 88      }catch (Exception e) {e.printstacktrace (); 90} 91} 92} 93 94/** * @Method: Update $ * @Description: Universal Update 97 * The CUD operation code for all entities is basically the same, only the SQL statements sent to the database are different, 98 * Therefore can be To extract all the same code for the cud operation into an update method of the tool class, and define the SQL statement that the parameter receives the change. * @Anthor: Aloof from the wolves * @param sql to execute SQL101 * @param params Parameters used when executing SQL 102 * @throwsSQLEXCEPTION103 */104 public static void update (String sql,object params[]) throws sqlexception{105 Conne             Ction conn = null;106 PreparedStatement st = null;107 ResultSet rs = null;108 try{109 conn = getconnection (); st = conn.preparestatement (SQL); 111 for (int i=0;i<params.length;i++)         {St.setobject (i+1, params[i]); 113}114th.executeupdate (); 115 116 }FINALLY{117 release (conn, St, RS) 118}119}120 121/**122 * @Method: Query12 3 * @Description: Universal Query 124 * Entity's R operation, in addition to the SQL statements, depending on the entity of the operation, the mapping of the resultset is different, 125 * Therefore a query method, divided by the form of the parameter to receive the change of the SQL statement , the policy mode can be used by the caller of the Qurey method to determine how the data in the resultset is mapped to the entity object. 126 * @Anthor: Aloof and pale Wolf 127 *128 * @param sql to execute SQL129 * @param params parameters used when executing SQL * @param the results of the RSH query returned Set Processor 131 * @return132 * @throws SQLException133 */134 public static Object query (String Sql,object Params[],resultsethandler rsh) throws sqlexception{135 136 Connection conn = null;137 PreparedStatement st = null;138 ResultSet rs = null;139 try{141 conn = Getco                 Nnection (); 142nd = conn.preparestatement (SQL); 143 for (int i=0;i<params.length;i++) {144              St.setobject (i+1, params[i]); 145}146 rs = st.executequery (); 147/**148              * For the result set processing returned by the query using the policy mode, 149 * When designing the Query method, the query method cannot know beforehand how the user handles the returned query result set, that is, the processing strategy of the result set is not known, 150 * Then the processing policy of the result set is provided by the user, and the query method internally invokes the user-submitted result set processing policy for processing 151 * In order to enable the user to provide a processing strategy for the result set, a result set processing interface needs to be exposed to the user Resultsethandl er152 * As long as the user implements the Resultsethandler interface, the query method internally knows how the user will handle the result set 153 */154 return Rsh.hand Ler (RS); 155 156}finally{157 Release (conn, St, RS); 158}159}160}

When the Query method is designed, the policy mode is used for the result set processing returned by queries, and the query method cannot know beforehand how the user handles the returned query result set, that is, the processing policy of the result set is not known, so the processing strategy of the result set is provided by the user. The query method is processed internally by invoking a user-submitted result set processing policy, in order to enable the user to provide a processing policy for the result set, a result set processing interface Resultsethandler is exposed to the user. The result set processor interface Resultsethandler is defined as follows:

1 package me.gacl.util; 2  3 import java.sql.ResultSet; 4  5/** 6 * @ClassName: Resultsethandler 7 * @Description: Result set Processor Interface 8 * @author: Aloof Wolf 9 * @date: 2014-10-5 PM 12:01:2710 *11 * * * public interface Resultsethandler {     /**15     * @Method: Handle R16     * @Description: Result set processing method     * @Anthor: Aloof and pale wolf     *19     * @param rs Query result set     * @return21 */     22 Public     Object Handler (ResultSet RS); 23}

As long as the user implements the Resultsethandler interface, a processor is written for the query result set, and the user's own written processor is called within the querying method to process the result set.

2.2, write the common result set processor

To improve the ease of use of the framework, we can write some common processors for the result set beforehand, such as converting the result set to the processor of the Bean object, and converting the result set to the processor of the list collection of Bean objects.

2.2.1, beanhandler--Converts the result set to the processor of the Bean object

 1 package me.gacl.util; 2 3 Import Java.lang.reflect.Field; 4 Import Java.sql.ResultSet; 5 Import Java.sql.ResultSetMetaData; 6 7/** 8 * @ClassName: Beanhandler 9 * @Description: The processor that transforms the result set into a Bean object * @author: Aloof Wolf each * @date: 2014-10-5 12:00:33 *13 * * * public class Beanhandler implements Resultsethandler {The private class<?> clazz;16 public Beanh         Andler (class<?> clazz) {this.clazz = clazz;18}19 public Object Handler (ResultSet rs) {20 try{21 if (!rs.next ()) {return null;23}24 Object bean = clazz.newins Tance (); 25//Get result set meta data resultsetmetadata metadata = Rs.getmetadata (); int Columncou NT = Metadata.getcolumncount ();//Get a few columns of data in the result set (int i=0;i<columncount;i++) {String cou                 Lmnname = Metadata.getcolumnname (i+1);//Gets the column name of each column in Object coulmndata = Rs.getobject (i+1); 31 Field f = clazz.gEtdeclaredfield (coulmnname);//reflect the class name corresponding to the property of F.setaccessible (true); F.set (Bean, Coulmnda TA);}35 return bean;36}catch (Exception e) {Notoginseng throw new RuntimeException ( e); 38}39}40}

2.2.2, beanlisthandler--the processor that transforms the result set into a list collection of Bean objects

 1 package me.gacl.util; 2 3 Import Java.lang.reflect.Field; 4 Import Java.sql.ResultSet; 5 Import Java.sql.ResultSetMetaData; 6 Import java.util.ArrayList; 7 Import Java.util.List;  8 9/**10 * @ClassName: BeanListHandler11 * @Description: The processor that transforms the result set into a list collection of Bean objects * @author: Aloof Wolf * @date: 2014-10-5     PM 12:00:0614 *15 * * * public class Beanlisthandler implements Resultsethandler {+ Private class<?> clazz;18 Public Beanlisthandler (class<?> clazz) {this.clazz = clazz;20}21 The public Object handler ( ResultSet rs) {try{24 list<object> List = new arraylist<object> (); Rs.next ()) {$ Object bean = clazz.newinstance (); ResultSetMetaData m Etadata = Rs.getmetadata (); int count = Metadata.getcolumncount (); (int I=0;i<co         unt;i++) {String name = Metadata.getcolumnname (i+1); 32            Object value = rs.getobject (name); Field f = Bean.getclass (). getd                 Eclaredfield (name); F.setaccessible (true); F.set (bean, value); 37         }38 List.add (Bean),}40 return list.size () >0?list:null;41 42 }catch (Exception e) {runtimeexception (e); 44}45}46}

When the framework itself provides a result set processor that does not meet the user's requirements, then the user can implement the Resultsethandler interface on their own, writing a result set processor that satisfies their business requirements.

With the Jdbcutils framework described above, CRUD operations on a single Entity object are very handy, as shown here:

 1 package Me.gacl.dao; 2 3 Import java.sql.SQLException; 4 Import java.util.List; 5 Import Me.gacl.domain.Account; 6 Import Me.gacl.util.BeanHandler; 7 Import Me.gacl.util.BeanListHandler; 8 Import Me.gacl.util.JdbcUtils; 9 public class Accountdao {one-page public void-Add (account account) throws sqlexception{13 String sql = "Inse RT into account (Name,money) VALUES (?,?) ";     + Object params[] = {account.getname (), Account.getmoney ()};15 jdbcutils.update (SQL, params); 16}17 public void Delete (int id) throws sqlexception{20 String sql = "Delete from account where id=?"; Object params[] = {id};22 jdbcutils.update (SQL, params);}24 public void Update (Accoun T account) throws sqlexception{26 Strings sql = "Update account set name=?,money=?" where id=? "; params[Object] = {account.getname (), Account.getmoney (), Account.getid ()};29 jdbcutils.update (SQL, param         s); 30}32 Public Account find (Int. ID) throws sqlexception{34 String sql = "SELECT * From account where Id=? "; Params[] = {id};36 return (account) jdbcutils.query (SQL, params, new Beanhandler (Account.class)) ; PNs}38 list<account> GetAll () throws sqlexception{40 String sql = "SELECT * FROM Acco Unt "; params[" = {};42 return (list<account>) jdbcutils.query (SQL, Params,new Beanlisthandl ER (account.class)); 43}44}

Java Web Learning Summary (23)-------------------Writing your own JDBC framework