JDBC Learning notes-writing common query methods using reflection and JDBC meta-data

Our query operations, for different data tables Examstudent and customers, there will be different code writing process, using reflection and JDBC metadata can write a common method to query different data tables.

We did this before:

Query the fields in the Customers table and the field values:

1  PublicCustomer GetCustomer (String sql, Object ... args) {2Customer customer =NULL;3Connection Connection =NULL;4PreparedStatement PreparedStatement =NULL;5ResultSet ResultSet =NULL;6         Try {7Connection =jdbctools.getconnection ();8PreparedStatement =connection.preparestatement (SQL);9              for(inti = 0; i < args.length; i++) {TenPreparedstatement.setobject (i + 1), Args[i]); One             } AResultSet =preparedstatement.executequery (); -             if(Resultset.next ()) { -                 //student = new Student (Resultset.getint (1), the                 //Resultset.getint (2), -                 //resultset.getstring (3), resultset.getstring (4), -                 //resultset.getstring (5), resultset.getstring (6), -                 //Resultset.getint (7)); +Customer =NewCustomer (); -Customer.setid (Resultset.getint (1)); +Customer.setname (Resultset.getstring (2)); ACustomer.setemail (Resultset.getstring (3)); atCustomer.setbirth (Resultset.getdate (4)); -             } -}Catch(Exception e) { - e.printstacktrace (); -}finally { - jdbctools.release (ResultSet, PreparedStatement, connection); in         } -         returncustomer; to  +}

Query the fields in the Examstudent table and the field values:

1  PublicStudent getstudent (String sql, Object ... args) {2Student Student =NULL;3Connection Connection =NULL;4PreparedStatement PreparedStatement =NULL;5ResultSet ResultSet =NULL;6         Try {7Connection =jdbctools.getconnection ();8PreparedStatement =connection.preparestatement (SQL);9              for(inti = 0; i < args.length; i++) {TenPreparedstatement.setobject (i + 1), Args[i]); One             } AResultSet =preparedstatement.executequery (); -             if(Resultset.next ()) { -Student =NewStudent (Resultset.getint (1), Resultset.getint (2), theResultset.getstring (3), resultset.getstring (4), -Resultset.getstring (5), resultset.getstring (6), -Resultset.getint (7)); -             } +}Catch(Exception e) { - e.printstacktrace (); +}finally { A jdbctools.release (ResultSet, PreparedStatement, connection); at         } -         returnstudent; -  -}

Meta Data: Metadata is the data that describes the data

Can see two operations have a common step, different local code similarity is also very high, so we can write a common method on this basis to implement our query data table operation.

Yuan

/**      * ResultSetMetaData:      * 1). is a metadata object that describes the resultset, that is, from which you can get to the number of columns in the result set, what the column name is ...     * 2). How to use it? 1. Get ResultSetMetaData object: Call ResultSet's GetMetaData () method     * 2.ResultSetMetaData What are the good methods?     *      * >int getColumnCount (): Which columns are included in the SQL statement      * >string getcolumn (int column): Gets the alias of the specified column, where the index starts at 1     *       */

The specific code implementation:

1  Public<T> T Get (class<t>classz, String sql, Object ... args) {2T entity =NULL;3Connection Connection =NULL;4PreparedStatement PreparedStatement =NULL;5ResultSet ResultSet =NULL;6         Try {7Connection =jdbctools.getconnection ();8PreparedStatement =connection.preparestatement (SQL);9              for(inti = 0; i < args.length; i++) {TenPreparedstatement.setobject (i + 1), Args[i]); One             } AResultSet =preparedstatement.executequery (); -  -ResultSetMetaData RSMD =Resultset.getmetadata (); themap<string, object> values =NewHashmap<string, object>(); -             if(Resultset.next ()) { -                  for(inti = 0; I < Rsmd.getcolumncount (); i++) { -String ColumnLabel = Rsmd.getcolumnlabel (i + 1); +Object Columnvalue =Resultset.getobject (ColumnLabel); - values.put (ColumnLabel, columnvalue); +                 } A             } at             if(Values.size () > 0) { -entity =classz.newinstance (); -                  for(Map.entry<string, object>Entry:values.entrySet ()) { -String FieldName =Entry.getkey (); -Object Fieldvalue =Entry.getvalue (); - reflectionutils in . SetFieldValue (Entity, FieldName, fieldvalue); -                 } to             } +}Catch(Exception e) { - e.printstacktrace (); the}finally { * jdbctools.release (ResultSet, PreparedStatement, connection); $         }Panax Notoginseng         //returns the entity class object that contains the query information -         returnentity; the}

The Get method is written to implement the steps:

/**      * 1. Use SQL to query, get result set     * 2. Create an object of an entity class with reflection: Create a Student Object     * 3. Gets the alias of the column of the result set: Idcard, Studentname      * 4. Then get the value of each column of the result set, combined with 3 to get a map, the key is the alias of the column: value; The value of the column: {flowid:5,type=6,idcard:xxx ...} The alias of the column is the same as the attribute field name of our entity class     * 5. Then use the corresponding property assignment for reflection 2   Properties: Map key   property value: Map's key     value * /

Let's write a @test method to test:

1 @Test2      Public voidTestget () {3String sql = "Select Id,name,email,birth"4+ "from Customers where id=?";5 System.out.println (SQL);6Customer customer = Get (customer.class, SQL, 3);7 System.out.println (customer);8sql = "Select flow_id flowid,type, Idcard idcard, Exam_card examcard,"9+ "Student_name Studentname,location,grade from Examstudent where"Ten+ "Flow_id=?"; One System.out.println (SQL); AStudent Student = Get (Student.class, SQL, 3); - System.out.println (student); -}

Operation Result:

Select Id,name,email,birth from  customers where id=?  Customer [ID=3, NAME=ABCD, [email protected], birth=1992-06-07]select  flow_id flowid,type, Idcard Idcard, Exam_card examcard,student_name Studentname,location,grade from examstudent where  flow_id=? Student [Flowid=3, type=6, idcard=371522199206078411, examcard=2015534083, Studentname=li, Location=dalian, GRADE=87]

JDBC Learning notes-writing common query methods using reflection and JDBC meta-data