package com.example.librarysystem.controller; // import…
- User
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.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.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 java.io.Serializable;
import lombok.Getter; import lombok.Setter;
/**
- @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.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 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.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.example.librarysystem.entity.Favorite; import org.apache.ibatis.annotations.Mapper;
/**
- Mapper 接口
- @author xxx
- @since 2024-12-23 */ @Mapper public interface FavoritesMapper extends BaseMapper {
} 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); }}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_recordsDROP 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 favoritesDROP 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;
.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><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>POST http://localhost:8080/api/favorites/add 500 (Internal Server Error) addToFavorites @ user.html:475 onclick @ user.html:1 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 (匿名) @ user.html:495 Promise.catch addToFavorites @ user.html:494 onclick @ user.html:1qlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@e2ffb12] was not registered for synchronization because synchronization is not active JDBC Connection [HikariProxyConnection@15205543 wrapping com.mysql.cj.jdbc.ConnectionImpl@7dd633f9] 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@e2ffb12] 添加收藏时出错:
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
- Assistant
从你提供的错误信息来看,问题出在数据库查询语句中,特别是在执行插入
favorites表时,出现了以下错误:### Error updating database. Cause: java.sql.SQLSyntaxErrorException: Table 'librarysystem.favorite' doesn't exist这是由于你的数据库在执行
INSERT INTO favorite (user_id, book_id) VALUES (?, ?)语句时,找不到名为favorite的表。在你的 SQL 创建表语句中,表名是favorites而不是favorite。解决方案
-
检查 Mapper 类:首先,检查
FavoritesMapper类中 CRUD 操作的 SQL 语句,确保所有的 SQL 查询都使用favorites表而不是favorite表。修改
FavoritesMapper类中的相关 SQL 语句,如下所示:@Insert("INSERT INTO favorites (user_id, book_id) VALUES (#{userId}, #{bookId})") void insertFavorite(Favorite favorite);如果你未显式定义 SQL 语句,确保 MyBatis 的实体类与数据库表的映射是正确的。
-
检查 SQL 创建表语句:确保之前创建表时,使用的是
favorites而不是favorite。你所提供的创建表 SQL 语句是正确的。 -
更新表结构:如果你之前有一个错误的创建表操作,确保你删除了错误的表。你可以在数据库中执行以下命令来删除表(如果存在):
DROP TABLE IF EXISTS favorite; -
重新启动应用程序:在进行代码更改后,重新启动 Spring Boot 应用程序以确保所有更改生效。
-
执行数据库迁移(如果适用):对于更复杂的应用,可以考虑使用数据库迁移工具(如 Liquibase 或 Flyway),确保数据库结构与代码能够兼容。
通过上述检查和调整,应该可以解决你所遇到的数据库表不存在的错误。如果问题仍然存在,请查看日志输出以获取更多上下文信息。
-
- User
添加到收藏报错user.html:475
POST http://localhost:8080/api/favorites/add 500 (Internal Server Error) addToFavorites @ user.html:475 onclick @ user.html:1 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 (匿名) @ user.html:495 Promise.catch addToFavorites @ user.html:494 onclick @ user.html:1SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@60adc786] was not registered for synchronization because synchronization is not active JDBC Connection [HikariProxyConnection@158163666 wrapping com.mysql.cj.jdbc.ConnectionImpl@1ce2d9b4] 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@60adc786] 添加收藏时出错:
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 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.entity; import com.baomidou.mybatisplus.annotation.IdType; 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 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.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;
/**
- Mapper 接口
- @author xxx
- @since 2024-12-23 */ @Mapper public interface FavoritesMapper extends BaseMapper { @Insert("INSERT INTO favorites (user_id, book_id) VALUES (#{userId}, #{bookId})") void insertFavorite(Favorite favorite); } 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); }}
.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><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> - Assistant
根据你提供的错误信息,出现了以下问题:
Error updating database. Cause: java.sql.SQLSyntaxErrorException: Table 'librarysystem.favorite' doesn't exist这个错误表明在数据库中执行插入
favorite表的操作时找不到该表。问题的主要原因如下:-
表名错误:在你的 SQL 中,应该是
favorites而不是favorite。也就是说,你的 SQL 查询使用了错误的表名。 -
Mapper 接口和 SQL 语句:从你提供的代码中,
FavoritesMapper类的插入方法是使用了注解定义 SQL 语句,这部分是正确的。如果这个方法没有被调用,可能导致 SQL 执行无效。
解决步骤
-
修正 SQL表名: 确保在 FavoritesMapper.java 文件中,使用的 SQL 语句确实是对
favorites表的操作,不应为favorite。你的 Mapper 应该如下所示:@Mapper public interface FavoritesMapper extends BaseMapper<Favorite> { @Insert("INSERT INTO favorites (user_id, book_id) VALUES (#{userId}, #{bookId})") void insertFavorite(Favorite favorite); // 确保这里的表名是 favorites 而不是 favorite } -
确保 Favorites 表已创建:
- 确保在数据库中你已经创建了
favorites表。你可以执行以下 SQL 命令来检查该表是否真的存在:
SHOW TABLES; -- 这会列出所有表- 如果表不存在,可以使用以下命令创建该表:
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) ); - 确保在数据库中你已经创建了
-
使用正确的数据库配置: 确保你的应用连接到正确的数据库。检查
application.properties或application.yml中的配置,确保数据库名称和连接信息设置正确。 -
查看数据库连接:
- 确认数据库连接没有错误,检查是否有其他相关的错误日志。数据库异常日志有助于进一步调试问题。
-
清理及重启应用: 在做出修改后,重启你的 Spring Boot 应用以确保所有更改生效。如果之前有错误的 SQL 操作,可能需要重启应用来清除状态。
结论
通过确保应用中的 SQL 语句表名与数据库中的一致,清除未正确创建的表名,并确保数据库连接正常,这个问题应该能够得到解决。如果问题仍然存在,请继续监控日志输出,以获得更多的背景信息,便于进一步排查。
-