<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>用户页面</title>
    <link href="./css/user.css" rel="stylesheet" type="text/css">
    <style>
        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            background: linear-gradient(to right, #e2e3e5, #ffffff);
            margin: 0;
            color: #333;
        }

        .topbar {
            background-color: #343a40;
            color: white;
            padding: 15px 20px;
            display: flex;
            justify-content: space-between;
            align-items: center;
            box-shadow: 0 2px 10px rgba(0, 0, 0, 0.2);
        }

        .topbar button {
            background-color: #dc3545;
            border: none;
            color: white;
            padding: 10px 15px;
            border-radius: 20px;
            cursor: pointer;
            transition: background-color 0.3s, transform 0.3s;
            font-weight: bold;
        }

        .topbar button:hover {
            background-color: #c82333;
            transform: scale(1.05);
        }

        .sidebar {
            width: 240px;
            background-color: #343a40;
            color: white;
            padding: 20px;
            position: fixed;
            top: 75px;
            height: calc(100% - 75px);
            overflow-y: auto;
            box-shadow: 2px 0 10px rgba(0, 0, 0, 0.2);
        }

        .sidebar h1 {
            text-align: center;
            font-size: 24px;
            margin-bottom: 20px;
            font-weight: bold;
        }

        .sidebar button {
            font-size: 16px;
            width: 100%;
            padding: 12px;
            margin: 8px 0;
            border: none;
            border-radius: 8px;
            background-color: #495057;
            color: white;
            cursor: pointer;
            transition: background-color 0.3s;
        }

        .sidebar button:hover {
            background-color: #5a6268;
        }

        .main-content {
            margin-left: 240px;
            padding: 25px;
            flex-grow: 1;
            background-color: #f8f9fa;
            margin-top: 75px;
            flex-direction: column;
            align-items: center;
        }

        .section {
            width: 100%;
            max-width: 800px;
            margin-bottom: 30px;
            text-align: center;
            /* background-color: white;  */
            border-radius: 10px;
            /* 圆角 */
            /* box-shadow: 0 2px 15px rgba(0, 0, 0, 0.1);  */
            padding: 20px;
        }

        .form-container {
            padding: 20px;
            margin: 0 auto;
        }

        .form-container input {
            width: calc(100% - 30px);
            padding: 15px;
            border: 1px solid #ccc;
            border-radius: 5px;
            margin: 10px 0;
            transition: border-color 0.3s;
        }

        .form-container input:focus {
            border-color: #007bff;
            outline: none;
        }

        .query-button {
            padding: 12px 20px;
            font-size: 16px;
            border: none;
            border-radius: 5px;
            background-color: #007bff;
            color: white;
            cursor: pointer;
            transition: background-color 0.3s, transform 0.3s;
        }

        .query-button:hover {
            background-color: #0056b3;
            transform: scale(1.05);
        }

        .search-results-container {
            padding: 20px;
            border-radius: 10px;
            width: 100%;
            margin-top: 20px;
        }

        .search-result-item {
            background-color: white;
            padding: 15px;
            margin: 10px 0;
            border-radius: 10px;
            box-shadow: 0 2px 10px rgba(0, 0, 0, 0.1);
            display: flex;
            justify-content: space-between;
            align-items: center;
        }

        .search-result-item div {
            flex: 1;
            margin-right: 20px;
        }

        .book-actions button {
            background-color: #007bff;
            color: white;
            border: none;
            padding: 10px 15px;
            border-radius: 5px;
            cursor: pointer;
            transition: background-color 0.3s;
        }

        .book-actions button:hover {
            background-color: #0056b3;
        }

        .book-list {
            list-style-type: none;
            padding: 0;
            width: 100%;
        }

        .book-list li {
            background-color: white;
            padding: 15px;
            margin: 10px 0;
            border-radius: 10px;
            box-shadow: 0 2px 10px rgba(0, 0, 0, 0.1);
            display: flex;
            justify-content: space-between;
            align-items: center;
        }

        .book-list button {
            background-color: #007bff;
            padding: 8px 12px;
            border: none;
            border-radius: 4px;
            color: white;
            cursor: pointer;
            transition: background-color 0.3s, transform 0.3s;
        }

        .book-list button:hover {
            background-color: #0056b3;
            transform: scale(1.05);
        }
    </style>
</head>

<body>
    <div class="topbar">
        <div>用户：<span id="username-display">未登录</span></div>
        <button onclick="logout()">退出</button>
    </div>
    <div class="sidebar">
        <h1>用户菜单</h1>
        <button onclick="showSection('search')">搜索书籍</button>
        <button onclick="showSection('history')">借阅历史</button>
        <button onclick="showSection('favorites')">我的收藏</button>
    </div>
    <div class="main-content">
        <div class="section" id="searchSection">
            <div class="form-container">
                <h1>搜索书籍</h1>
                <input type="text" id="searchInput" placeholder="搜索书名或作者">
                <button class="query-button" onclick="searchBooks()">查询</button>
            </div>
            <div id="searchResults" class="search-results-container"></div>
        </div>
        <div class="section" id="historySection" style="display:none;">
            <h1>借阅历史</h1>
            <ul class="book-list" id="borrowHistory"></ul>
        </div>
        <div class="section" id="favoritesSection" style="display:none;">
            <h1>我的收藏</h1>
            <ul class="book-list" id="favoritesList"></ul>
        </div>
    </div>

    <script>
        document.addEventListener("DOMContentLoaded", function () {
            updateUsernameDisplay();
        });

        function logout() {
            alert('已退出登录');
            localStorage.removeItem('userInfo');
            localStorage.removeItem('username');
            updateUsernameDisplay();
            window.location.href = 'login.html';
        }

        function updateUsernameDisplay() {
            const username = localStorage.getItem('username');
            const usernameDisplay = document.getElementById('username-display');
            if (username) {
                usernameDisplay.textContent = username;
            } else {
                usernameDisplay.textContent = '未登录';
                console.log('在 localStorage 中未找到用户名');
            }
        }

        function showSection(section) {
            const sections = document.querySelectorAll('.section');
            sections.forEach(s => s.style.display = 'none');
            document.getElementById(section + 'Section').style.display = 'block';

            if (section === 'favorites') {
                loadFavorites();
            } else if (section === 'history') {
                loadBorrowHistory();
            }
        }

        function searchBooks() {
    const keyword = document.getElementById('searchInput').value.trim();
    if (keyword === '') {
        alert('请输入搜索关键词');
        return;
    }

    // 移除特殊字符
    const sanitizedKeyword = keyword.replace(/[%_]/g, '');
    
    fetch('http://localhost:8080/api/books/search', {
        method: 'POST',
        headers: {
            'Content-Type': 'application/json' // 声明使用 JSON 格式
        },
        body: JSON.stringify({ keyword }) // 将关键词封装成 JSON 发送到后端
    })
    .then(response => {
        if (!response.ok) {
            throw new Error('网络响应不正常');
        }
        return response.json(); // 解析 JSON 数据
    })
    .then(data => {
        displaySearchResults(data); // 将结果显示在页面上
    })
    .catch(error => console.error('Error:', error)); // 处理异常情况
}

function displaySearchResults(books) {
    const searchResults = document.getElementById('searchResults');
    searchResults.innerHTML = ''; // 清空之前的结果

    // 检查返回的书籍列表是否为空
    if (books.length === 0) {
        searchResults.innerHTML = '<p>没有找到相关书籍</p>'; // 提示用户未找到书籍
    } else {
        books.forEach(book => {
            const listItem = document.createElement('div');
            listItem.className = 'search-result-item';
            listItem.innerHTML = `
                <div>
                    <strong>书名:</strong> ${book.title}<br>
                    <strong>作者:</strong> ${book.author}<br>
                    <strong>出版年份:</strong> ${book.yearPublished || '未知'}
                </div>
                <div class="book-actions">
                    <button onclick="borrowBook(${book.id})">借阅</button>
                    <button onclick="addToFavorites(${book.id})">添加到收藏</button>
                </div>
            `;
            searchResults.appendChild(listItem); // 将书籍信息添加到显示区域
        });
    }
}
        function getUserId() {
            const userInfo = JSON.parse(localStorage.getItem('userInfo'));
            return userInfo ? userInfo.id : null;
        }

        // 借阅书籍
function borrowBook(bookId) {
    const userId = getUserId();
    if (!userId) {
        alert('用户信息已失效，请重新登录');
        window.location.href = 'login.html';
        return;
    }

    fetch('http://localhost:8080/api/borrow-records/borrow', {
        method: 'POST',
        headers: {
            'Content-Type': 'application/json'
        },
        body: JSON.stringify({ userId: userId, bookId: bookId }) // 应该在请求体中传递用户和书籍的 ID
    })
    .then(response => {
        if (!response.ok) {
            throw new Error('网络响应不正常');
        }
        return response.json(); // 解析 JSON 数据
    })
    .then(data => {
        alert('您已成功借阅');
        loadBorrowHistory();
    })
    .catch(error => {
        console.error('Error:', error);
        alert('借阅书籍时发生错误，请稍后再试');
    });
}

        // 加载借阅历史
        function loadBorrowHistory() {
    const userId = getUserId();
    if (!userId) {
        alert('用户信息已失效，请重新登录');
        window.location.href = 'login.html';
        return;
    }

    // 更新 API 路径
    fetch(`http://localhost:8080/api/borrow-records/user/borrow-history/${userId}`)
        .then(response => {
            if (!response.ok) {
                throw new Error('未找到借阅记录');
            }
            return response.json();
        })
        .then(data => {
            displayBorrowHistory(data);
        })
        .catch(error => {
            console.error('Error:', error);
            alert('加载借阅历史时发生错误，请稍后再试');
        });
}

function displayBorrowHistory(borrowRecords) {
    const borrowHistory = document.getElementById('borrowHistory');
    borrowHistory.innerHTML = '';

    if (borrowRecords.length === 0) {
        borrowHistory.innerHTML = '<p>没有借阅记录</p>';
    } else {
        borrowRecords.forEach(record => {
            const listItem = document.createElement('li');
            const borrowDate = new Date(record.borrow_date || Date.now()); // 若 borrow_date 为 null, 使用当前时间
            const formattedDate = borrowDate.toLocaleDateString('zh-CN', {
                year: 'numeric',
                month: '2-digit',
                day: '2-digit'
            });

            listItem.textContent = `书名: ${record.title || '未知'} - 作者: ${record.author || '未知'} - 借阅日期: ${formattedDate} - 状态: ${record.is_returned ? '已还书' : '未还书'}`;

            const returnButton = document.createElement('button');
            returnButton.textContent = '还书';
            returnButton.onclick = function () {
                console.log('Attempting to return book with Record ID:', record.id);
                returnBook(record.id, returnButton);
            };

            if (record.is_returned) {
                returnButton.disabled = true;
                returnButton.textContent = '已还书';
            }

            listItem.appendChild(returnButton);
            borrowHistory.appendChild(listItem);
        });
    }
}

        function returnBook(borrowRecordId, returnButton) {
            const userId = getUserId();
            console.log('User ID:', userId);
            console.log('Record ID:', borrowRecordId);
            if (!userId || !borrowRecordId) {
                alert('无效的用户 ID 或借阅记录 ID');
                return;
            }

            console.log("Returning book with Record ID:", borrowRecordId, "for User ID:", userId);

            returnButton.disabled = true;
            returnButton.textContent = '处理中...';

            fetch('http://localhost:8080/user/return-book', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json'
                },
                body: JSON.stringify({ userId: userId, recordId: borrowRecordId })
            })
                .then(response => {
                    if (!response.ok) {
                        throw new Error('网络响应不正常');
                    }
                    return response.json();
                })
                .then(data => {
                    alert(data.message);
                    loadBorrowHistory();
                })
                .catch(error => {
                    console.error('Error:', error);
                    alert('还书时发生错误，请稍后再试');
                    returnButton.disabled = false;
                    returnButton.textContent = '还书';
                });
        }

        // 添加到收藏
        function addToFavorites(bookId) {
    const userId = getUserId();
    if (!userId) {
        alert('用户信息已失效，请重新登录');
        window.location.href = 'login.html';
        return;
    }

    fetch('http://localhost:8080/api/favorites/add', {
        method: 'POST',
        headers: {
            'Content-Type': 'application/json'
        },
        body: JSON.stringify({ userId: userId, bookId: bookId }) // 确保这两个值非空
    })
    .then(response => {
        if (!response.ok) {
            return response.text().then(text => {
                throw new Error('添加收藏时发生错误：' + text);
            });
        }
        return response.json();
    })
    .then(data => {
        alert('已成功添加到收藏！');
        loadFavorites(); // 重新加载收藏列表
    })
    .catch(error => {
        console.error('Error:', error);
        alert('添加收藏时发生错误，请稍后再试');
    });
}

        // 加载收藏书籍
        function loadFavorites() {
    const userId = getUserId();
    if (!userId) {
        alert('用户信息已失效，请重新登录');
        window.location.href = 'login.html';
        return;
    }

    fetch(`http://localhost:8080/api/favorites/${userId}`)
        .then(response => {
            if (!response.ok) {
                throw new Error('网络响应不正常');
            }
            return response.json();
        })
        .then(data => {
            displayFavorites(data);
        })
        .catch(error => console.error('Error:', error));
}

        // 显示收藏书籍
        function displayFavorites(favoriteBooks) {
    const favoritesList = document.getElementById('favoritesList');
    favoritesList.innerHTML = '';
    if (favoriteBooks.length === 0) {
        favoritesList.innerHTML = '<p>没有收藏的书籍</p>';
    } else {
        favoriteBooks.forEach(book => {
            const listItem = document.createElement('li');
            listItem.textContent = `书名: ${book.title} - 作者: ${book.author}`;

            const borrowButton = document.createElement('button');
            borrowButton.textContent = '借阅';
            borrowButton.onclick = function () {
                borrowFromFavorites(book.id);
            };

            const deleteButton = document.createElement('button');
            deleteButton.textContent = '删除收藏';
            deleteButton.onclick = function () {
                removeFromFavorites(book.id);
            };

            listItem.appendChild(borrowButton);
            listItem.appendChild(deleteButton);
            favoritesList.appendChild(listItem);
        });
    }
}

        // 从收藏中借阅书籍
function borrowFromFavorites(bookId) {
    const userId = getUserId();
    if (!userId) {
        alert('用户信息已失效，请重新登录');
        window.location.href = 'login.html';
        return;
    }

    // 检查用户的借阅历史
    fetch(`http://localhost:8080/user/borrow-history/${userId}`)
        .then(response => response.json())
        .then(borrowedRecords => {
            // 检查该书籍是否已经被借阅
            const isAlreadyBorrowed = borrowedRecords.some(record => record.book_id === bookId && !record.is_returned);
            if (isAlreadyBorrowed) {
                alert('您已借阅此书，必须先还书才能借阅！');
                return; // 不允许借阅
            }

            // 在借阅前检查用户的收藏
            fetch(`http://localhost:8080/user/favorites/${userId}`)
                .then(response => response.json())
                .then(favorites => {
                    const isFavorited = favorites.some(fav => fav.book_id === bookId);
                    if (!isFavorited) {
                        alert('这本书再您的收藏里了');
                        return; // 不允许借阅
                    }

                    // 如果没有借过，允许借阅
                    fetch('http://localhost:8080/user/borrow-from-favorite', {
                        method: 'POST',
                        headers: {
                            'Content-Type': 'application/json'
                        },
                        body: JSON.stringify({ userId: userId, bookId: bookId })
                    })
                    .then(response => {
                        if (!response.ok) {
                            throw new Error('网络响应不正常');
                        }
                        return response.json();
                    })
                    .then(data => {
                        if (data.error) {
                            alert(data.error);
                        } else {
                            alert(data.message);
                            loadFavorites();
                            loadBorrowHistory();
                        }
                    })
                    .catch(error => {
                        console.error('Error:', error);
                        alert('借阅书籍时发生错误，请稍后再试');
                    });
                })
                .catch(error => {
                    console.error('Error:', error);
                    alert('获取收藏记录时发生错误，请稍后再试');
                });
        })
        .catch(error => {
            console.error('Error:', error);
            alert('获取借阅记录时发生错误，请稍后再试');
        });
}
        // 从收藏中删除书籍
        function removeFromFavorites(bookId) {
            const userId = getUserId();
            if (!userId) {
                alert('用户信息已失效，请重新登录');
                window.location.href = 'login.html';
                return;
            }

            fetch('http://localhost:8080/api/favorites/remove', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json'
                },
                body: JSON.stringify({ userId: userId, bookId: bookId })
            })
                .then(response => response.json())
                .then(data => {
                    if (data.error) {
                        alert(data.error);
                    } else {
                        alert('您已成功从收藏中删除这本书');
                        loadFavorites();
                    }
                })
                .catch(error => console.error('Error:', error));
        }
    </script>
</body>

</html> package com.example.librarysystem.controller;

import com.example.librarysystem.entity.Favorite;
import com.example.librarysystem.service.FavoriteService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/api/favorites")
public class FavoriteController {
    @Autowired
    private FavoriteService favoriteService;

    @PostMapping("/add")
    public ResponseEntity<?> addFavorite(@RequestBody Favorite favorite) {
        try {
            // 确保 userId 和 bookId 都不为空
            if (favorite.getUserId() == null || favorite.getBookId() == null) {
                return new ResponseEntity<>("用户ID和书籍ID不能为空", HttpStatus.BAD_REQUEST);
            }
            Favorite savedFavorite = favoriteService.addFavorite(favorite);
            return new ResponseEntity<>(savedFavorite, HttpStatus.OK);
        } catch (Exception e) {
            System.err.println("添加收藏时出错: " + e.getMessage());
            return new ResponseEntity<>("添加收藏失败: " + e.getMessage(), HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<?> deleteFavorite(@PathVariable Long id) {
        favoriteService.deleteFavorite(id);
        return new ResponseEntity<>("Favorite deleted successfully", HttpStatus.OK);
    }

    @GetMapping("/{userId}")
    public ResponseEntity<List<Favorite>> getFavoritesByUserId(@PathVariable Long userId) {
        List<Favorite> favorites = favoriteService.getFavoritesByUserId(userId);
        return new ResponseEntity<>(favorites, HttpStatus.OK);
    }
}package com.example.librarysystem.controller;
//
import com.example.librarysystem.entity.BorrowRecord;
import com.example.librarysystem.mapper.BorrowRecordsMapper;
import com.example.librarysystem.service.BorrowRecordService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/api/borrow-records")
public class BorrowRecordController {
    @Autowired
    private BorrowRecordService borrowRecordService;
    @Autowired
    private BorrowRecordsMapper borrowRecordsMapper;

    @PostMapping("/borrow")
    public ResponseEntity<?> borrowBook(@RequestBody BorrowRecord borrowRecord) {
        try {
            BorrowRecord savedRecord = borrowRecordService.borrowBook(borrowRecord);
            return new ResponseEntity<>(savedRecord, HttpStatus.OK);
        } catch (Exception e) {
            return new ResponseEntity<>("借阅图书失败：" + e.getMessage(), HttpStatus.BAD_REQUEST);
        }
    }
    @GetMapping("/user/borrow-history/{userId}")
    public ResponseEntity<List<Map<String, Object>>> getBorrowHistoryByUserId(@PathVariable Long userId) {
        List<Map<String, Object>> records = borrowRecordsMapper.findBorrowHistoryByUserId(userId);
        return new ResponseEntity<>(records, HttpStatus.OK);
    }
    @GetMapping()
    public ResponseEntity<List<BorrowRecord>> getBorrowRecords() {
        List<BorrowRecord> records = borrowRecordService.getBorrowRecords();
        return new ResponseEntity<>(records, HttpStatus.OK);
    }

    @GetMapping("/{id}")
    public ResponseEntity<BorrowRecord> getBorrowRecordById(@PathVariable Long id) {
        BorrowRecord record = borrowRecordService.getBorrowRecordById(id);
        if (record!= null) {
            return new ResponseEntity<>(record, HttpStatus.OK);
        } else {
            return new ResponseEntity<>(HttpStatus.NOT_FOUND);
        }
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<?> deleteBorrowRecord(@PathVariable Long id) {
        borrowRecordService.deleteBorrowRecord(id);
        return new ResponseEntity<>("Borrow record deleted successfully", HttpStatus.OK);
    }

    @PostMapping("/edit")
    public ResponseEntity<?> editBorrowRecord(@RequestBody BorrowRecord borrowRecord) {
        BorrowRecord editedRecord = borrowRecordService.editBorrowRecord(borrowRecord);
        return new ResponseEntity<>(editedRecord, HttpStatus.OK);
    }
}package com.example.librarysystem.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import java.io.Serializable;

import lombok.Getter;
import lombok.Setter;

/**
 * <p>
 * 
 * </p>
 *
 * @author xxx
 * @since 2024-12-23
 */
@Getter
@Setter

public class Favorite implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    private Integer userId;

    private Integer bookId;


}
package com.example.librarysystem.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.time.LocalDateTime;

import lombok.Getter;
import lombok.Setter;

/**
 * <p>
 * 
 * </p>
 *
 * @author xxx
 * @since 2024-12-23
 */
@Getter
@Setter
@TableName("borrow_records")

public class BorrowRecord implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    private Integer userId;

    private Integer bookId;

    private LocalDateTime borrowDate;

    private String returnDate;

    private Boolean isReturned;

    private String status;


}
package com.example.librarysystem.service;

import com.example.librarysystem.entity.Favorite;

import com.example.librarysystem.mapper.FavoritesMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class FavoriteService {
    @Autowired
    private FavoritesMapper favoriteMapper;

    public Favorite addFavorite(Favorite favorite) {
        favoriteMapper.insert(favorite);
        return favorite;
    }

    public void deleteFavorite(Long id) {
        favoriteMapper.deleteById(id);
    }

    public List<Favorite> getFavoritesByUserId(Long userId) {
        return favoriteMapper.selectList(null);
    }
}package com.example.librarysystem.mapper;



import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.librarysystem.entity.Favorite;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author xxx
 * @since 2024-12-23
 */
@Mapper
public interface FavoritesMapper extends BaseMapper<Favorite> {
    @Insert("INSERT INTO favorites (user_id, book_id) VALUES (#{userId}, #{bookId})")
    void insertFavorite(Favorite favorite); // 确保这里的表名是 favorites 而不是 favorite
}                               
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('user', 'admin') NOT NULL
);
 
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(100) NOT NULL,
    year_published INT
);
 
CREATE TABLE borrow_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    book_id INT,
    borrow_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);
CREATE TABLE favorites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    book_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

ALTER TABLE books MODIFY COLUMN id INT AUTO_INCREMENT;
ALTER TABLE users MODIFY COLUMN id INT AUTO_INCREMENT;
ALTER TABLE borrow_records MODIFY COLUMN id INT AUTO_INCREMENT;
ALTER TABLE favorites MODIFY COLUMN id INT AUTO_INCREMENT;
SELECT * FROM borrow_records;

ALTER TABLE borrow_records ADD COLUMN return_date DATE;

SELECT br.id, u.username, b.title, b.author, br.borrow_date 
FROM borrow_records br 
JOIN users u ON br.user_id = u.id 
JOIN books b ON br.book_id = b.id 
ORDER BY br.borrow_date DESC;
SELECT id, title, author, year_published FROM books;
	SELECT 
			TABLE_NAME,
			COLUMN_NAME,
			CONSTRAINT_NAME
	FROM
			INFORMATION_SCHEMA.KEY_COLUMN_USAGE
	WHERE
			REFERENCED_TABLE_NAME = 'users'
					AND REFERENCED_COLUMN_NAME = 'id'
					AND TABLE_NAME = 'borrow_records';
					
					ALTER TABLE borrow_records
DROP FOREIGN KEY borrow_records_ibfk_1;

ALTER TABLE borrow_records
ADD CONSTRAINT borrow_records_user_id_foreign_key
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
SELECT * FROM borrow_records WHERE id = 1;
SELECT * FROM borrow_records WHERE user_id = 12;
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_NAME = 'users'
        AND REFERENCED_COLUMN_NAME = 'id'
        AND TABLE_NAME = 'favorites';
				
				ALTER TABLE favorites
DROP FOREIGN KEY favorites_ibfk_1;

ALTER TABLE favorites
ADD CONSTRAINT favorites_user_id_foreign_key
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE borrow_records ADD COLUMN IF NOT EXISTS return_date DATE;
ALTER TABLE borrow_records ADD COLUMN returned BOOLEAN DEFAULT FALSE;

ALTER TABLE borrow_records 
ADD COLUMN is_returned BOOLEAN DEFAULT FALSE;
ADD COLUMN is_returned BOOLEAN DEFAULT FALSE,
ALTER TABLE borrow_records 
ADD COLUMN return_date DATE NULL;               搜索显示出数据后，点击添加收藏报错         前端Failed to load resource: the server responded with a status of 500 ()
user.html:495  Error: Error: 添加收藏时发生错误：添加收藏失败: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: Table 'librarysystem.favorite' doesn't exist
### The error may exist in com/example/librarysystem/mapper/FavoritesMapper.java (best guess)
### The error may involve com.example.librarysystem.mapper.FavoritesMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO favorite  ( user_id, book_id )  VALUES  ( ?, ? )
### Cause: java.sql.SQLSyntaxErrorException: Table 'librarysystem.favorite' doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Table 'librarysystem.favorite' doesn't exist
    at user.html:485:23        后端      SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@23bcdb3e] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@763044153 wrapping com.mysql.cj.jdbc.ConnectionImpl@323b513b] will not be managed by Spring
==>  Preparing: INSERT INTO favorite ( user_id, book_id ) VALUES ( ?, ? )
==> Parameters: 16(Integer), 114(Integer)
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@23bcdb3e]
添加收藏时出错: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: Table 'librarysystem.favorite' doesn't exist
### The error may exist in com/example/librarysystem/mapper/FavoritesMapper.java (best guess)
### The error may involve com.example.librarysystem.mapper.FavoritesMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO favorite  ( user_id, book_id )  VALUES  ( ?, ? )
### Cause: java.sql.SQLSyntaxErrorException: Table 'librarysystem.favorite' doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Table 'librarysystem.favorite' doesn't exist