[JDBC]基于三层架构和MVC架构的JDBCTools
0.三层架构和MVC架构
0.1DAO是什么
DAO 是 Java 分层开发中专门负责和数据库打交道的层，核心作用是：把所有数据库的增删改查（CRUD）操作封装起来，让业务层（比如你包中的 BAO/Service）不用关心数据库连接、SQL 编写、结果集处理等细节，只专注于业务逻辑。
0.2bean
0.2.1Department
package bean;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@NoArgsConstructor
@AllArgsConstructor
@Data
public class Department {
private int did;
private String dname;
private String description;
//构造器，get/set，toString，equals和hashCode
}
0.2.2Employee
package bean;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.math.BigDecimal;
import java.util.Date;
@NoArgsConstructor
@AllArgsConstructor
@Data
public class Employee {
private Integer eid;
private String ename;
private Double salary;
// private double commissionPct;
private BigDecimal commissionPct;
private Date birthday;
// private char gender;
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(int eid, String ename, double salary) {
this.eid = eid;
this.ename = ename;
this.salary = salary;
}
}
0.3DAO
0.3.1接口：DepartmentDAO
package DAO;
//引入bean文件夹下的
import bean.Department;
import java.util.ArrayList;
public interface DepartmentDAO {
//接口里面定义的方法，就是你要对部门对象和部门表做哪些对象（增、删、改、查）
int insert(Department department);//insert方法负责把一个部门对象，存储到t_department表
int update(Department department);//update方法负责把一个部门对象的信息，覆盖t_department表的一条记录
//大家默认为主键id是不变的，department对象中的did用于定位一条记录，剩余的属性 覆盖 这条记录的其他字段值
int delete(int did);//更常见一些
int delete(String dname);
ArrayList getAllDepartments();
}
0.3.2接口：EmployeeDAO
package DAO;
import bean.Employee;
import java.util.ArrayList;
public interface EmployeeDAO {
int insert(Employee employee);
int update(Employee employee);//主键eid不变
int delete(int eid);
ArrayList getAllEmployees();
}
0.3.3实现类：DepartmentDAOImpl
package DAO;
import BAO.BaseDaoThree;
import bean.Department;
import java.sql.SQLException;
import java.util.ArrayList;
public class DepartmentDAOImpl implements DepartmentDAO {
@Override
public int insert(Department department) {
try {
//t_department表中did自增（提醒大家对你操作的表的结构，包括字段、约束必须清楚）
String sql = "insert into t_department values(null,?,?)";
int len = BaseDaoThree.update(sql, department.getDname(), department.getDescription());
return len;
} catch (SQLException e) {
//把编译时类型的异常，包装为运行时类型的异常，异常没有真正处理，仍然扔出去了，编译器不报错了
throw new RuntimeException(e);
}
}
@Override
public int update(Department department) {
String sql = "update t_department set dname=?, description=? where did = ?";
try {
int len = BaseDaoThree.update(sql, department.getDname(), department.getDescription(),department.getDid());
return len;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public int delete(int did) {
String sql = "delete from t_department where did = ?";
try {
int len = BaseDaoThree.update(sql, did);
return len;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public int delete(String dname) {
String sql = "delete from t_department where dname = ?";
try {
int len = BaseDaoThree.update(sql, dname);
return len;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public ArrayList getAllDepartments() {
String sql = "select * from t_department";
try {
ArrayList list = BaseDaoThree.queryList(Department.class, sql);
return list;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
0.3.4实现类：EmployeeDAOImpl
package DAO;
import BAO.BaseDaoThree;
import bean.Employee;
import java.sql.SQLException;
import java.util.ArrayList;
public class EmployeeDAOImpl implements EmployeeDAO {
@Override
public int insert(Employee employee) {
//eid是自增的
String sql = """
INSERT INTO t_employee(`eid`,`ename`,`salary`,`commission_pct`,
`birthday`,`gender`,`tel`,`email`,`address`,
`work_place`,`hiredate`,`job_id`,`mid`,`did`)
VALUES(NULL,?,?,?,?,?,?,?,?,?,?,?,?,?);
""";//文本块
try {
int len = BaseDaoThree.update(sql, employee.getEname(),
employee.getSalary(),
employee.getCommissionPct(),
employee.getBirthday(),
employee.getGender(),
employee.getTel(),
employee.getEmail(),
employee.getAddress(),
employee.getWorkPlace(),
employee.getHiredate(),
employee.getJobId(),
employee.getMid(),
employee.getDid());
return len;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public int update(Employee employee) {
//eid不变
String sql = """
update t_employee set `ename`=?,`salary`=?,`commission_pct`=?,`birthday`=?,`gender`=?,`tel`=?,`email`=?,
`address`=?,`work_place`=?,`hiredate`=?,`job_id`=?,`mid`=?,`did`=? where `eid`=?;
""";
try {
int len = BaseDaoThree.update(sql, employee.getEname(),
employee.getSalary(),
employee.getCommissionPct(),
employee.getBirthday(),
employee.getGender(),
employee.getTel(),
employee.getEmail(),
employee.getAddress(),
employee.getWorkPlace(),
employee.getHiredate(),
employee.getJobId(),
employee.getMid(),
employee.getDid(),
employee.getEid());
return len;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public int delete(int eid) {
String sql = "delete from t_employee where eid = ?";
try {
int len = BaseDaoThree.update(sql, eid);
return len;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public ArrayList getAllEmployees() {
//String sql = "select * from t_employee";
String sql = """
SELECT `eid`,`ename`,`salary`,
`commission_pct` AS commissionPct,
`birthday`,`gender`,`tel`,`email`,`address`,
`work_place` AS workPlace,`hiredate`,
`job_id` AS jobId,`mid`,`did` FROM t_employee;
""";
try {
ArrayList list = BaseDaoThree.queryList(Employee.class, sql);
return list;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
0.4BaseDAO
0.4.1BaseDAOThree
package BAO;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
public class BaseDaoThree { //最终版
//通用的增、删、改功能
public static int update(String sql, Object... args) throws SQLException {
//1、获取连接
Connection connection = JDBCUtilTwo.getConnection();
//2、准备sql（通过参数传入）
//3、创建PreparedStatement对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//4、设置？
for(int i=1; i<=args.length; i++){
preparedStatement.setObject(i, args[i-1]);
}
//5、调用 PreparedStatement对象.executeUpdate()
int len = preparedStatement.executeUpdate();
//6、释放资源
preparedStatement.close();
// JDBCUtilOne.freeConnection(connection);//不关闭，如果关闭了，同一个事务的其他sql用不了了
return len;
}
//通用的查询功能
//第一个参数可以设置为Class clazz，它代表 这个查询结果的T的实际类型，T可能代表Employee，Department，Order....
//第一个参数也可以设置为String className， T的具体类型名 com.atguigu.bean.Employee等
public static ArrayList queryList(Class clazz, String sql, Object... args ) throws Exception {
//1、获取连接
Connection connection = JDBCUtilTwo.getConnection();
//2、准备sql（通过参数传入）
//3、创建PreparedStatement对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//4、设置？
for(int i=1; i<=args.length; i++){
preparedStatement.setObject(i, args[i-1]);
}
//5、调用 PreparedStatement对象.executeQuery()
ResultSet resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();//获取结果集的元数据
int columnCount = metaData.getColumnCount();
//怎么把结果集的数据 变为 一组对象，放到 ArrayList集合中返回
ArrayList result = new ArrayList<>();
//6、遍历resultSet
while(resultSet.next()){//while循环循环一次，代表一行，一行代表一个对象
//(1)创建对象
//获取无参构造
Constructor constructor = clazz.getDeclaredConstructor();
constructor.setAccessible(true);//以防这个构造器不是公共的
//new对象
T t = constructor.newInstance();
//(2)设置属性
for(int i=1; i<=columnCount; i++) {//每一次for循环代表一个属性
// String columnName = metaData.getColumnName(i);//getColumnName获取列的原名称
String columnName = metaData.getColumnLabel(i);//getColumnLabel获取列的别名或原名
//获取某个属性对应的Fieid对象
Field field = clazz.getDeclaredField(columnName);
//通常属性私有化
field.setAccessible(true);
//设置值
field.set(t, resultSet.getObject(i));
}
//添加t到集合中
result.add(t);
}
return result;
}
}
0.4.2JDBCUtilTwo
package BAO;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCUtilTwo {//最终版
private static DataSource ds;
private static ThreadLocal threadLocal = new ThreadLocal<>();
static {
try {
//静态代码块，在类加载和初始化时执行
Properties properties = new Properties();
properties.load(JDBCUtilTwo.class.getClassLoader().getResourceAsStream("druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
Connection connection = threadLocal.get();//比喻，某个线程到自己的背包map中获取(threadLocal,value)值
if(connection == null){//如果是空的，说明这个线程还没有获取过连接
connection = ds.getConnection();
threadLocal.set(connection);
}
//如果if不成立，说明这个线程之前已经拿过连接了，已经放入背包了，直接返回之前的连接对象即可
return connection;
}
public static void freeConnection(){
Connection connection = threadLocal.get();//比喻，某个线程到自己的背包map中获取(threadLocal,value)值
if(connection != null){
try {
threadLocal.remove();//从背包中删除，当前线程就没有这个连接了
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
0.5BaseDAO和DAO的区别
| 类型 | BaseDAO（基础类） | DepartmentDAO（业务 DAO） |
|---|---|---|
| 核心定位 | DAO 层的 “通用工具父类” | DAO 层的 “业务实现类” |
| 封装的逻辑 | 所有 DAO 都需要的通用操作（获取连接、关闭资源、通用 CRUD、分页） | 针对特定表的专属 CRUD（查部门、新增部门） |
| 能否直接使用 | 不能直接 new，只能被其他 DAOImpl 继承 | 可直接实例化 / 调用（如 new DepartmentDAOImpl ()） |
| 方法特点 | 泛型 / 通用方法（如 save(Object obj)） | 专属方法（如 findDepartmentById(int did)） |
一.基于druid的通用连接
1.1图示:
1.2code:
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCConnectionTool {
private static DataSource ds;
static {
try {
//静态代码块，在类加载和初始化时执行
Properties properties = new Properties();
properties.load(testConnection.class.getClassLoader().getResourceAsStream("druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
public static void freeConnection(Connection connection){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
=========================================================================
import org.junit.jupiter.api.Test;
import java.sql.Connection;
public class testConnection {
@Test
public void testConnection()throws Exception {
Connection conn = JDBCConnectionTool.getConnection();
System.out.println(conn);
}
}
二.通用的增
2.1Department
@Override
public int insert(Department department) {
try {
//t_department表中did自增（提醒大家对你操作的表的结构，包括字段、约束必须清楚）
String sql = "insert into t_department values(null,?,?)";
int len = BaseDaoThree.update(sql, department.getDname(), department.getDescription());
return len;
} catch (SQLException e) {
//把编译时类型的异常，包装为运行时类型的异常，异常没有真正处理，仍然扔出去了，编译器不报错了
throw new RuntimeException(e);
}
}
2.2Employee
@Override
public int insert(Employee employee) {
//eid是自增的
String sql = """
INSERT INTO t_employee(`eid`,`ename`,`salary`,`commission_pct`,
`birthday`,`gender`,`tel`,`email`,`address`,
`work_place`,`hiredate`,`job_id`,`mid`,`did`)
VALUES(NULL,?,?,?,?,?,?,?,?,?,?,?,?,?);
""";//文本块
try {
int len = BaseDaoThree.update(sql, employee.getEname(),
employee.getSalary(),
employee.getCommissionPct(),
employee.getBirthday(),
employee.getGender(),
employee.getTel(),
employee.getEmail(),
employee.getAddress(),
employee.getWorkPlace(),
employee.getHiredate(),
employee.getJobId(),
employee.getMid(),
employee.getDid());
return len;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
三.通用的删
3.1Department
@Override
public int delete(int did) {
String sql = "delete from t_department where did = ?";
try {
int len = BaseDaoThree.update(sql, did);
return len;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
3.2Employee
@Override
public int delete(int eid) {
String sql = "delete from t_employee where eid = ?";
try {
int len = BaseDaoThree.update(sql, eid);
return len;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
四.通用的改
4.1Department
@Override
public int update(Department department) {
String sql = "update t_department set dname=?, description=? where did = ?";
try {
int len = BaseDaoThree.update(sql, department.getDname(), department.getDescription(),department.getDid());
return len;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
4.2Employee
@Override
public int update(Employee employee) {
//eid不变
String sql = """
update t_employee set `ename`=?,`salary`=?,`commission_pct`=?,`birthday`=?,`gender`=?,`tel`=?,`email`=?,
`address`=?,`work_place`=?,`hiredate`=?,`job_id`=?,`mid`=?,`did`=? where `eid`=?;
""";
try {
int len = BaseDaoThree.update(sql, employee.getEname(),
employee.getSalary(),
employee.getCommissionPct(),
employee.getBirthday(),
employee.getGender(),
employee.getTel(),
employee.getEmail(),
employee.getAddress(),
employee.getWorkPlace(),
employee.getHiredate(),
employee.getJobId(),
employee.getMid(),
employee.getDid(),
employee.getEid());
return len;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
五.通用的查
5.1Department
@Override public ArrayListgetAllDepartments() { String sql = "select * from t_department"; try { ArrayList list = BaseDaoThree.queryList(Department.class, sql); return list; } catch (Exception e) { throw new RuntimeException(e); } }
5.2Employee
@Override public ArrayListgetAllEmployees() { //String sql = "select * from t_employee"; String sql = """ SELECT `eid`,`ename`,`salary`, `commission_pct` AS commissionPct, `birthday`,`gender`,`tel`,`email`,`address`, `work_place` AS workPlace,`hiredate`, `job_id` AS jobId,`mid`,`did` FROM t_employee; """; try { ArrayList list = BaseDaoThree.queryList(Employee.class, sql); return list; } catch (Exception e) { throw new RuntimeException(e); } }
