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.HashMap;
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);
    System.out.println("Borrow history records: " + records);
    return new ResponseEntity<>(records, HttpStatus.OK);
}
@GetMapping()
public ResponseEntity<List<BorrowRecord>> getBorrowRecords() {
    List<BorrowRecord> records = borrowRecordService.getBorrowRecords();
    return new ResponseEntity<>(records, HttpStatus.OK);
}
@PostMapping("/return/{id}")
public ResponseEntity<String> returnBook(@PathVariable Long id) {
    try {
        borrowRecordService.returnBook(id);
        return new ResponseEntity<>("还书成功", HttpStatus.OK); // 直接返回字符串
    } catch (Exception e) {
        return new ResponseEntity<>("还书失败: " + e.getMessage(), HttpStatus.INTERNAL_SERVER_ERROR);
    }
}
@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.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import java.io.Serializable;

import lombok.Getter;
import lombok.Setter;

/**

@author xxx
@since 2024-12-23
*/
@Getter
@Setter
public class Books implements Serializable {


private static final long serialVersionUID = 1L;

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

private String title;

private String author;

@TableField(value = "year_published")
private Integer yearPublished;

public Integer getYear_published(){
    return yearPublished;
}


public void setYear_published(Integer yearPublished){
    this.yearPublished = yearPublished;
}
}
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;

/**

@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.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;

import java.io.Serializable;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
@TableName("favorites")
public class Favorite implements Serializable {
private static final long serialVersionUID = 1L;


@TableId(value = "id", type = IdType.AUTO)
private Long id;
private Long userId;
private Long bookId;
}package com.example.librarysystem.mapper;

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

/**

Mapper 接口
@author xxx
@since 2024-12-23
*/
@Mapper
public interface BooksMapper extends BaseMapper {

}
package com.example.librarysystem.mapper;

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

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

/**

Mapper 接口
@author xxx
@since 2024-12-23
*/
@Mapper
public interface BorrowRecordsMapper extends BaseMapper {
@Select("SELECT br.id, u.username, b.title, b.author, br.borrow_date, br.return_date, br.is_returned " +
"FROM borrow_records br " +
"JOIN books b ON br.book_id = b.id " +
"JOIN users u ON br.user_id = u.id " +
"WHERE br.user_id = #{userId}")
List<Map<String, Object>> findBorrowHistoryByUserId(Long userId);
}
package com.example.librarysystem.service;
//
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.example.librarysystem.entity.BorrowRecord;
import com.example.librarysystem.mapper.BorrowRecordsMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.util.List;

@Service
public class BorrowRecordService {
@Autowired
private BorrowRecordsMapper borrowRecordMapper;


public BorrowRecord borrowBook(BorrowRecord borrowRecord) {
    if (borrowRecord.getBorrowDate() == null) {
        borrowRecord.setBorrowDate(LocalDateTime.now()); // 设置当前时间为借阅时间
    }
    borrowRecordMapper.insert(borrowRecord);
    return borrowRecord;
}
public List<BorrowRecord> getBorrowRecordsByUserId(Long userId) {
    return borrowRecordMapper.selectList(
            Wrappers.<BorrowRecord>lambdaQuery().eq(BorrowRecord::getUserId, userId)
    );
}
public void returnBook(Long recordId) {
    BorrowRecord record = borrowRecordMapper.selectById(recordId);
    if (record == null) {
        throw new RuntimeException("未找到借阅记录");
    }

    record.setIsReturned(true);
    record.setReturnDate(LocalDateTime.now().toString()); // 或者可以根据需要设定返回日期格式
    borrowRecordMapper.updateById(record);
}
public List<BorrowRecord> getBorrowRecords() {
    return borrowRecordMapper.selectList(null);
}

public BorrowRecord getBorrowRecordById(Long id) {
    return borrowRecordMapper.selectById(id);
}

public void deleteBorrowRecord(Long id) {
    borrowRecordMapper.deleteById(id);
}

public BorrowRecord editBorrowRecord(BorrowRecord borrowRecord) {

    borrowRecordMapper.updateById(borrowRecord);
    return borrowRecord;
}
} package com.example.librarysystem.service;


import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.example.librarysystem.entity.Books;
import com.example.librarysystem.mapper.BooksMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class BookService {

    @Autowired
    private BooksMapper bookMapper;

    public Books addBook(Books books) {
        try {
            // 可以添加日志打印出版年份的值，便于排查问题
            System.out.println("准备插入图书的出版年份: " + books.getYearPublished());
            bookMapper.insert(books);
            return books;
        } catch (Exception e) {
            throw new RuntimeException("图书插入数据库失败", e);
        }
    }

    public void deleteBook(Long id) {
        int rowsAffected = bookMapper.deleteById(id);
        if (rowsAffected == 0) {
            throw new RuntimeException("删除图书失败，可能该图书不存在或删除操作出现问题");
        }
    }
    public void deleteBookByTitle(String title) {
        Books book = bookMapper.selectOne(Wrappers.<Books>lambdaQuery().eq(Books::getTitle, title));
        if (book == null) {
            throw new RuntimeException("要删除的图书不存在");
        }
        int rowsAffected = bookMapper.deleteById(book.getId());
        if (rowsAffected == 0) {
            throw new RuntimeException("删除图书失败，可能删除操作出现问题");
        }
    }
    public List<Books> getBooks() {
        List<Books> booksList = bookMapper.selectList(null);
        for (Books book : booksList) {
            // 只在出版年份为 null 时设置默认值为 0，避免覆盖正确的出版年份
            if (book.getYearPublished() == null) {
                book.setYearPublished(0);
            }
        }
        return booksList;
    }
    public Books getBookById(Long id) {
        return bookMapper.selectById(id);
    }
    public Books editBook(Books books) {
        if (books.getId() == null) {
            throw new RuntimeException("要修改的图书 id 不能为空");
        }
        // 检查图书是否存在
        Books existingBook = bookMapper.selectById(books.getId());
        if (existingBook == null) {
            throw new RuntimeException("要修改的图书不存在");
        }
        bookMapper.updateById(books);
        return books;
    }
    // 新增根据书名修改图书的方法
    public void editBookByTitle(String oldTitle, Books newBookInfo) {

        Books existingBook = bookMapper.selectOne(Wrappers.<Books>lambdaQuery().eq(Books::getTitle, oldTitle));
        if (existingBook == null) {
            throw new RuntimeException("要修改的图书不存在");
        }
        // 将找到的图书的信息更新为新的信息
        existingBook.setTitle(newBookInfo.getTitle());
        existingBook.setAuthor(newBookInfo.getAuthor());
        existingBook.setYearPublished(newBookInfo.getYearPublished());
        bookMapper.updateById(existingBook);
    }
}<!DOCTYPE html>
管理员：
退出
管理员菜单
增加图书 删除图书 修改图书 查询图书 用户管理 图书管理 借阅记录
欢迎进入管理系统

`;

        const selectAllCheckbox = document.getElementById('selectAllBooks');
        selectAllCheckbox.addEventListener('change', function () {
            const checkboxes = document.querySelectorAll('.bookCheckbox');
            checkboxes.forEach(checkbox => {
                checkbox.checked = this.checked;
            });
        });
        const bookCheckboxes = document.querySelectorAll('.bookCheckbox');
        bookCheckboxes.forEach(checkbox => {
            checkbox.addEventListener('change', function () {
                const allChecked = Array.from(bookCheckboxes).every(cb => cb.checked);
                selectAllCheckbox.checked = allChecked;
            });
        });
    }
    function deleteSelectedBooks() {
        const checkboxes = document.querySelectorAll('.bookCheckbox:checked');
        const bookIds = Array.from(checkboxes).map(checkbox => checkbox.value);

        if (bookIds.length === 0) {
            alert('没有选中任何书籍。');
            return;
        }

        if (confirm('确定要删除选中的书籍吗？此操作不可撤销！')) {
            bookIds.forEach(id => {
                deleteBook(id);
            });
        }
    }


    function loadUsers() {
        return fetch('http://localhost:8080/api/users/all')
            .then(response => {
                if (!response.ok) {
                    throw new Error('网络响应不正常，状态码：' + response.status);
                }
                return response.json();
            })
            .then(users => {
                displayUsers(users);
                return users;
            })
            .catch(error => {
                console.error('Error loading users:', error);
                alert('加载用户时出错：' + error.message);
                // 可以考虑添加重试逻辑，比如延迟一段时间后重试
                setTimeout(() => {
                    loadUsers();
                }, 2000);
                return [];
            });
    }

    function displayUsers(users) {
        const formContainer = document.getElementById('formContainer');
        formContainer.innerHTML = `
    <h2 style="text-align: center;">用户管理</h2>
    <div class="user-list-container" style="display: flex; flex-direction: column; align-items: center;">
        <ul style="list-style-type: none; padding: 0;">
            ${users.map(user => `
                <li style="margin: 10px 0; display: flex; justify-content: space-between; align-items: center;">
                    <span style="flex-grow: 1; margin-right: 10px;">${user.username}</span>
                    <div class="book-actions">
                        <button onclick="editUserForm('${user.id}')" ${user.role === 'admin' ? 'disabled' : ''}>编辑</button>
                        <button onclick="deleteUser('${user.id}', '${user.username}')" ${user.role === 'admin' ? 'disabled' : ''}>删除</button>
                    </div>
                </li>
            `).join('')}
        </ul>
    </div>
`;
    }
    function loadBorrowRecords() {
fetch('http://localhost:8080/api/borrow-records/') // 假设16是当前用户ID
    .then(response => {
        if (!response.ok) {
            throw new Error('网络响应不正常');
        }
        return response.json();
    })
    .then(records => {
        console.log(records); // 调试输出
        displayBorrowRecords(records); // 显示获取的记录
    })
    .catch(error => {
        alert('加载借阅记录时出错：' + error.message);
    });
}
function displayBorrowRecords(records) {
console.log(records); // 查看 records 的内容
const borrowRecordsList = document.getElementById('borrowRecordsList');
borrowRecordsList.innerHTML = '';


if (records.length === 0) {
    borrowRecordsList.innerHTML = '<p>无借阅记录。</p>';
} else {
    const table = document.createElement('table');
    table.innerHTML = `
        <tr>
            <th>用户名</th>
            <th>书名</th>
            <th>作者</th>
            <th>借阅日期</th>
            <th>还书日期</th>
            <th>状态</th>
            <th>操作</th>
        </tr>
    `;

    records.forEach(record => {
        console.log(record); // 输出单个记录查看内容
        const borrowDate = new Date(record.borrowDate);
        const returnDate = record.returnDate ? new Date(record.returnDate) : '未还';
        const formattedBorrowDate = borrowDate.toLocaleDateString('zh-CN');
        const formattedReturnDate = returnDate instanceof Date ? returnDate.toLocaleDateString('zh-CN') : '未还';
        const row = document.createElement('tr');
        row.innerHTML = `
            <td>${record.id || '未知'}</td>
            <td>${record.userId || '未知'}</td>
            <td>${record.bookId || '未知'}</td>
            <td>${formattedBorrowDate}</td>
            <td>${formattedReturnDate}</td>
            <td>${record.isReturned ? '已还' : '未还'}</td>
            <td style="text-align: center;">
                <button onclick="deleteBorrowRecordConfirm(${record.id})">删除</button>
            </td>
        `;
        table.appendChild(row);
    });

    borrowRecordsList.appendChild(table);
}
}


    function deleteUser(id) {
        if (confirm(`确定要删除用户ID为${id}的用户吗？此操作不可撤销！`)) {
            fetch(`http://localhost:8080/api/users/${id}`, {
                method: 'DELETE',
                headers: { 'Content-Type': 'application/json' }
            })
                .then(response => {
                    if (response.status === 200) {
                        return response.text();
                    } else if (response.status === 404) {
                        throw new Error('用户不存在，无法删除');
                    } else {
                        throw new Error('删除用户时出现未知错误，状态码：' + response.status);
                    }
                })
                .then(data => {
                    alert('用户删除成功');
                    loadUsers(); // 重新加载用户列表
                })
                .catch(error => {
                    console.error('Error deleting user:', error);
                    alert('删除用户时出现错误：' + error.message);
                });
        }
    }
    function editUserForm(id) {
        fetch(`http://localhost:8080/api/users/${id}`)
            .then(response => {
                if (!response.ok) {
                    throw new Error('获取用户信息失败，状态码：' + response.status);
                }
                return response.json();
            })
            .then(user => {
                console.log('获取到的用户信息:', user);
                const formContainer = document.getElementById('formContainer');
                formContainer.innerHTML = `
                <form id="editUserForm">
                    <input type="hidden" value="${id}" name="id">
                    <input type="text" placeholder="用户名" value="${user.username}" name="username">
                    <input type="password" placeholder="旧密码" name="oldPassword" required>
                    <input type="password" placeholder="新密码" name="newPassword">
                    <select name="role">
                        <option value="user" ${user.role === 'user' ? 'selected' : ''}>用户</option>
                        <option value="admin" ${user.role === 'admin' ? 'selected' : ''}>管理员</option>
                    </select>
                    <button type="button" onclick="editUser(${id})">保存</button>
                    <button type="button" onclick="cancelEditUser()">取消</button>
                </form>
            `;
            })
            .catch(error => {
                console.error('编辑用户信息获取出错：', error);
                alert('获取用户信息失败，请稍后重试：' + error.message);
            });
    }
    function editUser(id) {
        const form = document.querySelector(`#editUserForm`);
        if (!form || !(form instanceof HTMLFormElement)) {
            return;
        }
        const formData = new FormData(form);
        // 处理可能存在格式问题的数据，比如去除密码字段前后空格
        formData.set('username', formData.get('username').trim());
        formData.set('oldPassword', formData.get('oldPassword').trim());
        formData.set('newPassword', formData.get('newPassword').trim());
        const data = Object.fromEntries(formData.entries());

        // 发送POST请求到/api/users/edit
        fetch('http://localhost:8080/api/users/edit', {
            method: 'POST',
            headers: { 'Content-Type': 'application/json' },
            body: JSON.stringify(data)
        })
            .then(response => {
                if (!response.ok) {
                    // 处理错误响应
                    return response.json().then(err => {
                        alert('编辑用户失败，原因：' + (err.message || '未知错误'));
                    });
                }
                return response.json(); // 假设成功响应也是JSON格式
            })
            .then(data => {
                // 处理成功响应
                alert(data.message || '用户信息更新成功');
                loadUsers(); // 重新加载用户列表
            })
            .catch(error => {
                console.error('Error editing user:', error);
                alert('编辑用户信息时出错，请稍后重试：' + error.message);
            });
    }
    function editBookForm(bookId) {
        fetch(`http://localhost:8080/api/books/${bookId}`)
            .then(response => {
                if (!response.ok) {
                    throw new Error('未能获取图书信息');
                }
                return response.json();
            })
            .then(book => {
                const formContainer = document.getElementById('formContainer');
                formContainer.innerHTML = `
            <h2>修改图书</h2>
            <input type="hidden" value="${book.id}" id="bookId">
            <input type="text" placeholder="书名" id="newTitle" value="${book.title}">
            <input type="text" placeholder="作者" id="newAuthor" value="${book.author}">
            <input type="number" placeholder="出版年份" id="newYearPublished" value="${book.yearPublished}">
            <button onclick="editBook()">保存</button>
            <button onclick="cancelEditBook()">取消</button>
        `;
            })
            .catch(error => {
                alert('获取图书信息失败，请稍后重试：' + error.message);
            });
    }

    function deleteBook(bookId) {
        if (!bookId) {
            alert('无效的图书ID');
            return;
        }
        fetch(`http://localhost:8080/api/books/${bookId}`, {
            method: 'DELETE',
            headers: {
                'Content-Type': 'application/json',
                'user-type': 'admin' // 加上相应头，确认请求权限
            }
        })
            .then(response => {
                if (!response.ok) {
                    throw new Error('网络响应不正常');
                }
                return response.text();
            })
            .then(data => {
                alert('图书删除成功');
                loadBooks();  // 重新加载图书列表
            })
            .catch(error => {
                alert('删除图书时出错：' + error.message);
            });
    }

    function editBook() {
        const bookId = document.getElementById('bookId').value;
        let newTitle = document.getElementById('newTitle').value;
        let newAuthor = document.getElementById('newAuthor').value;
        let newYearPublished = document.getElementById('newYearPublished').value;

        if (newTitle === '' || newAuthor === '' || newYearPublished === '') {
            alert('请填写完整信息');
            return;
        }

        const book = {
            id: parseInt(bookId),
            title: newTitle,
            author: newAuthor,
            year_published: parseInt(newYearPublished) // 确保使用合适的字段名称
        };

        fetch('http://localhost:8080/api/books/edit', {
            method: 'POST',
            headers: {
                'Content-Type': 'application/json',
                'user-type': 'admin' // 请确保权限设置
            },
            body: JSON.stringify(book)
        })
            .then(response => {
                if (!response.ok) {
                    throw new Error('网络响应不正常');
                }
                return response.text();
            })
            .then(data => {
                alert('图书修改成功');
                loadBooks(); // 刷新书籍列表
            })
            .catch(error => {
                alert('修改图书时出错：' + error.message);
            });
    }
    function editBookByTitle() {
        const oldTitle = document.getElementById('oldTitle').value;
        const newTitle = document.getElementById('newTitle').value;
        const newAuthor = document.getElementById('newAuthor').value;
        const newYearPublished = document.getElementById('newYearPublished').value;

        if (!oldTitle || !newTitle || !newAuthor || newYearPublished === '') {
            alert('请填写完整信息');
            return;
        }

        fetch('http://localhost:8080/api/books/editByTitle', {
            method: 'POST',
            headers: {
                'Content-Type': 'application/json'
            },
            body: JSON.stringify({
                oldTitle: oldTitle,
                newTitle: newTitle,
                newAuthor: newAuthor,
                newYearPublished: newYearPublished
            })
        })
            .then(response => {
                if (!response.ok) {
                    throw new Error('网络响应不正常: ' + response.statusText);
                }
                return response.text();
            })
            .then(data => {
                console.log('Book edited:', data);
                alert('图书修改成功');
                showForm('dataManagement');
            })
            .catch(error => {
                alert('修改图书时出错：' + error.message);
            });
    }
    function deleteBorrowRecordConfirm(recordId) {
        if (confirm(`确定要删除借阅记录ID为${recordId}的记录吗？此操作不可撤销！`)) {
            deleteBorrowRecord(recordId);
        }
    }

    function deleteBorrowRecord(recordId) {
        fetch(`http://localhost:8080/api/borrow-records/${recordId}`, {
            method: 'DELETE',
            headers: {
                'Content-Type': 'application/json'
            }
        })
            .then(response => {
                if (!response.ok) {
                    throw new Error('网络响应不正常');
                }
                return response.text();
            })
            .then(data => {
                alert('借阅记录删除成功');
                loadBorrowRecords(); // 重新加载借阅记录
            })
            .catch(error => {
                console.error('Error deleting borrow record:', error);
                alert('删除借阅记录时出错：' + error.message);
            });
    }

    // 获取当前管理员信息
    function fetchCurrentAdmin() {
        fetch('http://localhost:8080/admin/current')
            .then(response => {
                if (!response.ok) {
                    throw new Error('网络响应不正常');
                }
                return response.json();
            })
            .then(data => {
                console.log(data);

                document.getElementById('adminName').textContent = `管理员：${data.username}`;
            })
            .catch(error => {
                console.error('获取管理员名错误:', error);
                document.getElementById('adminName').textContent = '管理员：未知'; // 出错时处理
            });
    }
</script>
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;

SELECT br.id, u.username, b.title, b.author, br.borrow_date, br.return_date, br.is_returned
FROM borrow_records br
JOIN books b ON br.book_id = b.id
JOIN users u ON br.user_id = u.id
WHERE br.user_id = 16; 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;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**

Mapper 接口
@author xxx
@since 2024-12-23
*/
@Mapper
public interface FavoritesMapper extends BaseMapper {
@Select("SELECT id, user_id, book_id FROM favorites WHERE (user_id = #{userId})")
List getFavoritesByUserId(Long userId);
} 帮我把书名和作者数据正确的返回到页面，而不是返回他的id 63 16 118 2024/12/25 未还 未还