DAO通用CRUD方法

DAO：是指DataAccessObject，即访问数据信息的类和接口。它包括了对数据的CRUD（Create、Retrival、Update、Delete），但不包含任何业务相关的信息。

它的作用是为了实现功能的模块化，从而更易于代码的维护和升级。

1.1 表和 JavaBean 1.2 DAO 接口

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;

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;

1.3 DAO 实现类（1）原生版

DepartmentDAOImpl:

public class DepartmentDAOImpl implements DepartmentDAO { @Override public 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); }

@Override public 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); }

@Override public 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); }

@Override public 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; }

@Override public 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; } }

1.4 抽取 BasicDAO

BasicDAOImpl:

/** * 这个类的作用是：对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 =