该功能用到的软件为 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)begindeclare cone int default 0;set cone =(select quantity from borrowwhere rnumber =Borrowid and bnumber=bookid and quantity>0ORDER BY btime limit 1)-quant;-- 向还书表插入信息INSERT INTO still(rnumber,bnumber,quantity,stime)VALUES(Borrowid,bookid,quant,now());-- 图书表数量+还书的数量update books set extant=extant+quantwhere number=bookid ;-- 还书后减去对应的数量update borrow set quantity=quantity-quantwhere rnumber =Borrowid and bnumber=bookidORDER BY btime limit 1;-- while循环，如果借书表数值为负数，则循环减去正数的数值WHILE cone0 and rnumber =Borrowid and bnumber=bookidORDER BY btime limit 1;update borrow set quantity=quantity+1WHERE quantity<0 and rnumber =Borrowid and bnumber=bookidORDER 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)beginINSERT 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-quantwhere 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)beginINSERT 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,readerr 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)begindeclare cone int default 0;-- 减去对应的数量update amerce set quantity=quantity-quantwhere rnumber = rnum and bnumber = bnumORDER BY bstime limit 1;-- 把负数赋值给coneselect quantity into cone from amerce where quantity<0;-- while循环，如果cone为负数，则循环减去正数的数值WHILE cone0 and rnumber = rnum and bnumber = bnumORDER BY bstime limit 1;-- 正数 -1update amerce set quantity =quantity-1where quantity>0 and rnumber = rnum and bnumber = bnumORDER BY bstime limit 1;-- 负数 +1update amerce set quantity=quantity+1WHERE quantity0ORDER BY btime limit 1)-quant;SELECT * from borrow where quantity=cone;-- WHILE cone0-- ORDER BY btime limit 1;-- update borrow set quantity=quantity+1-- WHERE quantity=30and 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)beginINSERT 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)beginINSERT 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-quantwhere number=bookid;end ; //delimiter ;Call Borrowbooks('20215101020051','203','50');-- 5、读者还书 -- 完成delimiter //create procedure Bookreturn(in Borrowid char(20),in bookid int ,quant int)begindeclare cone int default 0;set cone =(select quantity from borrowwhere rnumber =Borrowid and bnumber=bookid and quantity>0ORDER BY btime limit 1)-quant;-- 向还书表插入信息INSERT INTO still(rnumber,bnumber,quantity,stime)VALUES(Borrowid,bookid,quant,now());-- 图书表数量+还书的数量update books set extant=extant+quantwhere number=bookid ;-- 还书后减去对应的数量update borrow set quantity=quantity-quantwhere rnumber =Borrowid and bnumber=bookidORDER BY btime limit 1;-- while循环，如果借书表数值为负数，则循环减去正数的数值WHILE cone0 and rnumber =Borrowid and bnumber=bookidORDER BY btime limit 1;update borrow set quantity=quantity+1WHERE quantity=30and 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语句，得到结果集 resultSetString 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语句，得到结果集 resultSetString 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语句，得到结果集 resultSetString 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语句，得到结果集 resultSetString sql = "SELECT sum(money) as arrearage FROM amerceWHERE 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语句，得到结果集 resultSetString 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语句，得到结果集 resultSetString 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语句，得到结果集 resultSetString 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语句，得到结果集 resultSetString 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语句，得到结果集 resultSetString 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连接数据库实现图书馆…系统（详细教程）阿里云盘分享