图书借阅系统
数据库表:
用户表:id,用户名,密码
图书表:id,图书名称,图书价格,出版社,作者,可借阅数量
借阅表:id,用户id,图书id,借阅时间,归还时间 ,时间使用datetime(java对应类型为Date)
主要功能:
1)添加图书
图书名不能相同
2)删除图书
如果删除的图书有人借阅,不能删除
3)搜索图书
根据书名模糊查询
4)用户登录
输入用户名和密码,如果登录成功,保存登录的用户的信息
如果登录失败,需要提示是用户名错误还是密码错误
5)用户注册
输入用户名、密码、二次确认密码
需要判断两次输入密码是否一致
注册时,用户名不能相同
6)已登录用户借阅图书
同一用户归还图书之前,不能再次借阅相同图书
需要判断可借阅数量是否足够
注意:需要判断是否登录
7)已登录用户查看自己所有的借阅信息
返回数据包括:图书id,图书名称,作者,价格,借阅时间,是否归还(显示 是/否)
根据借阅时间倒序显示记录
注意:需要判断是否登录
8)已登录用户查询未归还图书信息
返回数据包括:图书id,图书名称,作者,价格,借阅时间
根据借阅时间倒序显示记录
注意:需要判断是否登录
9)已登录用户还书
修改图书的可借阅数量
修改归还时间
注意:需要判断是否登录
在main方法中,选择相应功能进行操作
通过Scanner输入需要处理的数据
如果用户未登录,功能列表中不显示当前登录用户信息
功能列表显示如下:
******************************
图书借阅系统 当前登录用户:XXXX
******************************
1 添加图书
2 删除图书
3 根据书名搜索图书
4 用户注册
5 用户登录
6 查看已借阅图书信息
7 查看未归还图书信息
8 借书
9 还书
0 退出系统
******************************
请选择需要执行的功能:
本图书管理系统设置了三张表books,borrows,users
数据库如下:
链接:https://pan.baidu.com/s/1N6cnp2M-MtgWigK5WQBnYw?pwd=nglw
提取码:nglw
Java代码也可直接下载:
链接:https://pan.baidu.com/s/1XkKHMg9Grfnx8vluB0TQgg?pwd=xytr
提取码:xytr
Java代码部分由主函数和实现ROM的三个实体类组成。
主函数如下:
import java.sql.*;
import java.util.Scanner;public class system {public static void main(String[] args) {users user=null;boolean i=true;Scanner scanner=new Scanner(System.in);while (i){System.out.println("功能列表显示如下");System.out.println("******************");System.out.print("图书借阅系统 ");if (user!=null){System.out.println("当前登录用户"+user.getUsername());}else {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.归还书");System.out.println("0.退出");System.out.println("******************");System.out.println("请输入您的选择:");int i1 = scanner.nextInt();switch (i1){case 1:System.out.println("请输入书名:");String title1 = scanner.next();System.out.println("请输入价格:");double price1 = scanner.nextDouble();System.out.println("请输入出版社:");String publisher1 = scanner.next();System.out.println("请输入作者:");String author1 = scanner.next();addbook(title1,price1,publisher1,author1,1);break;case 2:System.out.println("请输入书的id号:");int id1 = scanner.nextInt();deletebook(id1);break;case 3:System.out.println("请输入书名:");String title2 = scanner.next();selectbook(title2);break;case 4:System.out.println("请输入用户名:");String username = scanner.next();System.out.println("请输入密码:");String password = scanner.next();System.out.println("请再次输入密码");String password1 = scanner.next();register(username,password,password1);break;case 5:if (user!=null){System.out.println("您已登录,请勿重复登录");}else {System.out.println("请输入用户名:");String username1 = scanner.next();System.out.println("请输入密码:");String password2 = scanner.next();user=login(username1,password2);}break;case 6:if (user!=null){System.out.println("请输入你的id");int id = scanner.nextInt();selectselfborrow(id);}else {System.out.println("您还未登录,请先登录");}break;case 7:if (user!=null){selectwhbooks();}else {System.out.println("您还未登录,请先登录");}break;case 8:if (user!=null){System.out.println("请输入您的id和需要的书的id");int id = scanner.nextInt();int id3 = scanner.nextInt();borrow(id,id3);}else {System.out.println("您还未登录,请先登录");}break;case 9:if (user!=null){System.out.println("请输入您的id和需要还书的id");int id = scanner.nextInt();int id3 = scanner.nextInt();returnbook(id,id3);}else {System.out.println("您还未登录,请先登录");}break;case 0:return;}}}public static void addbook(String title,double price,String publisher,String author,int num){Connection conn=null;PreparedStatement pps=null;try {Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC","root", "123456");String sql="insert into books(title, price, publisher, author, num) values (?, ?, ?, ?, ?)";pps = conn.prepareStatement(sql);pps.setString(1,title);pps.setDouble(2,price);pps.setString(3,publisher);pps.setString(4,author);pps.setInt(5,num);int i = pps.executeUpdate();if (i>0){System.out.println("数据插入成功!");}} catch (Exception e) {e.printStackTrace();}finally {try {pps.close();conn.close();} catch (Exception e) {e.printStackTrace();}}}//添加图书public static void deletebook(int id){Connection conn=null;PreparedStatement pps=null;try {Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC","root", "123456");String sql="select return_time from Borrows where book_id=?";pps = conn.prepareStatement(sql);pps.setInt(1,id);ResultSet rs = pps.executeQuery();if (rs.next()){java.sql.Date rt = rs.getDate("return_time");if (rt==null){System.out.println("该图书有人借阅,不能删除");}else {String sql3="delete from borrows where book_id=?";pps = conn.prepareStatement(sql3);pps.setInt(1,id);int i2 = pps.executeUpdate();String sql2="delete from books where id=?";pps = conn.prepareStatement(sql2);pps.setInt(1,id);int i = pps.executeUpdate();if (i>0 && i2>0){System.out.println("删除成功");}}}} catch (Exception e) {e.printStackTrace();}finally {try {pps.close();conn.close();} catch (Exception e) {e.printStackTrace();}}}//删除图书public static void selectbook(String title){Connection conn=null;PreparedStatement pps=null;try {Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC","root", "123456");String sql="select * from books where title like ?";pps = conn.prepareStatement(sql);pps.setString(1, "%"+title+"%");ResultSet rs = pps.executeQuery();if (rs.next()){int id = rs.getInt("id");String title1 = rs.getString("title");double decimal = rs.getDouble("price");String publisher = rs.getString("publisher");String author = rs.getString("author");int num = rs.getInt("num");books book = new books(id,title1,decimal,publisher,author,num);System.out.println("书的信息为"+book);}else {System.out.println("没有找到该图书");}} catch (Exception e) {e.printStackTrace();}finally {try {pps.close();conn.close();} catch (Exception e) {e.printStackTrace();}}}//搜索图书public static void register(String username,String password,String password1){Connection conn=null;PreparedStatement pps=null;PreparedStatement pps2=null;try {Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC", "root", "123456");pps = conn.prepareStatement("select id from users where username=?");pps.setString(1,username);ResultSet rs = pps.executeQuery();if (rs.next()){System.out.println("该用户名已存在");}else {if (password.equals(password1)){pps2 = conn.prepareStatement("insert into users(username, password) values (?, ?)");pps2.setString(1,username);pps2.setString(2,password);int i = pps2.executeUpdate();if (i>0){System.out.println("注册成功");}}}} catch (Exception e) {e.printStackTrace();}finally {try {pps2.close();pps.close();conn.close();} catch (Exception e) {e.printStackTrace();}}}//用户注册public static users login(String username,String password){Connection conn=null;PreparedStatement pps=null;try {Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC","root", "123456");String sql="select * from users where username=?";pps = conn.prepareStatement(sql);pps.setString(1,username);ResultSet rs = pps.executeQuery();if (rs.next()){String password1 = rs.getString("password");if (password.equals(password1)){System.out.println("登录成功");return new users(username,password);}else {System.out.println("密码错误");}}else {System.out.println("用户名不存在");}} catch (Exception e) {e.printStackTrace();}finally {try {pps.close();conn.close();} catch (Exception e) {e.printStackTrace();}}return null;
}//用户登录public static void borrow(int user_id,int book_id){Connection conn=null;PreparedStatement pps=null;ResultSet rs=null;ResultSet rs2=null;try {Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC","root", "123456");String sql="select book_id from Borrows where user_id=? and return_time is null";pps = conn.prepareStatement(sql);pps.setInt(1,user_id);rs = pps.executeQuery();while (rs.next()){int book_id1 = rs.getInt("book_id");if (book_id1==book_id){System.out.println("该用户已借过该书,不能再借书了");}else {String sql2="select num from books where id=?";pps = conn.prepareStatement(sql2);pps.setInt(1,book_id);rs2 = pps.executeQuery();if (rs2.next()){int num = rs2.getInt("num");if (num>0){String sql3="insert into borrows(user_id, book_id, borrow_time) values (?, ?, ?)";pps = conn.prepareStatement(sql3);pps.setInt(1,user_id);pps.setInt(2,book_id);pps.setDate(3,new Date(System.currentTimeMillis()));int i = pps.executeUpdate();if (i>0){String sql4="update books set num=num-1 where id=?";pps = conn.prepareStatement(sql4);pps.setInt(1,book_id);int i2 = pps.executeUpdate();if (i2>0){System.out.println("借书成功");}}else {System.out.println("借书失败");}}}}}} catch (Exception e) {e.printStackTrace();}finally {try {pps.close();conn.close();} catch (Exception e) {e.printStackTrace();}}}//借书public static void selectselfborrow(int user_id){Connection conn=null;PreparedStatement pps=null;try {Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC","root", "123456");String sql="select books.`id`,books.`title`,books.`publisher`,books.`price`,borrows.`borrow_time`,borrows.`return_time` from books,Borrows where books.`id`=borrows.`book_id` and borrows.`user_id`=?";pps = conn.prepareStatement(sql);pps.setInt(1,user_id);ResultSet rs = pps.executeQuery();while (rs.next()){int id = rs.getInt("id");String s = rs.getString("title");String s1 = rs.getString("publisher");double decimal = rs.getDouble("price");java.sql.Date borrowTime = rs.getDate("borrow_time");java.sql.Date returnTime = rs.getDate("return_time");if (returnTime==null){System.out.println(id+" "+s+" "+s1+" "+decimal+" "+borrowTime+" "+"是");}else {System.out.println(id+" "+s+" "+s1+" "+decimal+" "+borrowTime+" "+"否");}}} catch (Exception e) {e.printStackTrace();}}//查询自己借阅书籍public static void selectwhbooks(){Connection conn=null;PreparedStatement pps=null;try {Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC","root", "123456");String sql="select books.`id`,books.`title`,books.`publisher`,borrows.`borrow_time` from books,Borrows where books.`id`=borrows.`book_id` and borrows.`return_time` is null";pps = conn.prepareStatement(sql);ResultSet rs = pps.executeQuery();while (rs.next()){int id = rs.getInt("id");String title = rs.getString("title");String publisher = rs.getString("publisher");Date borrow_time = rs.getDate("borrow_time");System.out.println(id+" "+title+" "+publisher+" "+borrow_time);}} catch (Exception e) {e.printStackTrace();}finally {try {pps.close();conn.close();} catch (Exception e) {e.printStackTrace();}}}//查找没有归还的图书public static void returnbook(int user_id,int book_id){Connection conn=null;PreparedStatement pps=null;try {Class.forName("com.mysql.cj.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC","root", "123456");String sql="update borrows set return_time=? where book_id=? and user_id=?";pps = conn.prepareStatement(sql);pps.setDate(1,new Date(System.currentTimeMillis()));pps.setInt(2,book_id);pps.setInt(3,user_id);int i = pps.executeUpdate();if (i>0){String sql2="update books set num=num+1 where id=?";pps = conn.prepareStatement(sql2);pps.setInt(1,book_id);int i2 = pps.executeUpdate();if (i2>0){System.out.println("还书成功");}}} catch (Exception e) {e.printStackTrace();}finally {try {pps.close();conn.close();} catch (Exception e) {e.printStackTrace();}}}//还书}
实体类books:
public class books {private int id;private String title;private double decimal;private String publisher;private String author;private int num;public books() {}public books(int id, String title, double decimal, String publisher, String author, int num) {this.id = id;this.title = title;this.decimal = decimal;this.publisher = publisher;this.author = author;this.num = num;}@Overridepublic String toString() {return "books{" +"id=" + id +", title='" + title + '\'' +", decimal=" + decimal +", publisher='" + publisher + '\'' +", author='" + author + '\'' +", num=" + num +'}';}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}public double getDecimal() {return decimal;}public void setDecimal(double decimal) {this.decimal = decimal;}public String getPublisher() {return publisher;}public void setPublisher(String publisher) {this.publisher = publisher;}public String getAuthor() {return author;}public void setAuthor(String author) {this.author = author;}public int getNum() {return num;}public void setNum(int num) {this.num = num;}
}
实体类borrows:
import java.util.Date;public class borrows {private int id;private int user_id;private int book_id;private Date borrow_time;private Date return_time;public borrows() {}public borrows(int id, int user_id, int book_id, Date borrow_time, Date return_time) {this.id = id;this.user_id = user_id;this.book_id = book_id;this.borrow_time = borrow_time;this.return_time = return_time;}@Overridepublic String toString() {return "borrows{" +"id=" + id +", user_id=" + user_id +", book_id=" + book_id +", borrow_time=" + borrow_time +", return_time=" + return_time +'}';}public int getId() {return id;}public void setId(int id) {this.id = id;}public int getUser_id() {return user_id;}public void setUser_id(int user_id) {this.user_id = user_id;}public int getBook_id() {return book_id;}public void setBook_id(int book_id) {this.book_id = book_id;}public Date getBorrow_time() {return borrow_time;}public void setBorrow_time(Date borrow_time) {this.borrow_time = borrow_time;}public Date getReturn_time() {return return_time;}public void setReturn_time(Date return_time) {this.return_time = return_time;}
}
实体类users:
public class users {private int id;private String username;private String password;public users() {}public users(String username, String password) {this.username = username;this.password = password;}@Overridepublic String toString() {return "users{" +"id=" + id +", username='" + username + '\'' +", password='" + password + '\'' +'}';}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}
}