利用模板模式重构JDBC操作业务场景

创建一个模板类JdbcTemplate，封装所有的JDBC操作。以查询为例，每次查询的表不同，返回的数据结构也就不一样。我们针对不同的数据，都要封装不同的实体
对象。而每个实体封装的逻辑都不一样的，但是封装前后的处理流程是不变的，因为，我们可以使用模板方法模式来设计这样的业务场景。先创建约束ORM逻辑的接口。

RowMapper

package JDBCTemplate;

import java.sql.ResultSet;

/**
 * Created by Lenovo on 2019-7-18.
 */
public interface RowMapper<T> {
    T mapRow(ResultSet rs,int rowRum) throws Exception;

    
}

在创建封装了所有处理流程的

抽象类JdbcTemplate

package JDBCTemplate;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * Created by Lenovo on 2019-7-18.
 */
public  abstract class JdbcTemplate {
    private DataSource dataSource;

    public JdbcTemplate(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    public List<?> excuteQuery (String sql,RowMapper<?> rowMapper,Object[] values){
        try {
            //1.获取连接
            Connection conn=this.getConnection();
            //2.创建语句集
            PreparedStatement pstm=this.createPrepareStatement(conn,sql);
            //3.执行语句集
            ResultSet rs=this.excuteQuery(pstm,values);
            //4.处理语句集
            List<?> result=this.parseResultSet(rs,rowMapper);
            //5.关闭结果集
            this.closeResultSet(rs);
            //6.关闭语句集
            this.closeStatement(pstm);
            //7.关闭连接
            this.closeConnection(conn);
            return result;

        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
    protected void closeConnection (Connection conn) throws  Exception{
        conn.close();
    }
    protected void closeStatement(PreparedStatement pstm) throws  Exception{
        pstm.close();
    }
    protected void closeResultSet(ResultSet rs) throws Exception{
        rs.close();
    }
    protected List<?>  parseResultSet(ResultSet rs,RowMapper<?> rowMapper) throws Exception{
        List<Object> result=new ArrayList<Object>();
        int  rowNum=1;
        while (rs.next()){
            result.add(rowMapper.mapRow(rs,rowNum++));
        }
        return result;
    }
    protected  ResultSet excuteQuery(PreparedStatement pstm,Object[] values) throws Exception{
        for(int i=0;i<values.length;i++){
            pstm.setObject(i,values[i]);
        }
        return pstm.executeQuery();
    }
    protected PreparedStatement createPrepareStatement(Connection conn,String sql) throws Exception{
        return conn.prepareStatement(sql);
    }
    public Connection getConnection() throws Exception{
        return this.dataSource.getConnection();
    }
}

创建实体对象Member类：

package JDBCTemplate;

/**
 * Created by Lenovo on 2019-7-18.
 */
public class Member {
    private String username;
    private String password;
    private String nickName;

    private int age;
    private String addr;

    public String getAddr() {
        return addr;
    }

    public void setAddr(String addr) {
        this.addr = addr;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getNickName() {
        return nickName;
    }

    public void setNickName(String nickName) {
        this.nickName = nickName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }
}

创建数据库操作类MemberDao：

package JDBCTemplate;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.util.List;

/**
 * Created by Lenovo on 2019-7-18.
 */
public class MemberDao extends JdbcTemplate {
    public MemberDao(DataSource dataSource) {
        super(dataSource);
    }
    public List<?> selectAll(){
        String sql="select * from t_member";
        return super.excuteQuery(sql, new RowMapper<Member>() {
            @Override
            public Member mapRow(ResultSet rs, int rowRum) throws Exception {
                Member member=new Member();
                //字段过多可以采取原型模式
                member.setUsername(rs.getString("username"));
                member.setPassword(rs.getString("password"));
                member.setAge(rs.getInt("age"));
                member.setAddr(rs.getString("addr"));
                return member;
            }
        },null);
    }
}

理解思想为上，在次略去测试类，整个过程在JDBC模板类中保留了不变的一些基本操作，将封装数据库的逻辑交由子类去实现，这也是模板类在实际业务中的一个运用体现。