# JDBC学习 **Repository Path**: hrbu2023/jdbc-learning ## Basic Information - **Project Name**: JDBC学习 - **Description**: JDBC学习 Dao封装 - **Primary Language**: Unknown - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2025-11-07 - **Last Updated**: 2025-11-12 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # 1. 免安装 开机自动启动 - jieyasuo - 初始化 - mysqld --initialize --console ``` D:\app\mysql-8.0.30-winx64\bin>mysqld --initialize --console 2025-11-06T06:54:16.570037Z 0 [System] [MY-013169] [Server] D:\app\mysql-8.0.30-winx64\bin\mysqld.exe (mysqld 8.0.30) initializing of server in progress as process 47144 2025-11-06T06:54:16.631625Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2025-11-06T06:54:25.752529Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2025-11-06T06:54:33.105874Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: m8,ftVbtc-js D:\app\mysql-8.0.30-winx64\bin> ``` 注册成系统服务: mysqld --install ``` D:\app\mysql-8.0.30-winx64\bin>mysqld --install Service successfully installed. ``` ![image-20251106145625429](./assets/image-20251106145625429.png) - 本次手动启动 ``` D:\app\mysql-8.0.30-winx64\bin>net start mysql MySQL 服务正在启动 ... MySQL 服务已经启动成功。 ``` ## 使用 使用客户端连接 ```shell D:\app\mysql-8.0.30-winx64\bin>mysql -uroot -p Enter password: ************ Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.30 Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ``` 修改密码： ```shell mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; Query OK, 0 rows affected (0.12 sec) ``` 使用SQLyog 连接 ![image-20251106150432444](./assets/image-20251106150432444.png) # 设计数据库 基于RBAC权限模型 - 用户 - ​ 中间表 - 角色 - ​ 中间表 - 菜单 用户(User): - 主键 自增 bigint - 用户名 varchar(20) - 密码 varchar(20) - 昵称 varchar(20) - 最后一次登录时间 datetime - 状态 : 启动 停用 varchar(2) 1 2 - 创建时间 datetime 默认值当前时间 - 创建人 bigint - 更新时间 - datetime 默认值当前时间 - 更新人 bigint ```sql CREATE DATABASE his; USE his; CREATE TABLE USER( id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键' , username VARCHAR(20) NOT NULL COMMENT '用户', PASSWORD VARCHAR(20) COMMENT '密码 ', nickname VARCHAR(20) COMMENT '昵称 ', last_login VARCHAR(20) COMMENT '最后一次登录时间', STATUS VARCHAR(2) COMMENT '状态 : 1启动 2 停用', create_time DATETIME DEFAULT NOW() COMMENT '创建时间', create_user BIGINT COMMENT '创建人 ', update_time DATETIME COMMENT '更新时间', update_user BIGINT COMMENT '更新人' ); ``` 练习SQL ```sql CREATE DATABASE his; USE his; CREATE TABLE USER( id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键' , username VARCHAR(20) NOT NULL COMMENT '用户', PASSWORD VARCHAR(20) COMMENT '密码 ', nickname VARCHAR(20) COMMENT '昵称 ', last_login VARCHAR(20) COMMENT '最后一次登录时间', STATUS VARCHAR(2) DEFAULT '1' COMMENT '状态 : 1启动 2 停用', create_time DATETIME DEFAULT NOW() COMMENT '创建时间', create_user BIGINT COMMENT '创建人 ', update_time DATETIME COMMENT '更新时间', update_user BIGINT COMMENT '更新人' ); -- 查询数据 SELECT * FROM USER ; -- 插入一个 INSERT INTO `his`.`user` ( `username`, `password`, `nickname` ) VALUES ( 'admin', '123456', '管理员' ) ; -- 插入多条 INSERT INTO `his`.`user` ( `username`, `password`, `nickname` ) VALUES ( 'root', '123456', '系统管理员' ), ( 'super', '123456', '超级管理员' ) ; -- 修改 UPDATE USER SET create_user = 1 WHERE username ='admin'; UPDATE USER SET create_user = 1 ; -- 删除前增加数据 INSERT INTO `his`.`user` ( `username`, `password`, `nickname` ) VALUES ( 'abc', '123456', '系统管理员' ), ( 'abb', '123456', '超级管理员' ) ; SELECT * FROM USER WHERE username LIKE '%b%'; -- 删除 DELETE FROM USER WHERE username LIKE '%b%' UPDATE `user` SET `last_login` = 'last_login', `status` = 'status', `update_time` = 'update_time', `update_user` = 'update_user' WHERE `id` = 'id' ; SELECT `id`, `username`, `password`, `nickname`, `last_login`, `status`, `create_time`, `create_user`, `update_time`, `update_user` FROM `his`.`user` LIMIT 0, 1000 ; ``` # 编写JDBC ## 使用IDEA创建一个Java项目 ![image-20251107093239917](./assets/image-20251107093239917.png) ![image-20251107093413498](./assets/image-20251107093413498.png) ## 引用依赖 ![image-20251107093709038](./assets/image-20251107093709038.png) ![image-20251107093719777](./assets/image-20251107093719777.png) ![image-20251107093811151](./assets/image-20251107093811151.png) ![image-20251107093833929](./assets/image-20251107093833929.png) ## 编写测试类 main - 加载驱动（jdbc的高版本可以省略） - Class.forname - 建立连接 - Drivermanager.getConnection(url,ussername,password) - 创建Statement - executeQuery - executeUpdate - 执行sql语句 - select --》返回结果集 ResultSet - inser、update、delete -> 返回影响行数 - 处理结果集 - 逐行的读取Rs - 关闭资源 ```java package com.neuedu.jdbc.demo1; import java.sql.*; import java.util.Date; /*** * 项目名称: MyJDBC * 包： com.neuedu.jdbc.demo1 * date: 2025/11/7 9:42 * 描述: * @author : 张金山 * 作者博客: https://blog.fulfill.com.cn */ public class UseJDBC { /*** * - 加载驱动（jdbc的高版本可以省略） * - Class.forname * - 建立连接 * - Drivermanager.getConnection(url,ussername,password) * - 创建Statement * - executeQuery * - executeUpdate * - 执行sql语句 * - select --》返回结果集 ResultSet * - inser、update、delete -> 返回影响行数 * - 处理结果集 * - 逐行的读取Rs * - 关闭资源 * @param args */ public static void main(String[] args) { // String url = "http://www.baidu.com"; String url = "jdbc:mysql://localhost:3306/his?useUnicode=true&characterEncoding=utf8"; String user = "root"; String password = "root"; Connection conn = null; Statement stmt = null; ResultSet rs = null; // 旧版 5.xx- // Class.forName("com.mysql.jdbc.Driver"); //8.x + try { // 1 加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //2 创建连接 conn = DriverManager.getConnection(url, user, password); //3 创建语句块stmt stmt = conn.createStatement(); //4 执行sql 语句 String sql = "select * from user "; rs = stmt.executeQuery(sql); //解析结果集 while(rs.next()){ long id = rs.getLong("id"); String username = rs.getString("username"); String pd = rs.getString("password"); String nickname = rs.getString("nickname"); Date lastLogin = rs.getTimestamp("last_login"); String status = rs.getString("STATUS"); String createtime = rs.getString("create_time"); Long createuser = rs.getLong("create_user"); Date updateTime = rs.getTimestamp("update_time"); Long updateuser = rs.getLong("update_user"); System.out.println(String.format( "id = %s\tusername = %s\tpd = %s\tnickname = %s\tlastLogin = %s\tstatus = %s\tcreatetime = %s\tcreateuser = %s\tupdateTime = %s\tupdateuser = %s\r\n", id, username, pd, nickname, lastLogin, status, createtime, createuser, updateTime, updateuser )); } } catch (ClassNotFoundException e) { throw new RuntimeException(e); } catch (SQLException e) { throw new RuntimeException(e); } finally { //关闭资源 if(rs != null){ try { rs.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(stmt != null){ try { stmt.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(conn != null) { try { conn.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } } } ``` MVC 三层架构 - Controller - Service - Dao Date Access Object 某一张表常见操作的封装 - select - selectList - selectById - count() - updateById - --updateByContrin() - insert（obj） - insertBatch(list) - deleteById(id) ![image-20251107113347207](./assets/image-20251107113347207.png) ```java package com.neuedu.jdbc.dao; import java.sql.*; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; /*** * 项目名称: MyJDBC * 包： com.neuedu.jdbc.dao * date: 2025/11/7 11:30 * 描述: * @author : 张金山 * 作者博客: https://blog.fulfill.com.cn */ public class UserDao { /** * 根据主键查询单条结果 * @param id * @return */ public Map selectById(long id){ // String url = "http://www.baidu.com"; String url = "jdbc:mysql://localhost:3306/his?useUnicode=true&characterEncoding=utf8"; String user = "root"; String password = "root"; Connection conn = null; Statement stmt = null; ResultSet rs = null; Map obj = null; // 旧版 5.xx- // Class.forName("com.mysql.jdbc.Driver"); // 8.x + try { // 1 加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); // 2 创建连接 conn = DriverManager.getConnection(url, user, password); // 3 创建语句块stmt stmt = conn.createStatement(); // 4 执行sql 语句 String sql = "select `id`, `username`, `password`, `nickname`, `last_login`, " + " `status`, `create_time`, `create_user`, `update_time`, `update_user` from user where id = "+id ; rs = stmt.executeQuery(sql); // 解析结果集 if (rs.next()) { obj = new HashMap(); int index = 0; long userId = rs.getLong(++index); String username = rs.getString(++index); String pd = rs.getString(++index); String nickname = rs.getString(++index); Date lastLogin = rs.getTimestamp(++index); String status = rs.getString(++index); String createtime = rs.getString(++index); Long createuser = rs.getLong(++index); Date updateTime = rs.getTimestamp(++index); Long updateuser = rs.getLong(++index); obj.put("id",userId); obj.put("username",username); obj.put("password",pd); obj.put("nickname",nickname); obj.put("lastLogin",lastLogin); obj.put("status",status); obj.put("createtime",createtime); obj.put("createuser",createuser); obj.put("updateTime",updateTime); obj.put("updateuser",updateuser); } } catch (ClassNotFoundException e) { throw new RuntimeException(e); } catch (SQLException e) { throw new RuntimeException(e); } finally { // 关闭资源 if (rs != null) { try { rs.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } return obj; } /** * 根据条件查询集合 * @param * @return */ public List selectList(String username ,String nickname){ return null; } public int updateById(long id , String username,String password){ return 0; } public int insert(long id , String username,String password){ return 0; } public int deleteById(long id){ return 0; } public static void main(String[] args) { UserDao userDao = new UserDao(); //根据主键查询一条 long id = 2; Map user = userDao.selectById(id); System.out.println(user); } } ``` 修改字段类型： ```sql ALTER TABLE `his`.`user` CHANGE `last_login` `last_login` DATETIME NULL COMMENT '最后一次登录时间'; ``` # 事务 定义银行账户表 > Account > id AUTO_INCREMENT > NAME VARCHAR > amount DECIMAL(8,2) ## 建表 字符集 gbK UTF-8 (Unicode ) MYSQL UTF-8 UTF8-MB4(后出) 支持一些比较新 的字符编码 emjo 10,2 3.14 ````sql CREATE TABLE `his`.`account`( `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键', `name` VARCHAR(50) NOT NULL COMMENT '账户名称', `amount` DECIMAL(8,2) COMMENT '余额', PRIMARY KEY (`id`) ) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_bin; ```` ## 创建PO(实体类) Account ```java package com.neuedu.jdbc.po; /*** * 项目名称: MyJDBC * 包： com.neuedu.jdbc.po * date: 2025/11/12 9:55 * 描述: * @author : 张金山 * 作者博客: https://blog.fulfill.com.cn */ public class Account { private Long id; private String name; private Double amount; public Account() { } public Account(Long id, String name, Double amount) { this.id = id; this.name = name; this.amount = amount; } public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Double getAmount() { return amount; } public void setAmount(Double amount) { this.amount = amount; } /** * 为了测试打印对象属性的 * @return */ @Override public String toString() { return "Account{" + "id=" + id + ", name='" + name + '\'' + ", amount=" + amount + '}'; } } ``` ## 封装Dao ```java package com.neuedu.jdbc.dao; import com.neuedu.jdbc.po.Account; import com.neuedu.jdbc.po.User; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; /*** * 项目名称: MyJDBC * 包： com.neuedu.jdbc.dao * date: 2025/11/7 11:30 * 描述: Dao Date Access Object * 封装了一些常见的 CRUD(增删改查)的操作 * selectByID * selectlist * updateById * deleteById * isnert() * @author : 张金山 * 作者博客: https://blog.fulfill.com.cn */ public class AccountDao extends BaseDao { /** * 根据主键查询单条结果 * * @param id * @return */ public Account selectById(long id) { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; Account po = null; // 8.x + try { // 1 加载驱动 建立连接 conn = DBUtils.getConnection(); // 3 创建语句块stmt // stmt = conn.createStatement(); // 4 执行sql 语句 String sql = "select * from account where id = ? "; stmt = conn.prepareStatement(sql); //绑定变量 stmt.setLong(1,id); rs = stmt.executeQuery(); // 解析结果集 if (rs.next()) { po = new Account( rs.getLong("id"), rs.getString("name"), rs.getDouble("amount") ); } } catch (ClassNotFoundException e) { throw new RuntimeException(e); } catch (SQLException e) { throw new RuntimeException(e); } finally { // 关闭资源 super.close(conn, stmt, rs); } return po; } /** * 根据条件查询集合 * * @param * @return */ public List selectList() { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; List list = new ArrayList(); // 旧版 5.xx- // Class.forName("com.mysql.jdbc.Driver"); // 8.x + try { // 1 加载驱动 建立连接 conn = DBUtils.getConnection(); // 3 创建语句块stmt // 4 执行sql 语句 StringBuffer sql = new StringBuffer("select * from account "); stmt = conn.prepareStatement(sql.toString()); System.out.println("SQL: " + sql.toString()); rs = stmt.executeQuery(); // 解析结果集 while (rs.next()) { Account po = new Account( rs.getLong("id"), rs.getString("name"), rs.getDouble("amount") ); list.add(po); } } catch (ClassNotFoundException e) { throw new RuntimeException(e); } catch (SQLException e) { throw new RuntimeException(e); } finally { // 关闭资源 super.close(conn, stmt, rs); } return list; } public int updateById(Account po) { StringBuilder sql = new StringBuilder(); sql.append(" update "); sql.append(" `his`.`account` "); sql.append(" set "); sql.append(" `name` = ? , "); sql.append(" `amount` = ? "); sql.append(" WHERE `id` = ? " ); int count = super.executeUpdate(sql.toString(), po.getName(), po.getAmount(), po.getId() ); return count; } public int insert(Account po) { String sql = " INSERT INTO `his`.`account` ( `name`, `amount` ) VALUES ( ? , ?) "; int count = super.executeUpdate(sql, po.getName(), po.getAmount() ); return count; } public int deleteById(long id) { String sql = "delete from account where id = ? "; int count = super.executeUpdate(sql,id); return count; } public static void main(String[] args) { } } ``` ## 验证Dao 测试 ``` public static void main(String[] args) { AccountDao accountDao = new AccountDao(); //1 测试增加两个账户 // Account account1 = new Account(); // account1.setName("张三"); // account1.setAmount(5000.0); // int count1 = accountDao.insert(account1); // // System.out.println("count1 = " + count1); // // Account account2 = new Account(); // account2.setName("李四"); // account2.setAmount(5000.0); // int count2 = accountDao.insert(account2); // System.out.println("count2 = " + count2); //2 遍历 accountDao.selectList().forEach(System.out::println); } ``` 完成转账的业务(Service)