Mysql版本：8.0.26
可视化客户端：sql yog
编译软件：IntelliJ IDEA 2019.2.4 x64
运行环境：win10 家庭中文版
jdk版本：1.8.0_361
一、DAO是什么？
众所周知，Java是面向对象语言，数据在Java中通常是以对象的形式存在。而在数据库中，它的每张数据表我们可以视作是Java中的一个个类，而表的每一行记录和相应的字段，便可视作是一个个的对象和对象的属性。相应地字段值，便是对象的属性值。反之，Java的对象也可看作是数据库中数据表的每一行记录，而Java对象的属性名和属性值，就是记录的字段名和相应的字段值。
当Java程序通过JDBC与数据库交互时，我们把涉及
访问数据库的代码封装起来，这些类称为DAO，英文全称为Data Access Object。它相当于是一个数据访问接口，夹在业务逻辑与数据库资源中间，这样使得代码结构更加清晰、易于维护和扩展。
如下图所示：
二、案例演示
案例：尝试连接数据库0225db，以DAO层的封装思想的形式进行编码，访问部门表与员工表，实现增删改查相应的功能
2.1 准备数据
①建立员工表t_employee
Create Table CREATE TABLE `t_employee` ( `eid` int NOT NULL AUTO_INCREMENT COMMENT '员工编号', `ename` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工姓名', `salary` double NOT NULL COMMENT '薪资', `commission_pct` decimal(3,2) DEFAULT NULL COMMENT '奖金比例', `birthday` date NOT NULL COMMENT '出生日期', `gender` enum('男','女') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '男' COMMENT '性别', `tel` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手机号码', `email` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '邮箱', `address` varchar(150) DEFAULT NULL COMMENT '地址', `work_place` set('北京','深圳','上海','武汉') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '北京' COMMENT '工作地点', `hiredate` date NOT NULL COMMENT '入职日期', `job_id` int DEFAULT NULL COMMENT '职位编号', `mid` int DEFAULT NULL COMMENT '领导编号', `did` int DEFAULT NULL COMMENT '部门编号', PRIMARY KEY (`eid`), KEY `job_id` (`job_id`), KEY `did` (`did`), KEY `mid` (`mid`), CONSTRAINT `t_employee_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `t_job` (`jid`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `t_employee_ibfk_2` FOREIGN KEY (`did`) REFERENCES `t_department` (`did`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `t_employee_ibfk_3` FOREIGN KEY (`mid`) REFERENCES `t_employee` (`eid`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `t_employee_chk_1` CHECK ((`salary` > 0)), CONSTRAINT `t_employee_chk_2` CHECK ((`hiredate` > `birthday`)) ) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
②建立部门表t_department
CREATE TABLE `t_department` ( `did` int NOT NULL AUTO_INCREMENT COMMENT '部门编号', `dname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工名称', `description` varchar(200) DEFAULT NULL COMMENT '员工简介', PRIMARY KEY (`did`), UNIQUE KEY `dname` (`dname`) ) ENGINE=InnoDB AUTO_INCREMENT=1012 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2.2 创建bean包
目的：
创建两个类用于封装从t_employee和t_department拿过来的每一行记录
代码演示如下：
①创建department类，用于封装从t_department拿过来的每一行记录，同时一个department类的对象可以写入t_department表里
public class department { private Integer did; private String dname; private String description; public department() { } public department(int did) { this.did = did; } public department(int did, String dname, String description) { this.did = did; this.dname = dname; this.description = description; } public department(String dname, String description) { this.dname = dname; this.description = description; } public int getDid() { return did; } public void setDid(int did) { this.did = did; } public String getDname() { return dname; } public void setDname(String dname) { this.dname = dname; } public String getDescription() { return description; } public void setDescription(String description) { this.description = description; } @Override public String toString() { return "department{" + "did=" + did + ", dname='" + dname + '\'' + ", description='" + description + '\'' + '}'; } }
②创建employee类，用于封装从t_employee拿过来的每一行记录，同时一个employee类的对象可以写入t_employee表里
import java.math.BigDecimal; import java.util.Date; public class employee { private Integer eid; private String ename; private Double salary; private BigDecimal commissioPct; private Date birthday; private String gender; private String tel; private String email; private String address; private String workPlace="北京"; private Date hiredate; private Integer jobId; private Integer mid; private Integer did; public employee() { } public employee(Integer eid) { this.eid = eid; } public employee(String ename, Double salary, BigDecimal commissioPct, Date birthday, String gender, String tel, String email, String address, String workPlace, Date hiredate, Integer jobId, Integer mid, Integer did) { this.ename = ename; this.salary = salary; this.commissioPct = commissioPct; this.birthday = birthday; this.gender = gender; this.tel = tel; this.email = email; this.address = address; this.workPlace = workPlace; this.hiredate = hiredate; this.jobId = jobId; this.mid = mid; this.did = did; } public employee(Integer eid, String ename, Double salary, BigDecimal commissioPct, Date birthday, String gender, String tel, String email, String address, String workPlace, Date hiredate, Integer jobId, Integer mid, Integer did) { this.eid = eid; this.ename = ename; this.salary = salary; this.commissioPct = commissioPct; this.birthday = birthday; this.gender = gender; this.tel = tel; this.email = email; this.address = address; this.workPlace = workPlace; this.hiredate = hiredate; this.jobId = jobId; this.mid = mid; this.did = did; } public Integer getEid() { return eid; } public void setEid(Integer eid) { this.eid = eid; } public String getEname() { return ename; } public void setEname(String ename) { this.ename = ename; } public Double getSalary() { return salary; } public void setSalary(Double salary) { this.salary = salary; } public BigDecimal getCommissioPct() { return commissioPct; } public void setCommissioPct(BigDecimal commissioPct) { this.commissioPct = commissioPct; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public String getTel() { return tel; } public void setTel(String tel) { this.tel = tel; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getWorkPlace() { return workPlace; } public void setWorkPlace(String workPlace) { this.workPlace = workPlace; } public Date getHiredate() { return hiredate; } public void setHiredate(Date hiredate) { this.hiredate = hiredate; } public Integer getJobId() { return jobId; } public void setJobId(Integer jobId) { this.jobId = jobId; } public Integer getMid() { return mid; } public void setMid(Integer mid) { this.mid = mid; } public Integer getDid() { return did; } public void setDid(Integer did) { this.did = did; } @Override public String toString() { return "employee{" + "eid=" + eid + ", ename='" + ename + '\'' + ", salary=" + salary + ", commission_pct=" + commissioPct + ", birthday=" + birthday + ", gender='" + gender + '\'' + ", tel='" + tel + '\'' + ", email='" + email + '\'' + ", address='" + address + '\'' + ", work_place='" + workPlace + '\'' + ", hiredate=" + hiredate + ", job_id=" + jobId + ", mid=" + mid + ", did=" + did + '}'; } }
2.3 建立DAO包
2.2.1 建立BaseDAOImpl类
目的：
基本上每一个数据表都应该有一个对应的DAO接口及其实现类，发现对所有表的操作（增、删、改、查）代码重复度很高，所以可以抽取公共代码，给这些DAO的实现类可以抽取一个公共的父类，称为BaseDAOImpl。该类包含通用的增删改查的方法。
代码演示如下：
import util.JDBCTools; import util.JDBCTools2; import java.lang.reflect.Field; import java.sql.*; import java.util.ArrayList; import java.util.List; public abstract class BaseDAOImpl { //通用的曾，删，改操作 //sql中包含？ 参数，需要设置？的值 //如果sgL中没有?，那么调用这个方法时，不用传args对应的实参 // 如果sgL中有5个?，那么调用这个方法时，要给args传入对应的5个实参 //实现流程 : java程序 ----> mysql数据库中的数据表 public int update(String sql,Object ...args) throws SQLException { //连接数据库，类似于网络编程中的socket //使用JDBCTools(1.0)获取的连接对象 Connection conn = JDBCTools.getConnection(); //使用JDBCTools2(2.0)获取的连接对象 // Connection conn = JDBCTools2.getConnection(); //将sql语句预编译 PreparedStatement pst = conn.prepareStatement(sql); if (args!=null && args.length>0) { for (int i = 0; i < args.length; i++) { pst.setObject(i+1,args[i]);//设置？的值 } } int len= pst.executeUpdate();//执行sql语句并返回同步更新的记录条数 //释放连接 // JDBCTools.freeConnection(conn); return len; } //查询多个对象的信息的通用方法 //实现流程: Mysql数据库中的数据表的记录 ---> java程序中 protected <T> List<T> getList(Class<T> clazz,String sql,Object ... args) throws SQLException, IllegalAccessException, InstantiationException, NoSuchFieldException { //泛型方法 //通过JDBCTools工具类开启数据库连接 //使用JDBCTools(1.0)获取的连接对象 Connection conn = JDBCTools.getConnection(); //使用第二版的JDBCTools2(2.0)的方法以开启数据据库的连接 // Connection conn = JDBCTools2.getConnection(); PreparedStatement pst = conn.prepareStatement(sql); //为sql语句中的? 赋值 if (args!=null && args.length>0) { for (int i = 0; i < args.length; i++) { pst.setObject(i+1,args[i]);//设置？的值 } } //从数据库中查到的表t_department的数据返回来是个结果集 ResultSet resultSet = pst.executeQuery(); //装从结果集遍历得到的对象 List<T> list =new ArrayList<>(); //得到结果集中的元数据对象【元数据：描述数据的数据】，取数据表中每一个字段名的名称,同样它返回的是元数据对象的结果集，一个表的元数据绝大数是多个，而非一个 ResultSetMetaData metaData = resultSet.getMetaData(); //得到元数据对象的结果集的列数，即有多少个元数据 int columnCount = metaData.getColumnCount(); //遍历结果集 while (resultSet.next()){ //每一行是一个对象 //利用反射来确定每一行对象的类型，但是反射的class对象，我们并不知道，所以希望别人传参告诉我们 T t = clazz.newInstance();//通过Class对象的newInsatnce()得到类的实例化对象 for (int i = 1; i <= columnCount ; i++) { //得到每一行的某一列，value是t对象的属性的值 Object value = resultSet.getObject(i);//获取结果集中第i列的值 //结果集中第i列的列名称恰好对于T类中的属性 // String columnName = metaData.getColumnClassName(i);//获取元数据结果集中的第i列的列名称 String columnName = metaData.getColumnLabel(i);//获取元数据结果集中的第i列的列名称,如果指定了列的别名，就用别名 //Class对象根据列名称找到对应类类型的类的某个属性 Field field = clazz.getDeclaredField(columnName); field.setAccessible(true); field.set(t,value);//设置t对象的field属性值 } list.add(t); } //释放连接 // JDBCTools.freeConnection(conn); // JDBCTools2.freeConnection();/这里不关闭连接，交给事务提交或回滚之后再去关闭连接 return list; } //查询一个对象（一条记录）的通用方法 protected <T> T getBean(Class<T> clazz,String sql,Object ... args) throws SQLException, NoSuchFieldException, InstantiationException, IllegalAccessException { List<T> list = getList(clazz, sql, args); if(list!=null && list.size()>0){ return list.get(0); } return null; } }
