Spring Boot 整合 JDBC 与 MyBatis 教学文档<\/h2>

第一部分：JDBC 数据库操作<\/h3>

一、JDBC 简介<\/h4>

1. 核心概念<\/strong><\/p>

JDBC<\/strong>：全称为 Java DataBase Connectivity，是 Java 语言中用于规范客户端程序如何访问关系型数据库的应用程序接口<\/strong>。它提供了一套标准的 API，用于执行 SQL 语句。<\/li>
驱动<\/strong>：JDBC 本身是接口，具体的实现由各个数据库厂商提供，这些实现就是 JDBC 驱动<\/strong>。开发者需要引入对应数据库的驱动 Jar 包。<\/li> <\/ul>
2. 核心步骤<\/strong>
使用 JDBC 操作数据库通常遵循以下固定步骤：<\/p>

注册驱动<\/li>
获取数据库连接<\/li>
创建语句执行对象<\/li>
执行 SQL 语句<\/li>
处理结果集<\/li>
释放资源<\/li> <\/ol>
二、JDBC 入门程序<\/h4>
1. 环境准备<\/strong><\/p>

创建一个 Maven 项目。<\/li>
在 pom.xml<\/code> 文件中引入 MySQL 驱动依赖。<\/li>
准备数据库表。<\/li> <\/ul> 2. 代码实现（以 DML 语句为例，如 UPDATE）<\/strong><\/p> \/\/ 1. 注册驱动 (新版本驱动可省略此步，但显式声明更规范) <\/span><\/span><\/span><\/span>Class.<\/span>forName<\/span>(<\/span>"com.mysql.cj.jdbc.Driver"<\/span>);<\/span> <\/span><\/span> <\/span><\/span>\/\/ 2. 获取数据库连接 <\/span><\/span><\/span>\/\/ 格式：jdbc:mysql:\/\/主机地址:端口号\/数据库名?参数键值对 <\/span><\/span><\/span><\/span>String url =<\/span> "jdbc:mysql:\/\/localhost:3306\/db_test?useSSL=false&serverTimezone=UTC"<\/span>;<\/span> <\/span><\/span>String username =<\/span> "your_username"<\/span>;<\/span> <\/span><\/span>String password =<\/span> "your_password"<\/span>;<\/span> <\/span><\/span>Connection connection =<\/span> DriverManager.<\/span>getConnection<\/span>(<\/span>url,<\/span> username,<\/span> password);<\/span> <\/span><\/span> <\/span><\/span>\/\/ 3. 创建 Statement 语句执行对象 <\/span><\/span><\/span><\/span>Statement statement =<\/span> connection.<\/span>createStatement<\/span>();<\/span> <\/span><\/span> <\/span><\/span>\/\/ 4. 执行 SQL 语句 (此处为 DML 更新语句) <\/span><\/span><\/span><\/span>String sql =<\/span> "UPDATE user SET age = 25 WHERE id = 1"<\/span>;<\/span> <\/span><\/span>int<\/span> affectedRows =<\/span> statement.<\/span>executeUpdate<\/span>(<\/span>sql);<\/span> \/\/ 返回受影响的行数 <\/span><\/span><\/span><\/span>System.<\/span>out<\/span>.<\/span>println<\/span>(<\/span>"更新了 "<\/span> +<\/span> affectedRows +<\/span> " 行数据。"<\/span>);<\/span> <\/span><\/span> <\/span><\/span>\/\/ 5. 释放资源 (务必进行，反向关闭) <\/span><\/span><\/span><\/span>statement.<\/span>close<\/span>();<\/span> <\/span><\/span>connection.<\/span>close<\/span>();<\/span> <\/span><\/span><\/code><\/pre>三、执行 DQL 查询语句<\/h4> 执行查询语句需要使用 PreparedStatement<\/code> 和 ResultSet<\/code>。<\/p> \/\/ 假设已经获取了 Connection 对象 conn <\/span><\/span><\/span><\/span> <\/span><\/span>\/\/ 使用预编译 SQL 语句，防止 SQL 注入 <\/span><\/span><\/span><\/span>String sql =<\/span> "SELECT id, username, password, name, age FROM user WHERE username = ? AND password = ?"<\/span>;<\/span> <\/span><\/span>\/\/ 3. 获取 PreparedStatement 对象，并预编译 SQL <\/span><\/span><\/span><\/span>PreparedStatement pstmt =<\/span> conn.<\/span>prepareStatement<\/span>(<\/span>sql);<\/span> <\/span><\/span> <\/span><\/span>\/\/ 为 SQL 语句中的占位符 ‘?’ 设置参数 (索引从1开始) <\/span><\/span><\/span><\/span>pstmt.<\/span>setString<\/span>(<\/span>1<\/span>,<\/span> "daqiao"<\/span>);<\/span> \/\/ 设置第一个问号的值为 "daqiao" <\/span><\/span><\/span><\/span>pstmt.<\/span>setString<\/span>(<\/span>2<\/span>,<\/span> "123456"<\/span>);<\/span> \/\/ 设置第二个问号的值为 "123456" <\/span><\/span><\/span><\/span> <\/span><\/span>\/\/ 4. 执行查询，返回 ResultSet 结果集 <\/span><\/span><\/span><\/span>ResultSet rs =<\/span> pstmt.<\/span>executeQuery<\/span>();<\/span> <\/span><\/span> <\/span><\/span>\/\/ 5. 处理结果集 <\/span><\/span><\/span>\/\/ ResultSet 对象内部有一个指针，初始指向第一行数据之前 <\/span><\/span><\/span>\/\/ rs.next() 方法将指针移动到下一行，如果存在下一行则返回 true，否则返回 false <\/span><\/span><\/span><\/span>while<\/span> (<\/span>rs.<\/span>next<\/span>())<\/span> {<\/span> <\/span><\/span> \/\/ 通过列名或列索引从当前行获取数据 <\/span><\/span><\/span><\/span> User user =<\/span> new<\/span> User();<\/span> <\/span><\/span> user.<\/span>setId<\/span>(<\/span>rs.<\/span>getInt<\/span>(<\/span>"id"<\/span>));<\/span> <\/span><\/span> user.<\/span>setUsername<\/span>(<\/span>rs.<\/span>getString<\/span>(<\/span>"username"<\/span>));<\/span> <\/span><\/span> user.<\/span>setPassword<\/span>(<\/span>rs.<\/span>getString<\/span>(<\/span>"password"<\/span>));<\/span> <\/span><\/span> user.<\/span>setName<\/span>(<\/span>rs.<\/span>getString<\/span>(<\/span>"name"<\/span>));<\/span> <\/span><\/span> user.<\/span>setAge<\/span>(<\/span>rs.<\/span>getInt<\/span>(<\/span>"age"<\/span>));<\/span> <\/span><\/span> <\/span><\/span> System.<\/span>out<\/span>.<\/span>println<\/span>(<\/span>user);<\/span> <\/span><\/span>}<\/span> <\/span><\/span> <\/span><\/span>\/\/ 6. 释放资源 <\/span><\/span><\/span><\/span>rs.<\/span>close<\/span>();<\/span> <\/span><\/span>pstmt.<\/span>close<\/span>();<\/span> <\/span><\/span>conn.<\/span>close<\/span>();<\/span> <\/span><\/span><\/code><\/pre>四、SQL 预编译（PreparedStatement）<\/h4> 1. 优势<\/strong><\/p> 性能更高<\/strong>：SQL 语句会被数据库预编译并缓存。当多次执行相同结构的 SQL（仅参数不同）时，数据库会直接使用缓存的编译结果，无需再次编译，效率显著提升。<\/li> 防止 SQL 注入<\/strong>：这是最重要的安全优势。<\/li> <\/ul> 2. 原理<\/strong> 预编译的核心在于：SQL 语句在发送到数据库时，其中的参数使用占位符 ?<\/code> 代替。数据库的解析\/编译阶段只处理 SQL 的结构<\/strong>（将占位符视为参数标记），而不处理具体的参数值。后续传入的用户数据仅仅作为“值”绑定到这些占位符上。因此，用户输入的数据在解析阶段不可能成为 SQL 语法的一部分，从根本上杜绝了 SQL 注入攻击。<\/p> 示例：<\/strong><\/p> -- 普通 Statement，存在拼接，易被注入 <\/span><\/span><\/span><\/span>String sql<\/span> =<\/span> "SELECT ... FROM user WHERE username = '"<\/span> +<\/span> inputName +<\/span> "'"<\/span>; <\/span><\/span> <\/span><\/span>-- PreparedStatement，使用占位符 <\/span><\/span><\/span><\/span>String sql<\/span> =<\/span> "SELECT ... FROM user WHERE username = ?"<\/span>; <\/span><\/span>pstmt.setString(1<\/span>, inputName); \/\/<\/span> 无论<\/span> inputName 是什么，都只会被当作一个字符串值<\/span> <\/span><\/span><\/code><\/pre> 第二部分：MyBatis 框架<\/h3> 一、MyBatis 简介<\/h4> MyBatis 是一个优秀的持久层框架<\/strong>，它封装了 JDBC 的繁琐操作，使开发者只需关注 SQL 语句本身，而不需要花费精力去处理注册驱动、创建连接、手动设置参数、结果集检索等过程。<\/p> 二、MyBatis 入门程序<\/h4> 1. 环境准备<\/strong><\/p> 在 Spring Boot 项目中，引入 MyBatis 和数据库驱动依赖（如 mybatis-spring-boot-starter<\/code> 和 mysql-connector-java<\/code>）。<\/li> 配置数据源信息。<\/li> <\/ul> 2. 核心配置<\/strong><\/p> 配置文件<\/strong>：在 application.yml<\/code> 或 application.properties<\/code> 中配置数据源连接信息和 MyBatis 的基本设置（如映射文件位置）。 # application.yml 示例<\/span> <\/span><\/span>spring<\/span>: <\/span><\/span> datasource<\/span>: <\/span><\/span> url<\/span>: jdbc:mysql:\/\/localhost:3306\/db_test<\/span> <\/span><\/span> username<\/span>: your_username<\/span> <\/span><\/span> password<\/span>: your_password<\/span> <\/span><\/span> driver-class-name<\/span>: com.mysql.cj.jdbc.Driver<\/span> <\/span><\/span> <\/span><\/span># MyBatis 配置<\/span> <\/span><\/span>mybatis<\/span>: <\/span><\/span> mapper-locations<\/span>: classpath:mapper\/*.xml <\/span> # 指定 XML 映射文件的位置<\/span> <\/span><\/span> type-aliases-package<\/span>: com.example.entity <\/span> # 配置实体类别名包<\/span> <\/span><\/span><\/code><\/pre><\/li> 映射文件<\/strong>：创建 XML 文件（如 UserMapper.xml<\/code>）来编写 SQL 语句。 <?xml version="1.0" encoding="UTF-8" ?><\/span> <\/span><\/span><!DOCTYPE mapper PUBLIC "-\/\/mybatis.org\/\/DTD Mapper 3.0\/\/EN" "http:\/\/mybatis.org\/dtd\/mybatis-3-mapper.dtd"><\/span> <\/span><\/span><mapper<\/span> namespace=<\/span>"com.example.mapper.UserMapper"<\/span>><\/span> <\/span><\/span> <select<\/span> id=<\/span>"selectUserById"<\/span> resultType=<\/span>"User"<\/span>><\/span> <\/span><\/span> SELECT * FROM user WHERE id = #{id} <\/span><\/span> <\/select><\/span> <\/span><\/span><\/mapper><\/span> <\/span><\/span><\/code><\/pre><\/li> 接口<\/strong>：创建一个 Java 接口，方法名与映射文件中的 SQL 语句 ID 对应。 @Mapper<\/span> \/\/ 使用 @Mapper 注解，Spring Boot 会自动为其创建代理对象 <\/span><\/span><\/span><\/span>public<\/span> interface<\/span> UserMapper<\/span> {<\/span> <\/span><\/span> User selectUserById<\/span>(<\/span>Integer id);<\/span> <\/span><\/span>}<\/span> <\/span><\/span><\/code><\/pre><\/li> <\/ul> 三、数据库连接池<\/h4> 作用<\/strong>：MyBatis 集成了数据库连接池（如 HikariCP，Spring Boot 2.x 后的默认连接池）。连接池负责管理、复用数据库连接，避免了频繁创建和关闭连接带来的巨大性能开销。<\/li> 优势<\/strong>：提升应用响应速度，节约系统资源。<\/li> <\/ul> 四、增删改查操作<\/h4> 在 MyBatis 的映射文件中，使用不同的标签来执行相应的操作：<\/p> <insert><\/code>：执行插入语句，使用 useGeneratedKeys<\/code> 和 keyProperty<\/code> 可获取自增主键。<\/li> <delete><\/code>：执行删除语句。<\/li> <update><\/code>：执行更新语句。<\/li> <select><\/code>：执行查询语句，resultType<\/code> 或 resultMap<\/code> 属性指定返回结果类型。<\/li> <\/ul> 五、注解开发<\/h4> 除了 XML 配置，MyBatis 也支持使用注解直接在 Mapper 接口的方法上编写 SQL，使代码更简洁。<\/p> @Mapper<\/span> <\/span><\/span>public<\/span> interface<\/span> UserMapper<\/span> {<\/span> <\/span><\/span> <\/span><\/span> @Select<\/span>(<\/span>"SELECT * FROM user WHERE id = #{id}"<\/span>)<\/span> <\/span><\/span> User findById<\/span>(<\/span>Integer id);<\/span> <\/span><\/span> <\/span><\/span> @Insert<\/span>(<\/span>"INSERT INTO user (username, age) VALUES (#{username}, #{age})"<\/span>)<\/span> <\/span><\/span> @Options<\/span>(<\/span>useGeneratedKeys =<\/span> true<\/span>,<\/span> keyProperty =<\/span> "id"<\/span>)<\/span> <\/span><\/span> int<\/span> insert<\/span>(<\/span>User user);<\/span> <\/span><\/span> <\/span><\/span> @Update<\/span>(<\/span>"UPDATE user SET username=#{username} WHERE id=#{id}"<\/span>)<\/span> <\/span><\/span> int<\/span> update<\/span>(<\/span>User user);<\/span> <\/span><\/span> <\/span><\/span> @Delete<\/span>(<\/span>"DELETE FROM user WHERE id = #{id}"<\/span>)<\/span> <\/span><\/span> int<\/span> deleteById<\/span>(<\/span>Integer id);<\/span> <\/span><\/span>}<\/span> <\/span><\/span><\/code><\/pre>注解 vs XML：<\/strong><\/p> 注解<\/strong>：简单直观，适合简单的、固定的 SQL 语句。<\/li> XML<\/strong>：功能更强大，尤其适合编写复杂的、动态的 SQL（如条件查询 <if><\/code>, <where><\/code>，循环 <foreach><\/code> 等），且便于管理大量的 SQL 语句。<\/li> <\/ul> 关键点总结<\/h3> JDBC 是基础<\/strong>：理解 JDBC 的六个核心步骤是掌握任何 Java 持久层框架的基石。<\/li> PreparedStatement 是必备技能<\/strong>：它不仅提升性能，更重要的是其防止 SQL 注入<\/strong>的安全性，在任何数据库操作中都应优先使用。<\/li> MyBatis 是 JDBC 的封装<\/strong>：它的目的是简化开发，其底层依然通过 JDBC 与数据库交互。<\/li> 连接池的重要性<\/strong>：在生产环境中，使用数据库连接池是必须的，它能极大提升应用性能。<\/li> 选择开发方式<\/strong>：根据项目复杂度和团队习惯，灵活选择注解开发或 XML 配置。对于复杂 SQL，XML 的优势非常明显。<\/li> <\/ol> 希望这份详尽的教学文档能帮助您系统地掌握 Spring Boot 中 JDBC 和 MyBatis 的使用。<\/p>