该功能用到的软件为 IDEA 、Navicat 、云服务器（非必须）
源码下载
https://www.aliyundrive.com/s/UTz8pNxobGK
一、建立数据库
在自己的服务器或者电脑本机安装数据库系统，本次系统演示的数据库版本为5.6。
1.创建图书管理数据库library 字符集为：utf8 -utf8_general_ci
/*
Navicat Premium Data Transfer
Source Server : local
Source Server Type : MySQL
Source Server Version : 50739
Source Host : 121.37.205.242:3306
Source Schema : library
Target Server Type : MySQL
Target Server Version : 50739
File Encoding : 65001
Date: 31/01/2023 11:23:05
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for amerce
-- ----------------------------
DROP TABLE IF EXISTS `amerce`;
CREATE TABLE `amerce` (
`rnumber` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '读者编号',
`rname` varchar(35) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '读者名字',
`bnumber` int(11) DEFAULT NULL COMMENT '图书编号',
`bname` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书名称',
`quantity` int(11) DEFAULT 0 COMMENT '数量',
`bstime` datetime(0) DEFAULT NULL COMMENT '还书和借书时间',
`money` int(11) DEFAULT NULL COMMENT '罚款金额'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '罚款信息表' ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for books
-- ----------------------------
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
`number` int(11) NOT NULL AUTO_INCREMENT COMMENT '图书编号',
`name` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书名称',
`category` int(11) DEFAULT NULL COMMENT '图书类别',
`author` varchar(35) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '作者',
`press` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '出版社',
`pdate` date DEFAULT NULL COMMENT '出版日期',
`wdate` datetime(0) DEFAULT NULL COMMENT '入库日期',
`books` int(11) DEFAULT NULL COMMENT '库存总数量',
`extant` int(11) DEFAULT 0 COMMENT '现存数量',
PRIMARY KEY (`number`) USING BTREE,
UNIQUE INDEX `books_name`(`name`) USING BTREE,
INDEX `books_category`(`category`) USING BTREE,
CONSTRAINT `books_category` FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 214 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '图书信息表' ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for borrow
-- ----------------------------
DROP TABLE IF EXISTS `borrow`;
CREATE TABLE `borrow` (
`rnumber` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '读者编号',
`bnumber` int(11) DEFAULT NULL COMMENT '图书编号',
`quantity` int(11) DEFAULT 1 COMMENT '数量',
`btime` datetime(0) DEFAULT NULL COMMENT '借书时间'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '借书信息表' ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for category
-- ----------------------------
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '类别ID',
`name` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '类别名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 51 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '图书类别表' ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for reader
-- ----------------------------
DROP TABLE IF EXISTS `reader`;
CREATE TABLE `reader` (
`number` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',
`name` varchar(35) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '读者名字',
`sex` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`kind` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '读者种类',
`rdate` datetime(0) DEFAULT NULL COMMENT '登记时间',
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`number`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '读者信息表' ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for still
-- ----------------------------
DROP TABLE IF EXISTS `still`;
CREATE TABLE `still` (
`rnumber` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '读者编号',
`bnumber` int(11) NOT NULL COMMENT '图书编号',
`quantity` int(11) DEFAULT 1 COMMENT '数量',
`stime` datetime(0) DEFAULT NULL COMMENT '还书时间'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '还书信息表' ROW_FORMAT = Compact;
-- ----------------------------
-- View structure for view_aa
-- ----------------------------
DROP VIEW IF EXISTS `view_aa`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `view_aa` AS select `b`.`rnumber` AS `rnumber`,`r`.`name` AS `rname`,`b`.`bnumber` AS `bnumber`,`bs`.`name` AS `bname`,`b`.`btime` AS `bstime` from ((`borrow` `b` join `books` `bs`) join `reader` `r`) where ((`b`.`rnumber` = `r`.`number`) and (`b`.`bnumber` = `bs`.`number`));
-- ----------------------------
-- Procedure structure for Bookreturn
-- ----------------------------
DROP PROCEDURE IF EXISTS `Bookreturn`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `Bookreturn`(in Borrowid char(20),in bookid int ,quant int)
begin
declare cone int default 0;
set cone =(select quantity from borrow
where rnumber =Borrowid and bnumber=bookid and quantity>0
ORDER BY btime limit 1)-quant;
-- 向还书表插入信息
INSERT INTO still(rnumber,bnumber,quantity,stime)
VALUES(Borrowid,bookid,quant,now());
-- 图书表数量+还书的数量
update books set extant=extant+quant
where number=bookid ;
-- 还书后减去对应的数量
update borrow set quantity=quantity-quant
where rnumber =Borrowid and bnumber=bookid
ORDER BY btime limit 1;
-- while循环，如果借书表数值为负数，则循环减去正数的数值
WHILE cone<0 DO
set cone=cone+1;
update borrow set quantity =quantity-1
where quantity>0 and rnumber =Borrowid and bnumber=bookid
ORDER BY btime limit 1;
update borrow set quantity=quantity+1
WHERE quantity<0 and rnumber =Borrowid and bnumber=bookid
ORDER BY btime limit 1;
END WHILE;
-- 把等于0的数值删除
delete from borrow
where quantity=0;
end
;;
delimiter ;
-- ----------------------------
-- Procedure structure for Borrowbooks
-- ----------------------------
DROP PROCEDURE IF EXISTS `Borrowbooks`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `Borrowbooks`(Borrowid char(20),bookid int ,quant int)
begin
INSERT INTO borrow(rnumber,bnumber,quantity,btime)
VALUES(Borrowid,bookid,quant,now());
-- update borrow set quantity=quantity+quant WHERE Borrowid=rnumber and bookid=bnumber;
update books set extant=extant-quant
where number=bookid;
end
;;
delimiter ;
-- ----------------------------
-- Procedure structure for Insertbooks
-- ----------------------------
DROP PROCEDURE IF EXISTS `Insertbooks`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `Insertbooks`(b varchar(40),c int ,d varchar(35),e varchar(40),f date,h int)
begin
INSERT INTO books (number,`name`,category,author,press,pdate,wdate,books)
VALUES (null,b,c,d,e,f,now(),h) ON DUPLICATE KEY UPDATE books=books+h;
update books set extant = extant+h where `name`= b;
end
;;
delimiter ;
-- ----------------------------
-- Procedure structure for proc_2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_2`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_2`()
BEGIN
drop view if exists view_aa;
create view view_aa as
select b.rnumber,r.`name` rname,b.bnumber,bs.`name` bname,b.btime as bstime from borrow b,books bs,reader r
where b.rnumber=r.number and b.bnumber=bs.number;
insert into amerce(rnumber,rname,bnumber,bname,money,bstime)
select rnumber,rname,bnumber,bname,count(*) as money,bstime from view_aa;
end
;;
delimiter ;
-- ----------------------------
-- Procedure structure for repayment
-- ----------------------------
DROP PROCEDURE IF EXISTS `repayment`;
delimiter ;;
CREATE DEFINER=`root`@`%` PROCEDURE `repayment`(rnum char(20),bnum int ,quant int)
begin
declare cone int default 0;
-- 减去对应的数量
update amerce set quantity=quantity-quant
where rnumber = rnum and bnumber = bnum
ORDER BY bstime limit 1;
-- 把负数赋值给cone
select quantity into cone from amerce where quantity<0;
-- while循环，如果cone为负数，则循环减去正数的数值
WHILE cone<0 DO
set cone=cone+1;
-- 钱数 money-(money/quantity)
update amerce set money=money-(money/quantity)
where quantity>0 and rnumber = rnum and bnumber = bnum
ORDER BY bstime limit 1;
-- 正数 -1
update amerce set quantity =quantity-1
where quantity>0 and rnumber = rnum and bnumber = bnum
ORDER BY bstime limit 1;
-- 负数 +1
update amerce set quantity=quantity+1
WHERE quantity<0 and rnumber = rnum and bnumber = bnum
ORDER BY bstime limit 1;
END WHILE;
-- 把等于0的数值删除
delete from amerce where quantity=0;
end
;;
delimiter ;
-- ----------------------------
-- Procedure structure for updateMyTest
-- ----------------------------
DROP PROCEDURE IF EXISTS `updateMyTest`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateMyTest`(quant int)
BEGIN
declare cone int default 0;
set cone =(select quantity from borrow
where quantity>0
ORDER BY btime limit 1)-quant;
SELECT * from borrow where quantity=cone;
-- WHILE cone<0 DO
-- set cone=cone+1;
-- update borrow set quantity =quantity-1
-- where quantity>0
-- ORDER BY btime limit 1;
-- update borrow set quantity=quantity+1
-- WHERE quantity<0
-- ORDER BY btime limit 1;
-- END WHILE;
end
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table still
-- ----------------------------
DROP TRIGGER IF EXISTS `trigger_still_amerce`;
delimiter ;;
CREATE DEFINER = `root`@`localhost` TRIGGER `trigger_still_amerce` BEFORE INSERT ON `still` FOR EACH ROW -- 行级触发器
BEGIN
replace into amerce(rnumber,rname,bnumber,bname,quantity,bstime,money)
select borrow.rnumber rnumber,reader.name rname,
borrow.bnumber bnumber,books.name bname,
borrow.quantity quantity,reader.rdate bstime,
0.1*datediff(now(),borrow.btime)-30 money
from borrow,reader,books
where borrow.rnumber=reader.number and borrow.bnumber=books.number
and datediff(now(),borrow.btime)>=30
and books.number=borrow.bnumber;
END
;;
delimiter ;
SET FOREIGN_KEY_CHECKS = 1;
2.设计好将要使用的sql语句
视图、存储过程和触发器，在上面创建数据库的时候建好了，下面是一下视图、存储过程和触发器的创建语句。
-- 1、添加图书表书籍 -- 完成
CREATE UNIQUE index books_name on books(name);
delimiter //
create procedure Insertbooks(b varchar(40),c int ,d varchar(35),e varchar(40),f date,h int)
begin
INSERT INTO books (number,`name`,category,author,press,pdate,wdate,books)
VALUES (null,b,c,d,e,f,now(),h) ON DUPLICATE KEY UPDATE books=books+h;
update books set extant = extant+h where `name`= b;
end; //
delimiter ;
call Insertbooks('白鹿原','1','张三','人民出版社','2022-1-9','7');
-- 2、添加图书类别信息 -- 完成
INSERT INTO category()
VALUES(1,'话剧');
-- 3、添加读者信息 -- 完成
INSERT INTO reader(number,name,sex,kind,rdate)
VALUES('20215101020051','翠花','女','学生',now());
-- 4、读者借书 -- 完成
delimiter //
create procedure Borrowbooks(Borrowid char(20),bookid int ,quant int)
begin
INSERT INTO borrow(rnumber,bnumber,quantity,btime)
VALUES(Borrowid,bookid,quant,now());
-- update borrow set quantity=quantity+quant WHERE Borrowid=rnumber and bookid=bnumber;
update books set extant=extant-quant
where number=bookid;
end ; //
delimiter ;
Call Borrowbooks('20215101020051','203','50');
-- 5、读者还书 -- 完成
delimiter //
create procedure Bookreturn(in Borrowid char(20),in bookid int ,quant int)
begin
declare cone int default 0;
set cone =(select quantity from borrow
where rnumber =Borrowid and bnumber=bookid and quantity>0
ORDER BY btime limit 1)-quant;
-- 向还书表插入信息
INSERT INTO still(rnumber,bnumber,quantity,stime)
VALUES(Borrowid,bookid,quant,now());
-- 图书表数量+还书的数量
update books set extant=extant+quant
where number=bookid ;
-- 还书后减去对应的数量
update borrow set quantity=quantity-quant
where rnumber =Borrowid and bnumber=bookid
ORDER BY btime limit 1;
-- while循环，如果借书表数值为负数，则循环减去正数的数值
WHILE cone<0 DO
set cone=cone+1;
update borrow set quantity =quantity-1
where quantity>0 and rnumber =Borrowid and bnumber=bookid
ORDER BY btime limit 1;
update borrow set quantity=quantity+1
WHERE quantity<0 and rnumber =Borrowid and bnumber=bookid
ORDER BY btime limit 1;
END WHILE;
-- 把等于0的数值删除
delete from borrow
where quantity=0;
end ; //
delimiter ;
Call Bookreturn('20215101020051','203','5');
drop procedure Borrowbooks;
drop TRIGGER trigger_still_amerce;
-- 6、读者还书时超过30天罚款 -- 完成
-- 设置还书的时候触发，往罚款表插入数据
delimiter //
CREATE TRIGGER trigger_still_amerce
BEFORE INSERT ON still FOR EACH ROW -- 行级触发器
BEGIN
replace into amerce(rnumber,rname,bnumber,bname,quantity,bstime,money)
select borrow.rnumber rnumber,reader.name rname,
borrow.bnumber bnumber,books.name bname,
borrow.quantity quantity,reader.rdate bstime,
0.1*datediff(now(),borrow.btime)-30 money
from borrow,reader,books
where borrow.rnumber=reader.number and borrow.bnumber=books.number
and datediff(now(),borrow.btime)>=30
and books.number=borrow.bnumber;
END; //
delimiter ;
show TRIGGERS;
-- 7、图书废弃 -- 完成
update books set books=books-1,extant=extant-1 where number='202';
-- 8、图书下架 -- 完成
delete from books where number='202';
3.创建项目
在IEDA中新建一个空项目 library
创建软件包 Module
创建三个类， JavaOK、JavaTest、JDBCUtils 。JavaOK=>运行语句 ， JavaTest=> 存放方法 ， JOBCUtils =>存放方法
4.导入mysql包（必要）
下载包：mysql网站：MySQL :: Download Connector/J
下拉选择Platform Independent
下载mysql-connector-j-8.0.32.tar.gz
点击Download下载
点击No thanks, just start my download.//不登陆下载
解压后就可以看到mysql-connector-j-8.0.32.jar，只有3m大小
或者可以在阿里云盘下载
Java连接数据库实现图书馆…系统（详细教程）阿里云盘分享
在IEDA中导入刚刚下载的包：
文件-项目结构-模块-点击加号-1.JAR或目录-上传刚刚下载的jar包
导包工作完成，接下来可以正式编写代码了
5.测试连接数据库
先来一个简单的测试，看是否能成功连接
package com.moyida.linkage;
import java.sql.Connection;
import java.sql.DriverManager;
public class test {
public static void main(String[] args) {
Connection con;
//
String url = "jdbc:mysql://localhost:3306/library";
String user = "root";
String password = "root";
try {
//连接数据库,获得连接对象
con = DriverManager.getConnection(url, user, password);
if (!con.isClosed())
System.out.println("成功连接数据库");
} catch (Exception e) {
e.printStackTrace();
System.out.println("连接失败");
}
}
}运行栏显示”成功连接数据库“则表示连接成功了
如果显示”连接失败“，则没有连接成功，检查上述的步骤是否遗漏
测试完后可以将代码删除
6.JOBCUtili 页面
里边存入释放资源的方法
//释放资源
public static void close(Connection connection, Statement statement, ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
resultSet = null;
}
if (statement != null) {
statement.close();
statement = null;
}
if (connection != null) {
connection.close();
connection = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}7.JavaTest 页面
这里先写好数据库连接方法，修改的时候数据库的时候只要修改一处就好了
ip地址修改成自己的
static final String DB_URL = "jdbc:mysql://localhost:3306/library";
static final String USER = "root"; //账号
static final String PASS = "root"; //密码这里为了省事儿，我把JavaTest页面的所有代码都放到这里
把IP地址和账号密码给成自己的就可以用了，根据方法可以看出连接数据库和执行sql语句的原理，用上面的main方法可以测试这些方法的可行性。
package com.moyida.linkage;
import java.lang.constant.Constable;
import java.sql.*;
public class JavaTest {
public static void main(String[] args) {
//查询用户信息
// query_data("20215154515454", "456");
//查询登录信息
// System.out.println(UserType("20215154515454", "456"));
//插入书籍到图书信息表
// System.out.println("书名，类别，作者，出版社，出版日期，数量");
// AddBooks("高原", "1", "小儿", "中华出版社", "2022-1-9", "5");
//添加类别
// AddCategories(5, "神话类");
//3、添加读者信息
// AddReaders("20544545446546", "劳务", "女", "2", "1112222");
//4、读者借书
// Call Borrowbooks('20215101020051','203','3');
// BorrowBooks("20215101020051","203","3");
//5、读者还书
// AlsoBook("20215101020051","203","2");
//-- 7、图书废弃
//update books set books=books-1,extant=extant-1 where number='202';
// BooksAbandoned("203");
//-- 8、图书下架
//delete from books where number='202';
// Undercarriage("203");
//查询图书信息
// System.out.println(BookInformation("205"));
// AllBookInformation();
//欠费查询
// System.out.println(ArrearageTable("20215101020051", "203"));
//删除罚款账单
// repayment("20215154515454","210");
//查询读者信息表有无该账号
// System.out.println(ReaderTable("2021510102005"));
// 打印读者信息
// PrintReader("20215101024620");
}
static final String DB_URL = "jdbc:mysql://localhost:3306/library";
static final String USER = "root";
static final String PASS = "root";
/**
* 图书下架
*
* @param number
* @return
*/
public static boolean Undercarriage(String number) {
Connection conn = null;
Statement stmt = null;
try {
//连接数据库,获得连接对象
conn = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
stmt = conn.createStatement();
//执行sql语句，得到结果集 resultSet
String sql = "delete from books where number='" + number + "'";
System.out.println(sql);
int resultSet = stmt.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}//释放资源
finally {
JDBCUtils.close(conn, stmt, null);
}
return false;
}
/**
* 图书废弃
*
* @param number
* @return
*/
public static String BooksAbandoned(String number) {
Connection connection = null;
Statement statement = null;
try {
//连接数据库,获得连接对象
connection = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
statement = connection.createStatement();
//执行sql语句，得到结果集 resultSet
String sql = "update books set books=books-1,extant=extant-1 where number='" + number + "'";
int resultSet = statement.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}//释放资源
finally {
JDBCUtils.close(connection, statement, null);
}
return number;
}
/**
* 读者还书
*
* @param rnumber 读者编号
* @param bnumber 图书编号
* @param quantity 数量
*/
static void AlsoBook(String rnumber, String bnumber, String quantity) {
Connection connection = null;
Statement statement = null;
try {
//连接数据库,获得连接对象
connection = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
statement = connection.createStatement();
//执行sql语句，得到结果集 resultSet
String sql = "Call Bookreturn(" + "'" + rnumber + "'" + "," + "'" + bnumber + "'" + "," + "'" + quantity + "'" + ")";
System.out.println(sql);
int resultSet = statement.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}//释放资源
finally {
JDBCUtils.close(connection, statement, null);
}
}
/**
* 欠费罚款查询
*
* @param rnumber
* @param bnumber
* @return
*/
public static String ArrearageTable(String rnumber, String bnumber) {
Connection connection = null;
Statement statement = null;
try {
//连接数据库,获得连接对象
connection = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
statement = connection.createStatement();
//执行sql语句，得到结果集 resultSet
String sql = "SELECT sum(money) as arrearage FROM amerce WHERE rnumber =" + rnumber + " and bnumber= " + bnumber + " ORDER BY quantity";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String a = resultSet.getString("arrearage");
return a;
}
// 完成后关闭
resultSet.close();
statement.close();
connection.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
} finally {
// 关闭资源
try {
if (statement != null) statement.close();
} catch (SQLException se2) {
}// 什么都不做
try {
if (connection != null) connection.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
return null;
}
/**
* 通过学号打印借书信息表
*
* @param rnumber 读者编号
*/
public static void NumberBookTable(String rnumber) {
Connection conn = null;
Statement stmt = null;
try {
//连接数据库,获得连接对象
conn = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
stmt = conn.createStatement();
String sql = "SELECT * FROM borrow WHERE rnumber =" + rnumber;
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.print(rs.getString("rnumber"));
System.out.print("\t ,");
System.out.print(rs.getString("bnumber"));
System.out.print("\t ,");
System.out.print(rs.getString("quantity"));
System.out.print("\t ,");
System.out.println(rs.getString("btime"));
}
// 完成后关闭
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
} finally {
// 关闭资源
try {
if (stmt != null) stmt.close();
} catch (SQLException se2) {
}// 什么都不做
try {
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
/**
* 打印借书信息表
*
* @param rnumber 读者编号
* @param bnumber 书籍编号
*/
public static void BookTable(String rnumber, String bnumber) {
Connection conn = null;
Statement stmt = null;
try {
//连接数据库,获得连接对象
conn = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
stmt = conn.createStatement();
String sql = "SELECT * FROM borrow WHERE rnumber =" + rnumber + " and bnumber= " + bnumber;
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.print(rs.getString("rnumber"));
System.out.print("\t ,");
System.out.print(rs.getString("bnumber"));
System.out.print("\t ,");
System.out.print(rs.getString("quantity"));
System.out.print("\t ,");
System.out.println(rs.getString("btime"));
}
// 完成后关闭
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
} finally {
// 关闭资源
try {
if (stmt != null) stmt.close();
} catch (SQLException se2) {
}// 什么都不做
try {
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
/**
* 删除罚款账单
*
* @param rnumber
* @param bnumber
*/
//delete amerce WHERE rnumber='20215154515454' and bnumber='210'
public static void repayment(String rnumber, String bnumber) {
Connection connection = null;
Statement statement = null;
try {
//连接数据库,获得连接对象
connection = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
statement = connection.createStatement();
//执行sql语句，得到结果集 resultSet
String sql = "delete from amerce WHERE rnumber=" + rnumber + " and bnumber=" + bnumber;
int resultSet = statement.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}//释放资源
finally {
JDBCUtils.close(connection, statement, null);
}
}
/**
* 读者借书
*
* @param rnumber 读者编号
*/
public static void BorrowBooks(String rnumber, String bnumber, String quantity) {
Connection connection = null;
Statement statement = null;
try {
//连接数据库,获得连接对象
connection = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
statement = connection.createStatement();
//执行sql语句，得到结果集 resultSet
String sql = "Call Borrowbooks(" + "'" + rnumber + "'" + "," + "'" + bnumber + "'" + "," + "'" + quantity + "'" + ")";
System.out.println(sql);
int resultSet = statement.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}//释放资源
finally {
JDBCUtils.close(connection, statement, null);
}
}
/**
* 添加读者信息（注册）
*
* @param number
* @param name
* @param sex
* @param kind
* @param password
*/
public static void AddReaders(String number, String name, String sex, String kind, String password) {
Connection connection = null;
Statement statement = null;
try {
//连接数据库,获得连接对象
connection = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
statement = connection.createStatement();
//执行sql语句，得到结果集 resultSet
// // INSERT INTO reader(number,name,sex,kind,rdate) VALUES('20215101020051','翠花','女','学生',now(),password);
String sql = "INSERT INTO reader VALUES(" + "'" + number + "'" + "," + "'" + name + "'" + "," + "'" + sex + "'" + "," + "'" + kind + "'" + "," + "now()" + "," + "'" + password + "'" + ")";
int resultSet = statement.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}//释放资源
finally {
JDBCUtils.close(connection, statement, null);
}
}
/**
* 2、添加图书类别信息
*
* @param id
* @param name
* @return
*/
public static String AddCategories(int id, String name) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//连接数据库,获得连接对象
connection = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
statement = connection.createStatement();
//执行sql语句，得到结果集 resultSet
String sql = "INSERT INTO category() VALUES(" + id + "," + "'" + name + "'" + ")";
resultSet = statement.executeQuery(sql);
// int resultSet = statement.executeUpdate(sql);
// 完成后关闭
resultSet.close();
statement.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}//释放资源
finally {
JDBCUtils.close(connection, statement, resultSet);
}
return name;
}
/**
* 添加书籍到图书信息表
*
* @param name 图书名称
* @param category 图书类别
* @param author 作者
* @param press 出版社
* @param pdate 出版日期
* @param quantity 要存的数量
* @return
*/
public static String AddBooks(String name, String category, String author, String press, String pdate, String quantity) {
Connection connection = null;
ResultSet resultSet = null;
Statement statement = null;
try {
//连接数据库,获得连接对象
connection = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
statement = connection.createStatement();
//执行sql语句，得到结果集 resultSet
String sql = "call Insertbooks(" + "'" + name + "'" + "," + "'" + category + "'" + "," + "'" + author + "'" + "," + "'" + press + "'" + "," + "'" + pdate + "'" + "," + "'" + quantity + "'" + ")";
resultSet = statement.executeQuery(sql);
// 完成后关闭
resultSet.close();
statement.close();
connection.close();
} catch (Exception e) {
//错误处理
e.printStackTrace();
}//释放资源
finally {
JDBCUtils.close(connection, statement, resultSet);
}
return name;
}
/**
* 查询所有图书信息
*
* @return 返回值
*/
public static void AllBookInformation() {
Connection conn = null;
Statement stmt = null;
try {
//连接数据库,获得连接对象
conn = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
stmt = conn.createStatement();
String sql = "SELECT * FROM books";
ResultSet rs = stmt.executeQuery(sql);
System.out.println("图书编号 图书名称 图书类别 作者 出版社 出版日期 入库日期 库存总数量 现存数量");
while (rs.next()) {
System.out.print(rs.getString("number") + "\t");
System.out.print(rs.getString("name") + "\t");
System.out.print(rs.getString("category") + "\t");
System.out.print(rs.getString("author") + "\t");
System.out.print(rs.getString("press") + "\t");
System.out.print(rs.getString("pdate") + "\t");
System.out.print(rs.getString("wdate") + "\t");
System.out.print(rs.getString("books") + "\t");
System.out.println(rs.getString("extant"));
}
// 完成后关闭
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
} finally {
// 关闭资源
try {
if (stmt != null) stmt.close();
} catch (SQLException se2) {
}// 什么都不做
try {
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
/**
* 打印读者信息
*/
public static void PrintReader(String number){
Connection conn = null;
Statement stmt = null;
try {
//连接数据库,获得连接对象
conn = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
stmt = conn.createStatement();
String sql = "SELECT * FROM reader WHERE number =" + number;
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.print(rs.getString("number")+"\t");
System.out.print(rs.getString("name")+"\t");
System.out.print(rs.getString("sex")+"\t");
System.out.print(rs.getString("kind")+"\t");
System.out.print(rs.getString("rdate")+"\t");
System.out.println(rs.getString("password"));
}
// 完成后关闭
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
} finally {
// 关闭资源
try {
if (stmt != null) stmt.close();
} catch (SQLException se2) {
}// 什么都不做
try {
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
/**
* 查询读者信息表有无该账号
*
* @return
*/
public static Constable ReaderTable(String number) {
Connection conn = null;
Statement stmt = null;
try {
//连接数据库,获得连接对象
conn = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
stmt = conn.createStatement();
String sql = "select number from reader where number=" + number;
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
return false;
}
// 完成后关闭
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
} finally {
// 关闭资源
try {
if (stmt != null) stmt.close();
} catch (SQLException se2) {
}// 什么都不做
try {
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
return null;
}
/**
* 查询图书信息
*
* @param number 输入的密码
* @return 返回值
*/
public static String BookInformation(String number) {
Connection conn = null;
Statement stmt = null;
try {
//连接数据库,获得连接对象
conn = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
stmt = conn.createStatement();
String sql = "SELECT * FROM books WHERE number =" + number;
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
String a = rs.getString("number");
String a1 = ("\t");
String b = rs.getString("name");
String b1 = ("\t");
String c = rs.getString("category");
String c1 = ("\t");
String d = rs.getString("author");
String d1 = ("\t");
String e = rs.getString("press");
String e1 = ("\t");
String f = rs.getString("pdate");
String f1 = ("\t");
String g = rs.getString("wdate");
String g1 = ("\t");
String h = rs.getString("books");
String h1 = ("\t");
String i = rs.getString("extant");
return a + a1 + b + b1 + c + c1 + d + d1 + e + e1 + f + f1 + g + g1 + h + h1 + i;
}
// 完成后关闭
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
} finally {
// 关闭资源
try {
if (stmt != null) stmt.close();
} catch (SQLException se2) {
}// 什么都不做
try {
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
return null;
}
/**
* 查询学生表信息
*
* @param number 输入的账号
* @param password 输入的密码
* @return 返回值
*/
public static void query_data(String number, String password) {
Connection conn = null;
Statement stmt = null;
try {
//连接数据库,获得连接对象
conn = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
stmt = conn.createStatement();
String sql = "select * from reader where number= " + "'" + number + "'" + " and password=" + "'" + password + "'";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
String a = rs.getString("number");
String a1 = ("\t");
String b = rs.getString("name");
String b1 = ("\t");
String c = rs.getString("sex");
String c1 = ("\t");
String d = rs.getString("kind");
String d1 = ("\t");
String e = rs.getString("rdate");
String e1 = ("\t");
String f = rs.getString("password");
System.out.println(a + a1 + b + b1 + c + c1 + d + e + d1 + f);
}
// 完成后关闭
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
} finally {
// 关闭资源
try {
if (stmt != null) stmt.close();
} catch (SQLException se2) {
}// 什么都不做
try {
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
/**
* 判断用户类型，登录账号，返回1表示老师，返回2表示学生，返回null表示查无此人
*
* @param number 输入的学号
* @return 返回值
*/
public static String UserType(String number, String password) {
Connection connection = null;
Statement statement = null;
try {
//连接数据库,获得连接对象
connection = DriverManager.getConnection(DB_URL, USER, PASS);
//创建执行环境
statement = connection.createStatement();
//执行sql语句，得到结果集 resultSet
String sql = "select kind from reader where number= " + "'" + number + "'" + " and password=" + "'" + password + "'";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String a = resultSet.getString("kind");
return a;
}
// 完成后关闭
resultSet.close();
statement.close();
connection.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch (Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
} finally {
// 关闭资源
try {
if (statement != null) statement.close();
} catch (SQLException se2) {
}// 什么都不做
try {
if (connection != null) connection.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
return null;//这样可以使得调用方无需检查结果是否为null
}
}
8.JavaOK 页面
JavaOK是运行调用JavaTest和JOBCUtili方法的主页面，在这个类运行可以实现系统的管理，这个系统仍有不少的bug没修复，但是可以正常跑起来了，可以根据自己的理解，把代码经行完善和修复
下面是JavaOK页面的代码
package com.moyida.linkage;
import java.lang.constant.Constable;
import java.util.Scanner;
public class JavaOk {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
while (true) {
System.out.println("=============首页==============");
System.out.println("1、登录");
System.out.println("2、注册");
int reg = sc.nextInt();
switch (reg) {
case 1:
//登录
while (true) {
System.out.println("=============登陆页面==============");
System.out.println("请您输入账号");
String acc = sc.next();
System.out.println("请您输入密码");
String pass = sc.next();
String sum = JavaTest.UserType(acc, pass);
if (sum == null) {
System.out.println("对不起，查无此人,请重新输入");
break;
}
// int a = Integer.parseInt(sum);
// String a=sum;
//判断用户类型，登录账号，返回1表示老师，返回2表示学生，返回null表示查无此人
System.out.println("返回的数据是" + sum);
switch (sum) {
case "1":
System.out.println("欢迎管理员登录");
conservator(sc);
break;
case "2":
System.out.println("欢迎学生登录");
operate(sc, acc);
break;
default:
System.out.println("对不起，查无此人");
break;
}
break;
}
case 2:
//注册
Register(sc);
break;
default:
System.out.println("您输入的命令错误，请重新输入！");
break;
}
}
}
/**
* 注册
*
* @param sc
*/
private static void Register(Scanner sc) {
System.out.println("=============注册页面==============");
System.out.println("请输入要注册的账号（学号）");
String number = sc.next();
//查询数据库中有无该账号
Constable aaa = JavaTest.ReaderTable(number);
if (aaa == null) {
//没有账号，可以进行
System.out.println("请输入您的名字");
String name = sc.next();
System.out.println("请输入您的性别");
String sex = sc.next();
System.out.println("请输入您的类型 学生 2/管理员 1");
int kind = sc.nextInt();
System.out.println("请输入您的登录密码：");
String pass = sc.next();
//调用注册方法
JavaTest.AddReaders(number, name, sex, String.valueOf(kind), pass);
System.out.println("注册成功！");
//打印注册信息
System.out.println("您的注册信息如下：");
JavaTest.PrintReader(number);
}else {
System.out.println("该账号已注册!");
}
}
/**
* 管理员操作页面
*
* @param sc
*/
private static void conservator(Scanner sc) {
while (true) {
System.out.println("=============操作页面==============");
System.out.println("0、退出");
System.out.println("1、添加图书表书籍");
System.out.println("2、添加图书类别信息");
System.out.println("3、图书废弃");
System.out.println("4、图书下架");
System.out.println("5、查询全部图书信息");
int i = sc.nextInt();
switch (i) {
case 0:
System.out.println("已退出账号，欢迎再次登录！");
return;
case 1:
System.out.println("=============添加书籍页面==============");
System.out.println("图书名称");
String name = sc.next();
System.out.println("图书类别");
String category = sc.next();
System.out.println("作者");
String author = sc.next();
System.out.println("出版社");
String press = sc.next();
System.out.println("出版日期");
String pdate = sc.next();
System.out.println("要存的数量");
String quantity = sc.next();
String charu = JavaTest.AddBooks(name, category, author, press, pdate, quantity);
System.out.println("添加成功");
break;
case 2:
System.out.println("=============添加图书类别页面==============");
System.out.println("输入类别编号");
int leibie = sc.nextInt();
System.out.println("输入类别名称");
String name1 = sc.next();
JavaTest.AddCategories(leibie, name1);
System.out.println("添加成功");
break;
case 3:
System.out.println("=============图书废弃页面==============");
System.out.println("请输入要废弃图书的图书编号");
String reduce = sc.next();
System.out.println("您要废除的书籍是：");
System.out.println(JavaTest.BookInformation(reduce));
System.out.println("是否确认废除y/n");
String rs = sc.next();
switch (rs) {
case "y":
//可以废除
String ff = JavaTest.BooksAbandoned(reduce);
System.out.println(JavaTest.BookInformation(reduce));
System.out.println("废弃成功，该图书减一");
break;
default:
System.out.println("您取消废除，当前书籍继续保留~");
}
break;
case 4:
System.out.println("=============图书下架页面==============");
System.out.println("请输入要下架图书的图书编号");
String reduce1 = sc.next();
System.out.println("您要下架的书籍是：");
System.out.println(JavaTest.BookInformation(reduce1));
System.out.println("是否确认把该图书下架y/n");
String rs1 = sc.next();
switch (rs1) {
case "y":
//可以废除
String ff = JavaTest.BooksAbandoned(reduce1);
System.out.println(JavaTest.Undercarriage(reduce1));
System.out.println("下架成功，图书已从图书系统中去除");
break;
default:
System.out.println("您取消废除，当前书籍继续保留~");
}
break;
case 5:
//查看所有书籍
JavaTest.AllBookInformation();
break;
default:
System.out.println("输入错误！");
break;
}
}
}
/**
* 学生操作页面
*
* @param sc
*/
private static void operate(Scanner sc, String acc) {
while (true) {
System.out.println("=============学生操作页面==============");
System.out.println("0、退出");
System.out.println("1、查看所有书籍");
System.out.println("2、借书");
System.out.println("3、还书");
String student = sc.next();
switch (student) {
case "0":
System.out.println("已退出账号，欢迎再次登录！");
return;
case "1":
//查看所有书籍
JavaTest.AllBookInformation();
break;
case "2":
//借书
// public static boolean BorrowBooks(String rnumber, String bnumber, String quantity) {
System.out.println("请输入要借的书籍编号");
String Borrow = sc.next();
System.out.println("您要借的书籍是：");
System.out.println(JavaTest.BookInformation(Borrow));
System.out.println("是否借该图书？y/n");
String rs1 = sc.next();
switch (rs1) {
case "y":
//可以借书
System.out.println("您要借几本书？");
String books = sc.next();
JavaTest.BorrowBooks(acc, Borrow, books);
System.out.println("借书完成，您的借书信息如下");
//打印借书信息
JavaTest.BookTable(acc, Borrow);
System.out.println("");
break;
default:
System.out.println("您取消借书操作，欢迎再次使用！");
}
break;
case "3":
//还书
System.out.println("您可以还的书籍如下");
JavaTest.NumberBookTable(acc);
System.out.println("");
System.out.println("请输入要还的书籍编号");
String AlsoBook = sc.next();
System.out.println("您要还的书籍是：");
//打印借书信息
JavaTest.BookTable(acc, AlsoBook);
//再次确定归还
System.out.println("是否还该图书？y/n");
String rs2 = sc.next();
switch (rs2) {
case "y":
//可以还书
System.out.println("您要还此编号的几本书？");
String books = sc.next();
// 还书
JavaTest.AlsoBook(acc, AlsoBook, books);
//查询欠费账单有没有欠费
String bill = JavaTest.ArrearageTable(acc, AlsoBook);
if (bill == null) {
//可以了
System.out.println("还书完成，您还有此类书籍尚为归还");
//打印借书信息
JavaTest.BookTable(acc, AlsoBook);
break;
} else {
//钱没还
while (true) {
System.out.println("您欠费的金额是" + bill);
System.out.println("如已支付，请输入内容：支付完成");
String ff = sc.next();
String dd = "支付完成";
if (ff.equals(dd)) {
//删除欠费账单
JavaTest.repayment(acc, AlsoBook);
//打印借书信息
System.out.println("还书完成，您还有此类书籍尚为归还");
//打印借书信息
JavaTest.BookTable(acc, AlsoBook);
break;
} else {
System.out.println("支付失败，请完成支付");
}
}
}
}
}
}
}
}
9.运行
在IEDA的JavaOK页面，右键点击运行，就可以使用了。
10.资料
我在阿里云盘中存有该系统程序的源代码，可提供下载学习使用。
阿里网盘 链接
Java连接数据库实现图书馆…系统（详细教程）阿里云盘分享
文章出处登录后可见！
已经登录？立即刷新
