阅读量:1
该功能用到的软件为 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连接数据库实现图书馆...系统（详细教程）阿里云盘分享
