在Java中如何实现图书借阅历史记录查询

图书借阅历史记录查询是图书管理系统的必备功能，需要结合数据库存储、后端逻辑处理和前端展示三个部分共同实现，下面将从整体架构到具体代码逐步说明实现方法。

一、数据库表设计

首先需要设计存储借阅记录的数据库表，通常借阅记录需要关联图书信息、读者信息，以下是核心表结构示例：

-- 图书表
CREATE TABLE book (
    id INT PRIMARY KEY AUTO_INCREMENT,
    book_name VARCHAR(100) NOT NULL COMMENT '图书名称',
    author VARCHAR(50) COMMENT '作者',
    isbn VARCHAR(30) UNIQUE COMMENT 'ISBN号'
);

-- 读者表
CREATE TABLE reader (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reader_name VARCHAR(50) NOT NULL COMMENT '读者姓名',
    reader_no VARCHAR(30) UNIQUE COMMENT '读者编号'
);

-- 借阅记录表
CREATE TABLE borrow_record (
    id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT NOT NULL COMMENT '图书ID',
    reader_id INT NOT NULL COMMENT '读者ID',
    borrow_time DATETIME NOT NULL COMMENT '借阅时间',
    return_time DATETIME COMMENT '归还时间',
    status TINYINT NOT NULL DEFAULT 0 COMMENT '状态 0未归还 1已归还',
    FOREIGN KEY (book_id) REFERENCES book(id),
    FOREIGN KEY (reader_id) REFERENCES reader(id)
);

二、后端实体类定义

使用Spring Boot开发时，首先需要定义对应的实体类，这里采用MyBatis-Plus作为持久层框架：

// 借阅记录实体类
package com.example.library.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.time.LocalDateTime;

@Data
@TableName("borrow_record")
public class BorrowRecord {
    private Integer id;
    private Integer bookId;
    private Integer readerId;
    private LocalDateTime borrowTime;
    private LocalDateTime returnTime;
    private Integer status;
    // 关联查询的扩展字段，不对应数据库表列
    private String bookName;
    private String readerName;
}

三、借阅记录查询接口开发

查询功能通常需要支持多条件筛选，比如按读者编号、图书名称、借阅时间范围、归还状态等条件查询，同时需要支持分页，以下是Controller和Service层的实现：

1. Controller层代码

package com.example.library.controller;

import com.example.library.common.Result;
import com.example.library.dto.BorrowRecordQueryDTO;
import com.example.library.service.BorrowRecordService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;

@RestController
@RequestMapping("/borrowRecord")
public class BorrowRecordController {

    @Resource
    private BorrowRecordService borrowRecordService;

    @GetMapping("/list")
    public Result queryBorrowRecords(BorrowRecordQueryDTO queryDTO) {
        return Result.success(borrowRecordService.queryBorrowRecords(queryDTO));
    }
}

2. 查询参数DTO定义

package com.example.library.dto;

import lombok.Data;
import java.time.LocalDateTime;

@Data
public class BorrowRecordQueryDTO {
    private String readerNo; // 读者编号
    private String bookName; // 图书名称
    private LocalDateTime startTime; // 借阅开始时间
    private LocalDateTime endTime; // 借阅结束时间
    private Integer status; // 归还状态
    private Integer pageNum = 1; // 页码
    private Integer pageSize = 10; // 每页条数
}

3. Service层实现

Service层需要编写关联查询的逻辑，将借阅记录、图书信息、读者信息关联起来：

package com.example.library.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.library.dto.BorrowRecordQueryDTO;
import com.example.library.entity.BorrowRecord;
import com.example.library.mapper.BorrowRecordMapper;
import com.example.library.service.BorrowRecordService;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import javax.annotation.Resource;
import java.util.List;

@Service
public class BorrowRecordServiceImpl implements BorrowRecordService {

    @Resource
    private BorrowRecordMapper borrowRecordMapper;

    @Override
    public Page<BorrowRecord> queryBorrowRecords(BorrowRecordQueryDTO queryDTO) {
        Page<BorrowRecord> page = new Page<>(queryDTO.getPageNum(), queryDTO.getPageSize());
        // 这里使用自定义SQL实现关联查询，或者在Mapper中编写关联查询方法
        List<BorrowRecord> records = borrowRecordMapper.queryRecordsByCondition(
            page,
            queryDTO.getReaderNo(),
            queryDTO.getBookName(),
            queryDTO.getStartTime(),
            queryDTO.getEndTime(),
            queryDTO.getStatus()
        );
        page.setRecords(records);
        return page;
    }
}

4. Mapper层自定义SQL

package com.example.library.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.library.entity.BorrowRecord;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.time.LocalDateTime;
import java.util.List;

public interface BorrowRecordMapper extends BaseMapper<BorrowRecord> {

    @Select("""
        SELECT
            br.*,
            b.book_name AS bookName,
            r.reader_name AS readerName
        FROM borrow_record br
        LEFT JOIN book b ON br.book_id = b.id
        LEFT JOIN reader r ON br.reader_id = r.id
        <where>
            <if test="readerNo != null and readerNo != ''">
                AND r.reader_no = #{readerNo}
            </if>
            <if test="bookName != null and bookName != ''">
                AND b.book_name LIKE CONCAT('%', #{bookName}, '%')
            </if>
            <if test="startTime != null">
                AND br.borrow_time >= #{startTime}
            </if>
            <if test="endTime != null">
                AND br.borrow_time <= #{endTime}
            </if>
            <if test="status != null">
                AND br.status = #{status}
            </if>
        </where>
        ORDER BY br.borrow_time DESC
    """)
    List<BorrowRecord> queryRecordsByCondition(
        Page<BorrowRecord> page,
        @Param("readerNo") String readerNo,
        @Param("bookName") String bookName,
        @Param("startTime") LocalDateTime startTime,
        @Param("endTime") LocalDateTime endTime,
        @Param("status") Integer status
    );
}

四、前端查询页面示例

前端可以使用Vue或者普通HTML页面实现查询表单和结果展示，以下是简单的HTML示例：

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <title>借阅记录查询</title>
    <style>
        .search-form { margin: 20px 0; }
        .search-form input, .search-form select { margin-right: 10px; padding: 5px; }
        table { border-collapse: collapse; width: 100%; }
        th, td { border: 1px solid #ccc; padding: 8px; text-align: center; }
    </style>
</head>
<body>
    <div class="search-form">
        <input type="text" id="readerNo" placeholder="读者编号">
        <input type="text" id="bookName" placeholder="图书名称">
        <select id="status">
            <option value="">全部状态</option>
            <option value="0">未归还</option>
            <option value="1">已归还</option>
        </select>
        <button onclick="queryRecords()">查询</button>
    </div>
    <table>
        <thead>
            <tr>
                <th>ID</th>
                <th>图书名称</th>
                <th>读者姓名</th>
                <th>借阅时间</th>
                <th>归还时间</th>
                <th>状态</th>
            </tr>
        </thead>
        <tbody id="recordBody"></tbody>
    </table>
    <div id="pageInfo"></div>

    <script>
        function queryRecords(pageNum = 1) {
            const readerNo = document.getElementById('readerNo').value;
            const bookName = document.getElementById('bookName').value;
            const status = document.getElementById('status').value;
            // 这里使用fetch调用后端接口，实际项目中替换为自己的后端地址
            fetch(`http://ipipp.com:8080/borrowRecord/list?readerNo=${readerNo}&bookName=${bookName}&status=${status}&pageNum=${pageNum}`)
                .then(res => res.json())
                .then(data => {
                    if (data.code === 200) {
                        renderTable(data.data.records);
                        renderPage(data.data.current, data.data.pages);
                    }
                });
        }

        function renderTable(records) {
            const tbody = document.getElementById('recordBody');
            tbody.innerHTML = '';
            records.forEach(record => {
                const tr = document.createElement('tr');
                tr.innerHTML = `
                    <td>${record.id}</td>
                    <td>${record.bookName}</td>
                    <td>${record.readerName}</td>
                    <td>${record.borrowTime}</td>
                    <td>${record.returnTime || '未归还'}</td>
                    <td>${record.status === 0 ? '未归还' : '已归还'}</td>
                `;
                tbody.appendChild(tr);
            });
        }

        function renderPage(current, total) {
            const pageInfo = document.getElementById('pageInfo');
            let pageHtml = '';
            for (let i = 1; i <= total; i++) {
                if (i === current) {
                    pageHtml += `<span style="margin:0 5px;color:red">${i}</span>`;
                } else {
                    pageHtml += `<span style="margin:0 5px;cursor:pointer" onclick="queryRecords(${i})">${i}</span>`;
                }
            }
            pageInfo.innerHTML = pageHtml;
        }

        // 页面加载时查询第一页数据
        window.onload = () => queryRecords();
    </script>
</body>
</html>

五、常见问题说明

• 如果查询速度较慢，可以给borrow_record表的book_id、reader_id、borrow_time字段添加索引提升查询效率
• 时间范围查询需要注意时区问题，建议统一使用UTC时间或者服务器本地时间存储和查询
• 分页插件的配置需要在Spring Boot配置类中添加MyBatis-Plus的分页拦截器，否则分页不会生效

Java图书借阅系统借阅记录查询MySQLSpring_Boot修改时间：2026-06-16 09:45:59