JDBC-图书管理系统
数据库设计
BOOK
CREATE TABLE `book` (
`book_id` int(11) NOT NULL AUTO_INCREMENT,
`book_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`book_kind` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`book_author` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`book_print` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`book_state` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY USING BTREE (`book_id`)
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
Boorrow
CREATE TABLE `borrow` (
`borrow_id` int(11) NOT NULL AUTO_INCREMENT,
`borrow_bookid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`borrow_readerid` int(11) NOT NULL,
`borrow_gettime` date NOT NULL,
`borrow_returntime` date NOT NULL,
`ya_money` int(11) NULL DEFAULT NULL,
PRIMARY KEY USING BTREE (`borrow_id`)
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
reader
DROP TABLE IF EXISTS `reader`;
CREATE TABLE `reader` (
`reader_id` int(11) NOT NULL AUTO_INCREMENT,
`reader_book_id` int(11) NOT NULL,
`reader_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`reader_sex` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`reader_tel` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`reader_department` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`reader_class` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY USING BTREE (`reader_id`)
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
user
CREATE TABLE `user` (
`user_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`user_keyword` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`user_role` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY USING BTREE (`user_id`)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
数据库系统登录界面
管理员界面
1:用户登录
UserDao
public List<UserInfo> login(String username, String pwd){
//创建一个集合，用来存放从数据库查询到的数据封装的对象
ArrayList<UserInfo> list = new ArrayList<>();
String sql = "select * from user where user_id=? and user_keyword=?";
try {
ps=con.prepareStatement(sql);
//给？赋值
ps.setString(1, username);
ps.setString(2, pwd);
//执行sql 得到一个resultSet
rs = ps.executeQuery();
//处理rs
while (rs.next()){
UserInfo user = new UserInfo(); //创建一个user对象用来接收数据库查的信息
String user_id = rs.getString("user_id");
String user_keyword = rs.getString("user_keyword");
String user_role = rs.getString("user_role");
user.setUser_id(user_id);
user.setUser_ketword(user_keyword);
user.setUser_role(user_role); //截止到这里，我们把数据库查询出来的数据，封装到了user中
list.add(user); //把封装并赋值完成的对象存入list集合中
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
2:修改密码
UserDao
public int updateKeyWord(String username, String pwd){
//创建一个集合，用来存放从数据库查询到的数据封装的对象
String sql = "UPDATE user set user_keyword=? where user_id=? ";
try {
ps=con.prepareStatement(sql);
//给？赋值
ps.setString(1, pwd);
ps.setString(2, username);
//执行sql 得到一个resultSet
num = ps.executeUpdate();
//处理rs
} catch (SQLException e) {
e.printStackTrace();
}
return num;
}
3:新增用户功能
UserDao
public int insertMethod(UserInfo userInfo){
//ArrayList<UserInfo> list = new ArrayList<>();
String sql = "insert into user values(?,?,?) ";
try {
ps=con.prepareStatement(sql);
//给？赋值
ps.setString(1, userInfo.getUser_id());
ps.setString(2, userInfo.getUser_ketword());
ps.setString(3, userInfo.getUser_role());
//执行sql 得到一个resultSet
num = ps.executeUpdate();
//处理rs
} catch (SQLException e) {
e.printStackTrace();
}
return num;
}
4:删除用户功能
UserDao
public int delectMethod(UserInfo user1) {
// ArrayList<UserInfo> list = new ArrayList<>();
String sql = "delete FROM user where user_id = ?";
try {
ps = con.prepareStatement(sql);
ps.setObject(1, user1.getUser_id());
num = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
return num;
}
5:查询用户(根据用户名模糊查询)
UserDao
public List<UserInfo> selectUser(String content) {
ArrayList<UserInfo> list = new ArrayList<>();
String sql = "select * from user where user_id like ? ";
try {
ps = con.prepareStatement(sql);
ps.setObject(1, "%"+content+"%");
rs = ps.executeQuery();
while (rs.next()){
UserInfo user = new UserInfo();
String user_id = rs.getString("user_id");
String user_keyword = rs.getString("user_keyword");
String user_role = rs.getString("user_role");
user.setUser_id(user_id);
user.setUser_ketword(user_keyword);
user.setUser_role(user_role);
list.add(user);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
UserControl
package com.ts.controller;
import com.ts.Service.UserService;
import com.ts.pojo.UserInfo;
import java.util.Iterator;
import java.util.List;
import java.util.Scanner;
/**
* @description:com.ts.controller_Word.java
* @author: 霏宇
* @time: 2022/6/27,17:22
*/
public class UserControl {
Scanner sc = new Scanner(System.in);
// 创建user逻辑层对象
UserService service = new UserService();
BookControl control2= new BookControl();
ReadControl control3=new ReadControl();
// 数据库登录界面
public void login() {
System.out.println("------数据库系统登录界面------");
System.out.println("请输入您的登录名");
UserInfo user = new UserInfo();
String user_id = sc.next(); //获取控制台输入的内容 用作user_id
user.setUser_id(user_id); //给user对象设置user_id
System.out.println("请输入您的密码");
String user_keyWord = sc.next();
user.setUser_ketword(user_keyWord);
System.out.println(user.toString());
//调用service层方法 获取权限
String quanxian = service.login(user.getUser_id(), user.getUser_ketword());
if(quanxian.equals("admin")){
indexGuan(user);
}else if(quanxian.equals("普通用户")){
indexYon(user);
}else {
System.out.println("输入的登录名与密码不匹配");
login();
}
}
//管理员界面
public void indexGuan(UserInfo user) {
System.out.println("---------管理员界面----------");
System.out.println("输入数字,选择相应的功能");
System.out.println("1:用户登录");
System.out.println("2:修改密码");
System.out.println("3:新增用户功能");
System.out.println("4:删除用户功能");
System.out.println("5:查询用户(根据用户名模糊查询)");
System.out.println("6:新增图书");
System.out.println("7:删除图书");
System.out.println("8:修改图书");
System.out.println("9:退出数据库系统界面");
int choose = sc.nextInt();
if (choose == 1) {
login();
}
if (choose == 2) {
updateKeyWord(user);
}
if (choose == 3) {
insertMethod(user);
}
if (choose == 4) {
delectMethod(user);
}
if (choose == 5) {
selectUser(user);
}
if (choose == 6) {
control2.insertMethod();
indexGuan(user);
}
if (choose == 7) {
control2.delectMethod();
indexGuan(user);
}
if (choose == 8) {
control2.updateBookInfo();
indexGuan(user);
}
if (choose == 9) {
System.out.println("------退出数据库系统界面---------");
}
}
/**
* @param user
* 用户界面
*/
public void indexYon(UserInfo user) {
System.out.println("---------用户界面----------");
System.out.println("输入数字,选择相应的功能");
System.out.println("1:新增读者信息,同时新增一个读者用户信息");
System.out.println("2:删除读者信息,同时删除读者的用户信息");
System.out.println("3:修改读者信息,同时修改读者的用户信息");
System.out.println("4:查询最近1周将要到期图书名，读者姓名，还书时间，借阅时间");
System.out.println("5:查询已过还书时间的图书名，读者姓名，还书时间，借阅时间");
System.out.println("6:用户借书功能");
System.out.println("7:用户还书功能");
System.out.println("8:退出登录");
int choose = sc.nextInt();
if (choose == 1) {
control3.addMethod();
indexYon(user);
}
if (choose == 2) {
control3.delectMethod();
indexYon(user);
}
if (choose == 3) {
control3.updateReaderInfoMethod();
indexYon(user);
}
if (choose == 4) {
control3.userSelectByWeek();
indexYon(user);
}
if (choose == 5) {
control3.selectReaderInfoOutReturnTime();
indexYon(user);
}
if (choose == 6) {
control3.borrowBook(user);
indexYon(user);
}
if (choose == 7) {
control3.returnBorrowBook(user);
indexYon(user);
}
if (choose == 8) {
System.out.println("------退出数据库系统界面---------");
}
}
public void updateKeyWord(UserInfo user) {
System.out.println("--------修改密码界面--------- ");
System.out.println("请输入您要修改的密码值:");
String adminJS = user.getUser_id();
String key2 = sc.next();
int num2 = service.updateKeyWord(adminJS, key2);
if (num2 == 0) {
System.out.println("该用户修改失败");
updateKeyWord(user);
} else {
System.out.println("该用户密码修改成功");
indexGuan(user);
}
}
public void insertMethod(UserInfo user) {
System.out.println("-------------管理员新增用户界面--------------");
UserInfo user1 = new UserInfo();
System.out.println("请输入新增用户id:");
String user1Id = sc.next();
System.out.println("请输入新增用户密码");
String user1Key = sc.next();
System.out.println("请输入新增用户类型");
String user1Role = sc.next();
user1.setUser_id(user1Id);
user1.setUser_ketword(user1Key);
user1.setUser_role(user1Role);
int num = service.insert(user1);
if (num > 0) {
System.out.println("添加用户成功！");
indexGuan(user);
} else {
indexGuan(user);
}
}
public void delectMethod(UserInfo user) {
System.out.println("-------------管理员删除用户界面--------------");
UserInfo user1 = new UserInfo();
System.out.println("请输入删除用户id:");
String user1Id = sc.next();
user1.setUser_id(user1Id);
int num = service.delect(user1);
if (num > 0) {
System.out.println("删除成功");
indexGuan(user);
} else {
indexGuan(user);
}
}
public void selectUser(UserInfo user1) {
System.out.println("-------------管理员查询用户界面--------------");
System.out.println("请输入你要查询的用户");
String content = sc.next();
List<UserInfo> list = service.selectUser(content);
if (list != null) {
Iterator<UserInfo> it = list.iterator();
while (it.hasNext()) {
UserInfo user = it.next();
System.out.println(user.getUser_role() +","+ user.getUser_id() + "号" + ",用户存在");
}
} else {
indexGuan(user1);
}
}
}
UserService
package com.ts.Service;
import com.ts.Dao.UserDao;
import com.ts.pojo.UserInfo;
import java.util.Iterator;
import java.util.List;
/**
* @description:com.ts.Service_Word.java
* @author: 霏宇
* @time: 2022/6/27,17:23
*/
public class UserService {
UserDao dao = new UserDao(); //创建dao层对象 可以调用里面的方法
//登录 service层登录方法 返回值选用的是String 用来接收权限信息
//我们根据service层login方法得到的权限，然后需要它在controller层被调用进行判断
public String login(String username, String pwd) {
String quanxian = null;
//调用dao层login方法 得到一个user的集合
List<UserInfo> list = dao.login(username, pwd);
//遍历集合 取出user对象
Iterator<UserInfo> it = list.iterator();
while (it.hasNext()) {
UserInfo user = it.next();
quanxian = user.getUser_role();
}
if (quanxian != null && quanxian.equals("admin")) {
return "admin";
} else if (quanxian != null && quanxian.equals("普通用户")) {
return "普通用户";
} else {
return "other";
}
}
public int updateKeyWord(String adminJS, String key2) {
return dao.updateKeyWord(adminJS, key2);
}
public int insert(UserInfo user1) {
return dao.insertMethod(user1);
}
public int delect(UserInfo user1) {
return dao.delectMethod(user1);
}
public List<UserInfo> selectUser(String content) {
return dao.selectUser(content);
}
// public int insert(UserInfo user1) {
//
// }
}
UserService
package com.ts.Service;
import com.ts.Dao.UserDao;
import com.ts.pojo.UserInfo;
import java.util.Iterator;
import java.util.List;
/**
* @description:com.ts.Service_Word.java
* @author: 霏宇
* @time: 2022/6/27,17:23
*/
public class UserService {
UserDao dao = new UserDao(); //创建dao层对象 可以调用里面的方法
//登录 service层登录方法 返回值选用的是String 用来接收权限信息
//我们根据service层login方法得到的权限，然后需要它在controller层被调用进行判断
public String login(String username, String pwd) {
String quanxian = null;
//调用dao层login方法 得到一个user的集合
List<UserInfo> list = dao.login(username, pwd);
//遍历集合 取出user对象
Iterator<UserInfo> it = list.iterator();
while (it.hasNext()) {
UserInfo user = it.next();
quanxian = user.getUser_role();
}
if (quanxian != null && quanxian.equals("admin")) {
return "admin";
} else if (quanxian != null && quanxian.equals("普通用户")) {
return "普通用户";
} else {
return "other";
}
}
public int updateKeyWord(String adminJS, String key2) {
return dao.updateKeyWord(adminJS, key2);
}
public int insert(UserInfo user1) {
return dao.insertMethod(user1);
}
public int delect(UserInfo user1) {
return dao.delectMethod(user1);
}
public List<UserInfo> selectUser(String content) {
return dao.selectUser(content);
}
6:新增图书
BookDao
public int insertMethod(BookInfo bookInfo){
String sql = "insert into book values(?,?,?,?,?,?) ";
try {
ps=con.prepareStatement(sql);
//给？赋值
ps.setObject(1, bookInfo.getBookId());
ps.setString(2, bookInfo.getBookName());
ps.setString(3, bookInfo.getBookKind());
ps.setString(4, bookInfo.getBookAuthor());
ps.setString(5, bookInfo.getBookPrint());
ps.setString(6, bookInfo.getBookState());
//执行sql 得到一个resultSet
num = ps.executeUpdate();
//处理rs
} catch (SQLException e) {
e.printStackTrace();
}
return num;
}
7:删除图书
BookDao
public int delectMethod(BookInfo bookInfo) {
String sql = "delete FROM book where book_id = ?";
try {
ps = con.prepareStatement(sql);
ps.setObject(1, bookInfo.getBookId());
num = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
return num;
}
8:修改图书
BookDao
public int updateKeyWord(String bookName, String bookState){
//创建一个集合，用来存放从数据库查询到的数据封装的对象
String sql = "UPDATE book set book_state=? where book_name=? ";
try {
ps=con.prepareStatement(sql);
//给？赋值
ps.setString(1, bookState);
ps.setObject(2, bookName);
//执行sql 得到一个resultSet
num = ps.executeUpdate();
//处理rs
} catch (SQLException e) {
e.printStackTrace();
}
return num;
}
BookControl
package com.ts.controller;
import com.ts.Service.BookService;
import com.ts.pojo.BookInfo;
import java.util.Scanner;
/**
* @description:com.ts.controller_Word.java
* @author: 霏宇
* @time: 2022/6/28,9:36
*/
public class BookControl {
BookService service = new BookService();
BookInfo book =new BookInfo();
Scanner sc = new Scanner(System.in);
public void insertMethod(){
System.out.println("-------------管理员图书新增界面--------------");
System.out.println("请输入新增图书id:");
long bookId =sc.nextLong();
System.out.println("请输入新增图书名称");
String book1Name = sc.next();
System.out.println("请输入新增图书种类");
String book1Kind = sc.next();
System.out.println("请输入新增图书作者");
String book1Author = sc.next();
System.out.println("请输入新增图书出版社");
String book1Print = sc.next();
System.out.println("请输入新增图书借出情况");
String book1State =sc.next();
book.setBookId(bookId);
book.setBookName(book1Name);
book.setBookKind(book1Kind);
book.setBookAuthor(book1Author);
book.setBookPrint(book1Print);
book.setBookState(book1State);
int num = service.insert(book);
if (num > 0) {
System.out.println("新增图书成功！");
} else {
System.out.println("新增图书失败！");
}
}
public void delectMethod() {
System.out.println("-------------管理员删除图书界面--------------");
BookInfo book = new BookInfo();
System.out.println("请输入删除图书id:");
int bookId = sc.nextInt();
book.setBookId(bookId);
int num = service.delect(book);
if (num > 0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败！");
}
}
public void updateBookInfo() {
System.out.println("--------修改图书界面--------- ");
System.out.println("请输入您要输入要修改图书名称");
String bookName =sc.next();
System.out.println("请输入您要修改借出情况");
String book1State =sc.next();
int num2 = service.updateBookInfo(bookName, book1State);
if (num2 == 0) {
System.out.println("该图书修改失败");
} else {
System.out.println("该图书修改成功");
}
}
}
BookService
package com.ts.Service;
import com.ts.Dao.BookDao;
import com.ts.pojo.BookInfo;
/**
* @description:com.ts.Service_Word.java
* @author: 霏宇
* @time: 2022/6/27,17:23
*/
public class BookService {
BookDao bookDao=new BookDao();
int num = 0;
public int insert(BookInfo bookInfo) {
return bookDao.insertMethod(bookInfo);
}
public int delect(BookInfo bookInfo) {
return bookDao.delectMethod(bookInfo);
}
public int updateBookInfo(String bookName, String book1State) {
return bookDao.updateKeyWord(bookName,book1State);
}
}
用户界面
1:新增读者信息,同时新增一个读者用户信息
ReaderDao
public int insertMethod(ReaderInfo readerInfo){
String sql = "insert into reader values(?,?,?,?,?,?,?) ";
try {
ps=con.prepareStatement(sql);
//给？赋值
ps.setObject(1, readerInfo.getReaderId());
ps.setObject(2, readerInfo.getReaderBookId());
ps.setString(3, readerInfo.getReaderName());
ps.setString(4, readerInfo.getReaderSex());
ps.setString(5, readerInfo.getReaderTel());
ps.setString(6, readerInfo.getReaderDepartment());
ps.setString(7, readerInfo.getReaderClass());
//执行sql 得到一个resultSet
num = ps.executeUpdate();
//处理rs
} catch (SQLException e) {
e.printStackTrace();
}
return num;
}
2:删除读者信息,同时删除读者的用户信息
ReaderDao
public int delectMethod(ReaderInfo readerInfoo) {
String sql = "delete FROM reader where reader_id = ?";
try {
ps = con.prepareStatement(sql);
ps.setObject(1, readerInfoo.getReaderId());
num = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
return num;
}
3:修改读者信息,同时修改读者的用户信息
ReaderDao
public int updateKeyWord(Long readerId, Long readerBookId ,String readerTel ){
//创建一个集合，用来存放从数据库查询到的数据封装的对象
String sql = "UPDATE reader set reader_book_id=? ,reader_tel=? where reader_id=? ";
try {
ps=con.prepareStatement(sql);
//给？赋值
ps.setObject(1, readerBookId);
ps.setString(2, readerTel);
ps.setObject(3, readerId);
//执行sql 得到一个resultSet
num = ps.executeUpdate();
//处理rs
} catch (SQLException e) {
e.printStackTrace();
}
return num;
}
4:查询最近1周将要到期图书名，读者姓名，还书时间，借阅时间
ReaderDao
public List<UserSelectWeek> userSelectByWeek() {
ArrayList<UserSelectWeek> list = new ArrayList<>();
String sql = "select distinct reader.reader_name, book.book_name,borrow.borrow_gettime,borrow_returntime from borrow\n" +
"\n" +
"inner join reader on borrow.borrow_readerid = reader.reader_id\n" +
"\n" +
"inner join book on reader.reader_id = book.book_id\n" +
"\n" +
"where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(borrow_returntime); ";
try {
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()){
UserSelectWeek userSelectWeek = new UserSelectWeek(); //创建一个user对象用来接收数据库查的信息
String bookName = rs.getString("book_name");
String readerName = rs.getString("reader_name");
String borrowTime = rs.getString("borrow_gettime");
String returnTime = rs.getString("borrow_returntime");
userSelectWeek.setBookName(bookName);
userSelectWeek.setReaderName(readerName);
userSelectWeek.setBorrowTime(borrowTime);
userSelectWeek.setReturnTime(returnTime);
list.add(userSelectWeek); //把封装并赋值完成的对象存入list集合中
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
5:查询已过还书时间的图书名，读者姓名，还书时间，借阅时间
ReaderDao
public List<UserSelectWeek> userSelectByWeek() {
ArrayList<UserSelectWeek> list = new ArrayList<>();
String sql = "select distinct reader.reader_name, book.book_name,borrow.borrow_gettime,borrow_returntime from borrow\n" +
"\n" +
"inner join reader on borrow.borrow_readerid = reader.reader_id\n" +
"\n" +
"inner join book on reader.reader_id = book.book_id\n" +
"\n" +
"where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(borrow_returntime); ";
try {
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()){
UserSelectWeek userSelectWeek = new UserSelectWeek(); //创建一个user对象用来接收数据库查的信息
String bookName = rs.getString("book_name");
String readerName = rs.getString("reader_name");
String borrowTime = rs.getString("borrow_gettime");
String returnTime = rs.getString("borrow_returntime");
userSelectWeek.setBookName(bookName);
userSelectWeek.setReaderName(readerName);
userSelectWeek.setBorrowTime(borrowTime);
userSelectWeek.setReturnTime(returnTime);
list.add(userSelectWeek); //把封装并赋值完成的对象存入list集合中
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
查询图书
ReaderDao
public List<BookInfo> selectUser(String content) {
ArrayList<BookInfo> list = new ArrayList<>();
String sql = "SELECT *\n" +
"FROM book \n" +
"where book_name=?;";
try {
ps = con.prepareStatement(sql);
ps.setObject(1, content);
rs = ps.executeQuery();
while (rs.next()){
BookInfo bookInfo = new BookInfo(); //创建一个user对象用来接收数据库查的信息
long bookId=rs.getLong("book_id");
String bookName = rs.getString("book_name");
String bookState = rs.getString("book_state");
bookInfo.setBookId(bookId);
bookInfo.setBookName(bookName);
bookInfo.setBookState(bookState); //截止到这里，我们把数据库查询出来的数据，封装到了user中
list.add(bookInfo); //把封装并赋值完成的对象存入list集合中
System.out.println(133);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
6:用户借书功能
ReaderDao
public int updateUser(String user,Long bookId ){
//创建一个集合，用来存放从数据库查询到的数据封装的对象
ArrayList<ReaderInfo> list = new ArrayList<>();
String sql = "insert into borrow (borrow_bookid,borrow_readerid,borrow_gettime,borrow_returntime) " +
"values(?,?,CURDATE(),(DATE_SUB(CURDATE(), INTERVAL 7 DAY)));";
try {
ps=con.prepareStatement(sql);
//给？赋值
ps.setObject(1, bookId);
ps.setObject(2, user);
//执行sql 得到一个resultSet
num = ps.executeUpdate();
//处理rs
} catch (SQLException e) {
e.printStackTrace();
}
return num;
}
7:用户还书功能
ReaderDao
public int returnBorrowBook(Long borrowId ){
//创建一个集合，用来存放从数据库查询到的数据封装的对象
String sql = "delete FROM borrow where borrow_bookid = ?";
try {
ps = con.prepareStatement(sql);
ps.setObject(1,borrowId);
num = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
return num;
}
ReadService
package com.ts.Service;
import com.ts.Dao.ReaderDao;
import com.ts.pojo.BookInfo;
import com.ts.pojo.ReaderInfo;
import com.ts.pojo.UserSelectWeek;
import java.util.List;
/**
* @description:com.ts.Service_Word.java
* @author: 霏宇
* @time: 2022/6/27,17:23
*/
public class ReadService {
ReaderDao readerDao=new ReaderDao();
int num = 0;
public int insert(ReaderInfo readerInfo) {
return readerDao.insertMethod(readerInfo);
}
public int delect(ReaderInfo readerInfo) {
return readerDao.delectMethod(readerInfo);
}
public int updateBookInfo(Long readerId, Long readerBookId ,String readerTel) {
return readerDao.updateKeyWord(readerId,readerBookId,readerTel);
}
public List<UserSelectWeek> selectUser() {
return readerDao.userSelectByWeek();
}
public List<UserSelectWeek> selectReaderInfoOutReturnTime() {
return readerDao.selectReaderInfoOutReturnTime();
}
public List<BookInfo> borrowBook(String book) {
return readerDao.selectUser(book);
}
public int updateUser(String user, long bookId ) {
return readerDao.updateUser(user,bookId);
}
public int returnBorrowBook(Long borrowId) {
return readerDao.returnBorrowBook(borrowId);
}
}
ReadControl
package com.ts.controller;
import com.ts.Service.BookService;
import com.ts.Service.ReadService;
import com.ts.pojo.BookInfo;
import com.ts.pojo.ReaderInfo;
import com.ts.pojo.UserInfo;
import com.ts.pojo.UserSelectWeek;
import java.util.Iterator;
import java.util.List;
import java.util.Scanner;
/**
* @description:com.ts.controller_Word.java
* @author: 霏宇
* @time: 2022/6/28,9:36
*/
public class ReadControl {
ReadService service = new ReadService();
ReaderInfo read =new ReaderInfo();
BookService service2 =new BookService();
Scanner sc = new Scanner(System.in);
public void addMethod(){
System.out.println("-------------读者信息新增界面--------------");
System.out.println("请输入新增读者id:");
long readId =sc.nextLong();
System.out.println("请输入新增读者借书id:");
long bookId =sc.nextLong();
System.out.println("请输入新增读者名称");
String read1Name = sc.next();
System.out.println("请输入新增读者性别");
String readSex = sc.next();
System.out.println("请输入新增读者电话");
String readTel = sc.next();
System.out.println("请输入新增读者院系");
String readDepartment = sc.next();
System.out.println("请输入新增读者班级");
String readClass =sc.next();
read.setReaderId(readId);
read.setReaderBookId(bookId);
read.setReaderName(read1Name);
read.setReaderSex(readSex);
read.setReaderTel(readTel);
read.setReaderDepartment(readDepartment);
read.setReaderClass(readClass);
int num = service.insert(read);
if (num > 0) {
System.out.println("新增读者成功！");
} else {
System.out.println("新增读者失败！");
}
}
public void delectMethod() {
System.out.println("-------------删除读者界面--------------");
ReaderInfo reader = new ReaderInfo();
System.out.println("请输入删除图书id:");
int renderId = sc.nextInt();
reader .setReaderId(renderId);
int num = service.delect(reader );
if (num > 0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败！");
}
}
public void updateReaderInfoMethod() {
System.out.println("--------修改读者界面--------- ");
System.out.println("请输入您要输入要修改读者id");
long readerId =sc.nextLong();
System.out.println("请输入您要输入借书id");
long readerBookId =sc.nextLong();
System.out.println("请输入您要修改读者电话号");
String readerTel =sc.next();
int num2 = service.updateBookInfo(readerId ,readerBookId,readerTel);
if (num2 == 0) {
System.out.println("读者信息修改失败");
} else {
System.out.println("读者信息修改成功");
}
}
public void userSelectByWeek() {
System.out.println("-------------查询最近1周将要到期图书--------------");
List<UserSelectWeek> list = service.selectUser();
if (list != null) {
Iterator<UserSelectWeek> it = list.iterator();
while (it.hasNext()) {
UserSelectWeek userSelectWeek = it.next();
System.out.println(userSelectWeek.toString());
}
} else {
System.out.println("查询失败！");
}
}
public void selectReaderInfoOutReturnTime() {
System.out.println("-------------查询已过还书时间的用户--------------");
List<UserSelectWeek> list = service.selectReaderInfoOutReturnTime();
if (list != null) {
Iterator<UserSelectWeek> it = list.iterator();
while (it.hasNext()) {
UserSelectWeek userSelectWeek = it.next();
System.out.println(userSelectWeek.toString());
}
} else {
System.out.println("查询失败！");
}
}
public void borrowBook(UserInfo user) {
System.out.println("-------用户借书界面------- ");
System.out.println("请输入您要查询的图书的名称:");
String book = sc.next();
List<BookInfo> list = service.borrowBook(book);
if (list != null) {
Iterator<BookInfo> it = list.iterator();
while (it.hasNext()) {
BookInfo bookInfo = it.next();
System.out.println("图书id："+bookInfo.getBookId()+"\n"+"图书名称："+bookInfo.getBookName()+"\n"+"借出情况："+bookInfo.getBookState());
System.out.println("-------请输入借入图书-------");
String book2 = sc.next();
System.out.println("请输入您要借图书情况");
String book1State =sc.next();
int num2 = service2.updateBookInfo(book2, book1State);
if (num2 == 0) {
System.out.println("该图书查询失败");
} else {
System.out.println("该图书查询成功");
}
int num3=service.updateUser(user.getUser_id(),bookInfo.getBookId());
if (num3 == 0) {
System.out.println("该图书借出失败");
} else {
System.out.println("该图书借出成功");
}
}
} else {
System.out.println("查询失败！");
}
}
public void returnBorrowBook(UserInfo user) {
System.out.println("-------用户借书界面------- ");
System.out.println("请输入您要查询的图书的名称:");
String book = sc.next();
List<BookInfo> list = service.borrowBook(book);
if (list != null) {
Iterator<BookInfo> it = list.iterator();
while (it.hasNext()) {
BookInfo bookInfo = it.next();
System.out.println("图书id："+bookInfo.getBookId()+"\n"+"图书名称："+bookInfo.getBookName()+"\n"+"借出情况："+bookInfo.getBookState());
System.out.println("-------请输入借入图书-------");
String book2 = sc.next();
System.out.println("请输入您要还图书情况");
String book1State =sc.next();
int num2 = service2.updateBookInfo(book2, book1State);
if (num2 == 0) {
System.out.println("该图书查询失败");
} else {
System.out.println("该图书查询成功");
}
System.out.println(user.getUser_id());
int num3=service.returnBorrowBook(bookInfo.getBookId());
if (num3 == 0) {
System.out.println("该图书已还失败");
} else {
System.out.println("该图书已还成功");
}
}
} else {
System.out.println("查询失败！");
}
}
}
版权声明：本文为qq_53886038原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。