跳到主要内容

数据访问

本章将介绍 Spring Boot 中数据访问的核心技术，包括 Spring Data JPA、MyBatis 集成和事务管理。

数据源配置​

添加依赖​

<!-- MySQL 驱动 -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

<!-- JDBC 起步依赖 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

数据源配置​

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    
    # HikariCP 连接池配置（Spring Boot 默认）
    hikari:
      minimum-idle: 5              # 最小空闲连接数
      maximum-pool-size: 20        # 最大连接数
      idle-timeout: 30000          # 空闲连接超时时间（毫秒）
      max-lifetime: 1800000        # 连接最大存活时间（毫秒）
      connection-timeout: 30000    # 连接超时时间（毫秒）
      pool-name: MyHikariCP        # 连接池名称

多数据源配置​

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    @Primary
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }
}

Spring Data JPA​

添加依赖​

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

JPA 配置​

spring:
  jpa:
    hibernate:
      ddl-auto: update          # 自动更新表结构
    show-sql: true              # 显示 SQL
    properties:
      hibernate:
        format_sql: true        # 格式化 SQL
        dialect: org.hibernate.dialect.MySQLDialect

ddl-auto 选项说明：

值说明
none不做任何操作
validate验证表结构与实体是否一致
update自动更新表结构（保留数据）
create每次启动创建新表（删除旧数据）
create-drop启动创建，关闭删除

实体类​

@Entity
@Table(name = "t_user")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(nullable = false, length = 50)
    private String name;
    
    @Column(unique = true, nullable = false, length = 100)
    private String email;
    
    @Column(name = "phone_number", length = 20)
    private String phone;
    
    private Integer age;
    
    @Enumerated(EnumType.STRING)
    private Gender gender;
    
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "created_at", updatable = false)
    private Date createdAt;
    
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "updated_at")
    private Date updatedAt;
    
    @PrePersist
    public void prePersist() {
        this.createdAt = new Date();
        this.updatedAt = new Date();
    }
    
    @PreUpdate
    public void preUpdate() {
        this.updatedAt = new Date();
    }
}

public enum Gender {
    MALE, FEMALE, OTHER
}

常用 JPA 注解​

注解作用
@Entity标识为实体类
@Table指定表名
@Id标识主键
@GeneratedValue主键生成策略
@Column字段映射
@Enumerated枚举类型映射
@Temporal日期时间类型映射
@Transient忽略字段（不映射）
@OneToOne一对一关联
@OneToMany一对多关联
@ManyToOne多对一关联
@ManyToMany多对多关联

Repository 接口​

// 基础 Repository
public interface UserRepository extends JpaRepository<User, Long> {
    
    // 根据方法名自动生成查询
    User findByName(String name);
    
    List<User> findByAgeGreaterThan(Integer age);
    
    List<User> findByAgeBetween(Integer min, Integer max);
    
    List<User> findByNameContaining(String name);
    
    User findByEmail(String email);
    
    // 使用 @Query 自定义查询
    @Query("SELECT u FROM User u WHERE u.email LIKE %:domain")
    List<User> findByEmailDomain(@Param("domain") String domain);
    
    // 原生 SQL 查询
    @Query(value = "SELECT * FROM t_user WHERE age > :age", nativeQuery = true)
    List<User> findByAgeGreaterThanNative(@Param("age") Integer age);
    
    // 更新操作
    @Modifying
    @Query("UPDATE User u SET u.name = :name WHERE u.id = :id")
    int updateName(@Param("id") Long id, @Param("name") String name);
    
    // 分页查询
    Page<User> findByAgeGreaterThan(Integer age, Pageable pageable);
}

关键字查询​

关键字示例SQL 片段
AndfindByNameAndAgeWHERE name = ? AND age = ?
OrfindByNameOrAgeWHERE name = ? OR age = ?
BetweenfindByAgeBetweenWHERE age BETWEEN ? AND ?
LessThanfindByAgeLessThanWHERE age < ?
GreaterThanfindByAgeGreaterThanWHERE age > ?
LikefindByNameLikeWHERE name LIKE ?
ContainingfindByNameContainingWHERE name LIKE %?%
StartingWithfindByNameStartingWithWHERE name LIKE ?%
EndingWithfindByNameEndingWithWHERE name LIKE %?
InfindByAgeInWHERE age IN (?)
IsNullfindByNameIsNullWHERE name IS NULL
NotNullfindByNameNotNullWHERE name IS NOT NULL
OrderByfindByAgeOrderByNameWHERE age = ? ORDER BY name
NotfindByNameNotWHERE name <> ?

分页查询​

// Service 层
public Page<User> findByPage(int page, int size, String sort) {
    Pageable pageable = PageRequest.of(page, size, Sort.by(sort).descending());
    return userRepository.findAll(pageable);
}

// Controller 层
@GetMapping
public Result<Page<User>> list(
    @RequestParam(defaultValue = "0") int page,
    @RequestParam(defaultValue = "10") int size,
    @RequestParam(defaultValue = "createdAt") String sort
) {
    return Result.success(userService.findByPage(page, size, sort));
}

分页响应结构：

{
    "content": [...],
    "totalElements": 100,
    "totalPages": 10,
    "size": 10,
    "number": 0,
    "first": true,
    "last": false
}

MyBatis 集成​

添加依赖​

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>3.0.3</version>
</dependency>

MyBatis 配置​

mybatis:
  mapper-locations: classpath:mapper/**/*.xml   # Mapper XML 文件位置
  type-aliases-package: com.example.entity      # 实体类包路径
  configuration:
    map-underscore-to-camel-case: true          # 开启驼峰命名转换
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

Mapper 接口​

@Mapper
public interface UserMapper {
    
    /**
     * 查询所有用户
     */
    List<User> findAll();
    
    /**
     * 根据 ID 查询
     */
    User findById(Long id);
    
    /**
     * 插入用户
     */
    int insert(User user);
    
    /**
     * 更新用户
     */
    int update(User user);
    
    /**
     * 删除用户
     */
    int deleteById(Long id);
    
    /**
     * 条件查询
     */
    List<User> findByCondition(@Param("name") String name, @Param("age") Integer age);
}

Mapper XML​

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.mapper.UserMapper">
    
    <!-- 结果映射 -->
    <resultMap id="UserResultMap" type="com.example.entity.User">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="email" column="email"/>
        <result property="phone" column="phone_number"/>
        <result property="age" column="age"/>
        <result property="createdAt" column="created_at"/>
        <result property="updatedAt" column="updated_at"/>
    </resultMap>
    
    <!-- 查询所有 -->
    <select id="findAll" resultMap="UserResultMap">
        SELECT * FROM t_user ORDER BY created_at DESC
    </select>
    
    <!-- 根据 ID 查询 -->
    <select id="findById" resultMap="UserResultMap">
        SELECT * FROM t_user WHERE id = #{id}
    </select>
    
    <!-- 插入 -->
    <insert id="insert" parameterType="com.example.entity.User" 
            useGeneratedKeys="true" keyProperty="id">
        INSERT INTO t_user (name, email, phone_number, age, created_at, updated_at)
        VALUES (#{name}, #{email}, #{phone}, #{age}, NOW(), NOW())
    </insert>
    
    <!-- 更新 -->
    <update id="update" parameterType="com.example.entity.User">
        UPDATE t_user
        SET name = #{name},
            email = #{email},
            phone_number = #{phone},
            age = #{age},
            updated_at = NOW()
        WHERE id = #{id}
    </update>
    
    <!-- 删除 -->
    <delete id="deleteById">
        DELETE FROM t_user WHERE id = #{id}
    </delete>
    
    <!-- 条件查询 -->
    <select id="findByCondition" resultMap="UserResultMap">
        SELECT * FROM t_user
        <where>
            <if test="name != null and name != ''">
                AND name LIKE CONCAT('%', #{name}, '%')
            </if>
            <if test="age != null">
                AND age = #{age}
            </if>
        </where>
    </select>
</mapper>

注解方式​

@Mapper
public interface UserMapper {
    
    @Select("SELECT * FROM t_user WHERE id = #{id}")
    User findById(Long id);
    
    @Select("SELECT * FROM t_user ORDER BY created_at DESC")
    List<User> findAll();
    
    @Insert("INSERT INTO t_user (name, email, phone_number, age, created_at, updated_at) " +
            "VALUES (#{name}, #{email}, #{phone}, #{age}, NOW(), NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);
    
    @Update("UPDATE t_user SET name = #{name}, email = #{email}, " +
            "phone_number = #{phone}, age = #{age}, updated_at = NOW() WHERE id = #{id}")
    int update(User user);
    
    @Delete("DELETE FROM t_user WHERE id = #{id}")
    int deleteById(Long id);
}

事务管理​

声明式事务​

@Service
@Transactional
public class UserServiceImpl implements UserService {
    
    @Autowired
    private UserRepository userRepository;
    
    @Autowired
    private OrderRepository orderRepository;
    
    /**
     * 创建用户并初始化订单
     * 整个方法在事务中执行
     */
    public User createUserWithOrder(UserDTO userDTO) {
        User user = userRepository.save(convertToEntity(userDTO));
        // 创建初始订单
        Order order = new Order();
        order.setUserId(user.getId());
        orderRepository.save(order);
        return user;
    }
    
    /**
     * 只读事务
     */
    @Transactional(readOnly = true)
    public List<User> findAll() {
        return userRepository.findAll();
    }
    
    /**
     * 指定回滚异常
     */
    @Transactional(rollbackFor = Exception.class)
    public void delete(Long id) {
        userRepository.deleteById(id);
    }
    
    /**
     * 不回滚特定异常
     */
    @Transactional(noRollbackFor = BusinessException.class)
    public void update(User user) {
        userRepository.save(user);
    }
}

事务传播行为​

传播行为说明
REQUIRED（默认）有事务则加入，无则新建
SUPPORTS有事务则加入，无则非事务执行
MANDATORY必须在事务中，否则抛异常
REQUIRES_NEW总是新建事务，暂停当前事务
NOT_SUPPORTED非事务执行，暂停当前事务
NEVER非事务执行，有事务则抛异常
NESTED嵌套事务（保存点）
@Service
public class OrderService {
    
    /**
     * 新事务
     */
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void createOrderLog(Order order) {
        // 即使外层事务回滚，此日志也会保存
        orderLogRepository.save(new OrderLog(order));
    }
}

事务隔离级别​

隔离级别脏读不可重复读幻读
READ_UNCOMMITTED√√√
READ_COMMITTED×√√
REPEATABLE_READ（MySQL 默认）××√
SERIALIZABLE×××
@Transactional(isolation = Isolation.READ_COMMITTED)
public User findById(Long id) {
    return userRepository.findById(id).orElse(null);
}

数据库迁移​

Flyway​

添加依赖​

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>
</dependency>

配置​

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration    # 迁移脚本位置
    baseline-on-migrate: true            # 首次迁移时创建基线
    validate-on-migrate: true            # 迁移前验证

迁移脚本命名规则​

V{版本号}__{描述}.sql

示例：
V1__init_schema.sql
V2__add_user_table.sql
V3__add_order_table.sql

迁移脚本示例​

-- V1__init_schema.sql
CREATE TABLE t_user (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone_number VARCHAR(20),
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Liquibase​

添加依赖​

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
</dependency>

配置​

spring:
  liquibase:
    enabled: true
    change-log: classpath:db/changelog/db.changelog-master.yaml

变更日志​

# db/changelog/db.changelog-master.yaml
databaseChangeLog:
  - include:
      file: db/changelog/changes/001-init-schema.yaml
# db/changelog/changes/001-init-schema.yaml
databaseChangeLog:
  - changeSet:
      id: 1
      author: author
      changes:
        - createTable:
            tableName: t_user
            columns:
              - column:
                  name: id
                  type: BIGINT
                  autoIncrement: true
                  constraints:
                    primaryKey: true
              - column:
                  name: name
                  type: VARCHAR(50)
                  constraints:
                    nullable: false

小结​

本章我们学习了：

  1. 数据源配置：单数据源和多数据源配置
  2. Spring Data JPA：实体映射、Repository 接口、分页查询
  3. MyBatis 集成：Mapper 接口、XML 配置、注解方式
  4. 事务管理：声明式事务、传播行为、隔离级别
  5. 数据库迁移：Flyway 和 Liquibase 的使用

练习​

  1. 配置一个 MySQL 数据源并使用 JPA 创建实体类
  2. 实现一个完整的 CRUD Repository
  3. 使用 MyBatis 实现动态 SQL 查询
  4. 实现一个涉及多表操作的事务方法
  5. 使用 Flyway 管理数据库版本