问题描述
编写一个简单的图书管理子系统。图书馆中需要存储书名,编号,作者、出版社,图书类型、存放位置、同名图书的存放位置等信息。需要存储对应的学生或教师的基本信息,可以用学号和工号来借阅图书。
基本要求
程序应提供的基本管理功能有:
1)添加:即增加同名书的记录到图书馆。
2)修改:修改图书除编号以外的信息。
3)删除:删除已经过时的图书。
4)查询:可根据书名查找图书的相关信息,若找到显示其基本信息。
6)借阅:可完成教师或学生借阅图书,一次可以借多本书,并可存储借阅信息。
7)借阅信息查询:通过学号或工号可以查询借阅情况
7)归还:可完成教师或学生归还图书,一次可以还多本书,并可存储归还信息
8)借阅信息查询:通过学号或工号可以查询归还信息情况
9)排序:可以根据条目的某个项对所有条目进行排序,如姓名,或出版社等
连接MySQL数据库的类
package com.bit.Database;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @className Mysql
* @Description TODO
* @Author zhangqianqian
* @Date 2020/1/8 15:00
* @Version 1.0
**/
public class Mysql {
private final static String URL = "jdbc:mysql://localhost/library";
private final static String USER = "root";
private final static String PASSWORD = "12345";
public static DataSource getDataSource() {
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setURL(URL);
dataSource.setUser(USER);
dataSource.setPassword(PASSWORD);
return dataSource;
}
public static int judge(int no) {//判断是否是用户
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
connection = getDataSource().getConnection();
String sql = "select *from user where no=?";
pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, no);
rs = pstmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) {
pstmt.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs!=null)
return 1;
return 0;
}
}
接口,所有功能类实现的接口
public interface IOperation {
Scanner scanner =new Scanner(System.in);
public void work();
}
添加图书类
public class AddBook extends Mysql implements IOperation{//添加图书
public void work() {
System.out.println("添加书籍");
System.out.println("请输入图书的名字:");
String name=scanner.next();
System.out.println("请输入图书的编号");
int bno=scanner.nextInt();
System.out.println("请输入图书的作者");
String author=scanner.next();
System.out.println("请输入图书的出版社");
String publish=scanner.next();
System.out.println("请输入图书的类型");
String type=scanner.next();
System.out.println("请输入图书的位置");
String location=scanner.next();
System.out.println("请输入同名图书的位置");
String namelocation=scanner.next();
Book book=new Book(name,bno,author,publish,type,location,namelocation);
Connection connection = null;
PreparedStatement pstmt=null;
try{
connection = getDataSource().getConnection();
String sql = "insert into book values(?,?,?,?,?,?,?,?)";
pstmt = connection.prepareStatement(sql);
pstmt.setString(1, book.getName());
pstmt.setInt(2, book.getBno());
pstmt.setString(3, book.getAuthor());
pstmt.setString(4, book.getPublish());
pstmt.setString(5, book.getType());
pstmt.setString(6, book.getLocation());
pstmt.setString(7, book.getNameLocation());
pstmt.setBoolean(8, book.isBorrowed());
pstmt.execute();
} catch(SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) {
pstmt.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
System.out.println("添加成功");
}
}
借阅图书信息类
public class BorrowMessage extends Mysql implements IOperation {
@Override
public void work() {
System.out.println("借阅书籍信息查询");
System.out.println("请输入要查询借阅人的编号");
int id=scanner.nextInt();
Connection connection = null;
PreparedStatement pstmt=null;
ResultSet rs=null;
Message m=new Message();
try {
connection=getDataSource().getConnection();
String sql="select *from borrow,book where borrow.bno=book.bno and no=?";
pstmt=connection.prepareStatement(sql);
pstmt.setInt(1, id);
rs=pstmt.executeQuery();
while(rs.next()){
m.setNo(rs.getInt("no"));
m.setName(rs.getString("name"));
m.setBno(rs.getInt("bno"));
m.setBorrowTime(rs.getString("borrowTime"));
m.setReturnTime(rs.getString("returnTime"));
System.out.println(m);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) {
pstmt.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
借阅图书类
public class BrrowBook extends Mysql implements IOperation {//借阅图书
@Override
public void work() {
System.out.println("借阅书籍");
System.out.println("请输入要借阅书籍的编号");
int bno=scanner.nextInt();
System.out.println("请输入您的id");
int id=scanner.nextInt();
System.out.println("请输入您的借阅时间");
String borrowTime=scanner.next();
System.out.println("请输入您的还书时间");
String returnTime=scanner.next();
Connection connection = null;
PreparedStatement pstmt=null;
ResultSet rs=null;
Borrow book=new Borrow(id,bno,borrowTime,returnTime);
try {
connection=getDataSource().getConnection();
String sql1="select isBorrowed from book where bno=?";
pstmt=connection.prepareStatement(sql1);
pstmt.setInt(1,bno);
rs=pstmt.executeQuery();
boolean flag=false;
if(rs.next()){
flag=rs.getBoolean("isBorrowed");
}
if(!flag){
String sql2 = "update Book set isBorrowed=true where bno=?";
pstmt = connection.prepareStatement(sql2);
pstmt.setInt(1, bno);
pstmt.execute();
String sql3 = "insert into borrow values(?,?,?,?)";
pstmt = connection.prepareStatement(sql3);
pstmt.setInt(1, book.getBno());
pstmt.setInt(2, book.getNo());
pstmt.setString(3, book.getBorrowTime());
pstmt.setString(4, book.getReturnTime());
pstmt.execute();
System.out.println("借阅成功!");
}else {
System.out.println("该书已被借出");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null){
pstmt.close();
}
if (connection != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
删除图书类
public class DelBook extends Mysql implements IOperation {//删除图书
@Override
public void work() {
System.out.println("删除书籍");
System.out.println("请输入要删除图书的名字:");
String name=scanner.next();
Connection connection = null;
PreparedStatement pstmt=null;
try{
connection = getDataSource().getConnection();
String sql = "delete from book where name=?";
pstmt = connection.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.execute();
} catch(SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) {
pstmt.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
System.out.println("删除成功"); }
}
显示所有图书类
public class DisplayBook extends Mysql implements IOperation{//显示所有图书
@Override
public void work() {
Connection connection = null;
PreparedStatement pstmt=null;
ResultSet rs=null;
Book book=new Book();
try {
connection=getDataSource().getConnection();
String sql="select *from book";
pstmt=connection.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()){
book.setName(rs.getString("name"));
book.setBno(rs.getInt("bno"));
book.setAuthor(rs.getString("author"));
book.setPublish(rs.getString("publish"));
book.setType(rs.getString("type"));
book.setLocation(rs.getString("location"));
book.setNameLocation(rs.getString("nameLocation"));
book.setBorrowed(rs.getBoolean("isBorrowed"));
System.out.println(book);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) {
pstmt.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
退出程序
public class Exit implements IOperation{
public void work() {
System.out.println("byebye!");
System.exit(0);
}
}
查找书籍类
public class FindBook extends Mysql implements IOperation{//查找图书
@Override
public void work() {
System.out.println("查找书籍");
System.out.println("请输入要查找图书的编号:");
int bno=scanner.nextInt();
Book book=new Book();
Connection connection = null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try{
connection = getDataSource().getConnection();
String sql = "select *from book where bno=?";
pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, bno);
rs=pstmt.executeQuery();
while(rs.next()){
book.setName(rs.getString("name"));
book.setBno(rs.getInt("bno"));
book.setAuthor(rs.getString("author"));
book.setPublish(rs.getString("publish"));
book.setType(rs.getString("type"));
book.setLocation(rs.getString("location"));
book.setNameLocation(rs.getString("nameLocation"));
book.setBorrowed(rs.getBoolean("isBorrowed"));
}
} catch(SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) {
pstmt.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
if(book!=null){
System.out.println(book);
System.out.println("查找成功");
}else
System.out.println("没有此书籍");
}
}
还书类
public class ReturnBook extends Mysql implements IOperation {
@Override
public void work() {
System.out.println("归还书籍");
System.out.println("请输入要归还书籍的编号");
int bno=scanner.nextInt();
Connection connection = null;
PreparedStatement pstmt=null;
try {
connection=getDataSource().getConnection();
String sql="update Book set isBorrowed=false where bno=?";
pstmt=connection.prepareStatement(sql);
pstmt.setInt(1,bno);
pstmt.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null){
pstmt.close();
}
if (connection != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
System.out.println("归还成功!");
}
}
排序类
public class SortBook extends Mysql implements IOperation {
@Override
public void work() {
Connection connection = null;
PreparedStatement pstmt=null;
Book book=new Book();
ResultSet rs=null;
int count=0;
try {
connection=getDataSource().getConnection();
String sql="select * from book order by bno desc";
pstmt=connection.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()){
book.setName(rs.getString("name"));
book.setBno(rs.getInt("bno"));
book.setAuthor(rs.getString("author"));
book.setPublish(rs.getString("publish"));
book.setType(rs.getString("type"));
book.setLocation(rs.getString("location"));
book.setNameLocation(rs.getString("nameLocation"));
book.setBorrowed(rs.getBoolean("isBorrowed"));
System.out.println(book);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null){
pstmt.close();
}
if (connection != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
System.out.println("排序成功!");
}
}
修改图书信息类
public class UpdataBook extends Mysql implements IOperation {
@Override
public void work() {
System.out.println("修改书籍");
System.out.println("请输入要修改书籍的编号");
int bno=scanner.nextInt();
Connection connection = null;
PreparedStatement pstmt=null;
try {
connection=getDataSource().getConnection();
String sql="update Book set name=?,author=?,publish=?,type=?,location=? ,nameLocation=? where bno=?";
pstmt=connection.prepareStatement(sql);
System.out.println("请输入要修改的书籍的名称:");
String name=scanner.next();
pstmt.setString(1,name);
System.out.println("请输入要修改的书籍的作者:");
String author=scanner.next();
pstmt.setString(2,author);
System.out.println("请输入要修改的书籍的出版社:");
String publish=scanner.next();
pstmt.setString(3,publish);
System.out.println("请输入要修改的书籍的类型:");
String type=scanner.next();
pstmt.setString(4,type);
System.out.println("请输入要修改的书籍的位置:");
String location=scanner.next();
pstmt.setString(5,location);
System.out.println("请输入要修改的书籍的同名图书位置:");
String nameLocation=scanner.next();
pstmt.setString(6,nameLocation);
pstmt.setInt(7,bno);
pstmt.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null){
pstmt.close();
}
if (connection != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
System.out.println("修改成功!");
}
}
完整代码见:https://github.com/zqq234/test/commit/80a64e7a24eef2a19af33596b272e834fe8a5472