JDBC technology (2) - set up common sql and configuration files

引言

JDBC技术（一）——一个简单的JDBC测试,在这篇博客中,我们发现,Every time you complete a query, you need to write the corresponding code,And there are many repetitions.It's not very efficient to do so,And it looks messy.So we modify and simplify it.

前期准备

1、Same as the blog above
2、新建resourcesdirectory and marked as the root directory for resource files,创建jdbc配置文件

项目结构

编写代码

1、实体类

public class Emp2 implements Serializable {
    
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private LocalDateTime hiredate;
    private BigDecimal sal;
    private BigDecimal comm;
    private Integer deptno;

    public Emp2() {
    
    }

    public Emp2(Integer empno, String ename, String job, Integer mgr, LocalDateTime hiredate, BigDecimal sal, BigDecimal comm, Integer deptno) {
    
        this.empno = empno;
        this.ename = ename;
        this.job = job;
        this.mgr = mgr;
        this.hiredate = hiredate;
        this.sal = sal;
        this.comm = comm;
        this.deptno = deptno;
    }

    @Override
    public String toString() {
    
        return "Emp{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", mgr=" + mgr +
                ", hiredate=" + hiredate +
                ", sal=" + sal +
                ", comm=" + comm +
                ", deptno=" + deptno +
                '}';
    }

    public Integer getDeptno() {
    
        return deptno;
    }

    public void setDeptno(Integer deptno) {
    
        this.deptno = deptno;
    }

    public Integer getEmpno() {
    
        return empno;
    }

    public void setEmpno(Integer empno) {
    
        this.empno = empno;
    }

    public String getEname() {
    
        return ename;
    }

    public void setEname(String ename) {
    
        this.ename = ename;
    }

    public String getJob() {
    
        return job;
    }

    public void setJob(String job) {
    
        this.job = job;
    }

    public Integer getMgr() {
    
        return mgr;
    }

    public void setMgr(Integer mgr) {
    
        this.mgr = mgr;
    }

    public LocalDateTime getHiredate() {
    
        return hiredate;
    }

    public void setHiredate(LocalDateTime hiredate) {
    
        this.hiredate = hiredate;
    }

    public BigDecimal getSal() {
    
        return sal;
    }

    public void setSal(BigDecimal sal) {
    
        this.sal = sal;
    }

    public BigDecimal getComm() {
    
        return comm;
    }

    public void setComm(BigDecimal comm) {
    
        this.comm = comm;
    }
}

2、dao层接口

public interface EmpDAO2 {
    
    List<Emp2> empList();

    Emp2 findEmpById(Integer empno);

    int save(Emp2 emp2);

    int updateSalById(BigDecimal sal, Integer empno);

    int delete(Integer empno);
}

3、实现类

public class EmpDAOImpl2 extends BaseSqlUtil implements EmpDAO2 {
    

    @Override
    public List<Emp2> empList() {
    
        String sql = "select * from emp";
        return executeQuery(sql, Emp2.class);
    }

    @Override
    public Emp2 findEmpById(Integer empno) {
    
        String sql = "select * from emp where empno = ?";
        List<Emp2> emp2List = executeQuery(sql, Emp2.class, empno);
        if(emp2List != null){
    
            return emp2List.get(0);
        }
        return null;
    }

    @Override
    public int save(Emp2 emp2) {
    
        String sql = "insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(?,?,?,?,?,?,?,?)";
        return executeUpdate(sql, emp2.getEmpno(), emp2.getEname(), emp2.getJob(), emp2.getMgr(), emp2.getHiredate(), emp2.getSal(), emp2.getComm(), emp2.getDeptno());
    }

    @Override
    public int updateSalById(BigDecimal sal, Integer empno) {
    
        String sql = "update emp set sal=? where empno = ?";
        return executeUpdate(sql, sal, empno);
    }

    @Override
    public int delete(Integer empno) {
    
        String sql = "delete from emp where empno = ?";
        return executeUpdate(sql, empno);
    }
}

4、通用sql工具

public class BaseSqlUtil {
    

    private Connection conn = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;

    /** * 通用增删改 */
    public int executeUpdate(String sql, Object... params){
    
        int rows = 0;
        try {
    
            conn = JDBCUtil2.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
    
                ps.setObject(i+1, params[i]);
            }
            rows = ps.executeUpdate();
        } catch (SQLException e) {
    
            e.printStackTrace();
        } finally {
    
            JDBCUtil2.close(conn, ps, rs);
        }
        return rows;
    }

    /** * 通用查询 */
    public <T> List<T> executeQuery(String sql, Class<T> clz, Object... params){
    
        List<T> lists = new ArrayList<>();
        try {
    
            conn = JDBCUtil2.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
    
                ps.setObject(i+1, params[i]);
            }
            rs = ps.executeQuery();
            // 获取resultset的元数据信息
            ResultSetMetaData md = rs.getMetaData();
            while(rs.next()){
    
                // 1. 创建对象
                Constructor<T> constructor = clz.getDeclaredConstructor();
                T t = constructor.newInstance();
                // 2. 封装属性
                // Take out the value of each field,Finds the specified property based on the field name,赋值
                // Get the number of fields
                int columnCount = md.getColumnCount();
                for (int i = 0; i < columnCount; i++) {
    
                    // 1. Get the name of each field
                    String label = md.getColumnLabel(i + 1);
                    // 2. 获取字段值
                    Object value = rs.getObject(label);
                    // 3. Get a property based on the field's name
                    Field field = clz.getDeclaredField(label);
                    if(field != null){
    
                        // 权限
                        field.setAccessible(true);
                        // 赋值
                        field.set(t, value);
                    }
                }
                // 3. 添加到集合
                lists.add(t);
            }
        } catch (SQLException | NoSuchFieldException |
                IllegalAccessException | InstantiationException |
                InvocationTargetException | NoSuchMethodException e) {
    
            e.printStackTrace();
        } finally {
    
            JDBCUtil2.close(conn, ps, rs);
        }
        return lists;
    }
}

5、jdbc工具类

public class JDBCUtil2 {
    

    private static String url = null;
    private static String user = null;
    private static String password = null;

    static{
    
        try {
    
            // 加载配置文件并转换为流,加载到properties对象中
            Properties properties = new Properties();
            InputStream is = JDBCUtil2.class.getClassLoader()
                    .getResourceAsStream("jdbc.properties");
            properties.load(is);

            //1. 注册驱动
            Class.forName(properties.getProperty("jdbc.driver"));
            url = properties.getProperty("jdbc.url");
            user = properties.getProperty("jdbc.user");
            password = properties.getProperty("jdbc.password");
        } catch (IOException | ClassNotFoundException e) {
    
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
    
        try {
    
            return DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
    
            throw new RuntimeException("获取连接失败!");
        }
    }

    public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
    
        try {
    
            if (resultSet != null) {
    
                resultSet.close();
            }
            if (preparedStatement != null) {
    
                preparedStatement.close();
            }
            if (connection != null) {
    
                connection.close();
            }
        } catch (SQLException e) {
    
            e.printStackTrace();
        }
    }
}

6、测试类

public class MainTest2 {
    

    @Test
    public void empList(){
    
        EmpDAO2 empDAO2 = new EmpDAOImpl2();
        System.out.println(empDAO2.empList());
    }

    @Test
    public void findEmpById(){
    
        EmpDAO2 empDAO2 = new EmpDAOImpl2();
        System.out.println(empDAO2.findEmpById(7233));
    }

    @Test
    public void insert(){
    
        EmpDAO2 empDAO2 = new EmpDAOImpl2();
        LocalDateTime localDateTime = LocalDateTime.now();
        Emp2 emp2 = new Emp2(7000,"LX","CLERK",7698,localDateTime,new BigDecimal(1250),new BigDecimal(100),20);
        System.out.println(empDAO2.save(emp2));
    }

    @Test
    public void updateSalById(){
    
        EmpDAO2 empDAO2 = new EmpDAOImpl2();
        System.out.println(empDAO2.updateSalById(new BigDecimal(1500),7000));
    }

    @Test
    public void delete(){
    
        EmpDAO2 empDAO2 = new EmpDAOImpl2();
        System.out.println(empDAO2.delete(7000));
    }
}

7、jdbc配置文件

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql:///test?useSSL=true&serverTimezone=GMT%2B8
jdbc.user=root
jdbc.password=123456

测试结果