抽取数据库工具类

1)实体类

/*
 * yxl 2019/8/26 14:20
 * 佛祖保佑，永无BUG!
 */
public class Emp {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Integer deptno;

    public Emp(Integer empno, String ename, String job, Integer mgr, Date hiredate, Double sal, Double 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;
    }
    public Emp(){
    }

    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 Date getHiredate() {
        return hiredate;
    }

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

    public Double getSal() {
        return sal;
    }

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

    public Double getComm() {
        return comm;
    }

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

    public Integer getDeptno() {
        return deptno;
    }

    public void setDeptno(Integer deptno) {
        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 +
                '}';
    }
}

2)配置文件db.properties

#驱动
driver=com.mysql.jdbc.Driver
#连接字符串
url=jdbc:mysql://localhost:3306/mydb1?useSSL=true&characterEncoding=utf8
#用户名
user=root
#密码
password=root

3）数据库工具类

/*
 * yxl 2019/8/26 14:28
 * 佛祖保佑，永无BUG!
 */
/*四个功能(驱连执事)
* (1)注册驱动（仅需注册一次）
* (2)获取连接 
* (3)执行SQL （仅有增删改的操作,没有查的操作）
* (4)释放资源
* */
public class DbUtils {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;
    static{
        //方式一
        /*driver = "com.mysql.jdbc.Driver";
        url = "jdbc:mysql://localhost:3306/mydb1?useSSL=true&characterEncoding=utf8";
        user = "root";
        password = "root";
        */
        //方式二 读取配置文件
        /*Properties properties = null;
        try {
            properties = new Properties();
            FileInputStream fileInputStream = new FileInputStream("src\\db.properties");
            properties.load(fileInputStream);
            fileInputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        driver = properties.getProperty("driver");
        url = properties.getProperty("url");
        user = properties.getProperty("user");
        password = properties.getProperty("password");*/
        //方式三 通过类加载器加载配置文件
        Properties properties = new Properties();
        InputStream inputStream = DbUtils.class.getClassLoader().getResourceAsStream("db.properties");
        try {
            properties.load(inputStream);
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        driver = properties.getProperty("driver");
        url = properties.getProperty("url");
        user = properties.getProperty("user");
        password = properties.getProperty("password");
        //注册驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    }
	//获取连接
    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
	//执行SQL语句
    public static int executeUpdate(String sql,Object...params){ //insert update delete

        Connection conn = getConnection();
        PreparedStatement pstat=null;
        try {
            pstat = conn.prepareStatement(sql);
            //设置参数
            if(params!=null){
                for (int i = 0; i < params.length; i++) {
                    pstat.setObject(i+1, params[i]);
                }
            }
            return pstat.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(conn, pstat, null);
        }
        return -1;
    }
	//释放资源
    public static void closeAll(Connection conn, Statement stat, ResultSet rs){
        try {
            if(rs!=null){
                rs.close();
            }
            if(stat!=null){
                stat.close();
            }
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4)dao层操作

/*
 * yxl 2019/8/26 14:19
 * 佛祖保佑，永无BUG!
 */
public class EmpDao {

    public List<Emp> findAll(){

        return null;
    }
    public Emp findByEmpno(Integer empno){

        return null;
    }
    public void add(Emp emp){
        //System.out.println("add执行了");
        Object[] params={emp.getEmpno(),emp.getEname(),emp.getJob(),emp.getMgr(),emp.getHiredate(),emp.getSal(),emp.getComm(),emp.getDeptno()};
        int count=DbUtils.executeUpdate("insert into emp values(?,?,?,?,?,?,?,?)",params);
        System.out.println(count);
    }
    public void update(Emp emp){
        Object[] params={emp.getEname(),emp.getJob(),emp.getMgr(),emp.getHiredate(),emp.getSal(),emp.getComm(),emp.getDeptno(),emp.getEmpno()};
        int count=DbUtils.executeUpdate("update emp set ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? where empno=?",params);
        System.out.println(count);
    }
    public void delete(Integer empno){
        int count=DbUtils.executeUpdate("delete from emp where empno=?",empno);
        System.out.println(count);
    }
}

5)测试类(Junit单元测试)

/*
 * yxl 2019/8/26 14:25
 * 佛祖保佑，永无BUG!
 */
public class EmpDaoTest {
    @Test
    public void testAdd(){
  EmpDao empDao=new EmpDao();
  Emp emp=new Emp(7788,"hello" ,"java开发" ,6666 ,new Date(),20000.0 ,0.0 ,20 );
        empDao.add(emp);
    }
    @Test
    public void testUdpate(){
 EmpDao empDao=new EmpDao();
 Emp emp=new Emp(1234,"hello1" ,"java开发" ,6666 ,new Date(),30000.0 ,0.0 ,20 );
        empDao.update(emp);
    }
}