要求
使用java连接数据库制作一个图书管理系统，要求数据存储在数据库中有用户管理员、图书等，用户与管理员属性有姓名、电话、身份证、密码、性别、专业和地址这些属性，要求用户有借阅与归还图书的功能，管理员有查看所有用户信息、添加图书、修改图书的功能
SQL脚本
我的数据库是8.0的版本，使用SQLyog制作的数据库
图书
这个表用于存放图书的相关数据，用于图书的借阅，修改，归还，购买
CREATE DATABASE IF NOT EXISTS java;
USE java;
CREATE TABLE IF NOT EXISTS book(
bid INT(5) PRIMARY KEY AUTO_INCREMENT,
bname VARCHAR(20) UNIQUE NOT NULL,
price DOUBLE NOT NULL,
surplus INT NOT NULL,
bought INT NOT NULL,
borrowed INT NOT NULL
);
INSERT INTO book (bname,price,surplus,bought,borrowed) VALUES('西游记',50.0,100,0,0);
INSERT INTO book (bname,price,surplus,bought,borrowed) VALUES('红楼梦',30.0,100,0,0);
INSERT INTO book (bname,price,surplus,bought,borrowed) VALUES('水浒传',40.0,100,0,0);
INSERT INTO book (bname,price,surplus,bought,borrowed) VALUES('三国演义',60.0,100,0,0);用户
这个表用于存放用户的相关数据，包括用户的注册，登录等
USE java;
CREATE TABLE IF NOT EXISTS `user`(
uid INT(10) PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(20) NOT NULL,
uphone VARCHAR(15) UNIQUE NOT NULL,
ucard VARCHAR(20) UNIQUE NOT NULL,
upass VARCHAR(20) NOT NULL,
ugender VARCHAR(5) NOT NULL,
uobj VARCHAR(20) NOT NULL,
uaddress VARCHAR(30) NOT NULL,
borrowed VARCHAR(20) NULL,
ubalance DOUBLE,
ubuy VARCHAR(20) NULL
);
INSERT INTO `user` (uname,uphone,ucard,upass,ugender,uobj,uaddress,ubalance) VALUES('张三','15666873493','285608200903045678','zhangsan123456','女','计算机网络','安徽合肥',1000.0);
INSERT INTO `user` (uname,uphone,ucard,upass,ugender,uobj,uaddress,ubalance) VALUES('李四','13476453463','285608202012269733','lisi123456','男','软件开发','安徽芜湖',1000.0);管理员
这个表用于存放管理员的相关信息，用于管理员的登录，注册
USE java;
CREATE TABLE IF NOT EXISTS manager(
`mid` INT(10) PRIMARY KEY AUTO_INCREMENT,
mname VARCHAR(20) NOT NULL,
mphone VARCHAR(15) UNIQUE NOT NULL,
mcard VARCHAR(20) UNIQUE NOT NULL,
mpass VARCHAR(20) NOT NULL,
mgender VARCHAR(5) NOT NULL,
mobj VARCHAR(20) NOT NULL,
maddress VARCHAR(30) NOT NULL
);
INSERT INTO `manager` (mname,mphone,mcard,mpass,mgender,mobj,maddress) VALUES('王五','16798564323','286908198802281655','wangwu123456','男','汽车修理','安徽安庆');
INSERT INTO `manager` (mname,mphone,mcard,mpass,mgender,mobj,maddress) VALUES('赵六','16809876523','286908199901085418','zhaoliu123456','女','工程造价','安徽六安');老板
这个表用于存放老板的信息，用于老板的登录，因为老板只有一个，所以我就插入了一条数据，如果有多个老板也可以直接插入
USE java;
CREATE TABLE IF NOT EXISTS boss(
bossname VARCHAR(20) NOT NULL PRIMARY KEY,
bosscard VARCHAR(20)NOT NULL,
bosspass VARCHAR(20)NOT NULL,
balance DOUBLE
);
INSERT INTO boss VALUES('master','245674566789086433','master123456',10000.0);Java代码
properties文件
此处使用了跨平台方案，因为以后有可能要连接本机以外的数据库，使用跨平台方案可以更方便于操作，相当于只需要改一个文件就可以了，由于我的数据库是8.0的版本，所以我的jar包也是8.0的版本，所以我的Driver类路径是com.mysql.cj.jdbc.Driver，数据库版本不同，路径也不同
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/java
username=root
password=root封装工具类
将工具类进行封装，其他类注册驱动，获取数据库，关闭等就可以直接调用这个封装工具类里的方法即可，方便很多
package util;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @author zhutouaichirou
*/
public class DButil {
private static final Properties prop = new Properties();
static {
InputStream is = DButil.class.getResourceAsStream("/db.properties");
try {
prop.load(is);
Class.forName(prop.getProperty("driver"));
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws ClassNotFoundException, SQLException {
Connection conn = null;
conn= DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password"));
return conn;
}
public static void closeConnection(Connection conn, Statement stmt, PreparedStatement ps, ResultSet rs) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}ORM
ORM（Object Relational Mapping），对象关系映射，从数据库查询到的结果集（ResultSet）在进行遍历时，逐行遍历，然后一个字段一个字段的读取数据，取出的都是零散的数据，然后将零散数据一个一个输出，这样比较麻烦，不利于操作。在实际应用开发中，我们需要将零散的数据进行封装整理。将一个封装好的对象插入到数据库的相应表中，也可以将查询出来的数据封装成一个对象，相当于一个实体类的载体
图书
图书实体类的载体放在entity包中，以下的实体类均放在entity包中
package entity;
/**
* @author zhutouaichirou
*/
public class Book {
private int bid;
private String bname;
private double price;
private int surplus;
public Book() {
}
public Book(int bid, String bname, double price, int surplus) {
this.bid = bid;
this.bname = bname;
this.price = price;
this.surplus = surplus;
}
public Book(String bname, double price, int surplus) {
this.bname = bname;
this.price = price;
this.surplus = surplus;
}
public int getBid() {
return bid;
}
public void setBid(int bid) {
this.bid = bid;
}
public String getBname() {
return bname;
}
public void setBname(String bname) {
this.bname = bname;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public int getSurplus() {
return surplus;
}
public void setSurplus(int surplus) {
this.surplus = surplus;
}
@Override
public String toString() {
return "Book{" +
"bid=" + bid +
", bname='" + bname + '\'' +
", price=" + price +
", surplus=" + surplus +
'}';
}
}用户
用户实体类
package entity;
/**
* @author zhutouaichirou
*/
public class User {
private int uid;
private String uname;
private String uphone;
private String ucard;
private String upass;
private String ugender;
private String uobj;
private String uaddress;
public User() {
}
public User(int uid, String uname, String uphone, String ucard, String upass, String ugender, String uobj, String uaddress) {
this.uid = uid;
this.uname = uname;
this.uphone = uphone;
this.ucard = ucard;
this.upass = upass;
this.ugender = ugender;
this.uobj = uobj;
this.uaddress = uaddress;
}
public User(String uname, String uphone, String ucard, String upass, String ugender, String uobj, String uaddress) {
this.uname = uname;
this.uphone = uphone;
this.ucard = ucard;
this.upass = upass;
this.ugender = ugender;
this.uobj = uobj;
this.uaddress = uaddress;
}
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public String getUphone() {
return uphone;
}
public void setUphone(String uphone) {
this.uphone = uphone;
}
public String getUcard() {
return ucard;
}
public void setUcard(String ucard) {
this.ucard = ucard;
}
public String getUpass() {
return upass;
}
public void setUpass(String upass) {
this.upass = upass;
}
public String getUgender() {
return ugender;
}
public void setUgender(String ugender) {
this.ugender = ugender;
}
public String getUobj() {
return uobj;
}
public void setUobj(String uobj) {
this.uobj = uobj;
}
public String getUaddress() {
return uaddress;
}
public void setUaddress(String uaddress) {
this.uaddress = uaddress;
}
@Override
public String toString() {
return "User{" +
"uid=" + uid +
", uname='" + uname + '\'' +
", uphone='" + uphone + '\'' +
", ucard='" + ucard + '\'' +
", upass='" + upass + '\'' +
", ugender='" + ugender + '\'' +
", uobj='" + uobj + '\'' +
", uaddress='" + uaddress + '\'' +
'}';
}
}管理员
管理员实体类
package entity;
/**
* @author zhutouaichirou
*/
public class Manager {
private int mid;
private String mname;
private String mphone;
private String mcard;
private String mpass;
private String mgender;
private String mobj;
private String maddress;
public Manager(String mname, String mphone, String mcard, String mpass, String mgender, String mobj, String maddress) {
this.mname = mname;
this.mphone = mphone;
this.mcard = mcard;
this.mpass = mpass;
this.mgender = mgender;
this.mobj = mobj;
this.maddress = maddress;
}
public Manager(int mid, String mname, String mphone, String mcard, String mpass, String mgender, String mobj, String maddress) {
this.mid = mid;
this.mname = mname;
this.mphone = mphone;
this.mcard = mcard;
this.mpass = mpass;
this.mgender = mgender;
this.mobj = mobj;
this.maddress = maddress;
}
public Manager() {
}
public int getMid() {
return mid;
}
public void setMid(int mid) {
this.mid = mid;
}
public String getMname() {
return mname;
}
public void setMname(String mname) {
this.mname = mname;
}
public String getMphone() {
return mphone;
}
public void setMphone(String mphone) {
this.mphone = mphone;
}
public String getMcard() {
return mcard;
}
public void setMcard(String mcard) {
this.mcard = mcard;
}
public String getMpass() {
return mpass;
}
public void setMpass(String mpass) {
this.mpass = mpass;
}
public String getMgender() {
return mgender;
}
public void setMgender(String mgender) {
this.mgender = mgender;
}
public String getMobj() {
return mobj;
}
public void setMobj(String mobj) {
this.mobj = mobj;
}
public String getMaddress() {
return maddress;
}
public void setMaddress(String maddress) {
this.maddress = maddress;
}
@Override
public String toString() {
return "Manager{" +
"mid=" + mid +
", mname='" + mname + '\'' +
", mphone='" + mphone + '\'' +
", mcard='" + mcard + '\'' +
", mpass='" + mpass + '\'' +
", mgender='" + mgender + '\'' +
", mobj='" + mobj + '\'' +
", maddress='" + maddress + '\'' +
'}';
}
}老板
老板实体类，本来题目中是没有的，但是图书有一个价格属性感觉很突兀，大概是强迫症犯了吧，就多写了一个转账业务，既然有转账业务，肯定就有购买的选择，肯定也应该有转账的接收方，因此就有了老板类
package entity;
/**
* @author zhutouaichirou
*/
public class Boss {
private String bossname;
private String bosscard;
private String bosspass;
public Boss() {
}
public Boss(String bossname, String bosscard, String bosspass) {
this.bossname = bossname;
this.bosscard = bosscard;
this.bosspass = bosspass;
}
public String getBossname() {
return bossname;
}
public void setBossname(String bossname) {
this.bossname = bossname;
}
public String getBosscard() {
return bosscard;
}
public void setBosscard(String bosscard) {
this.bosscard = bosscard;
}
public String getBosspass() {
return bosspass;
}
public void setBosspass(String bosspass) {
this.bosspass = bosspass;
}
@Override
public String toString() {
return "Boss{" +
"bossname='" + bossname + '\'' +
", bosscard='" + bosscard + '\'' +
", bosspass='" + bosspass + '\'' +
'}';
}
}业务逻辑层
以下是业务逻辑层，用于存放用户、管理员、老板的方法业务即用户完成的一个功能，可以有一个或者多个Dao的调用组成
图书
图书的业务逻辑层，有借阅图书、归还图书等方法
package service;
import entity.Book;
import util.DButil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author zhutouaichirou
*/
public class ServiceForBook {
// 新增图书
public static int insertBook(Book book) {
Connection conn = null;
PreparedStatement ps = null;
String sql = "insert into book(bname,price,surplus,bought,borrowed) values(?,?,?,?,?);";
try {
conn = DButil.getConnection();
ps = conn.prepareStatement(sql);
ps.setString(1, book.getBname());
ps.setDouble(2, book.getPrice());
ps.setInt(3, book.getSurplus());
ps.setInt(4, 0);
ps.setInt(5, 0);
ps.executeUpdate();
int i = ps.executeUpdate();
return i;
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} finally {
DButil.closeConnection(conn, ps, null, null);
}
}
// 删除图书
public static int deleteBook(int bid) {
Connection conn = null;
PreparedStatement ps = null;
String sql = "delete from book where bid = ?;";
try {
conn = DButil.getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, bid);
int i = ps.executeUpdate();
return i;
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
// 修改图书
public static int updateBook(Book book) {
Connection conn = null;
PreparedStatement ps = null;
String sql = "update book set bname = ?, price = ?, surplus = ? where bid = ?;";
try {
conn = DButil.getConnection();
ps = conn.prepareStatement(sql);
ps.setString(1, book.getBname());
ps.setDouble(2, book.getPrice());
ps.setInt(3, book.getSurplus());
ps.setInt(4, book.getBid());
int i = ps.executeUpdate();
return i;
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally{
DButil.closeConnection(conn, ps, null, null);
}
}
// 借阅图书
public static int borrowBook(int bid, int uid) {
Connection conn = null;
PreparedStatement ps1 = null;
PreparedStatement ps2 = null;
String sql1 = "update book set surplus = surplus - 1,borrowed=borrowed+1 where bid = ?;";
String sql2 = "update user set borrowed = (select bname from book where bid = ?) where uid = ?;";
try {
conn = DButil.getConnection();
ps1 = conn.prepareStatement(sql1);
ps2 = conn.prepareStatement(sql2);
ps1.setInt(1, bid);
ps2.setInt(1, bid);
ps2.setInt(2, uid);
int i1 = ps1.executeUpdate();
int i2 = ps2.executeUpdate();
return i2+i1;
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally {
DButil.closeConnection(conn, ps1, ps2, null);
}
}
// 查看所有图书
public static void getAllBook() {
Connection conn = null;
PreparedStatement ps = null;
String sql = "select * from book;";
ResultSet rs = null;
try {
conn = DButil.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
int bid = rs.getInt("bid");
String bname = rs.getString("bname");
double price = rs.getDouble("price");
int surplus = rs.getInt("surplus");
int borrowed = rs.getInt("borrowed");
int bought = rs.getInt("bought");
System.out.println(bid + " " + bname + " " + price + " " + surplus+" " +borrowed+" " +bought);
System.out.println();
}
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally{
DButil.closeConnection(conn, ps, null, rs);
}
}
// 归还图书
public static int returnBook(int bid, int uid) {
Connection conn = null;
PreparedStatement ps1 = null;
PreparedStatement ps2 = null;
String sql1 = "update book set surplus = surplus + 1,borrowed=borrowed-1 where bid = ?;";
String sql2 = "update user set borrowed = null where uid = ?;";
try {
conn = DButil.getConnection();
ps1 = conn.prepareStatement(sql1);
ps2 = conn.prepareStatement(sql2);
ps1.setInt(1, bid);
ps2.setInt(1, uid);
int i1 = ps1.executeUpdate();
int i2 = ps2.executeUpdate();
return i2+i1;
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally{
DButil.closeConnection(conn, ps1, ps2, null);
}
}
// 购买图书
public static int boughtBook(int bid, int uid) {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs = null;
try {
conn = DButil.getConnection();
String sql = "update book set surplus=surplus-1,bought=bought+1 where bid=?;";
ps = conn.prepareStatement(sql);
ps.setInt(1, bid);
String sql1 = "update `user` set ubuy = (select bname from book where bid = ?) where uid = ?;";
ps = conn.prepareStatement(sql1);
ps.setInt(1, bid);
ps.setInt(2, uid);
String sql2 = "update `user` set ubalance = ublance-(select price from book where bid = ?) where uid = ?;";
ps = conn.prepareStatement(sql2);
ps.setInt(1, bid);
ps.setInt(2, uid);
String sql3 = "update boss set balance = balance+(select price from book where bid = ?) where uid = ?;";
ps = conn.prepareStatement(sql3);
ps.setInt(1, bid);
ps.setInt(2, uid);
return ps.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally{
DButil.closeConnection(conn, null, ps, rs);
}
}
}用户
用户的业务逻辑层，有用户数据的增删改查等功能
package service;
import entity.User;
import util.DButil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author zhutouaichirou
*/
public class ServiceForUser {
// 查看所有用户
public static void getAllUser(){
Connection conn=null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DButil.getConnection();
String sql = "select * from user;";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
int uid = rs.getInt("uid");
String uname = rs.getString("uname");
String uphone = rs.getString("uphone");
String ucard = rs.getString("ucard");
String upass = rs.getString("upass");
String ugender = rs.getString("ugender");
String uobj = rs.getString("uobj");
String uaddress = rs.getString("uaddress");
String borrowed = rs.getString("borrowed");
double ubalance = rs.getDouble("ubalance");
String ubuy=rs.getString("ubuy");
System.out.println(uid+" "+uname+" "+uphone+" "+ucard+" "+upass+" "+ugender+" "+uobj+" "+uaddress+" "+borrowed+" "+ubalance+" "+ubuy);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally{
DButil.closeConnection(conn, null, ps, rs);
}
}
// 删除用户
public static int deleteUser(int uid){
Connection conn=null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DButil.getConnection();
String sql = "delete from user where uid=?;";
ps = conn.prepareStatement(sql);
ps.setInt(1, uid);
int i = ps.executeUpdate();
return i;
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally{
DButil.closeConnection(conn, null, ps, rs);
}
}
// 修改信息
public static int updateUser(User user, int uid){
Connection conn=null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DButil.getConnection();
String sql = "update user set uname=?,uphone=?,ucard=?,upass=?,ugender=?,uobj=?,uaddress=? where uid=?;";
ps = conn.prepareStatement(sql);
ps.setString(1, user.getUname());
ps.setString(2, user.getUphone());
ps.setString(3, user.getUcard());
ps.setString(4, user.getUpass());
ps.setString(5, user.getUgender());
ps.setString(6, user.getUobj());
ps.setString(7, user.getUaddress());
ps.setInt(8,uid);
return ps.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally{
DButil.closeConnection(conn, null, ps, rs);
}
}
// 注册用户
public static int registerUser(User user){
Connection conn=null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DButil.getConnection();
String sql = "insert into user(uname,uphone,ucard,upass,ugender,uobj,uaddress) values(?,?,?,?,?,?,?);";
ps = conn.prepareStatement(sql);
ps.setString(1, user.getUname());
ps.setString(2, user.getUphone());
ps.setString(3, user.getUcard());
ps.setString(4, user.getUpass());
ps.setString(5, user.getUgender());
ps.setString(6, user.getUobj());
ps.setString(7, user.getUaddress());
return ps.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally{
DButil.closeConnection(conn, null, ps, rs);
}
}
// 查看当前用户信息
public static User getUser(int uid){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs = null;
try {
conn = DButil.getConnection();
String sql = "select * from user where uid=?;";
ps = conn.prepareStatement(sql);
ps.setInt(1, uid);
rs = ps.executeQuery();
User user = null;
while (rs.next()) {
user = new User();
user.setUid(rs.getInt("uid"));
user.setUname(rs.getString("uname"));
user.setUphone(rs.getString("uphone"));
user.setUcard(rs.getString("ucard"));
user.setUpass(rs.getString("upass"));
user.setUgender(rs.getString("ugender"));
user.setUobj(rs.getString("uobj"));
user.setUaddress(rs.getString("uaddress"));
}
return user;
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally{
DButil.closeConnection(conn, null, ps, rs);
}
}
// 用户使用身份证登录
public static int loginByCard(String ucard,String upass) {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs = null;
try {
conn = DButil.getConnection();
String sql = "select uid from user where ucard=? and upass=?;";
ps = conn.prepareStatement(sql);
ps.setString(1, ucard);
ps.setString(2, upass);
rs = ps.executeQuery();
if (rs.next()){
int uid = rs.getInt("uid");
return uid;
}else{
return 0;
}
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
}finally{
DButil.closeConnection(conn, null, ps, rs);
}
}
// 用户使用电话登录
public static int loginByPhone(String uphone,String upass) {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs = null;
try {
conn = DButil.getConnection();
String sql = "select uid from user where uphone=? and upass=?;";
ps = conn.prepareStatement(sql);
ps.setString(1, uphone);
ps.setString(2, upass);
rs = ps.executeQuery();
if (rs.next()){
int uid = rs.getInt("uid");
return uid;
}else{
return 0;
}
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
}finally{
DButil.closeConnection(conn, null, ps, rs);
}
}
// 购买图书
public static int boughtBook(int bid, int uid) {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs = null;
try {
conn = DButil.getConnection();
String sql = "update book set surplus=surplus-1,bought=bought+1 where bid=?;";
ps = conn.prepareStatement(sql);
ps.setInt(1, bid);
String sql1 = "update `user` set ubuy = (select bname from book where bid = ?) where uid = ?;";
ps = conn.prepareStatement(sql1);
ps.setInt(1, bid);
ps.setInt(2, uid);
String sql2 = "update `user` set ubalance = ublance-(select price from book where bid = ?) where uid = ?;";
ps = conn.prepareStatement(sql2);
ps.setInt(1, bid);
ps.setInt(2, uid);
String sql3 = "update boss set balance = balance+(select price from book where bid = ?) where uid = ?;";
ps = conn.prepareStatement(sql3);
ps.setInt(1, bid);
ps.setInt(2, uid);
return ps.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally{
DButil.closeConnection(conn, null, ps, rs);
}
}
}管理员
管理员的业务逻辑层，有管理员数据的增删改查等功能
package service;
import entity.Manager;
import util.DButil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author zhutouaichirou
*/
public class ServiceForManager {
// 注册管理员
public static int registerManager(Manager manager){
Connection connection = null;
PreparedStatement ps = null;
try {
connection = DButil.getConnection();
String sql = "insert into manager(mname,mphone,mcard,mpass,mgender,mobj,maddress) values(?,?,?,?,?,?,?)";
ps = connection.prepareStatement(sql);
ps.setString(1,manager.getMname());
ps.setString(2,manager.getMphone());
ps.setString(3,manager.getMcard());
ps.setString(4,manager.getMpass());
ps.setString(5,manager.getMgender());
ps.setString(6,manager.getMobj());
ps.setString(7,manager.getMaddress());
return ps.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally {
DButil.closeConnection(connection, null, ps,null);
}
}
// 修改管理员信息
public static int updateManager(Manager manager, int mid){
Connection connection = null;
PreparedStatement ps = null;
try {
connection = DButil.getConnection();
String sql = "update manager set mname=?,mphone=?,mcard=?,mpass=?,mgender=?,mobj=?,maddress=? where mid=?";
ps = connection.prepareStatement(sql);
ps.setString(1,manager.getMname());
ps.setString(2,manager.getMphone());
ps.setString(3,manager.getMcard());
ps.setString(4,manager.getMpass());
ps.setString(5,manager.getMgender());
ps.setString(6,manager.getMobj());
ps.setString(7,manager.getMaddress());
ps.setInt(8,mid);
return ps.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally{
DButil.closeConnection(connection,null,ps,null);
}
}
// 使用身份证登录
public static int loginByCard(String mcard,String mpass) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = DButil.getConnection();
String sql = "select mid from manager where mcard=? and mpass=?";
ps = connection.prepareStatement(sql);
ps.setString(1, mcard);
ps.setString(2, mpass);
rs = ps.executeQuery();
if (rs.next()) {
int mid = rs.getInt("mid");
return mid;
} else {
return 0;
}
} catch (SQLException | ClassNotFoundException e) {
throw new RuntimeException(e);
} finally {
DButil.closeConnection(connection, null, ps, rs);
}
}
// 使用电话号码登录
public static int loginByPhone(String mphone,String mpass) {
Connection connection = null;
PreparedStatement ps = null;
Manager manager = null;
try {
connection = DButil.getConnection();
String sql = "select mid from manager where mphone=? and mpass=?";
ps = connection.prepareStatement(sql);
ps.setString(1,mphone);
ps.setString(2,mpass);
ResultSet rs = ps.executeQuery();
if(rs.next()){
int mid = rs.getInt("mid");
return mid;
}else{
return 0;
}
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally{
DButil.closeConnection(connection,null,ps,null);
}
}
// 查看当前管理员信息
public static Manager getManager(int mid) {
Connection connection = null;
PreparedStatement ps = null;
Manager manager = null;
try {
connection = DButil.getConnection();
String sql = "select * from manager where mid=?";
ps = connection.prepareStatement(sql);
ps.setInt(1,mid);
ResultSet rs = ps.executeQuery();
if(rs.next()){
manager = new Manager(rs.getInt("mid"),rs.getString("mname"),rs.getString("mphone"),rs.getString("mcard"),rs.getString("mpass"),rs.getString("mgender"),rs.getString("mobj"),rs.getString("maddress"));
}
return manager;
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally {
DButil.closeConnection(connection,null,ps,null);
}
}
// 查看所有管理员信息
public static void getAllManager() {
Connection connection = null;
PreparedStatement ps = null;
try {
connection = DButil.getConnection();
String sql = "select * from manager";
ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next()){
Manager manager = new Manager(rs.getInt("mid"),rs.getString("mname"),rs.getString("mphone"),rs.getString("mcard"),rs.getString("mpass"),rs.getString("mgender"),rs.getString("mobj"),rs.getString("maddress"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally {
DButil.closeConnection(connection,null,ps,null);
}
}
// 删除管理员
public static int deleteManager(int mid) {
Connection connection = null;
PreparedStatement ps = null;
try {
connection = DButil.getConnection();
String sql = "delete from manager where mid=?";
ps = connection.prepareStatement(sql);
ps.setInt(1,mid);
return ps.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally {
DButil.closeConnection(connection,null,ps,null);
}
}
}老板
老板的业务逻辑层，有登录，查看余额的功能
package service;
import util.DButil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author zhutouaichirou
*/
public class ServiceForBoss {
// 登录
public static int loginByCard(String bcard,String bpass) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DButil.getConnection();
String sql = "select * from boss where bosscard=? and bosspass=?;";
ps = DButil.getConnection().prepareStatement(sql);
ps.setString(1, bcard);
ps.setString(2, bpass);
rs = ps.executeQuery();
if(rs.next()){
return 1;
}else{
return 0;
}
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}finally{
DButil.closeConnection(conn, null, ps, null);
}
}
// 查看余额
public static double getBalance() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
double balance = 0;
try {
conn = DButil.getConnection();
String sql = "select * from boss;";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
balance = rs.getDouble("balance");
}
} catch (SQLException | ClassNotFoundException e) {
throw new RuntimeException(e);
} finally {
DButil.closeConnection(conn, null, ps, rs);
}
return balance;
}
}测试
以下是测试类，本来打算将所有的功能整合到一起然后重新新建一个test类的，但是尝试之后发现这样做不仅麻烦而且会出现一些异常，所以我索性就直接将所有的功能以及测试全放到一个类里面了
package test;
import entity.*;
import service.*;
import java.util.Scanner;
/**
* @author zhutouaichirou
*/
public class BookManage {
public static void main(String[] args) {
int nowUid=0;
int nowMid=0;
String back;
boolean flag = true;
boolean u=false;
boolean m=false;
boolean b=false;
int lock=123456;
do{
// 主界面
System.out.println("1.注册");
System.out.println("2.登录");
System.out.println("3.退出");
Scanner sc=new Scanner(System.in);
System.out.println("请输入你的选择:");
int i1=sc.nextInt();
switch (i1){
case 1:
// 注册页面
System.out.println("1.注册管理员");
System.out.println("2.注册用户");
int i2 = sc.nextInt();
switch (i2) {
case 1:
// 管理员注册需要密钥
System.out.println("请输入管理员密钥");
int key = sc.nextInt();
if (key == lock) {
System.out.println("请输入姓名");
String mname = sc.next();
System.out.println("请输入电话");
String mphone = sc.next();
System.out.println("请输入身份证");
String mcard = sc.next();
System.out.println("请输入密码");
String mpass = sc.next();
System.out.println("请输入性别");
String mgender = sc.next();
System.out.println("请输入专业");
String mobj = sc.next();
System.out.println("请输入地址");
String maddress = sc.next();
Manager manager = new Manager(mname, mphone, mcard, mpass, mgender, mobj, maddress);
int register = ServiceForManager.registerManager(manager);
if (register!=0) {
System.out.println("注册成功");
} else {
System.out.println("注册失败");
}
break;
} else {
System.out.println("密钥错误");
break;
}
case 2:
// 用户注册界面
System.out.println("请输入姓名");
String uname = sc.next();
System.out.println("请输入电话");
String uphone = sc.next();
System.out.println("请输入身份证");
String ucard = sc.next();
System.out.println("请输入密码");
String upass = sc.next();
System.out.println("请输入性别");
String ugender = sc.next();
System.out.println("请输入专业");
String uobj = sc.next();
System.out.println("请输入地址");
String uaddress = sc.next();
User user = new User(uname, uphone, ucard, upass, ugender, uobj, uaddress);
int register = ServiceForUser.registerUser(user);
if (register !=0) {
System.out.println("注册成功");
break;
} else {
System.out.println("注册失败");
break;
}
default:
System.out.println("输入有误，请重新输入");
break;
}
break;
case 2:
// 登录界面
System.out.println("1.管理员登录");
System.out.println("2.用户登录");
System.out.println("3.老板登录");
int i3 = sc.nextInt();
switch (i3) {
case 1:
// 管理员登录界面
System.out.println("请选择身份证登录还是电话登录");
System.out.println("1.身份证登录");
System.out.println("2.电话登录");
int i4 = sc.nextInt();
switch (i4) {
// 管理员使用身份证登录
case 1:
System.out.println("请输入身份证");
String mcard = sc.next();
System.out.println("请输入密码");
String mpassbycard = sc.next();
nowMid = ServiceForManager.loginByCard(mcard, mpassbycard);
if (nowMid != 0) {
System.out.println("登录成功");
m = true;
u = false;
b = false;
} else {
System.out.println("登录失败");
}
break;
case 2:
// 管理员使用电话登录
System.out.println("请输入电话");
String mphone = sc.next();
System.out.println("请输入密码");
String mpassbyphone = sc.next();
nowMid = ServiceForManager.loginByPhone(mphone, mpassbyphone);
if (nowMid != 0) {
System.out.println("登录成功");
Manager manager = new Manager();
nowMid = manager.getMid();
m = true;
u = false;
b = false;
break;
} else {
System.out.println("登录失败");
break;
}
default:
System.out.println("输入有误，请重新输入");
break;
}
break;
case 2:
// 用户登录界面
System.out.println("请选择身份证登录还是电话登录");
System.out.println("1.身份证登录");
System.out.println("2.电话登录");
int i5 = sc.nextInt();
switch (i5) {
case 1:
// 用户使用身份证登录
System.out.println("请输入身份证");
String ucard = sc.next();
System.out.println("请输入密码");
String upassbycard = sc.next();
nowUid = ServiceForUser.loginByCard(ucard, upassbycard);
if (nowUid != 0) {
System.out.println("登录成功");
System.out.println(nowUid);
m = false;
u = true;
b = false;
break;
} else {
System.out.println("登录失败");
break;
}
case 2:
// 用户使用电话登录
System.out.println("请输入电话");
String uphone = sc.next();
System.out.println("请输入密码");
String upassbyphone = sc.next();
nowUid = ServiceForUser.loginByPhone(uphone, upassbyphone);
if (nowUid != 0) {
System.out.println("登录成功");
User user = new User();
nowUid = user.getUid();
m = false;
u = true;
b = false;
break;
} else {
System.out.println("登录失败");
break;
}
default:
System.out.println("输入有误，请重新输入");
break;
}
break;
case 3:
System.out.println("请输入老板卡号");
String bosscard = sc.next();
System.out.println("请输入密码");
String bosspass = sc.next();
nowMid = ServiceForBoss.loginByCard(bosscard, bosspass);
if (nowMid != 0) {
System.out.println("登录成功");
m = false;
u = false;
b = true;
break;
} else {
System.out.println("登录失败");
break;
}
default:
System.out.println("输入有误，请重新输入");
break;
}
if(u){
boolean use=true;
do{
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("请选择：");
int i6 = sc.nextInt();
switch (i6) {
case 1:
ServiceForBook.getAllBook();
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
use=false;
}
break;
case 2:
System.out.println("请输入图书编号");
int bid = sc.nextInt();
int borrow = ServiceForBook.borrowBook(bid, nowUid);
if (borrow != 0) {
System.out.println("借阅成功");
} else {
System.out.println("借阅失败");
}
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
use=false;
}
break;
case 3:
System.out.println("请输入图书编号");
int bid1 = sc.nextInt();
int returnBook = ServiceForBook.returnBook(bid1, nowUid);
if (returnBook != 0) {
System.out.println("归还成功");
} else {
System.out.println("归还失败");
}
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
use=false;
}
break;
case 4:
System.out.println("请输入图书编号");
int bid2 = sc.nextInt();
int buy = ServiceForBook.boughtBook(bid2, nowUid);
if (buy != 0) {
System.out.println("购买成功");
} else {
System.out.println("购买失败");
}
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
use=false;
}
break;
case 5:
ServiceForUser.getUser(nowUid);
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
use=false;
}
break;
case 6:
System.out.println("请输入用户姓名");
String uname = sc.next();
System.out.println("请输入用户电话");
String uphone1 = sc.next();
System.out.println("请输入用户身份证");
String ucard1 = sc.next();
System.out.println("请输入用户密码");
String upass1 = sc.next();
System.out.println("请输入用户性别");
String ugender1 = sc.next();
System.out.println("请输入用户对象");
String uobj1 = sc.next();
System.out.println("请输入用户地址");
String uaddress1 = sc.next();
User user1 = new User(uname, uphone1, ucard1, upass1, ugender1, uobj1, uaddress1);
int update = ServiceForUser.updateUser(user1,nowUid);
if (update != 0) {
System.out.println("修改成功");
use=false;
} else {
System.out.println("修改失败");
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
use=false;
}
}
break;
case 7:
use = false;
break;
case 8:
int delete = ServiceForUser.deleteUser(nowUid);
if (delete != 0) {
System.out.println("注销成功");
use = false;
} else {
System.out.println("注销失败");
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
use=false;
}
}
break;
default:
System.out.println("输入有误，请重新输入");
}
}while(use);
} else if (m) {
boolean manage = true;
do{
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("请选择：");
int i7 = sc.nextInt();
switch (i7) {
case 1:
ServiceForBook.getAllBook();
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
manage=false;
}
break;
case 2:
System.out.println("请输入图书名称");
String bname = sc.next();
System.out.println("请输入图书价格");
double price = sc.nextDouble();
System.out.println("请输入图书数量");
int surplus = sc.nextInt();
Book book = new Book(bname, price, surplus);
int insert = ServiceForBook.insertBook(book);
if (insert !=0) {
System.out.println("添加成功");
} else {
System.out.println("添加失败");
}
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
manage=false;
}
break;
case 3:
System.out.println("请输入图书编号");
int bid = sc.nextInt();
System.out.println("请输入图书名称");
String bname1 = sc.next();
System.out.println("请输入图书价格");
double price1 = sc.nextDouble();
System.out.println("请输入图书数量");
int surplus1 = sc.nextInt();
Book book1 = new Book(bid, bname1, price1, surplus1);
int update = ServiceForBook.updateBook(book1);
if (update !=0) {
System.out.println("修改成功");
} else {
System.out.println("修改失败");
}
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
manage=false;
}
break;
case 4:
System.out.println("请输入图书编号");
int bid1 = sc.nextInt();
int delete = ServiceForBook.deleteBook(bid1);
if (delete !=0) {
System.out.println("删除成功");
manage = false;
} else {
System.out.println("删除失败");
}
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
manage=false;
}
break;
case 5:
ServiceForUser.getAllUser();
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
manage=false;
}
break;
case 6:
System.out.println(ServiceForManager.getManager(nowMid));
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
manage=false;
}
break;
case 7:
System.out.println("请输入姓名：");
String mname = sc.next();
System.out.println("请输入电话：");
String mphone = sc.next();
System.out.println("请输入身份证号：");
String mcard = sc.next();
System.out.println("请输入密码：");
String mpass = sc.next();
System.out.println("请输入性别：");
String mgender = sc.next();
System.out.println("请输入职业：");
String mobj = sc.next();
System.out.println("请输入地址：");
String maddress = sc.next();
Manager manager = new Manager(mname, mphone, mcard, mpass, mgender, mobj, maddress);
int managerupdate = ServiceForManager.updateManager(manager,nowMid);
if (managerupdate != 0) {
System.out.println("修改成功");
manage = false;
} else {
System.out.println("修改失败");
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
manage=false;
}
}
break;
case 8:
manage = false;
break;
default:
System.out.println("输入有误，请重新输入");
break;
}
}while(manage);
}else if(b){
boolean master = true;
do{
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("请选择：");
int choose = sc.nextInt();
switch (choose) {
case 1:
ServiceForBook.getAllBook();
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
master=false;
}
break;
case 2:
ServiceForUser.getAllUser();
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
master=false;
}
break;
case 3:
ServiceForManager.getAllManager();
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
master=false;
}
break;
case 4:
System.out.println("请输入管理员编号");
int mid = sc.nextInt();
int delete = ServiceForManager.deleteManager(mid);
if (delete != 0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
master=false;
}
break;
case 5:
System.out.println("请输入用户编号");
int uid = sc.nextInt();
int delete1 = ServiceForUser.deleteUser(uid);
if (delete1 != 0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
master=false;
}
break;
case 6:
System.out.println("还有"+ServiceForBoss.getBalance()+"元");
System.out.println("按0返回到主菜单，按任意键返回上一级");
back = sc.next();
if(back.equals("0")){
master=false;
}
break;
case 7:
master = false;
break;
default:
System.out.println("输入有误，请重新输入");
break;
}
}while(master);
}
break;
case 3:
flag=false;
break;
default:
System.out.println("输入有误，请重新输入");
break;
}
}while(flag);
}
}小结
相比于我之前做的那个使用数组完成的图书馆管理系统，使用数据库就好了很多，节省了很多不必要的麻烦，而且数据不是固定的，可以随时添加或删除，这段时间也尝试过使用对象，使用集合等方式重新写图书管理系统，但是都有或多或少的麻烦且数据大多数情况下都数据固定，数据超过了固定的数据量就无法继续添加，数据比较少的话没问题，但要存储的数据比较大的话就会超出，出现报错，所以我还是发这个使用java连接数据库做的图书管理系统发出来了，这个里面有一个小bug，就是当删除数据时，数据的编号还是有点问题，因为无论是图书的bid，还是用户的uid，亦或者管理员的mid我都设置了AUTO_INCREMENT，即自动增长，但是数据删除之后自动增长的ID数据还是会被占用，然后就会出现数据断层，我也搜了很多的解决方法，其中有一个是ALTER TABLE TABLENAME AUTO_INCREMENT = 1，将1替换成当前删除的ID号就可以了，但是这样又会产生一个新的问题，这个命令只能用于删除最末位的数据，如果删除的数据不是最末位就会造成ID从当前删除的位置开始重新增长，但ID号是主键，具有唯一性，这样会造成与后面的ID号重复，会造成报错，故而我就没有使用这条命令了，但这个bug不会影响该系统的整体使用
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
