網(wǎng)站營銷活動(dòng),福田公司門口,新手學(xué)做網(wǎng)站需要注意的幾點(diǎn),動(dòng)漫網(wǎng)站開發(fā)優(yōu)勢(shì)1 JDBC介紹JDBC : java database connectivity 編寫java程序 實(shí)現(xiàn)對(duì)任意一個(gè)DBMS軟件的數(shù)據(jù)進(jìn)行增刪改查#xff0c;都需要使用JDBC JDBC是sun公司發(fā)布的一套關(guān)于數(shù)據(jù)庫的規(guī)范 JDBC實(shí)際上就是一套接口#xff0c;各個(gè)數(shù)據(jù)庫廠商都需要實(shí)現(xiàn)這個(gè)接口#xff0c;實(shí)…1 JDBC介紹JDBC : java database connectivity 編寫java程序 實(shí)現(xiàn)對(duì)任意一個(gè)DBMS軟件的數(shù)據(jù)進(jìn)行增刪改查都需要使用JDBC JDBC是sun公司發(fā)布的一套關(guān)于數(shù)據(jù)庫的規(guī)范 JDBC實(shí)際上就是一套接口各個(gè)數(shù)據(jù)庫廠商都需要實(shí)現(xiàn)這個(gè)接口實(shí)現(xiàn)這一套接口中的方法 好處:程序員只需要學(xué)會(huì)JDBC接口就可以調(diào)用各個(gè)數(shù)據(jù)庫廠商的內(nèi)容輕松實(shí)現(xiàn)增刪改查 各個(gè)數(shù)據(jù)庫廠商需要提供JDBC接口的實(shí)現(xiàn)這些實(shí)現(xiàn)統(tǒng)稱為 驅(qū)動(dòng)2 獲取DBMS的連接用戶名 密碼 mysql驅(qū)動(dòng) 數(shù)據(jù)庫的ip和端口號(hào)和數(shù)據(jù)庫名稱2.1 獲取連接public class TestDemo { public static void main(String[] args) { // 定義數(shù)據(jù)庫的url jdbc:mysql://ip:端口號(hào)/數(shù)據(jù)庫名稱 // 如果ip是localhost 端口號(hào)是 3306 可以省略 // String url jdbc:mysql://localhost:3306/mydb; String url jdbc:mysql:///mydb; // 用戶名 String username root; // 密碼 String password root; ? Connection connection null; try { // 注冊(cè)驅(qū)動(dòng)(讓jvm知道使用的是哪一個(gè)DBMS軟件) // 在java項(xiàng)目中不需要注冊(cè)mysql驅(qū)動(dòng)包中存在一個(gè)服務(wù)發(fā)現(xiàn)機(jī)制會(huì)去META-INF/services/java.sql.Driver文件中的內(nèi)容自動(dòng)注冊(cè)驅(qū)動(dòng)。 // 但是如果是web/maven項(xiàng)目結(jié)構(gòu)中就不能省略 // DriverManager.registerDriver(new Driver()); // 編寫代碼 獲取mysql連接 connection DriverManager.getConnection(url, username, password); System.out.println(connection); } catch (SQLException e) { throw new RuntimeException(e); }finally { // 關(guān)閉連接 if (connection ! null){ try { connection.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } } } ?2.2 獲取連接優(yōu)化注冊(cè)驅(qū)動(dòng)優(yōu)化工具類的封裝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: 數(shù)據(jù)庫相關(guān)工具類 * since : jdk17 */ public class DBUtil { ? private DBUtil(){} ? private static Properties properties new Properties(); ? static { // 靜態(tài)代碼塊中 讀取文件 try { properties.load(new FileInputStream(src/jdbc.properties)); // 注冊(cè)驅(qū)動(dòng) 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 CRUD3.1 添加 insertpublic interface UserDao { /** * 添加用戶 * return 影響數(shù)據(jù)庫的行數(shù) */ int addUser(); } ? ? public class UserDaoImpl implements UserDao { Override public int addUser() { // 獲取數(shù)據(jù)庫的連接 Connection conncetion DBUtil.getConncetion(); // 編寫sql語句 String sql INSERT INTO user (username,age,password,balance) VALUES (張無忌,20,123456,8899.9); PreparedStatement preparedStatement null; try { // 通過連接 獲取預(yù)編譯對(duì)象 preparedStatement conncetion.prepareStatement(sql); // 執(zhí)行sql語句,返回?cái)?shù)據(jù)庫受影響的行數(shù) int row preparedStatement.executeUpdate(); return row; } catch (SQLException e) { throw new RuntimeException(e); }finally { // 關(guān)閉連接 DBUtil.release(conncetion,preparedStatement); } } }優(yōu)化:創(chuàng)建實(shí)體類Userpackage 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 { /** * 用戶標(biāo)識(shí) */ private Integer id; /** * 用戶名 */ private String username; ? /** * 年齡 */ private Integer age; ? /** * 密碼 */ private String password; ? /** * 頭像 */ private String image; ? /** * 余額 */ private BigDecimal balance; ? /** * 創(chuàng)建時(shí)間 */ private LocalDateTime createTime; ? /** * 更新時(shí)間 */ private LocalDateTime updateTime; ? } ?UserDao接口中/** * 添加指定的用戶 * return 影響數(shù)據(jù)庫的行數(shù) */ int addUser(User user);UserDaoImpl中Override public int addUser(User user) { // 獲取數(shù)據(jù)庫連接 Connection connection DBUtil.getConncetion(); // 編寫sql語句 // ? 代表占位符在JDBC中 一個(gè)?占一個(gè)位置 表示一個(gè)數(shù)據(jù) String sql insert into user (username,age,password,balance) values (?,?,?,?); PreparedStatement preparedStatement null; try { // 獲取預(yù)編譯對(duì)象 preparedStatement connection.prepareStatement(sql); // 給?賦值 // 參數(shù)1?所在的位置 從1開始 preparedStatement.setString(1,user.getUsername()); preparedStatement.setInt(2,user.getAge()); preparedStatement.setObject(3,user.getPassword()); preparedStatement.setBigDecimal(4,user.getBalance()); ? // 執(zhí)行sql語句 executeUpdate 執(zhí)行DML insert update delete int row preparedStatement.executeUpdate(); return row; } catch (SQLException e) { throw new RuntimeException(e); }finally { // 關(guān)閉連接 DBUtil.release(connection,preparedStatement); } }測(cè)試類public static void main(String[] args) { Scanner scanner new Scanner(System.in); System.out.println(請(qǐng)輸入用戶名); String username scanner.next(); System.out.println(請(qǐng)輸入密碼); String password scanner.next(); System.out.println(請(qǐng)輸入年齡); int age scanner.nextInt(); System.out.println(請(qǐng)輸入余額); 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 刪除 deletedelete from user where id ?UserDao接口/** * 根據(jù)id刪除用戶 * param id 用戶的id * return 受影響的行數(shù) */ 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 { // 獲取預(yù)編譯對(duì)象 preparedStatement connection.prepareStatement(sql); // 給?賦值 preparedStatement.setInt(1,id); // 執(zhí)行sql語句 int row preparedStatement.executeUpdate(); return row; } catch (SQLException e) { throw new RuntimeException(e); }finally { // 關(guān)閉連接 DBUtil.release(connection,preparedStatement); } }3.3 更新 updateupdate user set username ?,age ?,password ? where id ?UserDao接口中/** * 根據(jù)id更新用戶 * param user 用戶 * return 受影響的行數(shù) */ 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 ?; // 獲取預(yù)編譯對(duì)象 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()); // 執(zhí)行sql語句 int row preparedStatement.executeUpdate(); return row; } catch (SQLException e) { throw new RuntimeException(e); }finally { // 關(guān)閉連接 DBUtil.release(connection,preparedStatement); } }3.4 查詢3.4.1 根據(jù)id查詢select * from user where id ?UserDao接口/** * 根據(jù)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 { // 獲取預(yù)編譯對(duì)象 preparedStatement connection.prepareStatement(sql); // 給?賦值 preparedStatement.setInt(1,id); // 執(zhí)行sql語句獲取結(jié)果集 一行記錄是 結(jié)果集 中的一個(gè)結(jié)果 resultSet preparedStatement.executeQuery(); // next() 返回true代表有下一個(gè)元素 返回false代表沒有下一個(gè)元素 if (resultSet.next()){ // 根據(jù)位置獲取結(jié)果的數(shù)據(jù) int uid resultSet.getInt(1); // 根據(jù)列名獲取結(jié)果的數(shù)據(jù) 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 { // 關(guān)閉連接 DBUtil.release(connection,preparedStatement,resultSet); } return null; }3.4.2 查詢所有UserDao接口ListUser queryAll();UserDaoImplOverride public ListUser queryAll() { // 獲取連接 Connection connection DBUtil.getConncetion(); // 編寫sql語句 String sql select * from user; // 獲取預(yù)編譯對(duì)象 PreparedStatement preparedStatement null; ResultSet resultSet null; // 創(chuàng)建集合 ListUser list new ArrayList(); try { preparedStatement connection.prepareStatement(sql); // 執(zhí)行sql語句 resultSet preparedStatement.executeQuery(); while (resultSet.next()){ list.add(getUser(resultSet)); } return list; } catch (SQLException e) { throw new RuntimeException(e); }finally { // 關(guān)閉資源 DBUtil.release(connection,preparedStatement,resultSet); } }3.4.3 分頁查詢后端需要前端傳遞的是哪一頁 page每頁顯示多少條 pageSize后端傳遞給前端的是 當(dāng)前頁的數(shù)據(jù) 頁面的總數(shù)量-- 分頁查詢 select * from user limit ?,? 第一個(gè)?: (page - 1) * pageSize 第二個(gè)?: pageSize -- 查詢總記錄數(shù) select count(*) from user;UserDao接口/** * 分頁查詢 * param page 哪一頁 * param pageSize 每頁顯示的數(shù)量 * return */ ListUser queryUserByPage(int page,int pageSize); /** * 查詢數(shù)據(jù)的總數(shù)量 用于分頁 * return 總數(shù)量 */ int queryUserCount();UserDaoImpl類Override public ListUser queryUserByPage(int page, int pageSize) { // 獲取連接 Connection connection DBUtil.getConncetion(); // 執(zhí)行sql語句 String sql select * from user limit ?,?; PreparedStatement preparedStatement null; ResultSet resultSet null; // 創(chuàng)建集合 ListUser list new ArrayList(); try { // 獲取預(yù)編譯對(duì)象 preparedStatement connection.prepareStatement(sql); // 給?賦值 preparedStatement.setInt(1,(page - 1) * pageSize); preparedStatement.setInt(2,pageSize); // 執(zhí)行sql語句 resultSet preparedStatement.executeQuery(); while (resultSet.next()){ list.add(getUser(resultSet)); } return list; } catch (SQLException e) { throw new RuntimeException(e); }finally { // 關(guān)閉連接 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 { // 獲取預(yù)編譯對(duì)象 preparedStatement connection.prepareStatement(sql); // 執(zhí)行sql語句 resultSet preparedStatement.executeQuery(); // 結(jié)果集中是總數(shù)量 if (resultSet.next()){ return resultSet.getInt(1); } } catch (SQLException e) { throw new RuntimeException(e); }finally { // 關(guān)閉連接 DBUtil.release(connection,preparedStatement,resultSet); } return 0; }根據(jù)條件查詢 并分頁用戶名模糊查詢年齡區(qū)間查詢create_time區(qū)間查詢3.4.4 根據(jù)條件查詢 并分頁-- 條件 根據(jù)用戶名模糊搜索 根據(jù)年齡在區(qū)間范圍內(nèi)搜索 minAge maxAge 根據(jù)創(chuàng)建時(shí)間在區(qū)間范圍內(nèi)搜索 beginTime endTime -- 沒有條件 select * from user limit ?,? -- 用戶名 select * from user where username like ? limit ?,? -- 用戶名 年齡 select * from user where username like ? AND age between ? and ? limit ?,? -- 用戶名 年齡 創(chuàng)建時(shí)間 select * from user where username like ? AND age between ? and ? AND create_time between ? and ? limit ?,? -- 動(dòng)態(tài)拼接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 每頁顯示的數(shù)量 * return */ ListUser queryUserByParamAndPage(SearchParamVo searchParamVo,int page,int pageSize); /** * 查詢記錄的總數(shù)量 * param searchParamVo 查詢條件 * return 總數(shù)量 */ int queryUserByParamCount(SearchParamVo searchParamVo); }UserDaoImpl中public class UserDaoImpl implements UserDao { // where username like ? and age between ? and ? and create_time between ? and ? Override public ListUser 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; // 創(chuàng)建集合 ListUser list new ArrayList(); try { // 獲取預(yù)編譯對(duì)象 preparedStatement connection.prepareStatement(sql); // 給?賦值 int count setParam(searchParamVo, preparedStatement); // 給limit的?賦值 preparedStatement.setInt(count,(page - 1) * pageSize); preparedStatement.setInt(count,pageSize); // 執(zhí)行sql語句 resultSet preparedStatement.executeQuery(); while (resultSet.next()){ list.add(getUser(resultSet)); } return list; } catch (SQLException e) { throw new RuntimeException(e); }finally { // 關(guān)閉連接 DBUtil.release(connection,preparedStatement,resultSet); } } private static int setParam(SearchParamVo searchParamVo,PreparedStatement preparedStatement) throws SQLException { // 獲取參數(shù) String nameParam searchParamVo.getNameParam(); Integer minAge searchParamVo.getMinAge(); String beginTime searchParamVo.getBeginTime(); Integer maxAge searchParamVo.getMaxAge(); String endTime searchParamVo.getEndTime(); // 記錄參數(shù)的位置 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){ // 獲取參數(shù) 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){ // 只要有條件最后必定多一個(gè)AND stringBuilder.delete(stringBuilder.lastIndexOf(AND),stringBuilder.length()); } } private static User getUser(ResultSet resultSet) throws SQLException { // 根據(jù)位置獲取結(jié)果的數(shù)據(jù) int uid resultSet.getInt(1); // 根據(jù)列名獲取結(jié)果的數(shù)據(jù) 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 { // 獲取預(yù)編譯對(duì)象 preparedStatement connection.prepareStatement(stringBuilder.toString()); // 給?賦值 setParam(searchParamVo,preparedStatement); // 執(zhí)行sql語句 resultSet preparedStatement.executeQuery(); if (resultSet.next()){ totalRow resultSet.getInt(1); } } catch (SQLException e) { throw new RuntimeException(e); }finally { // 關(guān)閉連接 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 */ ListUserRoleDept queryUserAndRoleAndDept();UserDaoImpl中Override public ListUserRoleDept queryUserAndRoleAndDept() { // 獲取連接 Connection connection DBUtil.getConncetion(); // 編寫sql String sql select * FROM user u,role r,dept d WHERE u.rid r.id AND u.deptno d.deptno; PreparedStatement preparedStatement null; ResultSet resultSet null; // 創(chuàng)建集合 ListUserRoleDept list new ArrayList(); try { // 獲取預(yù)編譯對(duì)象 preparedStatement connection.prepareStatement(sql); // 執(zhí)行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(); ListMenu 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(); // 傳統(tǒng)方法 // 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()); // } // } // } // } // } // } } /** * 層級(jí)展示菜單 * param parentMenu 父菜單 * param menuList 全部菜單 * param str 拼接的字符串 */ private static void queryChildMenu(Menu parentMenu,ListMenu 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 角色對(duì)象 * 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(); // 執(zhí)行sql String sql insert into role (role_name,role_desc) values(?,?); // 獲取預(yù)編譯對(duì)象 PreparedStatement preparedStatement connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); // 給?賦值 preparedStatement.setString(1,role.getRoleName()); preparedStatement.setString(2,role.getRoleDesc()); // 執(zhí)行sql preparedStatement.executeUpdate(); // 獲取剛剛添加角色的id LAST_INSERT_ID() ResultSet resultSet preparedStatement.getGeneratedKeys(); int autoId 0; if (resultSet.next()){ autoId resultSet.getInt(1); } // 關(guān)閉連接 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(,); } // 獲取預(yù)編譯對(duì)象 PreparedStatement preparedStatement connection.prepareStatement(stringBuilder.toString()); // 給?賦值 int count 1; for (String menuId : mendIdArray) { preparedStatement.setObject(count,rid); preparedStatement.setObject(count,menuId); } // 執(zhí)行sql語句 int row preparedStatement.executeUpdate(); // 關(guān)閉連接 DBUtil.release(connection,preparedStatement); return row; } }4 DCL事務(wù)是邏輯上的一組操作組成這組操作的單元要么同時(shí)成功要么同時(shí)失敗4.1 Mysql進(jìn)行事務(wù)管理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); -- 開啟事務(wù) start transaction; -- 提交事務(wù) commit; -- 回滾事務(wù) rollback; update account set money money - 100 where name zs update account set money money 100 where name ls4.2 事務(wù)的特性和隔離級(jí)別【面試題】4.2.1 事務(wù)的特性原子性原子性是指事務(wù)是一個(gè)不可分割的工作單位。事務(wù)中的操作要么都發(fā)生要么都不發(fā)生一致性事務(wù)前后數(shù)據(jù)的完整性要保持一致zs 1000 ls 1000 總共2000 成功 zs 900 ls 1100 總共2000 不成功 zs 1000 ls 1000 總共2000持久性一個(gè)事務(wù)一旦提交對(duì)數(shù)據(jù)庫中的數(shù)據(jù)改動(dòng)是永久性的隔離性指多個(gè)用戶并發(fā)操作數(shù)據(jù)庫時(shí)一個(gè)用戶的事務(wù)不能被其他用戶的事務(wù)所干擾。4.2.2 沒有隔離性會(huì)引發(fā)的問題可能出現(xiàn)的問題含義臟讀一個(gè)事務(wù)讀取到了另一個(gè)事務(wù)中尚未提交的數(shù)據(jù)不可重復(fù)讀一個(gè)事務(wù)中兩次讀取到的數(shù)據(jù)內(nèi)容不一致幻讀一個(gè)事務(wù)中兩次讀取到的數(shù)據(jù)的數(shù)量不一致4.2.3 事務(wù)的隔離級(jí)別級(jí)別名字隔離級(jí)別臟讀不可重復(fù)讀幻讀數(shù)據(jù)庫的默認(rèn)級(jí)別1讀未提交read uncommitted是是是2讀已提交read committed否是是oracle3可重復(fù)讀repeatable read否否是mysql4串行化serializable否否否隔離級(jí)別越高安全性越高性能效率越差-- 查看當(dāng)前事務(wù)的隔離級(jí)別 select transaction_isolation; -- 設(shè)置當(dāng)前事務(wù)的隔離級(jí)別 set session transaction isolation level 隔離級(jí)別4.3 代碼級(jí)別使用事務(wù)// 開啟事務(wù) connection.setAutoCommit(false); // 提交事務(wù) connection.commit(); // 回滾事務(wù) 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) { // 業(yè)務(wù)邏輯層 密碼加密 事務(wù)控制 記錄日志 檢查權(quán)限... // 獲取連接 Connection connection DBUtil.getConncetion(); int row 0; try { // 手動(dòng)開啟事務(wù) connection.setAutoCommit(false); RoleDao roleDao new RoleDaoImpl(connection); int roleId roleDao.addRole(role); // int i 1 / 0; row roleDao.addRoleAndMenu(roleId, menuIdArray); // 提交事務(wù) connection.commit(); } catch (Exception e) { try { // 回滾事務(wù) connection.rollback(); } catch (SQLException ex) { throw new RuntimeException(ex); } throw new RuntimeException(e); }finally { // 關(guān)閉連接 DBUtil.release(connection); } return row; } }5 自定義連接池5.1 初級(jí)版本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 LinkedListConnection 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 進(jìn)階版本所有的廠商都要實(shí)現(xiàn)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 LinkedListConnection 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(ClassT iface) throws SQLException { return null; } Override public boolean isWrapperFor(Class? iface) throws SQLException { return false; } }5.3 最終版本裝飾設(shè)計(jì)模式:是23種常用的面向?qū)ο筌浖O(shè)計(jì)模式之一。動(dòng)態(tài)的將責(zé)任附件到對(duì)象上要想擴(kuò)展功能裝飾者提供了比繼承更加有彈性的替代方案。使用的條件:增強(qiáng)類和被增強(qiáng)類實(shí)現(xiàn)同一個(gè)接口或者有共同的父類增強(qiáng)類中要拿到被增強(qiáng)類的引用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(); } // 被增強(qiáng)類 class Mzd implements Car{ Override public void run() { System.out.println(跑的慢塞車); } Override public void stop() { System.out.println(skr~); } } // 增強(qiáng)類 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 LinkedListConnection pool; public MyConnection(Connection connection, LinkedListConnection 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 LinkedListConnection 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中實(shí)現(xiàn)加密 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對(duì)象 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); } }