JDBC的DML增删改查的代码重构设计（下）

JDBC的操作模板：

流程：1.创建ProductResultSetHandler对象：
     2.作为参数传递给query方法
     3.在query方法中：rsh.handler,其实调用的是ProductResultSetHanlder方法

结果集处理规范：

//定义一个专门的约束处理结果集的接口：ResultSetHandler：结果集处理器
public interface ResultSetHandler<T> {
    //同意规定，必须有一个处理结果集的方法：handle,处理之后返回结果
    T handle(ResultSet rs) throws SQLException;
}

调用的模板方法：

public class JdbcTemplate {

    private JdbcTemplate() {

    }

    /**
     * 
     *@param sql   DML各自的SQL,由调用者决定
     *@param params      DML操作需要的参数，由调用者决定
     *@return     受影响的行数
     */
    public static int update(String sql, Object... params) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtil.INSTANCE.getConn();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.INSTANCE.close(conn, ps, null);
        }
        return 0;
    }

    public static <T>T query(String sql, ResultSetHandler<T> rsh ,Object... params) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.INSTANCE.getConn();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            rs = ps.executeQuery();
            return rsh.handle(rs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.INSTANCE.close(conn, ps, rs);
        }
        return null;
    }

实现类的操作代码：

public class ProductDAOImpl implements IProductDAO {

    public void save(Product pro) {
        /*String sql = "insert into product (productName,brand,supplier,salePrice,costPrice,cutoff,dir_id ) values (?,?,?,?,?,?,?)";
        Object[] params = { pro.getProductName(), pro.getBrand(), pro.getSupplier(), pro.getSalePrice(),
                pro.getCostPrice(), pro.getCutoff(), pro.getDir_id() };
        JdbcTemplate.update(sql, params);*/
        //拓展Hibernate的save操作：
        HibernateMock.save(pro); 
    }

    public void delete(Long id) {
        String sql = "delete from product where id =  ? ";
        JdbcTemplate.update(sql, id);
    }

    public void update(Product pro) {
        String sql = "UPDATE product SET productName = ?, brand = ?, supplier = ?, salePrice = ?, costPrice = ?, cutoff = ?, dir_id = ?  WHERE id = ?";
        Object[] params = { pro.getProductName(), pro.getBrand(), pro.getSupplier(), pro.getSalePrice(),
                pro.getCostPrice(), pro.getCutoff(), pro.getDir_id(), pro.getId() };
        JdbcTemplate.update(sql, params);
    }

    public Product get(Long id) {
        String sql = "select * from product where id = ?";
        List<Product> list = JdbcTemplate.query(sql, new ProductResultHandler(),id);
        return list.size()==1? list.get(0):null;
    }

    public List<Product> list() {
        String sql = "SELECT * FROM product";
        return JdbcTemplate.query(sql,new ProductResultHandler());
    }

    //定义处理商品对象的结果集处理器
    class ProductResultHandler implements ResultSetHandler<List<Product>>{
        @Override
        public List<Product> handle(ResultSet rs) throws SQLException {
            List<Product> list = new ArrayList<>();
            while (rs.next()) {
                Product pro = new Product();
                list.add(pro);
                pro.setId(rs.getLong("id"));
                pro.setProductName(rs.getString("productName"));
                pro.setBrand(rs.getString("brand"));
                pro.setSupplier(rs.getString("supplier"));
                pro.setSalePrice(rs.getBigDecimal("salePrice"));
                pro.setCostPrice(rs.getBigDecimal("costPrice"));
                pro.setCutoff(rs.getDouble("cutoff"));
                pro.setDir_id(rs.getLong("dir_id"));
            }
            return list;
        }
    }

拓展Hibernate的save操作：

public class HibernateMock {

    /**
     * 保存操作
     * @param obj
     * 1.拼SQL
     * 2.设置封装参数
     */
    //约定优于配置：
    //要求：表中的列和对象的属性相同
    public static void save(Object obj){
        //使用内省机制获取JivaBean对象的属性
        try {
            BeanInfo beanInfo = Introspector.getBeanInfo(obj.getClass(),Object.class);
            PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();
            StringBuilder sqlBuilder = new StringBuilder();//拼接SQL
            List<Object> params = new ArrayList<>();//封装属性的值/参数值
            String tableName = obj.getClass().getSimpleName();
            sqlBuilder.append("INSERT INTO ").append(tableName).append("(");
            StringBuilder sign = new StringBuilder();//拼接占位符
            for (PropertyDescriptor pd : pds) {
                String propertyName = pd.getName();//属性名/表中的列名
                sqlBuilder.append(propertyName).append(",");//拼接列名
                sign.append("?").append(",");
                Object value = pd.getReadMethod().invoke(obj);//获取每个属性的值
                params.add(value);
            }
            sqlBuilder.deleteCharAt(sqlBuilder.length()-1);
            sign.deleteCharAt(sign.length()-1);
            sqlBuilder.append(")VALUES(").append(sign);
            sqlBuilder.append(")");
            //封装参数,执行SQL
            //调用之前编写的MDL操作模板
            JdbcTemplate.update(sqlBuilder.toString(),params.toArray());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

测试类

public class ProductDAOTest {
    private IProductDAO dao = new ProductDAOImpl();
    @Test
    public void testSave() {
        Product pro = new Product();
        pro.setProductName("iphone8s");
        pro.setBrand("apple");
        pro.setSupplier("苹果公司");
        pro.setSalePrice(new BigDecimal("7000"));
        pro.setCostPrice(new BigDecimal("2000"));
        pro.setCutoff(0.9);
        pro.setDir_id(3L);
        //pro.setId(12L);
        dao.save(pro);
    }

    @Test
    public void testDelete() {
        dao.delete(26L);
    }

    @Test
    public void testUpdate() {
        Product pro = new Product();
        pro.setProductName("iphone6s");
        pro.setBrand("apple9s");
        pro.setSupplier("苹果公司2");
        pro.setSalePrice(new BigDecimal("6000"));
        pro.setCostPrice(new BigDecimal("1000"));
        pro.setCutoff(0.8);
        pro.setDir_id(5L);
        pro.setId(8L);
        dao.update(pro);
    }

    @Test
    public void testGet() {
        Product pro = dao.get(6L);
        System.out.println(pro);
    }

    @Test
    public void testList() {
        List<Product> list = dao.list();
        for (Product p : list) {
            System.out.println(p);
        }
    }
}

JdbcUtil工具类

public enum JdbcUtil {

    INSTANCE;

    private static Properties p = new Properties();

    //只需要注册一次驱动即可，没必要每次都注册，放到jdbcutil类的静态代码块中（当字节码被加载进jvm，就会执行）
    static {
        try {
            //从classpath的根路径去加载db.properties文件
            InputStream inStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
            p.load(inStream);
            Class.forName(p.getProperty("driverClassName"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建connection对象
     * @return
     */
    public Connection getConn() {
        try {
            return DriverManager.getConnection(p.getProperty("url"), p.getProperty("usename"),p.getProperty("password"));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public void close(Connection conn, Statement st, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (conn != null) {
                        conn.close();
                    }

                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

db.properties文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcdemo?useSSL=false
usename=root
password=111111

商品对象

public class Product {
    private Long id;
    private String productName;
    private String brand;
    private String supplier;
    private BigDecimal salePrice;
    private BigDecimal costPrice;
    private Double cutoff;
    private Long dir_id;//分类编号
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getProductName() {
        return productName;
    }
    public void setProductName(String productName) {
        this.productName = productName;
    }
    public String getBrand() {
        return brand;
    }
    public void setBrand(String brand) {
        this.brand = brand;
    }
    public String getSupplier() {
        return supplier;
    }
    public void setSupplier(String supplier) {
        this.supplier = supplier;
    }
    public BigDecimal getSalePrice() {
        return salePrice;
    }
    public void setSalePrice(BigDecimal salePrice) {
        this.salePrice = salePrice;
    }
    public BigDecimal getCostPrice() {
        return costPrice;
    }
    public void setCostPrice(BigDecimal costPrice) {
        this.costPrice = costPrice;
    }
    public Double getCutoff() {
        return cutoff;
    }
    public void setCutoff(Double cutoff) {
        this.cutoff = cutoff;
    }
    public Long getDir_id() {
        return dir_id;
    }
    public void setDir_id(Long dir_id) {
        this.dir_id = dir_id;
    }
    @Override
    public String toString() {
        return "Product [id=" + id + ", productName=" + productName + ", brand=" + brand + ", supplier=" + supplier
                + ", salePrice=" + salePrice + ", costPrice=" + costPrice + ", cutoff=" + cutoff + ", dir_id=" + dir_id
                + "]";
    }
}