Simplifying JDBC operations using the template method pattern

When using JDBC, many repetitive code is written repeatedly, such as

Connection conn =NULL; PreparedStatement PS=NULL; ResultSet RS=NULL; String SQL= "INSERT into T_user (username,brithday) VALUES (?,?)"; Try{conn=jdbcutils.getconnection (); PS=conn.preparestatement (SQL); } Catch(SQLException e) {Throw Newdaoexception (E.getmessage (), E); } finally{Jdbcutils.free (RS, PS, conn); }    

This part of the code will be available in the database operations method. So we can extract this part of the unchanging content as a common method.

For example, we increase, delete, change the operation can be written like this

/*** increase, delete, change the method *@paramSQL *@paramargs SQL parameter *@return     */     Public intUpdate (String sql, object[] args) {Connection conn=NULL; PreparedStatement PS=NULL; ResultSet RS=NULL; Try{conn=jdbcutils.getconnection (); PS=conn.preparestatement (SQL);  for(inti = 0; i < args.length; i++) Ps.setobject (i+ 1, Args[i]); returnps.executeupdate (); } Catch(SQLException e) {Throw Newdaoexception (E.getmessage (), E); } finally{Jdbcutils.free (RS, PS, conn); }    }

The most troublesome thing is to return the operation of an object. Because I don't know what object to return, I'm not sure when I set a value in the object. So we can define an abstract method within this class, specifically how it is implemented, and what its subclasses know.

So this class of ours can be designed like this.

 Packagecom.zzg.jdbc.base;Importjava.sql.Connection;Importjava.sql.PreparedStatement;ImportJava.sql.ResultSet;Importjava.sql.SQLException;Importjava.util.ArrayList;Importjava.util.List;Importcom.zzg.jdbc.exception.DaoException;Importcom.zzg.jdbc.util.JdbcUtils; Public Abstract classBasedao {/*** increase, delete, change the method *@paramSQL *@paramargs SQL parameter *@return     */     Public intUpdate (String sql, object[] args) {Connection conn=NULL; PreparedStatement PS=NULL; ResultSet RS=NULL; Try{conn=jdbcutils.getconnection (); PS=conn.preparestatement (SQL);  for(inti = 0; i < args.length; i++) Ps.setobject (i+ 1, Args[i]); returnps.executeupdate (); } Catch(SQLException e) {Throw Newdaoexception (E.getmessage (), E); } finally{Jdbcutils.free (RS, PS, conn); }    }    /*** Returns an object *@param<T> *@paramSQL *@paramargs *@return     */     Public<T>T Find (String sql, object[] args) {Connection conn=NULL; PreparedStatement PS=NULL; ResultSet RS=NULL; Try{conn=jdbcutils.getconnection (); PS=conn.preparestatement (SQL);  for(inti = 0; i < args.length; i++) Ps.setobject (i+ 1, Args[i]); RS=Ps.executequery (); T T=NULL; if(Rs.next ()) {T=RowMapper (RS); }            returnT; } Catch(SQLException e) {Throw Newdaoexception (E.getmessage (), E); } finally{Jdbcutils.free (RS, PS, conn); }    }    /*** Returns a list *@param<T> *@paramSQL *@paramargs *@return     */     Public<T> list<t>list (String sql, object[] args) {Connection conn=NULL; PreparedStatement PS=NULL; ResultSet RS=NULL; Try{conn=jdbcutils.getconnection (); PS=conn.preparestatement (SQL);  for(inti = 0; i < args.length; i++) Ps.setobject (i+ 1, Args[i]); RS=Ps.executequery (); T T=NULL; List<T> list =NewArraylist<t>();  while(Rs.next ()) {T=RowMapper (RS);            List.add (t); }            returnlist; } Catch(SQLException e) {Throw Newdaoexception (E.getmessage (), E); } finally{Jdbcutils.free (RS, PS, conn); }    }    Abstract protected<T> T RowMapper (ResultSet rs)throwsSQLException;}

Our class only needs to inherit the above class when it is used.

 PackageCom.zzg.jdbc.dao.impl;ImportJava.sql.ResultSet;Importjava.sql.SQLException;Importjava.util.List;ImportCom.zzg.jdbc.base.BaseDao;ImportCom.zzg.jdbc.dao.UserDao;ImportCom.zzg.jdbc.domain.User; Public classUserdaoimplextendsBasedaoImplementsUserdao {@Override PublicUser Finduser (intID) {String SQL= "Select *from t_user where id=?"; Object[] args=Newobject[] {ID}; User User=Super. Find (sql, args); returnuser; } @Override PublicList<user>Listuser (string username) {String sql= "Select *from t_user where Username=?"; Object[] args=Newobject[] {username}; List<User> list =Super. List (sql, args);  for(User u:list) {System.out.println (U.getid ()); }        returnlist; } @OverrideprotectedObject RowMapper (ResultSet rs)throwsSQLException {User User=NewUser (); User.setid (Rs.getint ("id")); User.setusername (Rs.getstring ("Username")); User.setbrithday (Rs.getdate ("Brithday")); returnuser; }}

Additional pagination methods:

/*** Pagination *@paramTableName *@paramPK *@paramMethodpagenum *@paramMethodnumperpage *@paramconditions *@param<T> *@return     * @throwsSQLException*/     Public<T> list<t> getpagelistresultset (String tablename,string PK,intMethodpagenum,intmethodnumperpage,map<string,string> conditions)throwssqlexception{intPagenum = methodpagenum==0?Default_page_num:methodpagenum; intNumperpage = methodnumperpage==0?Default_num_per_page:methodnumperpage; List<T> list =NULL; Datasetop Datasetop=NULL; Try{datasetop=NewDatasetop (); String Page_sql_prefix= "SELECT * from" (select Row_number () over (ORDER by T1.id DESC) as Row_num, t1.* from "; String Page_sql_end= ") TT WHERE TT. row_num;? and TT. Row_num <=? "; if(Stringutil.isnotblank (PK)) {Page_sql_prefix= Page_sql_prefix.replace ("ID", PK); } StringBuffer SQL=NewStringBuffer (Page_sql_prefix); Sql.append (TableName). Append ("T1 WHERE 1=1"); //Set Conditions            if(conditions!=NULL&& conditions.size () >0) {Set<String> key =Conditions.keyset ();  for(Iterator it =key.iterator (); It.hasnext ();) {String column=(String) it.next (); //System.out.println (Conditions.get (s));StringBuffer CD =NewStringBuffer ("and T1.")); Cd.append (column). Append ("="). Append (conditions.get (column)). Append ("'");                Sql.append (CD);            }} sql.append (Page_sql_end);            SYSTEM.OUT.PRINTLN (SQL); PreparedStatement PS=datasetop.getconnection (). Preparestatement (Sql.tostring ()); Ps.setint (1, (pageNum-1) *numperpage); Ps.setint (2, pagenum*numperpage); ResultSet RS=Ps.executequery (); T T=NULL; List=NewArraylist<t>();  while(Rs.next ()) {T=RowMapper (RS);            List.add (t); }        } Catch(dataexception e) {e.printstacktrace (); }finally{            if(Datasetop! =NULL) Datasetop.close (); }        returnlist; }

Simplifying JDBC Operations using template method mode