Spring Boot自定义CRUD查询问题：家务追踪应用适配Thymeleaf

1. 先确认实体类的关联配置是否正确 #

首先确保你的Completion实体类中，Person和Chore字段的关联注解配置正确，明确指定数据库对应的外键列：

@Entity
@Table(name = "completion")
public class Completion {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "person_id", nullable = false) // 对应数据库person_id外键
    private Person person;

    @ManyToOne
    @JoinColumn(name = "chore_id", nullable = false) // 对应数据库chore_id外键
    private Chore chore;

    // 其他业务字段（如完成时间）、getter/setter、构造方法
}

2. 修正CompletionRepository的查询方法 #

针对传入的long类型ID参数，有两种简单可行的实现方式：

方式一：利用Spring Data JPA方法命名规则

直接定义符合规则的方法，JPA会自动生成对应的SQL逻辑：

public interface CompletionRepository extends JpaRepository<Completion, Long> {
    // 根据person_id和chore_id查询单条完成记录
    Optional<Completion> findByPersonIdAndChoreId(Long personId, Long choreId);

    // 根据person_id查询该用户的所有完成记录
    List<Completion> findByPersonId(Long personId);

    // 根据chore_id查询该家务的所有完成记录
    List<Completion> findByChoreId(Long choreId);
}

方式二：使用@Query注解自定义JPQL

如果需要更复杂的查询逻辑，可通过JPQL直接关联实体ID字段：

public interface CompletionRepository extends JpaRepository<Completion, Long> {
    @Query("SELECT c FROM Completion c WHERE c.person.id = :personId AND c.chore.id = :choreId")
    Optional<Completion> findByPersonIdAndChoreId(@Param("personId") Long personId, @Param("choreId") Long choreId);
}

3. 完善Service层逻辑 #

在CompletionService实现类中，直接调用Repository方法即可，无需手动转换实体对象：

@Service
public class CompletionServiceImpl implements CompletionService {
    private final CompletionRepository completionRepository;
    private final PersonRepository personRepository;
    private final ChoreRepository choreRepository;

    // 构造注入依赖
    public CompletionServiceImpl(CompletionRepository completionRepository,
                                 PersonRepository personRepository,
                                 ChoreRepository choreRepository) {
        this.completionRepository = completionRepository;
        this.personRepository = personRepository;
        this.choreRepository = choreRepository;
    }

    @Override
    public Optional<Completion> getCompletionByPersonAndChoreIds(Long personId, Long choreId) {
        return completionRepository.findByPersonIdAndChoreId(personId, choreId);
    }

    @Override
    public List<Completion> getCompletionsByPersonId(Long personId) {
        return completionRepository.findByPersonId(personId);
    }

    // 新增完成记录示例
    @Override
    public Completion saveCompletion(Long personId, Long choreId, LocalDateTime completedAt) {
        // 先校验并获取关联实体
        Person person = personRepository.findById(personId)
                .orElseThrow(() -> new IllegalArgumentException("不存在该ID的用户"));
        Chore chore = choreRepository.findById(choreId)
                .orElseThrow(() -> new IllegalArgumentException("不存在该ID的家务"));
        
        Completion completion = new Completion();
        completion.setPerson(person);
        completion.setChore(chore);
        completion.setCompletedAt(completedAt);
        return completionRepository.save(completion);
    }
}

4. Controller层传递数据到Thymeleaf #

在Controller中接收前端传入的ID参数，调用Service获取数据后渲染到页面：

@Controller
@RequestMapping("/completions")
public class CompletionController {
    private final CompletionService completionService;

    public CompletionController(CompletionService completionService) {
        this.completionService = completionService;
    }

    @GetMapping("/person/{personId}")
    public String getCompletionsByPerson(@PathVariable Long personId, Model model) {
        List<Completion> completions = completionService.getCompletionsByPersonId(personId);
        model.addAttribute("completions", completions);
        return "completion-list"; // 对应Thymeleaf模板文件名
    }

    // 新增完成记录的提交接口
    @PostMapping("/add")
    public String addCompletion(@RequestParam Long personId, @RequestParam Long choreId) {
        completionService.saveCompletion(personId, choreId, LocalDateTime.now());
        return "redirect:/completions/person/" + personId;
    }
}

5. Thymeleaf页面渲染示例 #

在completion-list.html中遍历数据展示：

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <title>用户家务完成记录</title>
</head>
<body>
    <h1>用户家务完成记录</h1>
    <table>
        <tr>
            <th>用户名</th>
            <th>家务名称</th>
            <th>完成时间</th>
        </tr>
        <tr th:each="completion : ${completions}">
            <td th:text="${completion.person.name}"></td>
            <td th:text="${completion.chore.name}"></td>
            <td th:text="${#temporals.format(completion.completedAt, 'yyyy-MM-dd HH:mm')}"></td>
        </tr>
    </table>
</body>
</html>

内容的提问来源于stack exchange，提问作者dave_m