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

1 设计商品对象

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 String getProductName() {        return productName;    }    public String getBrand() {        return brand;    }    public String getSupplier() {        return supplier;    }    public BigDecimal getSalePrice() {        return salePrice;    }    public BigDecimal getCostPrice() {        return costPrice;    }    public Double getCutoff() {        return cutoff;    }    public Long getDir_id() {        return dir_id;    }    public void setId(Long id) {        this.id = id;    }    public void setProductName(String productName) {        this.productName = productName;    }    public void setBrand(String brand) {        this.brand = brand;    }    public void setSupplier(String supplier) {        this.supplier = supplier;    }    public void setSalePrice(BigDecimal salePrice) {        this.salePrice = salePrice;    }    public void setCostPrice(BigDecimal costPrice) {        this.costPrice = costPrice;    }    public void setCutoff(Double cutoff) {        this.cutoff = cutoff;    }    public void setDir_id(Long dir_id) {        this.dir_id = dir_id;    }    @Override    public String toString() {        return "Product [id=" + id + ", productName=" + productName + ", brand=" + brand + ", suppliet=" + supplier                + ", salePrice=" + salePrice + ", costPrice=" + costPrice + ", cutoff=" + cutoff + ", dir_id=" + dir_id                + "]";    }}

2 设计实现DAO接口

import java.util.List;import cn.itsource._05_.shopping.domain.Product;public interface IProductDAO {    void save(Product pro);    void delete(Long id);    void update(Product pro);//long id 包装进去product    Product get(Long id);    List<Product> list();}

3 写增删改查的实现类

public class ProductDAOImpl implements IProductDAO {    public void save(Product pro) {        Connection conn = null;        Statement st = null;        try {            conn = JdbcUtil.INSTANCE.getConn();            st = conn.createStatement();            //使用StringBuilder可以减少性能的损耗，不然每次都要创建一个对象然后更改数据！            StringBuilder sql = new StringBuilder();            sql.append("insert into product (productName,brand,supplier,salePrice,costPrice,cutoff,dir_id ) values (");            sql.append("'").append(pro.getProductName()).append("'").append(",");            sql.append("'").append(pro.getBrand()).append("'").append(",");            sql.append("'").append(pro.getSupplier()).append("'").append(",");            sql.append("'").append(pro.getSalePrice()).append("'").append(",");            sql.append("'").append(pro.getCostPrice()).append("'").append(",");            sql.append("'").append(pro.getCutoff()).append("'").append(",");            sql.append("'").append(pro.getDir_id()).append("'");            sql.append(")");            st.executeUpdate(sql.toString());        } catch (Exception e) {            e.printStackTrace();        } finally {            JdbcUtil.INSTANCE.close(conn, st, null);        }    }    public void delete(Long id) {        Connection conn = null;        Statement st = null;        try {            conn = JdbcUtil.INSTANCE.getConn();            st = conn.createStatement();            String sql = "delete from product where id = " + id;            st.executeUpdate(sql);        } catch (Exception e) {            e.printStackTrace();        } finally {            JdbcUtil.INSTANCE.close(conn, st, null);        }    }    public void update(Product pro) {        Connection conn = null;        Statement st = null;        try {            conn = JdbcUtil.INSTANCE.getConn();            st = conn.createStatement();            StringBuilder sql = new StringBuilder();            sql.append("update product set ");            sql.append("productName = ").append("'").append(pro.getProductName()).append("',");            sql.append("brand =").append("'").append(pro.getBrand()).append("',");            sql.append("supplier =").append("'").append(pro.getSupplier()).append("',");            sql.append("salePrice =").append(pro.getSalePrice()).append(",");            sql.append("costPrice =").append(pro.getCostPrice()).append(",");            sql.append("cutoff =").append(pro.getCutoff()).append(",");            sql.append("dir_id =").append(pro.getDir_id());            sql.append(" where id = ").append(pro.getId());            st.executeUpdate(sql.toString());        } catch (Exception e) {            e.printStackTrace();        } finally {            JdbcUtil.INSTANCE.close(conn, st, null);        }    }    public Product get(Long id) {        Connection conn = null;        Statement st = null;        ResultSet rs = null;        try {            conn = JdbcUtil.INSTANCE.getConn();            st = conn.createStatement();            String sql = "select * from product where id = " + id;            rs = st.executeQuery(sql);            if (rs.next()) {                Product pro = new Product();                //把一行数据中的，每一列的值，存储到对象的属性中                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 pro;            }        } catch (Exception e) {            e.printStackTrace();        } finally {            JdbcUtil.INSTANCE.close(conn, st, rs);        }        return null;    }    public List<Product> list() {        List<Product> list = new ArrayList<Product>();        Connection conn = null;        Statement st = null;        ResultSet rs = null;        try {            conn = JdbcUtil.INSTANCE.getConn();            st = conn.createStatement();            String sql = "select * from product";            rs = st.executeQuery(sql);            while (rs.next()) {                //把当前的数据，封装成一个Product对象                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"));            }        } catch (Exception e) {            e.printStackTrace();        } finally {            JdbcUtil.INSTANCE.close(conn, st, rs);        }        return list;    }}

4 写他们的测试类

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(4L);        dao.save(pro);    }    @Test    public void testDelete() {        dao.delete(6L);    }    @Test    public void testUpdate() {        Product pro = new Product();        pro.setProductName("iphone6s");        pro.setBrand("apple2");        pro.setSupplier("苹果公司2");        pro.setSalePrice(new BigDecimal("6000"));        pro.setCostPrice(new BigDecimal("1000"));        pro.setCutoff(0.8);        pro.setDir_id(5L);        pro.setId(5L);        dao.update(pro);    }    @Test    public void testGet() {        Product pro = dao.get(10L);        System.out.println(pro);    }    @Test    public void testList() {        List<Product> list = dao.list();        for (Product p : list) {            System.out.println(p);        }    }}

4 代码重构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();                }            }        }    }}