SpringBoot结合H2按外键实体组合查询借阅记录方案

1. 先修正借阅实体的两处问题 #

你的BorrowingEntity存在两个影响功能的小问题，需先修正：

• 关联PatronEntity的外键列名错误，tutorial_id应改为patron_id
• JPA不支持直接持久化Optional<LocalDate>类型，将returndate改为普通LocalDate，用null表示未归还状态

修正后的代码：

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity
@Table(name="borrowing")
public class BorrowingEntity {

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

    private LocalDate borrowingdate;

    private LocalDate duedate;

    // 改为普通LocalDate，null代表未归还
    private LocalDate returndate;

    @ManyToOne
    @JoinColumn(name = "patron_id", nullable = false)
    private PatronEntity patron;

    @ManyToOne
    @JoinColumn(name = "book_id", nullable = false)
    private BookEntity book;
}

2. 编写Repository层：高效定位借阅记录 #

基于Spring Data JPA实现精准查询，避免全表扫描。提供两种实现方式：

方式一：利用方法命名规则自动生成SQL #

import org.springframework.data.jpa.repository.JpaRepository;
import java.util.Optional;

public interface BorrowingRepository extends JpaRepository<BorrowingEntity, Long> {
    // 查询指定书籍、读者的未归还记录
    Optional<BorrowingEntity> findByBookIdAndPatronIdAndReturndateIsNull(Long bookId, Long patronId);
}

方式二：自定义JPQL查询（更灵活） #

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.Optional;

public interface BorrowingRepository extends JpaRepository<BorrowingEntity, Long> {
    @Query("SELECT b FROM BorrowingEntity b WHERE b.book.id = :bookId AND b.patron.id = :patronId AND b.returndate IS NULL")
    Optional<BorrowingEntity> findActiveBorrowRecord(@Param("bookId") Long bookId, @Param("patronId") Long patronId);
}

3. Service层封装业务逻辑 #

处理查询、更新逻辑，同时处理异常场景：

import org.springframework.stereotype.Service;
import java.time.LocalDate;
import java.util.NoSuchElementException;

@Service
public class BorrowingService {

    private final BorrowingRepository borrowingRepository;

    // 构造注入（Spring推荐方式）
    public BorrowingService(BorrowingRepository borrowingRepository) {
        this.borrowingRepository = borrowingRepository;
    }

    public void updateReturnDate(Long bookId, Long patronId) {
        // 查询未归还的有效借阅记录
        BorrowingEntity record = borrowingRepository.findByBookIdAndPatronIdAndReturndateIsNull(bookId, patronId)
                .orElseThrow(() -> new NoSuchElementException("未找到该书籍与读者的有效借阅记录"));
        
        // 更新归还日期为当前日期
        record.setReturndate(LocalDate.now());
        borrowingRepository.save(record);
    }
}

4. Controller层实现API接口 #

按照指定路径实现PUT请求（符合REST更新资源的规范）：

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.NoSuchElementException;

@RestController
public class BorrowingController {

    private final BorrowingService borrowingService;

    public BorrowingController(BorrowingService borrowingService) {
        this.borrowingService = borrowingService;
    }

    @PutMapping("/api/borrow/{bookId}/patron/{patronId}")
    public ResponseEntity<Void> updateReturnDate(@PathVariable Long bookId, @PathVariable Long patronId) {
        try {
            borrowingService.updateReturnDate(bookId, patronId);
            return ResponseEntity.ok().build();
        } catch (NoSuchElementException e) {
            return ResponseEntity.status(HttpStatus.NOT_FOUND).build();
        }
    }
}

5. 可选：全局异常优化 #

添加全局异常处理器，返回更友好的错误信息：

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;
import java.util.NoSuchElementException;

@RestControllerAdvice
public class GlobalExceptionHandler {

    @ExceptionHandler(NoSuchElementException.class)
    public ResponseEntity<String> handleNoRecordException(NoSuchElementException e) {
        return ResponseEntity.status(HttpStatus.NOT_FOUND).body(e.getMessage());
    }
}

实现优势 #

• 高效查询：通过book_id+patron_id+returndate的条件组合，可直接命中索引（建议给borrowing表的这三个字段创建联合索引，进一步提升查询速度）
• 架构清晰：分层设计分离数据访问、业务逻辑和API控制，便于后续维护扩展
• 规范兼容：返回符合REST标准的HTTP状态码，异常处理逻辑统一

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