JDBC Learning Notes (iv)

There are several ways to reduce the duplication of code between the various DAO classes:

Write a dbconnectionmanager that makes the public query logic a way to pass the SQL statement as a parameter to the method.

 Public classdbconnectionmanager{Static{class.forname ("Com.mysql.jdbc.Driver"); } //Read Operation  Public Staticlist<map<string,object>> SelectObject (String sql, string[] params)throwsException {Connection conn=NULL; PreparedStatement pstmt=NULL; ResultSet RS=NULL; List<Map<String,Object>>> result =NewArraylist<map<string,object>>>(); Try{conn=dbconnectionmanager.getconnection (); Pstmt=conn.preparestatement (SQL);  for(inti = 0; Params! =NULL&& i < params.length; i++) {pstmt.setstring (i+ 1, Params[i]); } RS=Pstmt.executequery (); ResultSetMetaData Meta=Rs.getmetadata ();  while(Rs.next ()) {Map<String,Object> Columnvalue =NewHashmap<string,object>intSize =Meta.getcolumncount ();  for(inti = 1; I <= size; i++) {String columnName= Meta.getcolumnlabel (i);//getColumnName Returns the database column name, Getcolumnlabel returns the alias of the column if there is an alias, otherwise the same as getColumnNameColumnvalue.add (Columnname,rs.getobject (columnName));   } result.add (Columnvalue); }   returnresult; } Catch(Exception e) {//logger.info ("Execute sql:" + SQL + "fail!!!");   Throwe; } finally{dbconnectionmanager.free (conn, pstmt, RS); } }  //Adding and removing changes operation  Public Static voidUpdateobject (String sql, string[] params)throwsException {Connection conn=NULL; PreparedStatement pstmt=NULL; ResultSet RS=NULL; Try{conn=dbconnectionmanager.getconnection (); Pstmt=conn.preparestatement (SQL);  for(inti = 0; Params! =NULL&& i < params.length; i++) {Pstmt.setobject (i+ 1, Params[i]); } RS=pstmt.executeupdate (); } Catch(Exception e) {//logger.info ("Execute sql:" + SQL + "fail!!!");   Throwe; } finally{dbconnectionmanager.free (conn, pstmt, RS); } }  //a better approach is to fetch links from the database connection pool  Public StaticConnection getconnection () {String dbName= "NNM5"; String Passwrod= "OSSDB123"; String UserName= "Root"; String URL= "jdbc:mysql://localhost:13306/" +DbName; Connection Conn=drivermanager.getconnection (URL, username,passwrod); returnConn;}  Public Static voidFree (Connection conn,preparedstatement pstmt,resultset rs) {if(rs! =NULL) {               Try{rs.close (); } finally{if(PS! =NULL) {Try{ps.close (); }finally{if(Conn! =NULL) {conn.close (); } }}}}

The bad thing about this method is that the result of the return is LIST<MAP<STRING,OBJECT>> if you want to return objects like an ORM framework, you can implement a RowMapper, Similar to the simplejdbctemplate provided in spring.

There are two ways to add RowMapper to the code, the first is to use a template approach, and the second is to use a policy approach.

Examples of how to use templates are to modify the methods in the template class above:

 Public StaticList SelectObject (String sql, string[] params)throwsException {Connection conn=NULL; PreparedStatement pstmt=NULL; ResultSet RS=NULL; List<Map<String,Object>>> result =NewArraylist<map<string,object>>>(); Try{conn=dbconnectionmanager.getconnection (); Pstmt=conn.preparestatement (SQL);  for(inti = 0; Params! =NULL&& i < params.length; i++) {pstmt.setstring (i+ 1, Params[i]); } RS=Pstmt.executequery (); List List=NewArrayList (); intIndex=0;  while(Rs.next ()) {List.add (Objectmapper (RS)); //Pass the result set after subtracting one row at a timeindex++; }   returnlist; } Catch(Exception e) {//logger.info ("Execute sql:" + SQL + "fail!!!");   Throwe; } finally{dbconnectionmanager.free (conn, pstmt, RS); } } Public AbstractObject Objectmapper (ResultSet rs);

The code in the specific DAO is as follows:

 PublicList Getperson (Integer ID) {String SQL= "Select Id,name from the person where id<?"; Object [] Objs=NewObject[]{id}; return Super. GetObject (SQL,OBJS);//The Objectmapper in the class is actually called. }                     PublicObject Objectmapper (ResultSet rs) { person person=NewPerson ; Try{Person.setid (Integer) rs.getobject (1)); Person.setname (String) rs.getobject (2)); }Catch(Exception e) {Logger.log (E.printstacktrace ()); }             returnPerson ;}

　　

Use the policy mode to modify the code in the template class as follows:

First you need an interface:

 Public Interface RowMapper {    public  Object objectmapper (ResultSet rs);}

Then modify the template class:

 Public StaticList SelectObject (String sql, string[] params, RowMapper mapper)throwsException {Connection conn=NULL; PreparedStatement pstmt=NULL; ResultSet RS=NULL; List<Map<String,Object>>> result =NewArraylist<map<string,object>>>(); Try{conn=dbconnectionmanager.getconnection (); Pstmt=conn.preparestatement (SQL);  for(inti = 0; Params! =NULL&& i < params.length; i++) {pstmt.setstring (i+ 1, Params[i]); } RS=Pstmt.executequery (); List List=NewArrayList (); intIndex=0;  while(Rs.next ()) {List.add (Mapper.objectmapper (RS)); //Pass the result set after subtracting one row at a timeindex++; }   returnlist; } Catch(Exception e) {//logger.info ("Execute sql:" + SQL + "fail!!!");   Throwe; } finally{dbconnectionmanager.free (conn, pstmt, RS); } }

The code in the specific DAO:

 PublicList Getperson (Integer ID) {String SQL= "Select Id,name from the person where id<?"; Object [] Objs=NewObject[]{id}; returnMu.getobject (SQL,OBJS,NewMyRowMapper1 ());} classMyRowMapper1Implementsrowmapper{ PublicObject Objectmapper (ResultSet rs) { person person=NewPerson (); Try{Person.setid (Integer) rs.getobject (1)); Person.setname (String) rs.getobject (2)); }Catch(Exception e) {Logger.log (E.printstacktrace ()); }                     returnPerson ; }}

JDBC Learning Notes (iv)