根据下列描述开发图书管理系统
小王是一个爱书之人,家里各类书籍已过千册,而平时又时常有朋友外借,因此需要一个个人图书管理系统。该系统应该能够将书籍的基本信息按计算机类、非计算机类分别建档,实现按书名、作者、类别、出版社等关键字的组合查询功能。在使用该系统录入新书籍时系统会自动按规则生成书号,可以修改信息,但一经创建就不允许删除。该系统还应该能够对书籍的外借情况进行记录,可对外借情况列表打印。另外,还希望能够对书籍的购买金额、册数按特定时间周期进行统计
(上图为项目结构)
Service层——UI
该类用于展示界面,展示ui界面设计
package Service;
public class UI {
public static void showcase() {
System.out.println("============欢迎进入图书管理系统===============");
System.out.println("王先生你好 , 请选择您所需要进行的操作的编号\n" +
"1. 增加图书\n" +
"2. 检索书籍信息\n" +
"3. 删除(丢失)图书\n" +
"4.修改图书信息\n" +
"5. 借阅图书\n" +
"6. 归还图书\n" +
"7. 显示借阅信息\n" +
"8. 查看书籍丢失信息\n" +
"9. 退出系统\n");
System.out.println("==========================================");
}
}
主函数(MyMain)
主要用于对各类函数进行调用
package Controler;
import Dao.DaoForBorrow;
import Dao.DaoForClib;
import Dao.DaoForLostlist;
import Dao.DaoForOlib;
import Model.oBook;
import Service.UI;
import java.util.Scanner;
public class myMain {
DaoForOlib dfo = new DaoForOlib();
DaoForClib dfc = new DaoForClib();
public static void main(String[] args) {
while(true){
UI.showcase();
Scanner input = new Scanner(System.in);
int flag = 0;
System.out.println("你所要进行的操作的编号是:");
flag = input.nextInt();
if (flag == 1) {
// 增加图书
DaoForClib dfc = new DaoForClib();
DaoForOlib dfo = new DaoForOlib();
System.out.println("请选择图书类型,计算机类请按1,非计算机类请按2:");
int choise3=input.nextInt();
if (choise3 == 1){
dfc.addBook();
}else if (choise3 == 2){
dfo.addBook();
}else{
System.out.println("输入错误");
break;
}
}
else if (flag == 2) {
// 检索书籍信息
DaoForOlib dfo2 = new DaoForOlib();
DaoForClib dfc2 = new DaoForClib();
System.out.println("查询所有图书请按1 ; 查询单本图书请按2 :");
Scanner sc = new Scanner(System.in);
int choise4 = sc.nextInt();
if (choise4 ==1 ){
dfo2.retriveAll();
dfc2.retriveComputerAll();
}
else{
System.out.println("请输入书籍类型的序号:1.非计算机类;2.计算机类");
int num = sc.nextInt();
if (num ==1){
System.out.println("请输入书籍名称");
String bn = sc.next();
dfo2.retriveSpecificBook(bn);}
else {
System.out.println("请输入书籍名称");
String bn = sc.next();
dfc2.retriveSpecificComputerBook(bn);
}
}
break;
} else if (flag == 3) {
// 删除 丢失图书
DaoForLostlist daoForLostlist = new DaoForLostlist();
daoForLostlist.addLostBook();
break;
} else if (flag == 4) {
// 修改图书信息
DaoForOlib daoForOlib = new DaoForOlib();
DaoForClib daoForClib = new DaoForClib();
Scanner sc = new Scanner(System.in);
System.out.println("请选择书籍类型: 1. 非计算机类请按1; 2. 计算机类请按2 ");
int cho = sc.nextInt();
if (cho == 1){
System.out.println("请按下列指示进行操作");
daoForOlib.modifyOBook();
}
else{
System.out.println("请按下列指示进行操作");
daoForClib.modifyCBook();
}
break;
} else if (flag == 5) {
// borrow 借书业务
DaoForBorrow daoForBorrow =new DaoForBorrow();
System.out.println("请输入需要借书的书籍类别: 1.非计算机类; 2.计算机类书籍 (请直接输入序号)");
Scanner sc = new Scanner(System.in);
int count =sc.nextInt();
if (count == 1){
DaoForOlib dfo = new DaoForOlib();
System.out.println("请输入非计算机类书籍的ISBN号");
String string = sc.next();
dfo.borrow1(string);
daoForBorrow.borrow();
}else {
DaoForClib dfc = new DaoForClib();
System.out.println("请输入计算机类书籍的ISBN号:");
String string2 = sc.next();
dfc.borrow1(string2);
daoForBorrow.borrow();
}
break;
}else if (flag == 6){
// return 还书业务
DaoForBorrow daoForBorrow =new DaoForBorrow();
Scanner sc = new Scanner(System.in);
System.out.println("请输入需要还书的书籍体裁:1.非计算机类 2. 计算机类 (请直接输入序号)");
int choise = 0;
choise = sc.nextInt();
if (choise == 1){
System.out.println("请输入书籍ISBN码:");
String s =sc.next();
daoForBorrow.returnBook(s);
// String s2 = sc.next();
DaoForOlib.return2(s);
System.out.println("——>您已成功归还书籍<——");
}else{
System.out.println("请输入书籍ISBN码:");
String s =sc.next();
daoForBorrow.returnBook(s);
// String s3 = sc.next();
DaoForClib.return2(s);
System.out.println("——>您已成功归还书籍<——");
}
break;
}else if (flag ==7){
// 展示借阅信息
DaoForBorrow daoForBorrow = new DaoForBorrow();
daoForBorrow.retriveBorrowList();
break;
}else if (flag == 8){
// 查询丢失书籍的信息
DaoForLostlist daoForLostlist = new DaoForLostlist();
daoForLostlist.retriveLostList();
break;
}else if (flag == 9) {
System.out.println("系统已安全退出! 再会 Bye ~");
break;
} else {
System.out.println("您输入的数字有误,请重新输入");
}
}
}
}
Dao层——DaoForBorrow
package Dao;
import JDBCutils.DButil;
import Model.borrowBook;
import Model.oBook;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;
import java.util.Scanner;
public class DaoForBorrow {
public void borrow(){
Scanner sc =new Scanner(System.in);
Connection conn= null;
PreparedStatement ps = null;
ResultSet rs = null;
borrowBook borrowBook = new borrowBook(sc);
try {
// 获取数据库链接
conn = DButil.getConnetion();
// conn.setAutoCommit(false);
// 获取预编译的数据库操作对象
String sql = "insert into borrowlist(borrReader,bookName,bookNo,Bvalue,borrTime,Tel)" + "values(?,?,?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1,borrowBook.getBorrReader());
ps.setString(2,borrowBook.getBookName());
ps.setString(3,borrowBook.getBookNo());
ps.setDouble(4,borrowBook.getValue());
Timestamp t = new Timestamp(borrowBook.getBorrTime().getTime());
ps.setTimestamp(5, t);
ps.setString(6,borrowBook.getTel());
// ps = conn.prepareStatement(sql);
// String sql2 = ""
// conn.commit();
int count = ps.executeUpdate();
System.out.println(count==1?"数据写入成功":"数据写入失败");
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
//6. 释放资源
DButil.close(conn, ps, rs);
}catch (Exception e){
System.out.println("抛出成功"+e);
}
}
}
//执行前要输入ISBN
public void returnBook(String s){
Scanner sc =new Scanner(System.in);
Connection conn= null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 获取数据库链接
conn = DButil.getConnetion();
// conn.setAutoCommit(false);//设置JDBC事务自动提交改为手动提交
// 获取预编译的数据库操作对象
String sql = "delete from borrowlist where bookNo= ?" ;
ps = conn.prepareStatement(sql);
ps.setString(1,s);
int count = ps.executeUpdate();
// System.out.println(count==1?"执行成功":"执行失败");
System.out.println("执行完成");
} catch (Exception e) {
e.printStackTrace();
}finally {
//6. 释放资源
DButil.close(conn, ps, rs);
}
}
/**
* 查询计算机内所有 借出书籍 的信息
*
* @return
*/
public List<borrowBook> retriveBorrowList() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
//创建Linklist结合接受数据
List<borrowBook> linkedList = new LinkedList<>();
borrowBook bb = null;
try {
//设置成JDBC手动提交的事物
// conn.setAutoCommit(false);
bb= new borrowBook();
// 获取连接对象
conn = DButil.getConnetion();
// 获取预编译的数据库操作对象
String sql = "select * from borrowlist";
ps = conn.prepareStatement(sql);
// 执行SQL
rs = ps.executeQuery();
//处理查询出来的结果集合
if (rs.next()) {
bb.setBorrReader(rs.getString("borrReader"));
bb.setBookName(rs.getString("bookName"));
bb.setBookNo(rs.getString("bookNo"));
bb.setValue(rs.getDouble("Bvalue"));
// bb.setBorrTime(rs.getDate("borrTime"));
Timestamp t = rs.getTimestamp("borrTime");
Date date = new Date(t.getTime());
bb.setTel(rs.getString("Tel"));
linkedList.add(bb);
for (int i = 0; i < linkedList.size(); i++) {
System.out.println(linkedList.get(i).toString());
}
}
} catch (Exception e) {
System.out.println("错误代码:" + e);
} finally {
//6. 释放资源
DButil.close(conn, ps, rs);
}
return linkedList;
}
}
Dao层——DaoForClib
(对于计算机类书籍进行存储的SQL表格的操作)
package Dao;
import JDBCutils.DButil;
import Model.cBook;
import Model.oBook;
//import Model.oBook;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Scanner;
public class DaoForClib {
cBook cb = new cBook();
public void addBook() {
Scanner scanner = new Scanner(System.in);
cBook cb = new cBook(scanner);
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//设置成JDBC手动提交的事物
// conn.setAutoCommit(false);
// 获取连接对象
conn = DButil.getConnetion();
// 获取预编译的数据库操作对象
String sql = "insert into wang_clibrary" + "(BookName,BookNo,writer,publisher,Bvalue,genre,time,isLend)" + "value(?,?,?,?,?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1, cb.getBookName());
ps.setString(2, cb.getBookNo());
ps.setString(3, cb.getWriter());
ps.setString(4, cb.getPublicer());
ps.setDouble(5, cb.getValue());
ps.setString(6, "计算机类");
Timestamp t = new Timestamp(cb.getPurchaseDay().getTime());//将时间转换成Timestamp类型,这样便可以存入到Mysql数据库中
ps.setTimestamp(7, t);
// ps.setString(7,cb.getPurchaseDay());
ps.setBoolean(8, cb.getIslend());
int count = ps.executeUpdate();
System.out.println("count = " + count);
// conn.commit();
} catch (Exception e) {
e.printStackTrace();
} //finally {
// try {
if (conn != null){
conn.rollback();
}
// } catch (SQLException throwables) {
// throwables.printStackTrace();
// }
// 释放资源
DButil.close(conn, ps, rs);
}
/**
* 查询计算机类书籍的所有信息
*
* @return
*/
public List<cBook> retriveComputerAll() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
//创建Linklist结合接受数据
List<cBook> linkedList3 = new ArrayList<cBook>();
try {
//设置成JDBC手动提交的事物
// conn.setAutoCommit(false);
// 获取连接对象
conn = DButil.getConnetion();
// 获取预编译的数据库操作对象
String sql = "select * from wang_clibrary";
ps = conn.prepareStatement(sql);
// 执行SQL
rs = ps.executeQuery();
// cBook cBook = new cBook();
// cBook = null;
cBook cBook0 = null;
//处理查询出来的结果集合
while (rs.next()) {
cBook0 = new cBook();
cBook0.setBookNo(rs.getString("BookNo"));
cBook0.setBookName(rs.getString("BookName"));
cBook0.setWriter(rs.getString("writer"));
cBook0.setPublicer(rs.getString("publisher"));
cBook0.setValue(rs.getDouble("Bvalue"));
cBook0.setGenre(rs.getString("genre"));
// cBook.setPurchaseDay(rs.getString("time"));
Timestamp t = rs.getTimestamp("time");
Date date = new Date(t.getTime());
cBook0.setPurchaseDay(date);
cBook0.setIslend(rs.getBoolean("isLend"));
// int id = rs.getInt("id");
// String BookName = rs.getString("BookName");
// String BookNo = rs.getString("BookNo");
// String writer = rs.getString("writer");
// String publisher = rs.getString("publisher");
// String genre = rs.getString("genre");
linkedList3.add(cBook0);
// conn.commit();
System.out.println(linkedList3);
}
} catch (Exception e) {
System.out.println("错误代码:" + e);
} finally {
// 设置事务回滚
// try {
// if (conn != null) {
// conn.rollback();
// }
// } catch (SQLException throwables) {
// throwables.printStackTrace();
// }
//6. 释放资源
DButil.close(conn, ps, rs);
}
return linkedList3;
}
/**
* 查询计算机内特定书籍的信息
*
* @param s 书籍名
* @return 展示集合
*/
public List<cBook> retriveSpecificComputerBook(String s) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
//创建Linklist结合接受数据
List<cBook> linkedList4 = new LinkedList<>();
try {
//设置成JDBC手动提交的事物
// conn.setAutoCommit(false);
// 获取连接对象
conn = DButil.getConnetion();
// 获取预编译的数据库操作对象
String sql = "select id,BookName,BookNo,writer,publisher,Bvalue,time,isLend from wang_clibrary where BookName =?";
ps = conn.prepareStatement(sql);
ps.setString(1, s);
// 执行SQL
rs = ps.executeQuery();
cBook cBook = new cBook();
//处理查询出来的结果集合
if (rs.next()) {
cBook.setBookNo(rs.getString("BookNo"));
cBook.setBookName(rs.getString("BookName"));
cBook.setWriter(rs.getString("writer"));
cBook.setPublicer(rs.getString("publisher"));
cBook.setValue(rs.getDouble("Bvalue"));
// cBook.setGenre(rs.getString("genre"));
// cBook.setPurchaseDay(rs.getString("time"));
// Timestamp t = rs.getTimestamp("time");
Timestamp t = rs.getTimestamp("time");
Date date = new Date(t.getTime());
cBook.setPurchaseDay(date);
cBook.setIslend(rs.getBoolean("isLend"));
linkedList4.add(cBook);
// conn.commit();
for (int i = 0; i < linkedList4.size(); i++) {
System.out.println(linkedList4.get(i).toString());
}
}
} catch (Exception e) {
e.printStackTrace();
}
return linkedList4;
}
/**
* 借书时修改wang_clibrary表,将书籍状态改为 借出
*
* @param s 书记的ISBN书号
*/
public void borrow1(String s) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DButil.getConnetion();
String sql = "update wang_clibrary set isLend=true where bookNo = ?";
ps = conn.prepareStatement(sql);
ps.setString(1, s);
int count = ps.executeUpdate();
System.out.println(count == 1 ? "修改成功" : "修改失败");
} catch (Exception e) {
e.printStackTrace();
} finally {
DButil.close(conn, ps, rs);
}
}
/**
* 还书时修改wang_clibrary表,将书籍状态改为 未借出
*
* @param s 书记的ISBN书号
*/
public static void return2(String s) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DButil.getConnetion();
String sql = "update wang_clibrary set isLend=false where bookNo = ?";
ps = conn.prepareStatement(sql);
ps.setString(1, s);
int count = ps.executeUpdate();
System.out.println(count == 1 ? "主表还书成功" : "主表还书失败");
} catch (Exception e) {
e.printStackTrace();
} finally {
DButil.close(conn, ps, rs);
}
}
/**
* 修改图书信息
*/
public String modifyCBook() {
Scanner scanner = new Scanner(System.in);
cBook cb = new cBook(scanner);
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DButil.getConnetion();
// 获取预编译的数据库操作对象
String sql = "update wang_clibrary set BookName = ?,writer = ?,publisher = ?,Bvalue = ?,genre = ?,isLend = ?,time = ? where BookNo = ? ";
ps = conn.prepareStatement(sql);
// 执行SQL语句
ps.setString(1, cb.getBookName());
ps.setString(8, cb.getBookNo());
ps.setString(2, cb.getWriter());
ps.setString(3, cb.getPublicer());
ps.setDouble(4, cb.getValue());
ps.setString(5, "计算机类");
// ps.setString(7, cb.getPurchaseDay());
Timestamp t = new Timestamp(cb.getPurchaseDay().getTime());
ps.setTimestamp(7, t);
ps.setBoolean(6, cb.getIslend());
int count = ps.executeUpdate();
System.out.println(count == 1 ? "修改成功" : "修改失败");
// while (rs.next())
// conn.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
// try {
// if (conn != null){
// conn.rollback();}
// } catch (SQLException throwables) {
// throwables.printStackTrace();
// }
DButil.close(conn, ps, rs);
// }
}
return "ISBN:" + cb.getBookNo() + "号图书信息修改成功";
}
}
Dao层——DaoForOlib
(对于非计算机类书籍进行存储的SQL表格的操作)
package Dao;
import JDBCutils.DButil;
import Model.oBook;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Scanner;
public class DaoForOlib {
oBook ob = new oBook();
public void addBook() {
Scanner scanner = new Scanner(System.in);
oBook ob = new oBook(scanner);
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//设置成JDBC手动提交的事物
// conn.setAutoCommit(false);
// 获取连接对象
conn = DButil.getConnetion();
// 获取预编译的数据库操作对象
String sql = "insert into wang_othlibrary" + "(BookName,BookNo,writer,publisher,Bvalue,genre,time,isLend)" + "value(?,?,?,?,?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1, ob.getBookName());
ps.setString(2, ob.getBookNo());
ps.setString(3, ob.getWriter());
ps.setString(4, ob.getPublicer());
ps.setDouble(5, ob.getValue());
ps.setString(6, "非计算机类");
Timestamp t = new Timestamp(ob.getPurchaseDay().getTime());
ps.setTimestamp(7, t);
// ps.setDate(7, ob.getPurchaseDay());
ps.setBoolean(8, ob.getIslend());
int count = ps.executeUpdate();
System.out.println(count==8?"操作成功":"操作失败");
// conn.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
// if (conn != null) {
// conn.rollback();
// }
} catch (Exception throwables) {
throwables.printStackTrace();
}
// 释放资源
DButil.close(conn, ps, rs);
}
}
/**
* 查询 非计算机类 书籍的所有信息
*
* @return
*/
public List<oBook> retriveAll() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
//创建Linklist结合接受数据
List<oBook> linkedList = new ArrayList<oBook>();
oBook oBook1 = null;
try {
//设置成JDBC手动提交的事物
// conn.setAutoCommit(false);
// 获取连接对象
conn = DButil.getConnetion();
// 获取预编译的数据库操作对象
String sql = "select * from wang_othlibrary";
ps = conn.prepareStatement(sql);
// 执行SQL
rs = ps.executeQuery();
//处理查询出来的结果集合
if (rs.next()) {
oBook1 = new oBook();
oBook1.setBookNo(rs.getString("BookNo"));
oBook1.setBookName(rs.getString("BookName"));
oBook1.setWriter(rs.getString("writer"));
oBook1.setPublicer(rs.getString("publisher"));
oBook1.setValue(rs.getDouble("Bvalue"));
oBook1.setGenre(rs.getString("genre"));
Timestamp t = rs.getTimestamp("time");
Date date = new Date(t.getTime());
oBook1.setPurchaseDay(date);
// oBook1.setPurchaseDay(t);
oBook1.setPurchaseDay(rs.getDate("time"));
oBook1.setIslend(rs.getBoolean("isLend"));
linkedList.add(oBook1);
// System.out.println(linkedList);
// conn.commit();
for (int i = 0; i < linkedList.size(); i++) {
System.out.println(linkedList.get(i).toString());
}
}
} catch (Exception e) {
System.out.println("错误代码:" + e);
} finally {
// 设置事务回滚
// try {
// if (conn != null) {
// conn.rollback();
// }
// } catch (SQLException throwables) {
// throwables.printStackTrace();
// }
//6. 释放资源
DButil.close(conn, ps, rs);
}
return linkedList;
}
/**
* 查询计算机内特定书籍的信息
* @param s 书籍名
* @return 展示集合
*/
public List<oBook> retriveSpecificBook(String s) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
//创建Linklist结合接受数据
List<oBook> linkedList2 = new LinkedList<>();
try {
//设置成JDBC手动提交的事物
// conn.setAutoCommit(false);
// 获取连接对象
conn = DButil.getConnetion();
// 获取预编译的数据库操作对象
String sql = "select id,BookName,BookNo,writer,publisher,Bvalue,time,isLend from wang_othlibrary where BookName = ?";
ps = conn.prepareStatement(sql);
ps.setString(1, s);
// 执行SQL
rs = ps.executeQuery();
oBook oBook = new oBook();
// oBook = null;
//处理查询出来的结果集合
if (rs.next()) {
oBook.setBookNo(rs.getString("BookNo"));
oBook.setBookName(rs.getString("BookName"));
oBook.setWriter(rs.getString("writer"));
oBook.setPublicer(rs.getString("publisher"));
oBook.setValue(rs.getDouble("Bvalue"));
oBook.setGenre(rs.getString("genre"));
// oBook.setPurchaseDay(rs.getDate("time"));
Timestamp t = rs.getTimestamp("time");
Date date = new Date(t.getTime());
oBook.setPurchaseDay(date);
oBook.setIslend(rs.getBoolean("isLend"));
linkedList2.add(oBook);
// conn.commit();
for (int i = 0; i < linkedList2.size(); i++) {
System.out.println(linkedList2.get(i).toString());
}
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DButil.close(conn,ps,rs);
}
return linkedList2;
}
/**
* 修改图书信息
*
*/
public String modifyOBook() {
Scanner scanner = new Scanner(System.in);
oBook ob = new oBook(scanner);
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DButil.getConnetion();
// 获取预编译的数据库操作对象
String sql = "update wang_othlibrary set BookName = ?,writer = ?,publisher = ?,Bvalue = ?,genre = ?,isLend = ?,time = ? where BookNo = ? ";
ps = conn.prepareStatement(sql);
ps.setString(8, ob.getBookNo());
// 执行SQL语句
ps.setString(1, ob.getBookName());
// ps.setString(2, ob.getBookNo());
ps.setString(2, ob.getWriter());
ps.setString(3, ob.getPublicer());
ps.setDouble(4, ob.getValue());
ps.setString(5, "非计算机类");
Timestamp t = new Timestamp(ob.getPurchaseDay().getTime());
ps.setTimestamp(7, t);
// ps.setDate(7, ob.getPurchaseDay());
ps.setBoolean(6, ob.getIslend());
int count = ps.executeUpdate();
System.out.println(count==1?"修改成功":"修改失败");
// while (rs.next())
// conn.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
// try {
// if (conn != null){
// conn.rollback();}
// } catch (SQLException throwables) {
// throwables.printStackTrace();
// }
DButil.close(conn, ps, rs);
// }
return "ISBN:" + ob.getBookNo() + "号图书信息修改成功";
}
}
/**
* 借书——》将图wang_othlibrary表的是否借出字段改为借出
* @param s 图书isbn
*/
public void borrow1(String s){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DButil.getConnetion();
String sql = "update wang_othlibrary set isLend =true where bookNo = ? ";
ps = conn.prepareStatement(sql);
ps.setString(1, s);
int count = ps.executeUpdate();
System.out.println(count == 1 ? "主表借书成功" : "主表借书失败");
} catch (Exception e) {
e.printStackTrace();
} finally {
DButil.close(conn,ps,rs);
}
}
/**
* 借书——》将图wang_othlibrary表的是否借出字段改为 未借出
* @param s 图书isbn
*/
public static void return2(String s){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DButil.getConnetion();
String sql = "update wang_othlibrary set isLend =false where bookNo = ? ";
ps = conn.prepareStatement(sql);
ps.setString(1, s);
int count = ps.executeUpdate();
System.out.println(count == 1 ? "主表还书成功" : "主表还书失败");
} catch (Exception e) {
e.printStackTrace();
} finally {
DButil.close(conn,ps,rs);
}
}
}
Dao层——DaoForLostlist
(意为对于丢失书籍的SQL表的操作)
package Dao;
import JDBCutils.DButil;
import Model.lost;
import Model.oBook;
import java.sql.*;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Scanner;
public class DaoForLostlist {
/**
* 丢失非计算机类的图书使用此方法
* @param
* @return
*/
public String addLostBook(){
Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;
Scanner sc = new Scanner(System.in);
System.out.println("请输入书籍体裁:");
String genre = sc.nextLine();
oBook oBook = new oBook(sc);
try {
// 设置事务手动提交
// conn.setAutoCommit(false);
conn = DButil.getConnetion();
// 获取预编译的数据库操作对象
String sql = "insert into lostlist(BookName,BookNo,writer,publisher,Bvalue,genre,time,isLend)" + " value(?,?,?,?,?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1, oBook.getBookName());
ps.setString(2, oBook.getBookNo());
ps.setString(3, oBook.getWriter());
ps.setString(4, oBook.getPublicer());
ps.setDouble(5, oBook.getValue());
ps.setString(6, genre);
Timestamp t = new Timestamp(oBook.getPurchaseDay().getTime());
ps.setTimestamp(7, t);
ps.setBoolean(8,oBook.getIslend());
int count = ps.executeUpdate();
System.out.println( count == 1? "丢失成功" : "丢失失败");
// ps = conn.prepareStatement(sql);
// int result= ps.executeUpdate();
// System.out.println(result);
// conn.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
// try {
// conn.rollback();
DButil.close(conn,ps,rs);
// } catch (SQLException throwables) {
// throwables.printStackTrace();
// }
}
return "操作完成";
}
/**
* 查询计算机内所有 丢失书籍 的信息
*
* @return
*/
public List<lost> retriveLostList() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
lost lo =null;
//创建Linklist结合接受数据
List<lost> linkedList = new LinkedList<>();
try {
//设置成JDBC手动提交的事物
// conn.setAutoCommit(false);
lo =new lost();
// 获取连接对象
conn = DButil.getConnetion();
// 获取预编译的数据库操作对象
String sql = "select * from lostlist";
ps = conn.prepareStatement(sql);
// 执行SQL
rs = ps.executeQuery();
//处理查询出来的结果集合
if (rs.next()) {
lo.setBookName(rs.getString("bookName"));
lo.setBookNo(rs.getString("bookNo"));
lo.setWriter(rs.getString("writer"));
lo.setPublicer(rs.getString("publisher"));
lo.setValue(rs.getDouble("Bvalue"));
lo.setGenre(rs.getString("genre"));
Timestamp ts = rs.getTimestamp("Time");
Date date =new Date(ts.getTime());
lo.setIslend(rs.getBoolean("isLend"));
linkedList.add(lo);
// conn.commit();
for (int i = 0; i < linkedList.size(); i++) {
System.out.println(linkedList.get(i).toString());
}
}
} catch (Exception e) {
System.out.println("错误代码:" + e);
} finally {
// 设置事务回滚
// try {
if (conn != null) {
conn.rollback();
}
// } catch (SQLException throwables) {
// throwables.printStackTrace();
// }
//6. 释放资源
DButil.close(conn, ps, rs);
}
return linkedList;
}
}
JDBCutil是对于JDBC工具的封装,在使用jdbc时可以直接调用
package JDBCutils;
import java.sql.*;
public class DButil {
/**
* 静态代码块在类执行时执行,且只执行一次
*/
static{
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 工具类中的构造方法都是私有的
* 因为工具当中的方法都是静态的,不需要new对象,直接类名.方法名调用
*/
public static Connection getConnetion() throws Exception{
return DriverManager.getConnection("jdbc:mysql://localhost:3306/wang_library","root","admin");
}
/**
* 关闭资源
* @param conn 数据库连接对象
* @param stmt 数据库操作对象
* @param rs 结果集合
*/
public static void close(Connection conn, Statement ps, ResultSet rs){
try {
if (rs != null){
rs.close();
}
} catch (Exception throwables) {
throwables.printStackTrace();
}
try {
if (ps != null){
ps.close();
}
} catch (Exception throwables) {
throwables.printStackTrace();
}
try {
if (conn != null){
conn.close();
}
} catch (Exception throwables) {
throwables.printStackTrace();
}
}
}
Model层
该层主要是各类操作的具体实现
borrowBook
package Model;
import java.security.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.util.Date;
import java.util.Scanner;
public class borrowBook {
private String borrReader;
private String bookName;
private String bookNo;
private double value;
private Date borrTime;
private String tel;
public borrowBook() {
}
public borrowBook(Scanner scanner){
scanner =new Scanner(System.in);
System.out.println("请输入借阅者姓名:");
this.borrReader = scanner.next();
System.out.println("请输入图书名称:");
this.bookName = scanner.next();
System.out.println("请输入图书ISBN:");
this.bookNo = scanner.next();
System.out.println("请输入图书价格 ");
this.value = scanner.nextDouble();
System.out.println("请输入借阅者电话:");
this.tel=scanner.next();
System.out.println("请输入借阅时间(格式:2022-10-10):");
String tim=scanner.next();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
this.borrTime = sdf.parse(tim);
} catch (ParseException e) {
e.printStackTrace();
}
// borrTime= java.sql.Date.valueOf(borrTime);
}
public borrowBook(String borrReader, String bookName, String bookNo, double value, Date borrTime, String tel) {
this.borrReader = borrReader;
this.bookName = bookName;
this.bookNo = bookNo;
this.value = value;
this.borrTime = borrTime;
this.tel = tel;
}
public String getBorrReader() {
return borrReader;
}
public void setBorrReader(String borrReader) {
this.borrReader = borrReader;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public String getBookNo() {
return bookNo;
}
public void setBookNo(String bookNo) {
this.bookNo = bookNo;
}
public double getValue() {
return value;
}
public void setValue(double value) {
this.value = value;
}
public Date getBorrTime() {
return borrTime;
}
public void setBorrTime(Date borrTime) {
this.borrTime = borrTime;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
@Override
public String toString() {
return "borrowBook{" +
"borrReader='" + borrReader + '\'' +
", bookName='" + bookName + '\'' +
", bookNo='" + bookNo + '\'' +
", value=" + value +
", borrTime=" + borrTime +
", tel='" + tel + '\'' +
'}';
}
}
cBook
对于计算机类书籍的具体操作,对接DaoForClib以及Daoforlostlist、daoforborrow
package Model;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;
public class cBook {
private String bookNo;
private String bookName;
private String genre;
private Date purchaseDay;
private boolean islend;
private Double value;
private String writer;
private String publicer;
@Override
public String toString() {
return "cBook{" +
"bookNo='" + bookNo + '\'' +
", bookName='" + bookName + '\'' +
", genre='" + genre + '\'' +
", purchaseDay=" + purchaseDay +
", islend=" + islend +
", value=" + value +
", writer='" + writer + '\'' +
", publicer='" + publicer + '\'' +
'}';
}
public cBook() {
}
public cBook(String bookNo, String bookName, String genre, Date purchaseDay, boolean islend, double value, String writer, String publicer) {
this.bookNo = bookNo;
this.bookName = bookName;
this.genre = genre;
this.purchaseDay = purchaseDay;
this.islend = islend;
this.value = value;
this.writer = writer;
this.publicer = publicer;
}
public cBook(Scanner scanner) {
scanner=new Scanner(System.in);
System.out.println("请输入图书的ISBN号:");
this.bookNo= scanner.nextLine();
System.out.println("请输入书籍的名字:");
this.bookName= scanner.nextLine();
System.out.println("请输入图书购买的时间:(示例:2022-10-15)");
String S = scanner.next();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
this.purchaseDay = sdf.parse(S);
// Timestamp ts = new Timestamp(this.purchaseDate.getTime());
} catch (ParseException e) {
e.printStackTrace();
}
System.out.println("请选择书籍状态(根据提示输入序号):0.未借出;1.借出");
int choise1;
// while (true){
choise1=scanner.nextInt();
if (choise1 == 0){
this.islend = false;
}else if (choise1 ==1){
this.islend = true;
}else{
System.out.println("输入错误!!请重新进行输入。");
}
// }
System.out.println("请输入图书的价格:");
this.value= scanner.nextDouble();
System.out.println("请输入图书的作者:");
this.writer= scanner.next();
System.out.println("请输入图书的出版社:");
this.publicer= scanner.next();
}
public String getBookNo() {
return bookNo;
}
public void setBookNo(String bookNo) {
this.bookNo = bookNo;
}
public String getGenre() {
return genre;
}
public void setGenre(String genre) {
this.genre = genre;
}
public Double getValue() {
return value;
}
public void setValue(double value) {
this.value = value;
}
public String getWriter() {
return writer;
}
public void setWriter(String writer) {
this.writer = writer;
}
public String getPublicer() {
return publicer;
}
public void setPublicer(String publicer) {
this.publicer = publicer;
}
public boolean getIslend() {
return islend;
}
public void setIslend(boolean islend) {
this.islend = islend;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public Date getPurchaseDay() {
return purchaseDay;
}
public void setPurchaseDay(Date purchaseDay) {
this.purchaseDay = purchaseDay;
}
}
lost
用于对接丢失书籍表。书籍丢失后将会把书放入丢失库
package Model;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.Date;
import java.util.Scanner;
public class lost {
private String bookNo;
private String bookName;
private String genre;
private Date Time;
private boolean islend;
private Double value;
private String writer;
private String publicer;
public lost() {
}
public lost(String bookNo, String bookName, String genre, Date time, boolean islend, Double value, String writer, String publicer) {
this.bookNo = bookNo;
this.bookName = bookName;
this.genre = genre;
this.Time = time;
this.islend = islend;
this.value = value;
this.writer = writer;
this.publicer = publicer;
}
public lost(Scanner scanner){
scanner =new Scanner(System.in);
System.out.println("请输入图书ISBN:");
this.bookNo = scanner.nextLine();
System.out.println("请输入图书名称:");
this.bookName = scanner.nextLine();
System.out.println("请输入图书体裁:");
this.genre= scanner.nextLine();
System.out.println("请输入图书价格 ");
this.value = scanner.nextDouble();
System.out.println("请输入作者名称:");
this.writer=scanner.nextLine();
System.out.println("请输入出版社名称:");
this.publicer=scanner.nextLine();
System.out.println("请输入图书购买的时间:(示例:2022-10-15)");
String string01= scanner.nextLine();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
this.Time = sdf.parse(string01);
} catch (ParseException e) {
e.printStackTrace();
}
// Time= java.sql.Date.valueOf(Time);
}
public String getBookNo() {
return bookNo;
}
public void setBookNo(String bookNo) {
this.bookNo = bookNo;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public String getGenre() {
return genre;
}
public void setGenre(String genre) {
this.genre = genre;
}
public Date getTime() {
return Time;
}
public void setTime(Date time) {
Time = time;
}
public boolean isIslend() {
return islend;
}
public void setIslend(boolean islend) {
this.islend = islend;
}
public Double getValue() {
return value;
}
public void setValue(Double value) {
this.value = value;
}
public String getWriter() {
return writer;
}
public void setWriter(String writer) {
this.writer = writer;
}
public String getPublicer() {
return publicer;
}
public void setPublicer(String publicer) {
this.publicer = publicer;
}
@Override
public String toString() {
return "lost{" +
"书籍ISBN='" + bookNo + '\'' +
", 图书名='" + bookName + '\'' +
", 类别='" + genre + '\'' +
", 丢失时间=" + Time +
", 是否外借=" + islend +
", 图书价值=" + value +
", 作者='" + writer + '\'' +
", 出版社='" + publicer + '\'' +
'}';
}
}
oBook
package Model;
import java.security.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;
public class oBook {
private String bookNo;
private String bookName;
private String genre;
private Date purchaseDay;
private boolean islend;
private Double value;
private String writer;
private String publicer;
public oBook() {
}
public oBook(String bookNo, String bookName, String genre, Date purchaseDay, boolean islend, double value, String writer, String publicer) {
this.bookNo = bookNo;
this.bookName = bookName;
this.genre = genre;
this.purchaseDay = (java.sql.Date) purchaseDay;
this.islend = islend;
this.value = value;
this.writer = writer;
this.publicer = publicer;
}
public oBook(Scanner scanner) {
scanner=new Scanner(System.in);
System.out.println("请输入图书的ISBN号:");
this.bookNo= scanner.nextLine();
System.out.println("请输入图书名字");
this.bookName= scanner.nextLine();
// System.out.println("请输入书籍名称:");
// this.bookName = scanner.nextLine();
// System.out.println("请选择书籍的体裁(请根据提示输入序号:计算机类请输入0;非计算机类请输入1;):");
// int choise = 3;
// while (true){
// choise=scanner.nextInt();
// if (choise == 0){
// this.genre = "计算机类";
// }else if (choise ==1){
// this.genre = "非计算机类";
// }else{
// System.out.println("输入错误!!请重新进行输入。");
// }
// }
// System.out.println("请输入图书的价格:");
// this.value= scanner.nextDouble();
// System.out.println("请输入图书的作者:");
// this.writer= scanner.next();
// System.out.println("请输入图书的出版社:");
// this.publicer= scanner.nextLine();
System.out.println("请输入图书购买的时间:(示例:2022-10-15)");
String string00= scanner.nextLine();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
// java.sql.Date date = sdf.parse(string00);
// Timestamp ts = new Timestamp(date.getTime());
try {
this.purchaseDay = sdf.parse(string00);
// date = (java.sql.Date) new SimpleDateFormat("yyyy-mm-dd").parse(string00);
// Date purchaseDate = sdf.parse(string00);
// Timestamp ts = new Timestamp((java.sql.Date)purchaseDate.getTime());
} catch (ParseException e) {
e.printStackTrace();
}
System.out.println("请输入图书的价格:");
this.value= scanner.nextDouble();
System.out.println("请输入图书的作者:");
this.writer= scanner.next();
System.out.println("请输入图书的出版社:");
this.publicer= scanner.next();
System.out.println("请选择书籍状态(根据提示输入序号):0.未借出;1.借出(请输入序号)");
int choise1 ;
// while (true){
choise1=scanner.nextInt();
if (choise1 == 0){
this.islend = false;
}else if (choise1 ==1){
this.islend = true;
}else{
System.out.println("输入错误!!请重新进行输入。");
}
}
public String getBookNo() {
return bookNo;
}
public void setBookNo(String bookNo) {
this.bookNo = bookNo;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public String getGenre() {
return genre;
}
public void setGenre(String genre) {
this.genre = genre;
}
public Date getPurchaseDay() {
return purchaseDay;
}
public void setPurchaseDay(Date purchaseDay) {
this.purchaseDay = purchaseDay;
}
public boolean getIslend() {
return islend;
}
public void setIslend(boolean islend) {
this.islend = islend;
}
public Double getValue() {
return value;
}
public void setValue(Double value) {
this.value = value;
}
public String getWriter() {
return writer;
}
public void setWriter(String writer) {
this.writer = writer;
}
public String getPublicer() {
return publicer;
}
public void setPublicer(String publicer) {
this.publicer = publicer;
}
@Override
public String toString() {
return "oBook{" +
"bookNo='" + bookNo + '\'' +
", bookName='" + bookName + '\'' +
", genre='" + genre + '\'' +
", purchaseDay=" + purchaseDay +
", islend=" + islend +
", value=" + value +
", writer='" + writer + '\'' +
", publicer='" + publicer + '\'' +
'}';
}
}
returnBook
package Model;
import java.util.Date;
import java.util.Scanner;
public class returnBook {
private String borrReader;
private String bookName;
private String bookNo;
private double value;
private Date borrTime;
private String tel;
public returnBook(){}
public returnBook(String borrReader, String bookName, String bookNo, double value, Date borrTime, String tel) {
this.borrReader = borrReader;
this.bookName = bookName;
this.bookNo = bookNo;
this.value = value;
this.borrTime = borrTime;
this.tel = tel;
}
public String getBorrReader() {
return borrReader;
}
public void setBorrReader(String borrReader) {
this.borrReader = borrReader;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public String getBookNo() {
return bookNo;
}
public void setBookNo(String bookNo) {
this.bookNo = bookNo;
}
public double getValue() {
return value;
}
public void setValue(double value) {
this.value = value;
}
public Date getBorrTime() {
return borrTime;
}
public void setBorrTime(Date borrTime) {
this.borrTime = borrTime;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
}