jdbc-day02-dao

dao_impl_EmployeeDaoImpl

package com.youcai.emp.dao.impl;

import com.youcai.emp.dao.EmployeeDao;
import com.youcai.emp.util.DruidUtil;
import com.youcai.emp.vo.Employee;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 定义EmployeeDao接口的实现类型
 */
public class EmployeeDaoImpl implements EmployeeDao {
    @Override
    public void addEmployee(Employee employee) {
        Connection conn = null;
        try{
            conn = DruidUtil.getConnection();
            //获取预编译语句对象
            String sql = "insert into emp values (?,?,?,?,?,?,?,?)";
            PreparedStatement prep = conn.prepareStatement(sql);
            //给问号赋值
            prep.setInt(1, employee.getEmpno());
            prep.setString(2, employee.getEname());
            prep.setString(3, employee.getJob());
            prep.setInt(4, employee.getMgr());
            prep.setDate(5, employee.getHiredate());
            prep.setDouble(6, employee.getSal());
            prep.setDouble(7, employee.getComm());
            prep.setInt(8, employee.getDeptno());

            //再次发送数据到DBMS里，进行执行
            prep.executeUpdate();

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DruidUtil.closeConnection(conn);
        }
    }

    @Override
    public void deleteEmployee(int empno) {
        Connection conn = null;
        try{
            conn = DruidUtil.getConnection();
            //获取预编译语句对象
            String sql = "delete from emp where empno=?";
            PreparedStatement prep = conn.prepareStatement(sql);
            //给问号赋值
            prep.setInt(1, empno);
            //再次发送数据到DBMS里，进行执行
            prep.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DruidUtil.closeConnection(conn);
        }
    }

    @Override
    public void updateEmployee(Employee employee) {
        Connection conn = null;
        try{
            conn = DruidUtil.getConnection();
            //获取预编译语句对象
            String sql = "update emp set ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? where empno=?";
            PreparedStatement prep = conn.prepareStatement(sql);
            //给问号赋值
            prep.setString(1, employee.getEname());
            prep.setString(2, employee.getJob());
            prep.setInt(3, employee.getMgr());
            prep.setDate(4, employee.getHiredate());
            prep.setDouble(5, employee.getSal());
            prep.setDouble(6, employee.getComm());
            prep.setInt(7, employee.getDeptno());
            prep.setInt(8, employee.getEmpno());

            //再次发送数据到DBMS里，进行执行
            prep.executeUpdate();

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DruidUtil.closeConnection(conn);
        }
    }

    @Override
    public Employee findEmployeeById(int empno) {

        Employee employee = null;
        Connection conn = null;
        try{
            conn = DruidUtil.getConnection();
            //获取预编译语句对象
            String sql = "select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where empno=?";
            PreparedStatement prep = conn.prepareStatement(sql);
            //给问号赋值
            prep.setInt(1, empno);
            ResultSet set = prep.executeQuery();
            if (set.next()){
                String ename = set.getString("ename");
                String job = set.getString("job");
                int mgr = set.getInt("mgr");
                Date hiredate = set.getDate("hiredate");
                double sal = set.getDouble("sal");
                double comm = set.getDouble("comm");
                int deptno = set.getInt("deptno");
                employee = new Employee(empno, ename, job, mgr, hiredate, sal, comm, deptno);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DruidUtil.closeConnection(conn);
        }
        return employee;
    }

    @Override
    public List<Employee> findAll() {
        //创建一个集合容器，用于存储多个员工对象
        List<Employee> list = new ArrayList<>();
        Connection conn = null;
        try{
            conn = DruidUtil.getConnection();
            //获取预编译语句对象
            String sql = "select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp";
            PreparedStatement prep = conn.prepareStatement(sql);
            //给问号赋值
            ResultSet set = prep.executeQuery();
            while (set.next()){
                int empno = set.getInt("empno");
                String ename = set.getString("ename");
                String job = set.getString("job");
                int mgr = set.getInt("mgr");
                Date hiredate = set.getDate("hiredate");
                double sal = set.getDouble("sal");
                double comm = set.getDouble("comm");
                int deptno = set.getInt("deptno");
                Employee employee = new Employee(empno, ename, job, mgr, hiredate, sal, comm, deptno);
                list.add(employee);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DruidUtil.closeConnection(conn);
        }
        return list;
    }

    @Override
    public List<Employee> findByPage(int page, int pageSize) {
        //创建一个集合容器，用于存储多个员工对象
        List<Employee> list = new ArrayList<>();
        Connection conn = null;
        try{
            conn = DruidUtil.getConnection();
            //获取预编译语句对象
            String sql = "select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by deptno limit ?,?";
            PreparedStatement prep = conn.prepareStatement(sql);
            //给问号赋值
            prep.setInt(1, (page-1)*pageSize);
            prep.setInt(2, pageSize);
            //再次执行查询
            ResultSet set = prep.executeQuery();
            while (set.next()){
                int empno = set.getInt("empno");
                String ename = set.getString("ename");
                String job = set.getString("job");
                int mgr = set.getInt("mgr");
                Date hiredate = set.getDate("hiredate");
                double sal = set.getDouble("sal");
                double comm = set.getDouble("comm");
                int deptno = set.getInt("deptno");
                Employee employee = new Employee(empno, ename, job, mgr, hiredate, sal, comm, deptno);
                list.add(employee);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DruidUtil.closeConnection(conn);
        }
        return list;
    }
}

dao_EmployeeDao

package com.youcai.emp.dao;

import com.youcai.emp.vo.Employee;

import java.util.List;

/**
 * 根据实体类Employee 和数据库中的emp表，来设计DA0层的接口类型
 * 该接口里实际上就是封装了一些与数据库进行交互的方法:
 * 增，删，改，查。
 */

public interface EmployeeDao {
    /**
     * 从java的面向对象思想来考虑，前端提供了一个员工的所有的零散的信息
     *   传入到服务端后，应该封装到实体类的具体实例里，然后在DAO层，
     *   我们将具体实例保存到数据库中，所以，方法带实体类参数
     * @param employee
     */
    void addEmployee(Employee employee);

    /**
     * 删除后一个员工，一定是前端传入了一个代表该员工的唯一标识，即主键字段
     *   因此该方法需要带参数
     * @param empno
     */
    void deleteEmployee(int empno);

    /**
     * 修改一个员工的信息，在前端的员工信息的文本框中，不一定是修改了什么信息
     *  因此，后端需要考虑全面。认为全都可能修改了，所以重新封装成对象。
     *  传入方法
     *
     *  注意：形参已经是修改后的数据了。
     * @param employee
     */
    void updateEmployee(Employee employee);

    /**
     * 通过唯一标识，查询某一个员工的所有信息，结果封装成实体类对象
     * @param empno
     * @return
     */
    Employee findEmployeeById(int empno);

    /**
     * 查询表中的所有记录，不需要形参，因为 select * from 表名;
     * 每一条记录都应该封装成一个实体类对象
     * 多个对象，应该存储到集合容器中，所以返回值应该是一个集合
     * @return
     */
    List<Employee> findAll();

    /**
     * 分页查询：
     * select ....  from emp
     * order by .... limit (page-1)*pageSize, pageSize;
     */
    List<Employee> findByPage(int page, int pageSize);

}

test_EmployeeDaoTest

package com.youcai.emp.test;

import com.youcai.emp.dao.EmployeeDao;
import com.youcai.emp.util.DaoFactory;
import com.youcai.emp.vo.Employee;
import org.junit.Test;

import java.sql.Date;
import java.util.List;

public class EmployeeDaoTest {
    /**
     * 添加员工的测试
     */
    @Test
    public void testAddEmployee(){
        Employee e1 = new Employee(10001,"spider","hero",7369,
                Date.valueOf("2024-09-01"),1000.0,10.0,10);
        EmployeeDao dao = DaoFactory.getEmployeeDaoInstance();
        dao.addEmployee(e1);
    }

    /**
     * 删除员工的测试
     */
    @Test
    public void testDeleteEmployee(){
        EmployeeDao dao = DaoFactory.getEmployeeDaoInstance();
        dao.deleteEmployee(10001);
    }
    @Test
    public void testFindEmployeeById(){
        EmployeeDao dao = DaoFactory.getEmployeeDaoInstance();
        Employee emp = dao.findEmployeeById(10001);
        System.out.println(emp);
    }
    /**
     * 修改员工的测试：
     */
    @Test
    public void testUpdateEmployee(){
        //通过工厂类型里的工具方法，获取EmployeeDAO接口实例
        EmployeeDao dao = DaoFactory.getEmployeeDaoInstance();

        //将一个员工先查询出来，在修改
        Employee e1 = dao.findEmployeeById(10000);
        e1.setMgr(7499);
        e1.setHiredate(Date.valueOf("2024-08-01"));
        e1.setSal(10000.0);
        e1.setComm(100.0);
        e1.setDeptno(10);
        //调用接口里的修改方法，提交数据到数据库
        dao.updateEmployee(e1);
    }

    @Test
    public void testFindAllEmployee(){
        EmployeeDao dao = DaoFactory.getEmployeeDaoInstance();
        List<Employee> emps = dao.findAll();
        emps.forEach(System.out::println);
    }

    @Test
    public void testFindAllEmployeeByPage(){
        EmployeeDao dao = DaoFactory.getEmployeeDaoInstance();
        //每页五条，查询第三页的数据
        List<Employee> emps = dao.findByPage(3, 5);
        emps.forEach(System.out::println);
    }


    /**
     * junit是用来进行测试的。其中有很多注解。
     * @Test：   用于测试方法，相当于main方法，可以直接运行
     * @Before:  位于方法上， 有该注解的方法会优先于有@Test注解的方法执行
     * @After:   位于方法上， 有该注解的方法会后于有@Test注解的方法执行
     */
    @Test
    public void test1(){
        System.out.println(1+2);
    }
    @Test
    public void test2(){
        System.out.println(Math.random());
    }
}

util_DaoFactory

package com.youcai.emp.util;

import com.youcai.emp.dao.EmployeeDao;
import com.youcai.emp.dao.impl.EmployeeDaoImpl;

/**
 * 定义一个持久层的工厂类型，
 * 在该类型中提供一些静态工具方法，用于获取每个实体类对应的DAO接口实例
 */

public class DaoFactory {
    private static EmployeeDao employeDao;
    //private static DeptDao deptDao;

    //私有化构造器，防止在外部直接new对象
    private DaoFactory() {
    }

    //提供一个共有的静态方法，来访问接口的实例对象
    public static EmployeeDao getEmployeeDaoInstance() {
        if (employeDao == null) {
            employeDao = new EmployeeDaoImpl();
        }
        return employeDao;
    }
}

util_DruidUtil

package com.youcai.emp.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

public class DruidUtil {
    //定义一个静态变量：关于Druid的连接池的
    private static DataSource ds;

    static {
        try {
            //先自己读取配置文件，封装到Properties对象里
            InputStream io = DruidUtil.class.getClassLoader().getResourceAsStream("druid.properties");
            Properties prop = new Properties();
            prop.load(io);

            //Druid提供了一个工厂类，里面提供了一个createDataSource(Properties prop)方法
            // 会自动解析prop里的各种键值对，进行赋值
            ds = DruidDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = ds.getConnection();
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        Connection conn = getConnection();
        System.out.println(conn);
        closeConnection(conn);
    }
}

vo_Employee

package com.youcai.emp.vo;

import java.sql.Date;
import java.util.Objects;

/**
 * 根据ORM对象关系映射，为数据库中的emp表设计成一个实体类Employee
 *  1. 表的字段 --> 类的属性
 *  2. 表的每一行记录 --> 类的具体实例
 *
 *  建议： 数据库的数值类型，在java中映射成对应的包装类类型
 */

public class Employee {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Integer deptno;

    private Employee() {}

    public Employee(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 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 boolean equals(Object object) {
        if (this == object) return true;
        if (object == null || getClass() != object.getClass()) return false;
        Employee employee = (Employee) object;
        return Objects.equals(empno, employee.empno) && Objects.equals(ename, employee.ename) && Objects.equals(job, employee.job) && Objects.equals(mgr, employee.mgr) && Objects.equals(hiredate, employee.hiredate) && Objects.equals(sal, employee.sal) && Objects.equals(comm, employee.comm) && Objects.equals(deptno, employee.deptno);
    }

    @Override
    public int hashCode() {
        return Objects.hash(empno, ename, job, mgr, hiredate, sal, comm, deptno);
    }

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

druid.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai&useTimezone=true
username=root
password=111111
initialSize=5
maxActive=50
maxIdle=10
minIdle=3
maxWait=60000