DAO：Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、Delete），而不包含任何业务相关的信息

作用：为了实现功能的模块化，更有利于代码的维护和升级。

package com.atguigu.dao;import java.util.List;import com.atguigu.bean.Department;public interface DepartmentDAO {void addDepartment(Department department)throws Exception;void updateDepartment(Department department)throws Exception;void deleteById(String did)throws Exception;Department getById(String did)throws Exception;List getAll()throws Exception;}

package com.atguigu.dao;import java.util.List;import java.util.Map;import com.atguigu.bean.Employee;public interface EmployeeDAO {void addEmployee(Employee emp)throws Exception;void updateEmployee(Employee emp)throws Exception;void deleteById(String eid)throws Exception;Employee getById(String eid)throws Exception;List getAll()throws Exception;Long getCount()throws Exception;List getAll(int page, int pageSize)throws Exception;Double getMaxSalary()throws Exception;Map getAvgSalaryByDid()throws Exception;}

package com.atguigu.dao.impl.original;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.List;import com.atguigu.bean.Department;import com.atguigu.dao.DepartmentDAO;import com.atguigu.utils.JDBCUtils;public class DepartmentDAOImpl implements DepartmentDAO{@Overridepublic void addDepartment(Department department) throws Exception {Connection conn = JDBCUtils.getConnection();String sql = "INSERT INTO t_department(did,dname,description) VALUES(NULL,?,?)";PreparedStatement pst = conn.prepareStatement(sql);pst.setString(1, department.getName());pst.setString(2, department.getDescription());pst.executeUpdate();JDBCUtils.closeQuietly(pst, conn);}@Overridepublic void updateDepartment(Department department) throws Exception {Connection conn = JDBCUtils.getConnection();String sql = "UPDATE t_department SET dname = ?,description = ? WHERE did = ?";PreparedStatement pst = conn.prepareStatement(sql);pst.setString(1, department.getName());pst.setString(2, department.getDescription());pst.setInt(3, department.getId());pst.executeUpdate();JDBCUtils.closeQuietly(pst, conn);}@Overridepublic void deleteById(String did) throws Exception {Connection conn = JDBCUtils.getConnection();String sql = "DELETE FROM t_department WHERE did = ?";PreparedStatement pst = conn.prepareStatement(sql);pst.setString(1, did);pst.executeUpdate();JDBCUtils.closeQuietly(pst, conn);}@Overridepublic Department getById(String did) throws Exception {Connection conn = JDBCUtils.getConnection();String sql = "SELECT did,dname,description FROM t_department WHERE did = ?";PreparedStatement pst = conn.prepareStatement(sql);pst.setString(1, did);ResultSet rs = pst.executeQuery();Department dept = null;if(rs.next()){dept = new Department();dept.setId(rs.getInt("did"));dept.setName(rs.getString("dname"));dept.setDescription(rs.getString("description"));}JDBCUtils.closeQuietly(rs, pst, conn);return dept;}@Overridepublic List getAll() throws Exception {Connection conn = JDBCUtils.getConnection();String sql = "SELECT did,dname,description FROM t_department";PreparedStatement pst = conn.prepareStatement(sql);ResultSet rs = pst.executeQuery();ArrayList list = new ArrayList();while(rs.next()){Department dept = new Department();dept.setId(rs.getInt("did"));dept.setName(rs.getString("dname"));dept.setDescription(rs.getString("description"));list.add(dept);}JDBCUtils.closeQuietly(rs, pst, conn);return list;}}

package com.atguigu.dao.impl;/** 这个类的作用是：对DAOImpl再次抽象，把共同的部分再次抽取*/import java.lang.reflect.Field;import java.lang.reflect.ParameterizedType;import java.lang.reflect.Type;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import com.atguigu.utils.JDBCUtils;//泛型类public abstract class BasicDAOImpl {private Class type;@SuppressWarnings("all")protected BasicDAOImpl() {// 为什么要在构造器中写，因为子类继承BasicDAOImpl类一定会调用父类的构造器Class clazz = this.getClass();// this代表的是正在创建的那个对象，即子类的对象// 获取clazz的带泛型父类信息Type superType = clazz.getGenericSuperclass();// Father：参数化的类型ParameterizedType p = (ParameterizedType) superType;// 获取泛型实参Type[] ts = p.getActualTypeArguments();// 因为当前类只有一个泛型形参，即子类中只有一个泛型实参type = (Class) ts[0];}protected int update(String sql, Object... params) throws SQLException {//1、获取连接Connection conn = JDBCUtils.getConnection();//2、执行更新数据库语句int len = executeUpdate(conn, sql, params);//3、关闭连接JDBCUtils.closeQuietly(conn);return len;}// 如果有需要在一个事务中完成更新，可以调用这个带conn的方法protected int update(Connection conn, String sql, Object... params) throws SQLException {//执行更新数据库语句int len = executeUpdate(conn, sql, params);return len;}private int executeUpdate(Connection conn, String sql, Object... params) throws SQLException {//1、sql预编译PreparedStatement pst = conn.prepareStatement(sql);//2、设置sql中的?if (params != null && params.length > 0) {// 数组的下标是从0开始，？的编号是1开始for (int i = 0; i < params.length; i++) {pst.setObject(i + 1, params[i]);}}//3、执行sqlint len = pst.executeUpdate();//4、释放资源JDBCUtils.closeQuietly(pst);return len;}//通用的查询方法之一：查询一行，即一个对象/*** 执行查询操作的SQL语句，SQL可以带参数(?)* @param sql String 执行查询操作的SQL语句* @param args Object... 对应的每个?设置的值，顺序要与?对应* @return T 封装了查询结果的实体* @throws Exception*/protected T get(String sql, Object... params) throws Exception {//1、获取连接Connection conn = JDBCUtils.getConnection();//2、执行查询语句ResultSet rs = executeQuery( conn,sql, params);//3、处理查询结果//（1）获取查询的结果集的元数据信息ResultSetMetaData rsmd = rs.getMetaData();//（2）这是查询的结果集中，一共有几列int count = rsmd.getColumnCount();//（3）创建实例对象T t = type.newInstance();// 要求这个Javabean类型必须有无参构造//（4）遍历结果集while (rs.next()) {/** 问题？ （1）sql语句中查询了几列，每一列是什么属性 （2）怎么把这个值设置到Javabean的属性中*/// （5）循环每一行有几列for (int i = 0; i < count; i++) {// （6）获取第几列的名称// String columnName = rsmd.getColumnName(i+1);// 如果sql中没有取别名，那么就是列名，如果有别名，返回的是别名String fieldName = rsmd.getColumnLabel(i + 1);// （7）获取该列的值// Object value = rs.getObject(columnName);Object value = rs.getObject(fieldName);// （8）设置obj对象的某个属性中Field field = type.getDeclaredField(fieldName);// JavaBean的属性名field.setAccessible(true);field.set(t, value);}}//4、释放资源JDBCUtils.closeQuietly(rs);JDBCUtils.closeQuietly(conn);return t;}private static ResultSet executeQuery(Connection conn, String sql, Object... params) throws SQLException {//1、sql预编译PreparedStatement pst = conn.prepareStatement(sql);//2、设置？if (params != null && params.length > 0) {// 数组的下标是从0开始，？的编号是1开始for (int i = 0; i < params.length; i++) {pst.setObject(i + 1, params[i]);}}//3、查询ResultSet rs = pst.executeQuery();return rs;}// 通用的查询方法之二：查询多行，即多个对象// Class clazz：用来创建实例对象，获取对象的属性，并设置属性值/*** 执行查询操作的SQL语句，SQL可以带参数(?)* @param sql String 执行查询操作的SQL语句* @param args Object... 对应的每个?设置的值，顺序要与?对应* @return ArrayList 封装了查询结果的集合* @throws Exception*/public ArrayList getList(String sql, Object... args) throws Exception {// 1、获取连接Connection conn = JDBCUtils.getConnection();//2、执行查询sqlResultSet rs = executeQuery(conn,sql, args);//3、获取结果集的元数据ResultSetMetaData metaData = rs.getMetaData();// 获取结果中总列数int count = metaData.getColumnCount();// 创建集合对象ArrayList list = new ArrayList();while (rs.next()) {// 遍历的行// 1、每一行是一个对象T obj = type.newInstance();// 2、每一行有很多列// for的作用是为obj对象的每一个属性设置值for (int i = 0; i < count; i++) {// (1)每一列的名称String fieldName = metaData.getColumnLabel(i + 1);// 获取第几列的名称，如果有别名获取别名，如果没有别名获取列名// (2)每一列的值Object value = rs.getObject(i + 1);// 获取第几列的值// (3)获取属性对象Field field = type.getDeclaredField(fieldName);// (4)设置可见性field.setAccessible(true);// (5)设置属性值field.set(obj, value);}// 3、把obj对象放到集合中list.add(obj);}// 6、释放资源JDBCUtils.closeQuietly(rs);JDBCUtils.closeQuietly(conn);// 7、返回结果return list;}//通用的查询方法之三：查询单个值//单值：select max(salary) from employee; 一行一列//select count(*) from t_goods; 一共几件商品public Object getValue(String sql,Object... args)throws Exception{//1、获取连接Connection conn = JDBCUtils.getConnection();//2、执行查询sqlResultSet rs = executeQuery(conn, sql, args);Object value = null;if(rs.next()){//一行value = rs.getObject(1);//一列}//3、释放资源JDBCUtils.closeQuietly(rs);JDBCUtils.closeQuietly(conn);return value;}//通用的查询方法之四：查询多行多列，但每一行又不是一个JavaBean/** SELECT did,AVG(salary),MAX(Salary) FROM t_employee GROUP BY did;* did avg(salary) max(salary)1 1990.90 89002 4889 6899*/public List> getListMap(String sql,Object... args)throws Exception{//1、获取连接Connection conn = JDBCUtils.getConnection();//2、执行sqlResultSet rs = executeQuery(conn, sql, args);//获取结果集的元数据对象ResultSetMetaData metaData = rs.getMetaData();//一共有几列int count = metaData.getColumnCount();//创建ListArrayList> list = new ArrayList>();while(rs.next()){//每一行是一个Map的对象HashMap map = new HashMap();//map的key是列名for (int i = 0; i < count; i++) {//（1）获取列名或别名String columnName = metaData.getColumnLabel(i+1);//（2）获取对应的值Object value = rs.getObject(i+1);//（3）把这对值放到map中map.put(columnName, value);}//把map放到List中list.add(map);}//6、释放资源JDBCUtils.closeQuietly(rs);JDBCUtils.closeQuietly(conn);return list;}//通用的查询方法之四：查询一行多列，但一行又不是一个JavaBeanpublic Map getMap(String sql,Object... args)throws Exception{List> listMap = getListMap(sql,args);if(listMap.size()>0){return listMap.get(0);}return null;}}

package com.atguigu.dao.impl.basic;import java.util.ArrayList;import java.util.List;import com.atguigu.bean.Department;import com.atguigu.dao.DepartmentDAO;import com.atguigu.dao.impl.BasicDAOImpl;public class DepartmentDAOImplBasic extends BasicDAOImpl implements DepartmentDAO{@Overridepublic void addDepartment(Department department) throws Exception {String sql = "INSERT INTO t_department(did,dname,description) VALUES(NULL,?,?)";update(sql, department.getName(),department.getDescription());}@Override

public void updateDepartment(Department department) throws Exception {String sql = "UPDATE t_department SET dname = ?,description = ? WHERE did = ?";update(sql, department.getName(),department.getDescription(),department.getId());}@Overridepublic void deleteById(String did) throws Exception {String sql = "DELETE FROM t_department WHERE did = ?";update(sql, did);}@Overridepublic Department getById(String did) throws Exception {String sql = "SELECT did as id,dname as name,description FROM t_department WHERE did = ?";Department department = get(sql, did);return department;}@Overridepublic List getAll() throws Exception {String sql = "SELECT did as id,dname as name,description FROM t_department";ArrayList list = getList(sql);return list;}}

package com.atguigu.dao.impl.basic;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Map.Entry;import java.util.Set;import com.atguigu.bean.Employee;import com.atguigu.dao.EmployeeDAO;import com.atguigu.dao.impl.BasicDAOImpl;public class EmployeeDAOImpl extends BasicDAOImpl implements EmployeeDAO{@Overridepublic void addEmployee(Employee emp) throws Exception {String sql = "INSERT INTO t_employee "+ "(eid,ename,tel,gender,salary,commission_pct,birthday,hiredate,job_id,email,mid,address,native_place,did)"+ "VALUES(NULL,?,?,?,?,?,?,?,?,?,?,?,?,?)";Object[] args = new Object[13];args[0] = emp.getEname();args[1] = emp.getTel();args[2] = emp.getGender();args[3] = emp.getSalary();args[4] = emp.getCommissionPct();args[5] = emp.getBirthday();args[6] = emp.getHiredate();args[7] = emp.getJobId();args[8] = emp.getEmail();args[9] = emp.getMid();args[10] = emp.getAddress();args[11] = emp.getNativePlace();args[12] = emp.getDid();update(sql, args);}@Overridepublic void updateEmployee(Employee emp) throws Exception {String sql = "UPDATE t_employee SET "+ "ename=?,tel=?,gender=?,salary=?,commission_pct=?,birthday=?,hiredate=?,job_id=?,email=?,MID=?,address=?,native_place=?,did=?"+" WHERE eid = ?";Object[] args = new Object[14];args[0] = emp.getEname();args[1] = emp.getTel();args[2] = emp.getGender();args[3] = emp.getSalary();args[4] = emp.getCommissionPct();args[5] = emp.getBirthday();args[6] = emp.getHiredate();args[7] = emp.getJobId();args[8] = emp.getEmail();args[9] = emp.getMid();args[10] = emp.getAddress();args[11] = emp.getNativePlace();args[12] = emp.getDid();args[13] = emp.getEid();update(sql, args);}@Overridepublic void deleteById(String eid) throws Exception {String sql = "DELETE FROM t_employee WHERE eid = ?";update(sql, eid);}@Overridepublic Employee getById(String eid) throws Exception {String sql = "SELECT eid,ename,tel,gender,salary,commission_pct as 'commissionPct',birthday,hiredate,"+ "job_id as 'jobId',email,mid,address,native_place as 'nativePlace' ,did FROM t_employee WHERE eid = ?";Employee employee = get(sql, eid);return employee;}@Overridepublic List getAll() throws Exception {String sql = "SELECT eid,ename,tel,gender,salary,commission_pct as 'commissionPct',birthday,hiredate,"+ "job_id as 'jobId',email,mid,address,native_place as 'nativePlace' ,did FROM t_employee";ArrayList list = getList(sql);return list;}@Overridepublic Long getCount() throws Exception {String sql = "SELECT COUNT(*) FROM t_employee";Long value = (Long) getValue(sql);return value;}@Overridepublic List getAll(int page, int pageSize) throws Exception {String sql = "SELECT eid,ename,tel,gender,salary,commission_pct as 'commissionPct',birthday,hiredate,"+ "job_id as 'jobId',email,mid,address,native_place as 'nativePlace' ,did FROM t_employee LIMIT ?,?";ArrayList list = getList( sql, (page-1)*pageSize, pageSize);return list;}@Overridepublic Double getMaxSalary() throws Exception {String sql = "SELECT MAX(salary) FROM t_employee";Double value = (Double) getValue(sql);return value;}@Overridepublic Map getAvgSalaryByDid() throws Exception {String sql = "SELECT did,MAX(salary) FROM t_employee GROUP BY did";List> list = getListMap(sql);HashMap result = new HashMap<>();for (Map map : list) {Set> entrySet = map.entrySet();Integer did = null;Double salary = null;for (Entry entry : entrySet) {String key = entry.getKey();if("did".equals(key)){did = (Integer) entry.getValue();}else{salary = (Double) entry.getValue();}}result.put(did, salary);}return result;}}

package com.atguigu.dao.test;import java.util.List;import java.util.Scanner;import org.junit.Test;import com.atguigu.bean.Department;import com.atguigu.dao.DepartmentDAO;import com.atguigu.dao.impl.basic.DepartmentDAOImplBasic;import com.atguigu.dao.impl.original.DepartmentDAOImpl;import com.atguigu.utils.CMUtility;public class TestDepartmentDAO {// DepartmentDAO dao = new DepartmentDAOImpl();DepartmentDAO dao = new DepartmentDAOImplBasic();@Testpublic void addDepartment() {Scanner input = new Scanner(System.in);System.out.println("请输入部门名称：");String name = input.nextLine();System.out.println("请输入部门简介：");String description = input.nextLine();Department department = new Department(name, description);try {dao.addDepartment(department);System.out.println("添加成功");} catch (Exception e) {e.printStackTrace();System.out.println("添加失败");}}@Testpublic void getAllDepartment() throws Exception {List all = dao.getAll();for (Department department : all) {System.out.println(department);}}@Testpublic void updateDepartment() {try {getAllDepartment();Scanner input = new Scanner(System.in);System.out.println("请选择要修改的部门编号：");String did = input.nextLine();Department dept = dao.getById(did);System.out.println("请输入部门名称("+dept.getName()+")：");String name = CMUtility.readString(dept.getName());System.out.println("请输入部门简介("+dept.getDescription()+")：");String description = CMUtility.readString(dept.getDescription());Department department = new Department(dept.getId(),name, description);dao.updateDepartment(department);System.out.println("修改成功");} catch (Exception e) {e.printStackTrace();System.out.println("修改失败");}}@Testpublic void deleteDepartment() {try {getAllDepartment();Scanner input = new Scanner(System.in);System.out.println("请选择要删除的部门编号：");String did = input.nextLine();dao.deleteById(did);System.out.println("删除成功");} catch (Exception e) {e.printStackTrace();System.out.println("删除失败");}}}

package com.atguigu.dao.test;import java.util.Date;import java.util.List;import java.util.Map;import java.util.Map.Entry;import java.util.Set;import org.junit.Test;import com.atguigu.bean.Employee;import com.atguigu.dao.EmployeeDAO;import com.atguigu.dao.impl.basic.EmployeeDAOImpl;public class TestEmployeeDAO {EmployeeDAO ed = new EmployeeDAOImpl();@Testpublic void addEmployee()throws Exception{//省略键盘输入String ename = "张三";String tel = "10080";String gender = "男";double salary = 10000;

double commissionPct = 0.3;Date birthday = new Date();Date hiredate = new Date();;int jobId = 2;String email="zhangsan@lina.com";int mid = 1;String address = "xx";String nativePlace = "xxx";int did = 2;Employee emp = new Employee(ename, tel, gender, salary, commissionPct, birthday, hiredate, jobId, email, mid, address, nativePlace, did);try {ed.addEmployee(emp);System.out.println("添加成功");} catch (Exception e) {e.printStackTrace();

System.out.println("添加失败");}}@Testpublic void updateEmployee()throws Exception{//省略键盘输入String eid = "1";Employee emp = ed.getById(eid);//这里只演示修改一下，可以修改除了eid以外的所有项目emp.setSalary(emp.getSalary() + 1000);try {ed.updateEmployee(emp);System.out.println("修改成功");} catch (Exception e) {e.printStackTrace();System.out.println("修改失败");}}@Testpublic void deleteById()throws Exception{//省略键盘输入String eid = "26";try {ed.deleteById(eid);System.out.println("删除成功");} catch (Exception e) {e.printStackTrace();System.out.println("删除失败");}}@Testpublic void getAll()throws Exception{List all = ed.getAll();for (Employee employee : all) {System.out.println(employee);}}@Testpublic void getAllPage()throws Exception{Long count = ed.getCount();System.out.println("总记录数：" + count);int pageSize = 5;System.out.println("每页显示5条");int page = 2;System.out.println("用户选择第" + page + "页");List all = ed.getAll(page, pageSize);for (Employee employee : all) {System.out.println(employee);}}@Testpublic void getMaxSalary()throws Exception{Double maxSalary = ed.getMaxSalary();System.out.println("公司最高工资是： " + maxSalary);}@Testpublic void getAvgSalaryByDid()throws Exception{Map map = ed.getAvgSalaryByDid();Set> entrySet = map.entrySet();for (Entry entry : entrySet) {System.out.println(entry.getKey()+ ":" + entry.getValue());}}}

相关阅读：

JDBC的操作有哪些

java编程技术JDBC

JDBC API的使用与操作详解

JDBC工具类解读，让你快速入门

如何使用JDBC API操作数据库