JDBC使用

1 JDBC介绍

JDBC : java database connectivity "编写java程序 实现对任意一个DBMS软件的数据进行增删改查，都需要使用JDBC" JDBC是sun公司发布的一套关于数据库的规范 JDBC实际上就是一套接口，各个数据库厂商都需要实现这个接口，实现这一套接口中的方法 好处:程序员只需要学会JDBC接口，就可以调用各个数据库厂商的内容，轻松实现增删改查 各个数据库厂商需要提供JDBC接口的实现，这些实现统称为 "驱动"

2 获取DBMS的连接

用户名 密码 mysql驱动 数据库的ip和端口号和数据库名称

2.1 获取连接

public class TestDemo { public static void main(String[] args) { // 定义数据库的url jdbc:mysql://ip:端口号/数据库名称 // 如果ip是localhost 端口号是 3306 可以省略 // String url = "jdbc:mysql://localhost:3306/mydb"; String url = "jdbc:mysql:///mydb"; // 用户名 String username = "root"; // 密码 String password = "root"; ​ Connection connection = null; try { // 注册驱动(让jvm知道使用的是哪一个DBMS软件) // 在java项目中，不需要注册，mysql驱动包中存在一个服务发现机制，会去META-INF/services/java.sql.Driver文件中的内容，自动注册驱动。 // 但是，如果是web/maven项目结构中就不能省略 // DriverManager.registerDriver(new Driver()); // 编写代码 获取mysql连接 connection = DriverManager.getConnection(url, username, password); System.out.println(connection); } catch (SQLException e) { throw new RuntimeException(e); }finally { // 关闭连接 if (connection != null){ try { connection.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } } } ​

2.2 获取连接优化

注册驱动优化

工具类的封装

package cn.javasm.utils; ​ import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; ​ /** * @author : gfs * @version : 0.1 * @className: DBUtil * @date : 2025/12/8 15:05 * @description: 数据库相关工具类 * @since : jdk17 */ public class DBUtil { ​ private DBUtil(){} ​ private static Properties properties = new Properties(); ​ static { // 静态代码块中 读取文件 try { properties.load(new FileInputStream("src/jdbc.properties")); // 注册驱动 Class.forName(properties.getProperty("jdbc.driver")); System.out.println(properties); } catch (IOException e) { throw new RuntimeException(e); } catch (ClassNotFoundException e) { throw new RuntimeException(e); } } ​ ​ /** * 获取连接 * @return 连接 */ public static Connection getConncetion(){ Connection connection = null; try { connection = DriverManager.getConnection(properties.getProperty("jdbc.url"), properties.getProperty("jdbc.username"), properties.getProperty("jdbc.password")); } catch (Exception e) { throw new RuntimeException(e); } return connection; } ​ /** * 释放连接 * @param connection */ public static void release(Connection connection){ if (connection != null){ try { connection.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } } ​

3 CRUD

3.1 添加 insert

public interface UserDao { /** * 添加用户 * @return 影响数据库的行数 */ int addUser(); } ​ ​ public class UserDaoImpl implements UserDao { @Override public int addUser() { // 获取数据库的连接 Connection conncetion = DBUtil.getConncetion(); // 编写sql语句 String sql = "INSERT INTO user (username,age,password,balance) VALUES ('张无忌',20,'123456',8899.9)"; PreparedStatement preparedStatement = null; try { // 通过连接 获取预编译对象 preparedStatement = conncetion.prepareStatement(sql); // 执行sql语句,返回数据库受影响的行数 int row = preparedStatement.executeUpdate(); return row; } catch (SQLException e) { throw new RuntimeException(e); }finally { // 关闭连接 DBUtil.release(conncetion,preparedStatement); } } }

优化:

创建实体类User

package cn.javasm.entity; ​ import lombok.Data; import lombok.experimental.Accessors; ​ import java.math.BigDecimal; import java.time.LocalDateTime; ​ /** * @author : gfs * @version : 0.1 * @className: User * @date : 2025/12/8 15:41 * @description: * @since : jdk17 */ @Data @Accessors(chain = true) public class User { /** * 用户标识 */ private Integer id; /** * 用户名 */ private String username; ​ /** * 年龄 */ private Integer age; ​ /** * 密码 */ private String password; ​ /** * 头像 */ private String image; ​ /** * 余额 */ private BigDecimal balance; ​ /** * 创建时间 */ private LocalDateTime createTime; ​ /** * 更新时间 */ private LocalDateTime updateTime; ​ } ​

UserDao接口中

/** * 添加指定的用户 * @return 影响数据库的行数 */ int addUser(User user);

UserDaoImpl中

@Override public int addUser(User user) { // 获取数据库连接 Connection connection = DBUtil.getConncetion(); // 编写sql语句 // ? 代表占位符，在JDBC中 一个?占一个位置 表示一个数据 String sql = "insert into user (username,age,password,balance) values (?,?,?,?)"; PreparedStatement preparedStatement = null; try { // 获取预编译对象 preparedStatement = connection.prepareStatement(sql); // 给?赋值 // 参数1：?所在的位置 从1开始 preparedStatement.setString(1,user.getUsername()); preparedStatement.setInt(2,user.getAge()); preparedStatement.setObject(3,user.getPassword()); preparedStatement.setBigDecimal(4,user.getBalance()); ​ // 执行sql语句 executeUpdate 执行DML insert update delete int row = preparedStatement.executeUpdate(); return row; } catch (SQLException e) { throw new RuntimeException(e); }finally { // 关闭连接 DBUtil.release(connection,preparedStatement); } }

测试类

public static void main(String[] args) { Scanner scanner = new Scanner(System.in); System.out.println("请输入用户名"); String username = scanner.next(); System.out.println("请输入密码"); String password = scanner.next(); System.out.println("请输入年龄"); int age = scanner.nextInt(); System.out.println("请输入余额"); BigDecimal balance = scanner.nextBigDecimal(); User user = new User().setUsername(username).setAge(age).setPassword(password).setBalance(balance); UserDao userDao = new UserDaoImpl(); int row = userDao.addUser(user); System.out.println(row > 0 ? "success" : "error"); }

3.2 删除 delete

delete from user where id = ?

• UserDao接口

/** * 根据id删除用户 * @param id 用户的id * @return 受影响的行数 */ int deleteUserById(int id);

• UserDaoImpl中

@Override public int deleteUserById(int id) { // 获取连接 Connection connection = DBUtil.getConncetion(); // 编写sql语句 String sql = "DELETE FROM user WHERE id = ?"; PreparedStatement preparedStatement = null; try { // 获取预编译对象 preparedStatement = connection.prepareStatement(sql); // 给?赋值 preparedStatement.setInt(1,id); // 执行sql语句 int row = preparedStatement.executeUpdate(); return row; } catch (SQLException e) { throw new RuntimeException(e); }finally { // 关闭连接 DBUtil.release(connection,preparedStatement); } }

3.3 更新 update

update user set username = ?,age = ?,password = ? where id = ?

UserDao接口中

/** * 根据id更新用户 * @param user 用户 * @return 受影响的行数 */ int updateUserById(User user);

UserDaoImpl中

@Override public int updateUserById(User user) { // 获取连接 Connection connection = DBUtil.getConncetion(); // 编写sql语句 String sql = "update user set username = ?,age = ?, password = ? where id = ?"; // 获取预编译对象 PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(sql); // 给?赋值 preparedStatement.setString(1,user.getUsername()); preparedStatement.setInt(2,user.getAge()); preparedStatement.setString(3,user.getPassword()); preparedStatement.setInt(4,user.getId()); // 执行sql语句 int row = preparedStatement.executeUpdate(); return row; } catch (SQLException e) { throw new RuntimeException(e); }finally { // 关闭连接 DBUtil.release(connection,preparedStatement); } }

3.4 查询

3.4.1 根据id查询

select * from user where id = ?

UserDao接口

/** * 根据id查询用户 * @param id 用户的id * @return 查询到的用户 */ User queryUserById(int id);

UserDaoImpl中

@Override public User queryUserById(int id) { // 获取连接 Connection connection = DBUtil.getConncetion(); // 编写sql语句 String sql = "select * from user where id = ?"; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { // 获取预编译对象 preparedStatement = connection.prepareStatement(sql); // 给?赋值 preparedStatement.setInt(1,id); // 执行sql语句，获取结果集 一行记录是 结果集 中的一个结果 resultSet = preparedStatement.executeQuery(); // next() 返回true代表有下一个元素 返回false代表没有下一个元素 if (resultSet.next()){ // 根据位置获取结果的数据 int uid = resultSet.getInt(1); // 根据列名获取结果的数据 String username = resultSet.getString("username"); int age = (int) resultSet.getObject(3); String password = resultSet.getString(4); String image = resultSet.getString("image"); BigDecimal balance = resultSet.getBigDecimal(6); LocalDateTime createTime = (LocalDateTime) resultSet.getObject(7); LocalDateTime updateTime = (LocalDateTime) resultSet.getObject(8); User user = new User().setId(uid).setUsername(username).setPassword(password).setAge(age).setBalance(balance).setImage(image).setCreateTime(createTime).setUpdateTime(updateTime); return user; } } catch (SQLException e) { throw new RuntimeException(e); }finally { // 关闭连接 DBUtil.release(connection,preparedStatement,resultSet); } return null; }

3.4.2 查询所有

UserDao接口

List<User> queryAll();

UserDaoImpl

@Override public List<User> queryAll() { // 获取连接 Connection connection = DBUtil.getConncetion(); // 编写sql语句 String sql = "select * from user"; // 获取预编译对象 PreparedStatement preparedStatement = null; ResultSet resultSet = null; // 创建集合 List<User> list = new ArrayList<>(); try { preparedStatement = connection.prepareStatement(sql); // 执行sql语句 resultSet = preparedStatement.executeQuery(); while (resultSet.next()){ list.add(getUser(resultSet)); } return list; } catch (SQLException e) { throw new RuntimeException(e); }finally { // 关闭资源 DBUtil.release(connection,preparedStatement,resultSet); } }

3.4.3 分页查询

后端需要前端传递的是哪一页 page，每页显示多少条 pageSize

后端传递给前端的是 当前页的数据 页面的总数量

-- 分页查询 select * from user limit ?,? 第一个?: (page - 1) * pageSize 第二个?: pageSize -- 查询总记录数 select count(*) from user;

UserDao接口

/** * 分页查询 * @param page 哪一页 * @param pageSize 每页显示的数量 * @return */ List<User> queryUserByPage(int page,int pageSize); /** * 查询数据的总数量 用于分页 * @return 总数量 */ int queryUserCount();

UserDaoImpl类

@Override public List<User> queryUserByPage(int page, int pageSize) { // 获取连接 Connection connection = DBUtil.getConncetion(); // 执行sql语句 String sql = "select * from user limit ?,?"; PreparedStatement preparedStatement = null; ResultSet resultSet = null; // 创建集合 List<User> list = new ArrayList<>(); try { // 获取预编译对象 preparedStatement = connection.prepareStatement(sql); // 给?赋值 preparedStatement.setInt(1,(page - 1) * pageSize); preparedStatement.setInt(2,pageSize); // 执行sql语句 resultSet = preparedStatement.executeQuery(); while (resultSet.next()){ list.add(getUser(resultSet)); } return list; } catch (SQLException e) { throw new RuntimeException(e); }finally { // 关闭连接 DBUtil.release(connection,preparedStatement,resultSet); } } @Override public int queryUserCount() { // 获取连接 Connection connection = DBUtil.getConncetion(); // 编写sql String sql = "select count(*) from user"; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { // 获取预编译对象 preparedStatement = connection.prepareStatement(sql); // 执行sql语句 resultSet = preparedStatement.executeQuery(); // 结果集中是总数量 if (resultSet.next()){ return resultSet.getInt(1); } } catch (SQLException e) { throw new RuntimeException(e); }finally { // 关闭连接 DBUtil.release(connection,preparedStatement,resultSet); } return 0; }

• 根据条件查询 并分页

用户名模糊查询

年龄区间查询

create_time区间查询

3.4.4 根据条件查询 并分页

-- 条件 根据用户名模糊搜索 根据年龄在区间范围内搜索 minAge maxAge 根据创建时间在区间范围内搜索 beginTime endTime -- 没有条件 select * from user limit ?,? -- 用户名 select * from user where username like ? limit ?,? -- 用户名 年龄 select * from user where username like ? AND age between ? and ? limit ?,? -- 用户名 年龄 创建时间 select * from user where username like ? AND age between ? and ? AND create_time between ? and ? limit ?,? -- 动态拼接sql语句 where username like ? and age between ? and ? and create_time between ? and ? -- 没有条件 select count(*) from user; -- 用户名 select count(*) from user where username like ? where username like ? and age between ? and ? and create_time between ? and ?

UserDao接口中

public interface UserDao { /** * 按照条件查询并分页 * @param searchParamVo 查询条件 * @param page 第几页 * @param pageSize 每页显示的数量 * @return */ List<User> queryUserByParamAndPage(SearchParamVo searchParamVo,int page,int pageSize); /** * 查询记录的总数量 * @param searchParamVo 查询条件 * @return 总数量 */ int queryUserByParamCount(SearchParamVo searchParamVo); }

UserDaoImpl中

public class UserDaoImpl implements UserDao { // where username like ? and age between ? and ? and create_time between ? and ? @Override public List<User> queryUserByParamAndPage(SearchParamVo searchParamVo, int page, int pageSize) { // 获取连接 Connection connection = DBUtil.getConncetion(); // 编写sql StringBuilder stringBuilder = new StringBuilder("select * from user"); // 拼接sql appendSql(searchParamVo,stringBuilder); stringBuilder.append(" limit ?,? "); String sql = stringBuilder.toString(); PreparedStatement preparedStatement = null; ResultSet resultSet = null; // 创建集合 List<User> list = new ArrayList<>(); try { // 获取预编译对象 preparedStatement = connection.prepareStatement(sql); // 给?赋值 int count = setParam(searchParamVo, preparedStatement); // 给limit的?赋值 preparedStatement.setInt(count++,(page - 1) * pageSize); preparedStatement.setInt(count,pageSize); // 执行sql语句 resultSet = preparedStatement.executeQuery(); while (resultSet.next()){ list.add(getUser(resultSet)); } return list; } catch (SQLException e) { throw new RuntimeException(e); }finally { // 关闭连接 DBUtil.release(connection,preparedStatement,resultSet); } } private static int setParam(SearchParamVo searchParamVo,PreparedStatement preparedStatement) throws SQLException { // 获取参数 String nameParam = searchParamVo.getNameParam(); Integer minAge = searchParamVo.getMinAge(); String beginTime = searchParamVo.getBeginTime(); Integer maxAge = searchParamVo.getMaxAge(); String endTime = searchParamVo.getEndTime(); // 记录参数的位置 int count = 1; // 用户名 if (nameParam != null && !nameParam.isBlank()){ preparedStatement.setString(count++,"%" + nameParam + "%"); } if (minAge != null){ preparedStatement.setInt(count++,minAge); preparedStatement.setInt(count++,maxAge); } if (beginTime != null && !beginTime.isBlank()){ preparedStatement.setString(count++,beginTime); preparedStatement.setString(count++,endTime); } return count; } private static void appendSql(SearchParamVo searchParamVo,StringBuilder stringBuilder){ // 获取参数 String nameParam = searchParamVo.getNameParam(); Integer minAge = searchParamVo.getMinAge(); String beginTime = searchParamVo.getBeginTime(); // 判断是否添加过where true代表添加过 boolean flag = false; if (nameParam != null && !nameParam.isBlank()){ stringBuilder.append(" where username like ? AND"); flag = true; } if (minAge != null){ if (!flag) { stringBuilder.append(" where "); flag = true; } stringBuilder.append(" age BETWEEN ? AND ? AND"); } if (beginTime != null && !beginTime.isBlank()){ if (!flag){ stringBuilder.append(" where "); flag = true; } stringBuilder.append(" create_time BETWEEN ? AND ? AND"); } if (flag){ // 只要有条件，最后必定多一个AND stringBuilder.delete(stringBuilder.lastIndexOf("AND"),stringBuilder.length()); } } private static User getUser(ResultSet resultSet) throws SQLException { // 根据位置获取结果的数据 int uid = resultSet.getInt(1); // 根据列名获取结果的数据 String username = resultSet.getString("username"); int age = (int) resultSet.getObject(3); String password = resultSet.getString(4); String image = resultSet.getString("image"); BigDecimal balance = resultSet.getBigDecimal(6); LocalDateTime createTime = (LocalDateTime) resultSet.getObject(7); LocalDateTime updateTime = (LocalDateTime) resultSet.getObject(8); User user = new User().setId(uid).setUsername(username).setPassword(password).setAge(age).setBalance(balance).setImage(image).setCreateTime(createTime).setUpdateTime(updateTime); return user; } @Override public int queryUserByParamCount(SearchParamVo searchParamVo) { // 获取连接 Connection connection = DBUtil.getConncetion(); // 拼接sql StringBuilder stringBuilder = new StringBuilder(); stringBuilder.append("select count(*) from user "); appendSql(searchParamVo,stringBuilder); PreparedStatement preparedStatement = null; ResultSet resultSet = null; int totalRow = 0; try { // 获取预编译对象 preparedStatement = connection.prepareStatement(stringBuilder.toString()); // 给?赋值 setParam(searchParamVo,preparedStatement); // 执行sql语句 resultSet = preparedStatement.executeQuery(); if (resultSet.next()){ totalRow = resultSet.getInt(1); } } catch (SQLException e) { throw new RuntimeException(e); }finally { // 关闭连接 DBUtil.release(connection,preparedStatement,resultSet); } return totalRow; } }

3.4.5 多表查询

Role类

@Data @Accessors(chain = true) public class Role { /** * 角色id */ private Integer id; /** * 角色名称 */ private String roleName; /** * 角色描述 */ private String roleDesc; }

Dept类

@Data @Accessors(chain = true) public class Dept { /** * 部门id */ private int deptno; /** * 部门名称 */ private String dname; /** * 部门位置 */ private String loc; }

UserDao接口中

/** * 查询用户 角色和部门信息 * @return */ List<UserRoleDept> queryUserAndRoleAndDept();

UserDaoImpl中

@Override public List<UserRoleDept> queryUserAndRoleAndDept() { // 获取连接 Connection connection = DBUtil.getConncetion(); // 编写sql String sql = "select *\n" + "FROM\n" + "user u,role r,dept d\n" + "WHERE\n" + "u.rid = r.id \n" + "AND\n" + "u.deptno = d.deptno"; PreparedStatement preparedStatement = null; ResultSet resultSet = null; // 创建集合 List<UserRoleDept> list = new ArrayList<>(); try { // 获取预编译对象 preparedStatement = connection.prepareStatement(sql); // 执行sql resultSet = preparedStatement.executeQuery(); while (resultSet.next()){ int id = resultSet.getInt(1); String username = resultSet.getString(2); int age = resultSet.getInt(3); String password = resultSet.getString(4); User user = new User().setId(id).setUsername(username).setAge(age).setPassword(password); String roleName = resultSet.getString(12); String roleDesc = resultSet.getString(13); Role role = new Role().setRoleName(roleName).setRoleDesc(roleDesc); String dname = resultSet.getString("dname"); Dept dept = new Dept().setDname(dname); UserRoleDept userRoleDept = new UserRoleDept().setUser(user).setRole(role).setDept(dept); list.add(userRoleDept); } return list; } catch (SQLException e) { throw new RuntimeException(e); }finally { DBUtil.release(connection,preparedStatement,resultSet); } }

3.5 新增角色

select * from menu; insert into role (role_name,role_desc) values(?,?) insert into role_menu(rid,mid) values(?,?)

public class TestDemo { public static void main(String[] args) throws SQLException { MenuDao menuDao = new MenuDaoImpl(); List<Menu> menus = menuDao.queryAll(); String str = "|- "; menus.stream() .filter(menu -> menu.getParentMenuId() == 0) .peek(parentMenu -> { System.out.println(str + parentMenu.getId() + ":" + parentMenu.getMenuName()); queryChildMenu(parentMenu,menus,"| " + str); }) .count(); // 传统方法 // for (Menu menu : menus) { // if (menu.getParentMenuId() == 0){ // System.out.println("|- " + menu.getId() + menu.getMenuName()); // for (Menu menu1 : menus) { // if (menu1.getParentMenuId() == menu.getId()){ // System.out.println("|- " + menu1.getId() + menu1.getMenuName()); // for (Menu menu2 : menus) { // if (menu2.getParentMenuId() == menu1.getId()){ // System.out.println("|- " + menu2.getId() + menu2.getMenuName()); // } // } // } // } // } // } } /** * 层级展示菜单 * @param parentMenu 父菜单 * @param menuList 全部菜单 * @param str 拼接的字符串 */ private static void queryChildMenu(Menu parentMenu,List<Menu> menuList,String str){ menuList.stream().filter(menu -> menu.getParentMenuId() == parentMenu.getId()).peek(menu -> { System.out.println(str + menu.getId() + ":" + menu.getMenuName()); queryChildMenu(menu,menuList,"| " + str); }).count(); } }

RoleDao接口中

public interface RoleDao { /** * 添加角色 * @param role 角色对象 * @return 生成的id */ int addRole(Role role) throws SQLException; int addRoleAndMenu(int rid,String[] mendIdArray) throws SQLException; } public class RoleDaoImpl implements RoleDao { @Override public int addRole(Role role) throws SQLException { // 获取连接 Connection connection = DBUtil.getConncetion(); // 执行sql String sql = "insert into role (role_name,role_desc) values(?,?)"; // 获取预编译对象 PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); // 给?赋值 preparedStatement.setString(1,role.getRoleName()); preparedStatement.setString(2,role.getRoleDesc()); // 执行sql preparedStatement.executeUpdate(); // 获取刚刚添加角色的id LAST_INSERT_ID() ResultSet resultSet = preparedStatement.getGeneratedKeys(); int autoId = 0; if (resultSet.next()){ autoId = resultSet.getInt(1); } // 关闭连接 DBUtil.release(connection,preparedStatement); return autoId; } // insert into role_menu(rid,mid) values (?,?),(?,?),(?,?)... @Override public int addRoleAndMenu(int rid, String[] mendIdArray) throws SQLException { // 获取连接 Connection connection = DBUtil.getConncetion(); // 拼接sql StringBuilder stringBuilder = new StringBuilder(); stringBuilder.append("insert into role_menu (rid,mid) values "); for (int i = 0; i < mendIdArray.length; i++) { stringBuilder.append("(?,?)"); if (i == mendIdArray.length - 1) break; stringBuilder.append(","); } // 获取预编译对象 PreparedStatement preparedStatement = connection.prepareStatement(stringBuilder.toString()); // 给?赋值 int count = 1; for (String menuId : mendIdArray) { preparedStatement.setObject(count++,rid); preparedStatement.setObject(count++,menuId); } // 执行sql语句 int row = preparedStatement.executeUpdate(); // 关闭连接 DBUtil.release(connection,preparedStatement); return row; } }

4 DCL

事务是逻辑上的一组操作，组成这组操作的单元要么同时成功，要么同时失败

4.1 Mysql进行事务管理

create table account( id int PRIMARY KEY auto_increment, name VARCHAR(255), money double ) insert into account values (null,'zs',1000); insert into account values (null,'ls',1000); insert into account values (null,'ww',1000); -- 开启事务 start transaction; -- 提交事务 commit; -- 回滚事务 rollback; update account set money = money - 100 where name = 'zs' update account set money = money + 100 where name = 'ls'

4.2 事务的特性和隔离级别【面试题】

4.2.1 事务的特性

• 原子性

  • 原子性是指事务是一个不可分割的工作单位。事务中的操作要么都发生，要么都不发生

• 一致性

  • 事务前后数据的完整性要保持一致

zs 1000 ls 1000 总共2000 成功 zs 900 ls 1100 总共2000 不成功 zs 1000 ls 1000 总共2000

• 持久性

  • 一个事务一旦提交，对数据库中的数据改动是永久性的

• 隔离性

  • 指多个用户并发操作数据库时，一个用户的事务不能被其他用户的事务所干扰。

4.2.2 没有隔离性会引发的问题

4.2.3 事务的隔离级别

隔离级别越高，安全性越高，性能效率越差

-- 查看当前事务的隔离级别 select @@transaction_isolation; -- 设置当前事务的隔离级别 set session transaction isolation level 隔离级别

4.3 代码级别使用事务

// 开启事务 connection.setAutoCommit(false); // 提交事务 connection.commit(); // 回滚事务 connection.rollback();

package cn.javasm.service.impl; import cn.javasm.dao.RoleDao; import cn.javasm.dao.impl.RoleDaoImpl; import cn.javasm.entity.Role; import cn.javasm.service.RoleService; import cn.javasm.utils.DBUtil; import java.sql.Connection; import java.sql.SQLException; /** * @author : gfs * @version : 0.1 * @className: RoleServiceImpl * @date : 2025/12/10 11:00 * @description: * @since : jdk17 */ public class RoleServiceImpl implements RoleService { @Override public int insertRoleService(Role role, String[] menuIdArray) { // 业务逻辑层 密码加密 事务控制 记录日志 检查权限... // 获取连接 Connection connection = DBUtil.getConncetion(); int row = 0; try { // 手动开启事务 connection.setAutoCommit(false); RoleDao roleDao = new RoleDaoImpl(connection); int roleId = roleDao.addRole(role); // int i = 1 / 0; row = roleDao.addRoleAndMenu(roleId, menuIdArray); // 提交事务 connection.commit(); } catch (Exception e) { try { // 回滚事务 connection.rollback(); } catch (SQLException ex) { throw new RuntimeException(ex); } throw new RuntimeException(e); }finally { // 关闭连接 DBUtil.release(connection); } return row; } }

5 自定义连接池

5.1 初级版本

package cn.javasm.utils; import java.sql.Connection; import java.util.LinkedList; /** * @author : gfs * @version : 0.1 * @className: MyDataSource1 * @date : 2025/12/10 11:30 * @description: * @since : jdk17 */ public class MyDataSource1 { private static LinkedList<Connection> pool; static { pool = new LinkedList<>(); for (int i = 0; i < 5; i++) { pool.add(DBUtil.getConncetion()); } } /** * 获取连接 * @return */ public Connection getConnection(){ Connection connection = pool.removeFirst(); return connection; } /** * 归还 */ public void addBack(Connection connection){ pool.addLast(connection); } }

5.2 进阶版本

所有的厂商都要实现sun公司提供的连接池接口

package cn.javasm.utils; import javax.sql.DataSource; import java.io.PrintWriter; import java.sql.Connection; import java.sql.SQLException; import java.sql.SQLFeatureNotSupportedException; import java.util.LinkedList; import java.util.logging.Logger; /** * @author : gfs * @version : 0.1 * @className: MyDataSource2 * @date : 2025/12/10 11:35 * @description: * @since : jdk17 */ public class MyDataSource2 implements DataSource { private static LinkedList<Connection> pool; static { pool = new LinkedList<>(); for (int i = 0; i < 5; i++) { pool.add(DBUtil.getConncetion()); } } @Override public Connection getConnection() throws SQLException { return pool.removeFirst(); } public void addBack(Connection connection){ pool.addLast(connection); } @Override public Connection getConnection(String username, String password) throws SQLException { return null; } @Override public PrintWriter getLogWriter() throws SQLException { return null; } @Override public void setLogWriter(PrintWriter out) throws SQLException { } @Override public void setLoginTimeout(int seconds) throws SQLException { } @Override public int getLoginTimeout() throws SQLException { return 0; } @Override public Logger getParentLogger() throws SQLFeatureNotSupportedException { return null; } @Override public <T> T unwrap(Class<T> iface) throws SQLException { return null; } @Override public boolean isWrapperFor(Class<?> iface) throws SQLException { return false; } }

5.3 最终版本

装饰设计模式:是23种常用的面向对象软件设计模式之一。动态的将责任附件到对象上，要想扩展功能，装饰者提供了比继承更加有弹性的替代方案。

使用的条件:

• 增强类和被增强类实现同一个接口或者有共同的父类

• 增强类中要拿到被增强类的引用

public class TestDemo2 { public static void main(String[] args) { Car car = new Benz(new Mzd()); car.run(); car.stop(); } } interface Car{ void run(); void stop(); } // 被增强类 class Mzd implements Car{ @Override public void run() { System.out.println("跑的慢，塞车"); } @Override public void stop() { System.out.println("skr~"); } } // 增强类 class Benz implements Car{ private Car car; public Benz(Car car) { this.car = car; } @Override public void run() { System.out.println("3s破百"); } @Override public void stop() { car.stop(); } }

public class MyConnection implements Connection { private Connection connection; private LinkedList<Connection> pool; public MyConnection(Connection connection, LinkedList<Connection> pool) { this.connection = connection; this.pool = pool; } @Override public void close() throws SQLException { pool.addLast(connection); System.out.println("归还了连接" + connection); } @Override public PreparedStatement prepareStatement(String sql) throws SQLException { return connection.prepareStatement(sql); } @Override public void setAutoCommit(boolean autoCommit) throws SQLException { connection.setAutoCommit(autoCommit); } @Override public void commit() throws SQLException { connection.commit(); } @Override public void rollback() throws SQLException { connection.rollback(); } @Override public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException { return connection.prepareStatement(sql,autoGeneratedKeys); } public class MyDataSource3 implements DataSource { private static LinkedList<Connection> pool; static { pool = new LinkedList<>(); for (int i = 0; i < 5; i++) { pool.add(DBUtil.getConncetion()); } } @Override public Connection getConnection() throws SQLException { Connection connection = pool.removeFirst(); System.out.println("获取了连接" + connection); MyConnection myConnection = new MyConnection(connection,pool); return myConnection; }

6 MD5加密

JDK中实现加密 BASE64(可逆的) public static void main(String[] args) { Base64.Encoder encoder = Base64.getEncoder(); String str = encoder.encodeToString("1234".getBytes()); System.out.println(str); Base64.Decoder decoder = Base64.getDecoder(); byte[] decode = decoder.decode(str); System.out.println(new String(decode)); } MD5加密 public class MD5Util { private MD5Util(){} private static final String SALT = "eeg54yhrtg"; public static String encode(String str) throws NoSuchAlgorithmException { // 获取MD5对象 MessageDigest messageDigest = MessageDigest.getInstance("MD5"); str += SALT; messageDigest.update(str.getBytes()); byte[] bytes = messageDigest.digest(); BigInteger bigInteger = new BigInteger(1,bytes); String string = bigInteger.toString(16); System.out.println(string); return new String(bytes); } }