【Java全栈教程】第 42 课：Spring Boot数据层

第 42 课：Spring Boot数据层

本课目标：掌握Spring Data JPA的集成与使用，理解Repository接口体系，实现完整的数据访问层。

---

一、概念讲解

1.1 Spring Data JPA简介

Spring Data JPA 是 Spring Data 的子项目，简化了 JPA 的数据访问层开发。核心思想：通过接口方法名自动生成查询SQL。

Repository继承体系：
┌──────────────────────────────────────────┐
│           Repository (标记接口)            │
├──────────────────────────────────────────┤
│           CrudRepository                 │
│  (CRUD: save, findById, delete, findAll) │
├──────────────────────────────────────────┤
│           PagingAndSortingRepository     │
│  (分页+排序: findAll(Pageable))           │
├──────────────────────────────────────────┤
│           JpaRepository                  │
│  (JPA特有: flush, saveAndFlush, etc.)    │
└──────────────────────────────────────────┘

1.2 方法名查询原理

方法名 → 解析 → 生成SQL
findByUsername(String username)
  → SELECT * FROM user WHERE username = ?

findByAgeBetween(int min, int max)
  → SELECT * FROM user WHERE age BETWEEN ? AND ?

findByUsernameContaining(String keyword)
  → SELECT * FROM user WHERE username LIKE '%keyword%'

deleteByAgeLessThan(int age)
  → DELETE FROM user WHERE age < ?

1.3 JPA实体映射

Java类                    数据库表
┌──────────────┐          ┌──────────────┐
│ @Entity      │  ──映射──>│  user        │
│ @Table       │          │  (表名)      │
├──────────────┤          ├──────────────┤
│ @Id          │  ──映射──>│  id (PK)     │
│ @GeneratedValue│         │              │
├──────────────┤          ├──────────────┤
│ username     │  ──映射──>│  username    │
│ email        │  ──映射──>│  email       │
└──────────────┘          └──────────────┘

---

二、语法格式

2.1 实体注解

@Entity                    // 标识为JPA实体
@Table(name = "table_name") // 指定表名
@Id                        // 主键
@GeneratedValue            // 自增策略
@Column(name = "col_name") // 指定列名
@Enumerated(EnumType.STRING) // 枚举存储方式
@Temporal(TemporalType.DATE) // 日期类型
@Lob                       // 大文本/BLOB
@Transient                 // 非持久化字段

2.2 Repository方法名约定

 findBy[属性名]                        → WHERE 属性名 = ?
 findBy[属性名]Like                    → WHERE 属性名 LIKE ?
 findBy[属性名]In(Collection)          → WHERE 属性名 IN (...)
 findBy[属性名]Between(a, b)           → WHERE 属性名 BETWEEN a AND b
 findBy[属性名]IsNull / IsNotNull      → WHERE 属性名 IS NULL / IS NOT NULL
 findBy[属性名]GreaterThan / LessThan  → WHERE 属性名 > ? / < ?
 deleteBy[属性名]                      → DELETE WHERE 属性名 = ?
 countBy[属性名]                       → SELECT COUNT(*) WHERE 属性名 = ?
 existsBy[属性名]                      → SELECT EXISTS(...) WHERE 属性名 = ?

---

三、代码案例

3.1 添加依赖（pom.xml新增）

<dependencies>
    <!-- Spring Boot Web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- Spring Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

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

    <!-- Lombok（简化实体代码） -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

3.2 数据库配置

# application.yml
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/demo_db?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf-8
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver

  jpa:
    hibernate:
      ddl-auto: update    # 自动建表/更新表结构
    show-sql: true         # 控制台打印SQL
    properties:
      hibernate:
        format_sql: true   # 格式化SQL输出

3.3 建表SQL

CREATE DATABASE IF NOT EXISTS demo_db DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE demo_db;

CREATE TABLE IF NOT EXISTS sys_user (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(100) NOT NULL COMMENT '邮箱',
    password VARCHAR(200) NOT NULL COMMENT '密码',
    age INT DEFAULT 0 COMMENT '年龄',
    status INT DEFAULT 1 COMMENT '状态: 0-禁用, 1-启用',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

3.4 用户实体类

package com.example.demo.entity;

import jakarta.persistence.*;
import java.time.LocalDateTime;

@Entity
@Table(name = "sys_user")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true, length = 50)
    private String username;

    @Column(nullable = false, length = 100)
    private String email;

    @Column(nullable = false)
    private String password;

    @Column(columnDefinition = "INT DEFAULT 0")
    private int age;

    @Column(columnDefinition = "INT DEFAULT 1")
    private int status;

    @Column(name = "create_time")
    private LocalDateTime createTime;

    @Column(name = "update_time")
    private LocalDateTime updateTime;

    @PrePersist
    public void prePersist() {
        this.createTime = LocalDateTime.now();
        this.updateTime = LocalDateTime.now();
    }

    @PreUpdate
    public void preUpdate() {
        this.updateTime = LocalDateTime.now();
    }

    // 无参构造（JPA必须）
    public User() {}

    public User(String username, String email, String password, int age) {
        this.username = username;
        this.email = email;
        this.password = password;
        this.age = age;
        this.status = 1;
    }

    // getter/setter
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public String getUsername() { return username; }
    public void setUsername(String username) { this.username = username; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    public String getPassword() { return password; }
    public void setPassword(String password) { this.password = password; }
    public int getAge() { return age; }
    public void setAge(int age) { this.age = age; }
    public int getStatus() { return status; }
    public void setStatus(int status) { this.status = status; }
    public LocalDateTime getCreateTime() { return createTime; }
    public LocalDateTime getUpdateTime() { return updateTime; }
}

3.5 Repository接口

package com.example.demo.repository;

import com.example.demo.entity.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Optional;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    // 方法名查询
    Optional<User> findByUsername(String username);

    Optional<User> findByEmail(String email);

    List<User> findByAgeBetween(int minAge, int maxAge);

    List<User> findByStatus(int status);

    // 模糊查询（分页）
    Page<User> findByUsernameContaining(String keyword, Pageable pageable);

    // 自定义JPQL查询
    @Query("SELECT u FROM User u WHERE u.age >= :age ORDER BY u.createTime DESC")
    List<User> findByAgeGreaterThanOrEqual(@Param("age") int age);

    // 自定义原生SQL查询
    @Query(value = "SELECT * FROM sys_user WHERE email LIKE %:keyword%", nativeQuery = true)
    List<User> searchByEmail(@Param("keyword") String keyword);

    // 更新操作
    @Modifying
    @Query("UPDATE User u SET u.status = :status WHERE u.id = :id")
    int updateStatus(@Param("id") Long id, @Param("status") int status);

    // 判断是否存在
    boolean existsByUsername(String username);

    boolean existsByEmail(String email);

    // 统计
    long countByStatus(int status);

    // 删除
    void deleteByStatus(int status);
}

3.6 Service层

package com.example.demo.service;

import com.example.demo.entity.User;
import com.example.demo.repository.UserRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;

@Service
@Transactional
public class UserService {

    private final UserRepository userRepository;

    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    // 创建用户
    public User create(User user) {
        if (userRepository.existsByUsername(user.getUsername())) {
            throw new RuntimeException("用户名已存在");
        }
        if (userRepository.existsByEmail(user.getEmail())) {
            throw new RuntimeException("邮箱已被注册");
        }
        user.setStatus(1);
        return userRepository.save(user);
    }

    // 根据ID查询
    @Transactional(readOnly = true)
    public User findById(Long id) {
        return userRepository.findById(id)
            .orElseThrow(() -> new RuntimeException("用户不存在"));
    }

    // 查询所有
    @Transactional(readOnly = true)
    public List<User> findAll() {
        return userRepository.findAll();
    }

    // 分页查询
    @Transactional(readOnly = true)
    public Page<User> findPage(int page, int size, String keyword) {
        Sort sort = Sort.by(Sort.Direction.DESC, "createTime");
        Pageable pageable = PageRequest.of(page - 1, size, sort);

        if (keyword != null && !keyword.isEmpty()) {
            return userRepository.findByUsernameContaining(keyword, pageable);
        }
        return userRepository.findAll(pageable);
    }

    // 更新用户
    public User update(Long id, User user) {
        User existing = findById(id);
        existing.setUsername(user.getUsername());
        existing.setEmail(user.getEmail());
        existing.setAge(user.getAge());
        return userRepository.save(existing);
    }

    // 更新状态
    public int updateStatus(Long id, int status) {
        return userRepository.updateStatus(id, status);
    }

    // 删除用户
    public void delete(Long id) {
        if (!userRepository.existsById(id)) {
            throw new RuntimeException("用户不存在");
        }
        userRepository.deleteById(id);
    }

    // 按年龄范围查询
    @Transactional(readOnly = true)
    public List<User> findByAgeRange(int min, int max) {
        return userRepository.findByAgeBetween(min, max);
    }

    // 统计活跃用户数
    @Transactional(readOnly = true)
    public long countActiveUsers() {
        return userRepository.countByStatus(1);
    }
}

3.7 Controller层

package com.example.demo.controller;

import com.example.demo.common.PageResult;
import com.example.demo.common.Result;
import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import jakarta.validation.Valid;
import org.springframework.data.domain.Page;
import org.springframework.web.bind.annotation.*;
import java.util.Map;

@RestController
@RequestMapping("/api/users")
public class UserController {

    private final UserService userService;

    public UserController(UserService userService) {
        this.userService = userService;
    }

    @PostMapping
    public Result<User> create(@Valid @RequestBody User user) {
        return Result.success("创建成功", userService.create(user));
    }

    @GetMapping("/{id}")
    public Result<User> getById(@PathVariable Long id) {
        return Result.success(userService.findById(id));
    }

    @GetMapping
    public Result<PageResult<User>> list(
            @RequestParam(defaultValue = "1") int page,
            @RequestParam(defaultValue = "10") int size,
            @RequestParam(required = false) String keyword) {

        Page<User> userPage = userService.findPage(page, size, keyword);
        PageResult<User> pageResult = new PageResult<>(
            userPage.getContent(),
            userPage.getTotalElements(),
            page,
            size
        );
        return Result.success(pageResult);
    }

    @PutMapping("/{id}")
    public Result<User> update(@PathVariable Long id, @Valid @RequestBody User user) {
        return Result.success("更新成功", userService.update(id, user));
    }

    @DeleteMapping("/{id}")
    public Result<Void> delete(@PathVariable Long id) {
        userService.delete(id);
        return Result.success("删除成功", null);
    }

    @PatchMapping("/{id}/status")
    public Result<Void> updateStatus(@PathVariable Long id, @RequestBody Map<String, Integer> body) {
        userService.updateStatus(id, body.get("status"));
        return Result.success("状态更新成功", null);
    }

    @GetMapping("/age/{min}/{max}")
    public Result<?> findByAgeRange(@PathVariable int min, @PathVariable int max) {
        return Result.success(userService.findByAgeRange(min, max));
    }

    @GetMapping("/stats/active")
    public Result<Long> countActive() {
        return Result.success(userService.countActiveUsers());
    }
}

3.8 分页排序查询测试

package com.example.demo;

import com.example.demo.entity.User;
import com.example.demo.repository.UserRepository;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;

@SpringBootTest
public class UserRepositoryTest {

    @Autowired
    private UserRepository userRepository;

    @Test
    public void testPagination() {
        // 第1页，每页5条，按创建时间降序
        Pageable pageable = PageRequest.of(0, 5, Sort.by(Sort.Direction.DESC, "createTime"));
        Page<User> page = userRepository.findAll(pageable);

        System.out.println("总记录数: " + page.getTotalElements());
        System.out.println("总页数: " + page.getTotalPages());
        System.out.println("当前页数据: " + page.getContent().size());
        page.getContent().forEach(u ->
            System.out.println(u.getUsername() + " - " + u.getCreateTime()));
    }
}

▶ 运行结果：

总记录数: 25
总页数: 5
当前页数据: 5
admin - 2024-01-15T10:30:00
zhangsan - 2024-01-14T09:20:00
lisi - 2024-01-13T14:45:00
wangwu - 2024-01-12T11:15:00
zhaoliu - 2024-01-11T16:30:00

---

四、常见错误

4.1 Table doesn't exist

原因：数据库未创建或ddl-auto配置不正确。

解决：检查spring.jpa.hibernate.ddl-auto=update，或手动执行建表SQL。

4.2 could not extract ResultSet

原因：实体字段与数据库列名不匹配。

解决：使用@Column(name = "列名")指定映射，或检查数据库表结构。

4.3 Detached entity passed to persist

原因：尝试持久化一个已有ID的实体。

解决：使用save代替persist，或先检查实体是否存在。

4.4 N+1查询问题

现象：循环中执行了大量SQL查询。

解决：使用@EntityGraph或JOIN FETCH预加载关联数据。

---

五、课后练习

1. 创建Product实体，包含name、price、categoryId字段，实现Repository和CRUD接口
2. 实现按价格范围查询（findByPriceBetween）和按分类统计数量（countByCategoryId）
3. 编写分页查询测试，验证分页参数的正确性
4. 使用@Query自定义一个查询：统计每个年龄段的用户数量

---

六、本课小结

知识点	说明
JpaRepository	继承即可获得基本CRUD能力
方法名约定	按命名规范自动生成查询，无需手写SQL
@Query	自定义JPQL或原生SQL查询
Pageable/Page	分页查询的参数和结果封装
@Transactional	事务管理，保证数据一致性
@PrePersist/@PreUpdate	实体生命周期回调，自动填充时间戳

---

本课程持续更新中，欢迎关注！