本文档介绍如何在已有的Java图书管理系统基础上，将数据存储从文本文件改为MySQL数据库。详细步骤包括数据库连接配置、数据操作方法实现、以及系统功能如添加、查询、排序等。同时提供了数据库初始化和异常处理的方案。
在之前的 (1条消息) java图书管理系统（io操作）_may10的博客-CSDN博客 基础上，将普通记事本存储信息改为MySQL数据库存储。
使用前最好提前准备好 MySQL (1条消息) java连接MySQL_may10的博客-CSDN博客 ，
这篇文章有该过程中所需全部资源以及教程，亲测有效，一路踩坑，一路出坑，自己终于整理好了一份详细资料。
注：
准备好工程 bookManager 子包 Work Demo libs
以下.java文件都放在work包下
最好先初始化建立数据库 bookData
将jdbc内的账号，密码改成自己的。（建议全部设成root）
导入jar包(mysql-connector-java-8.0.11.jar)，在项目内创建libs目录，将jar包复制到libs目录下
在idea内右键libs目录选择 Add As Library
Main:
package Work;
/******主函数，程序入口*****/
public class Main {
/**
* 此方法是程序入口，只需创建menu对象即可开启本程序.
* @param args main方法，大家都懂
*/
public static void main(String[] args) {
Menu menu = new Menu();
}
}
Menu:
package Work;
import java.util.ArrayList;
import java.util.Scanner;
/******菜单页面*****/
public class Menu {
/**
* 菜单方法，所有的功能入口，使用本程序只需在main方法中创建本类对象即可.
*/
public Menu() {
//创建数据库连接类对象
String str = "jdbc:mysql://localhost:3306/bookData?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT";
String id = "root";
String password = "root";
Jdbc db = new Jdbc(str, id, password);
//创建图书操作类对象
ChangeBook change = new ChangeBook();
//创建图书排序类对象
BookList bList = new BookList();
Scanner sc = new Scanner(System.in);
//创建图书列表(初始数据从文件导入)
ArrayList<Book> list = change.read(db);
while(true){
System.out.println("================================");
System.out.println(" 图书管理系统\n");
System.out.println(" 1.查看图书列表");
System.out.println(" 2.添加图书");
System.out.println(" 3.图书排行");
System.out.println(" 4.历史记录");
System.out.println(" 5.重置数据");
System.out.println(" 6.退出系统\n");
System.out.println(" 请选择服务项目\n ");
switch (sc.nextInt()){
case 1:change.printList(list);//先打印列表
change.search(list, db);//再查询
break;
case 2:change.insert(list, db);//插入方法
break;
case 3:bList.demo(list);//排序方法
break;
case 4:change.printMessage(db);
break;
case 5:db.restart();
break;
default:db.close();
System.exit(0);
break;
}
//文件重新存储读取，刷新数据
list = change.read(db);
}
}
}
ChangBook:
package Work;
import java.util.ArrayList;
import java.util.Scanner;
/******图书查询、修改、创建、存储*****/
public class ChangeBook {
//修改
public void change(ArrayList<Book> list, String id, Jdbc db){
int c;
String str;
double d;
Scanner sc = new Scanner(System.in);
System.out.println("确认修改 ‘" + id + "’这本书吗？(键入1确认)");
c = sc.nextInt();
if(c==1){
for (Book book : list) {
if (book.getId().equals(id)) {
System.out.println(book.toString());
System.out.println("请输入图书新的name(输入-1，不修改):");
str = sc.next();
//记录修改信息
String s = "";
if (!str.equals("-1")) {
s += "name: ‘" + book.getName() + "’ --> ‘";
book.setName(str);
s += book.getName() + "’; ";
db.sql("update book set name = '" + str + "' where id = '" + Integer.parseInt(id) + "'");
}
System.out.println("请输入图书新的author:");
str = sc.next();
if (!str.equals("-1")) {
s += "author: ‘" + book.getAuthor() + "’ --> ‘";
book.setAuthor(str);
s += book.getAuthor() + "’; ";
db.sql("update book set author = '" + str + "' where id = '" + Integer.parseInt(id) + "'");
}
System.out.println("请输入图书新的price:");
d = sc.nextDouble();
if (!(d + "").equals("-1") && d >= 0) {
s += "price: ‘" + book.getPrice() + "’ --> ‘";
book.setPrice(d);
s += book.getPrice() + "’; ";
db.sql("update book set price = '" + d + "' where id = '" + Integer.parseInt(id) + "'");
}
System.out.println("请输入图书新的url:");
str = sc.next();
if (!str.equals("-1")) {
s += "url: ‘" + book.getUrl() + "’ --> ‘";
book.setUrl(str);
s += book.getUrl() + "’; ";
db.sql("update book set url = '" + str + "' where id = '" + Integer.parseInt(id) + "'");
}
System.out.println("修改完毕(id不可以修改)!");
db.record("修改图书信息", id, s);
System.out.println(book.toString());
return;
}
}
}else {
System.out.println("已经取消修改！");
}
}
//打印列表
public void printList(ArrayList<Book> list){
System.out.println("\n\n《图书列表》\n");
System.out.println("ID\t\t\t\tName\t\t\t\tAuthor\t\t\t\tPrice\t\tURL");
//遍历book列表，并打印对应属性
for(Book book:list){
System.out.println(book.getId()+"\t\t"+book.getName()+"\t\t\t\t"
+book.getAuthor()+"\t\t\t\t"+book.getPrice()+"\t\t"+book.getUrl());
}
}
//查询
public void search(ArrayList<Book> list, Jdbc db){
Scanner sc = new Scanner(System.in);
String str,demoStr;
while(true){
System.out.println("\n\n请输入您想要查询的图书的“name”或者“id”(输入“-1”退出查询系统):");
str=sc.next();
boolean flag = false;
if(str.equals("-1")){
return;
}
else{
//不适用 for-each 因为可能出现删除操作
for(int i=0;i<list.size();i++) {
//查找到对应的id或者name
if (str.equals(list.get(i).getId()) || str.equals(list.get(i).getName())) {
System.out.println(list.get(i).getId() + "\t\t" + list.get(i).getName() + "\t\t"
+ list.get(i).getAuthor() + "\t\t" + list.get(i).getPrice() + "\t\t" + list.get(i).getUrl());
demoStr = list.get(i).getId();
flag = true;
int c;
System.out.println("修改图书属性(1)--删除图书(2)--取消操作(-1)");
c = sc.nextInt();
if (c == 1) {
change(list, demoStr, db);
break;
} else if (c == 2) {
delete(list, demoStr, db);
break;
} else {
break;
}
}
}
if(!flag){
System.out.println("您输入的图书name或id ‘" + str + "’ 不存在,请重新输入。");
}
}
}
}
//创建
public void insert(ArrayList<Book> list, Jdbc db){
String str1, str2, str3, s="";
double d;
Scanner sc = new Scanner(System.in);
while(true){
boolean flag = true;//判断图书名称是否合法
System.out.println("输入-1退出增加图书(输入其他内容则继续添加):");
str1 = sc.next();
if(str1.equals("-1")){
return;
}
Book book = new Book();
System.out.println("请输入您要添加的图书的name:");
str1 = sc.next();
for(Book bookDemo:list){//图书名称不得重复
if(bookDemo.getName().equals(str1)){
System.out.println("已经存在此图书，请勿添加重复图书。\n" +
"若是在那个要添加同名书籍，请在书名后方加上大写字母区分\n");
flag = false;
break;
}
}
if(flag){
book.setName(str1);
System.out.println("请输入您要添加的图书的author:");
str2 = sc.next();
book.setAuthor(str2);
System.out.println("请输入您要添加的图书的price:");
d = sc.nextDouble();
if(d >= 0){
book.setPrice(d);
}
System.out.println("请输入您要添加的图书的url:");
str3 = sc.next();
book.setUrl(str3);
System.out.println(book.toString());
list.add(book);
db.sql("insert into book (name, author, price, url) " +
"value ('" + str1 + "', '" + str2 + "', '" + book.getPrice() + "', '" + str3 + "')");
db.record("创建图书信息", str1, "创建成功");
}
}
}
//删除
public void delete(ArrayList<Book> list, String id, Jdbc db){
int c;
Scanner sc = new Scanner(System.in);
System.out.println("确认删除 ‘" + id + "’这本书吗？(键入1确认)");
c = sc.nextInt();
if(c==1){
for(int i=0; i<list.size();i++) {
if (list.get(i).getId().equals(id)) {
Book demo = list.remove(i);
System.out.println("图书 ‘" + demo.getName() + "’ 已经删除!");
db.sql("delete from book where id = " + Integer.parseInt(demo.getId()));
db.record("删除图书信息", demo.getId(), "删除成功");
printList(list);
return;
}
}
}else {
System.out.println("已经取消删除！");
}
}
//读取数据库文件
public ArrayList<Book> read(Jdbc db){
ArrayList<Book> list = null;
//初始化Book类的count和idCount
Book.setCount();
try{
list=db.searchBook();
System.out.println("读取数据成功");
return list;
}catch (Exception e){
e.printStackTrace();
}
return list;
}
public void printMessage(Jdbc db){
ArrayList<Message> list = db.searchMessage();
System.out.println("\n\n《记录列表》\n");
System.out.println("ID\t\t\t\tBook\t\t\t\tType\t\t\t\tTime\t\tInfo");
//遍历book列表，并打印对应属性
for(Message message:list){
System.out.println(message.getId()+"\t\t"+message.getBook()+"\t\t\t\t"
+message.getType()+"\t\t\t\t"+message.getTime()+"\t\t"+message.getInfo());
}
}
}
Book:
package Work;
import java.util.Objects;
/*****图书类*****/
public class Book {
private String id;//图书号
private String name;//书名
private String author;//作者
private double price;//价格
private String url;//网址
//计数与id分配变量
private static int count=0;//书本计数
//全参构造
public Book(String id, String name, String author, double price, String url) {
this.id = id;
this.name = name;
this.author = author;
if(price >= 0){
this.price = price;
}else{
System.out.println("price不能小于0，请使用修改方法修改price!");
}
this.url = url;
count++;//书本数量加一
}
public Book() {
count++;//书本数量加一
}
//比较判断方法
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Book book = (Book) o;
return name.equals(book.name);
}
//未知
@Override
public int hashCode() {
return Objects.hash(name);
}
//toString
@Override
public String toString() {
return "Book{" +
"id='" + id + '\'' +
", name='" + name + '\'' +
", author='" + author + '\'' +
", price=" + price +
", url='" + url + '\'' +
'}';
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
if(price >= 0){
this.price = price;
}else{
System.out.println("price不能小于0，请重新使用使用修改方法修改price!");
}
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getId() {
return id;
}
public void setId(String id) { this.id = id; }
public static int getCount() { return count; }
//书本数量只许清零
public static void setCount() { Book.count=0;}
}
BookList:
package Work;
import java.util.*;
/******图书排序类*****/
public class BookList {
/**
* @param list 图书列表
*/
//图书排序
public void demo(ArrayList<Book> list){
ChangeBook change = new ChangeBook();
int s;
Scanner sc = new Scanner(System.in);
while(true){
System.out.println("\n请选择排序方式(1.价格升序--2.价格降序--3.id升序--4.id降序,-1.退出排序):");
s = sc.nextInt();
if(s==1){
/*
//集合排序,价格升序
list.sort(new Comparator<Book>() {
@Override
public int compare(Book o1, Book o2) {
return (int) o1.getPrice() - (int) o2.getPrice();
}
});
*/
//集合排序,价格升序
list.sort((o1, o2) -> (int) o1.getPrice() - (int) o2.getPrice());
change.printList(list);
}else if(s==2){
//集合排序，价格降序
list.sort((o1, o2) -> (int) o2.getPrice() - (int) o1.getPrice());
change.printList(list);
}else if(s==3){
//集合排序，id降序
list.sort((o1, o2) -> Integer.parseInt(o1.getId()) - Integer.parseInt(o2.getId()));
change.printList(list);
}else if(s==4){
//集合排序，价格降序
list.sort((o1, o2) -> Integer.parseInt(o2.getId()) - Integer.parseInt(o1.getId()));
change.printList(list);
}
else{
return;
}
}
}
}
Message:
package Work;
/******记录信息类*****/
public class Message {
private String id;
private String book;
private String type;
private String time;
private String info;
public Message(String id, String book, String type, String time, String info) {
this.id = id;
this.book = book;
this.type = type;
this.time = time;
this.info = info;
}
public Message() {
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getBook() {
return book;
}
public void setBook(String book) {
this.book = book;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getTime() {
return time;
}
public void setTime(String time) {
this.time = time;
}
public String getInfo() {
return info;
}
public void setInfo(String info) {
this.info = info;
}
}
Jdbc:
package Work;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
/*****MySQL数据库连接类*****/
public class Jdbc {
/*
private String path;//数据库连接IP地址及端口号和数据库名
//例： "localhost:3306/data"
// IP 端口号 数据库名
private String str = "jdbc:mysql://" + path
+ "?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT";
//例："jdbc:mysql://localhost:3306/data?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT"
*/
private String path;//数据库地址及设置
//标准格式参考上面注释部分
private String id;//数据库账号
private String password;//数据库密码
Connection con;//连接对象
Statement state;//执行对象
public Jdbc(String path, String id, String password) {
//1.导入jar包，在项目内创建libs目录，将jar包复制到libs目录下
//在idea内右键libs目录选择 Add As Library
//注册数据库驱动
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
this.path = path;
this.id = id;
this.password = password;
dataBase();
init();
}
//创建连接对象和执行对象
private void dataBase(){
//获取连接对象
try {
this.con = DriverManager.getConnection( this.path, this.id, this.password);
//获取执行对象
this.state = this.con.createStatement();
} catch (Exception e) {
e.printStackTrace();
}
}
//执行SQL语句(查询之外)
public void sql(String str){
try {
boolean execute = this.state.execute(str);
if(execute){
System.out.println("执行失败");
}else{
System.out.println("执行成功.");
}
} catch (Exception e) {
e.printStackTrace();
}
}
//执行查询SQL语句(使用集合返回)
public ArrayList<Book> searchBook(){
ArrayList<Book> array = new ArrayList<>();
String str = "select * from book";
try {
//获取结果集
ResultSet resultSet = this.state.executeQuery(str);
while (resultSet.next()){
Book book = new Book();
//补足八位，不够前导0
book.setId(String.format("%08d", Integer.parseInt(resultSet.getString("id"))));
book.setName(resultSet.getString("name"));
book.setAuthor(resultSet.getString("author"));
book.setPrice(Double.parseDouble(resultSet.getString("price")) );
book.setUrl(resultSet.getString("url"));
array.add(book);
}
return array;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
//查询记录信息
public ArrayList<Message> searchMessage(){
ArrayList<Message> array = new ArrayList<>();
String str = "select * from message";
try {
//获取结果集
ResultSet resultSet = this.state.executeQuery(str);
while (resultSet.next()){
Message message = new Message();
//补足八位，不够前导0
message.setId(String.format("%08d", Integer.parseInt(resultSet.getString("id"))));
message.setBook(resultSet.getString("book"));
message.setType(resultSet.getString("type"));
message.setTime(resultSet.getString("time"));
message.setInfo(resultSet.getString("info"));
array.add(message);
}
return array;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
//执行查询SQL语句(返回所需图书的id)
public String find(String str, ArrayList<Book> array){
for(Book book:array){
//如果id或者名称相符
if(book.getId().equals(str) || book.getName().equals(str)){
return book.getId();
}
}
return null;
}
//初始化数据库
public void init(){
//新创建的数据库要开启使用
String[] str = {"use bookData",
"create table book (id INT AUTO_INCREMENT, PRIMARY KEY (id)" +
", name varchar(30), author varchar(20), price double, url varchar(255))",
"insert into book (name , author, price, url) value ('西游记', '吴承恩', 88.88, 'www.bookGood.com')",
"insert into book (name , author, price, url) value ('红楼梦', '曹雪芹', 99.99, 'www.bookNice.com')",
"insert into book (name , author, price, url) value ('三国演义', '罗贯中', 77.77, 'www.bookWe.com')",
"insert into book (name , author, price, url) value ('水浒传', '施耐庵', 66.66, 'www.bookOld.com')",
"create table message (id INT AUTO_INCREMENT, PRIMARY KEY (id)" +
", book varchar(30), type varchar(30), time varchar(30), info varchar(255))",
"insert into message (book, type, time, info) value ('null', '创建表格 book', '" + Calendar.getInstance().getTime() + "', 'ok')",
"insert into message (book, type, time, info) value ('null', '导入demo数据', '" + Calendar.getInstance().getTime() + "', 'ok')",
"insert into message (book, type, time, info) value ('null', '创建表格 message', '" + Calendar.getInstance().getTime() + "', 'ok')",
"insert into message (book, type, time, info) value ('null', '导入初始化信息', '" + Calendar.getInstance().getTime() + "', 'ok')"};
/*
//创建图书列表
String str1 = "create table book (id INT AUTO_INCREMENT, PRIMARY KEY (id)" +
", name varchar(30), author varchar(20), price double, url varchar(255))";
//导入demo数据（id不导入）
String str2 = "insert into book (name , author, price, url) value ('西游记', '吴承恩', 88.88, 'www.bookGood.com')";
String str3 = "insert into book (name , author, price, url) value ('红楼梦', '曹雪芹', 99.99, 'www.bookNice.com')";
String str4 = "insert into book (name , author, price, url) value ('三国演义', '罗贯中', 77.77, 'www.bookWe.com')";
String str5 = "insert into book (name , author, price, url) value ('水浒传', '施耐庵', 66.66, 'www.bookOld.com')";
//创建信息列表
String str6 = "create table message (id INT AUTO_INCREMENT, PRIMARY KEY (id)" +
", type varchar(30), time varchar(30), info varchar(255))";
//导入创建数据库及表格数据
String str7 = "insert into message (type, time, info) value ('创建表格 book', '" + Calendar.getInstance().getTime() + "', 'ok')";
String str8 = "insert into message (type, time, info) value ('导入demo数据', '" + Calendar.getInstance().getTime() + "', 'ok')";
String str9 = "insert into message (type, time, info) value ('创建表格 message', '" + Calendar.getInstance().getTime() + "', 'ok')";
String str10 = "insert into message (type, time, info) value ('导入初始化信息', '" + Calendar.getInstance().getTime() + "', 'ok')";
*/
//验证语句（判断数据库中是否有表）
try {
ResultSet count = state.executeQuery(" SELECT COUNT(*) AS cou FROM information_schema.`TABLES` WHERE table_schema = 'bookData' AND (table_name = 'book' OR table_name = 'message') ");
if(count.next()){
//数据库与表格完整
if(Integer.parseInt(count.getString("cou")) == 2){
System.out.println("初始化完成");
}
//数据库存在表格不存在
else if (Integer.parseInt(count.getString("cou")) == 0){
//执行初始化语句
for(String s:str){
try {
this.state.execute(s);
} catch (SQLException e) {
e.printStackTrace();
}
}
System.out.println("初始化完成");
}else {//数据库存在但是表格数量不对，直接删除数据库重新初始化
state.execute("DROP DATABASE bookData");
init();
System.out.println("初始化完成");
}
}else{//数据库不存在则创建数据库，并且初始化
state.execute("create database bookData");
//新创建的数据库要开启使用
state.execute("use bookData");
init();
System.out.println("初始化完成");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//存储操作记录
public void record(String type, String book, String info){
try {
state.execute("insert into message (book, type, time, info) " +
"value ( '"+ book +"', '" + type +"', '"
+ Calendar.getInstance().getTime() + "', '" + info + "')");
System.out.println("记录完成");
} catch (Exception e) {
e.printStackTrace();
}
}
public void close(){
try {
this.state.close();
this.con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//出现异常情况，数据库和表格数量没变但是出现其他不可逆现象，使用这方法重置数据库
public void restart(){
try {
//删除数据库
state.execute("DROP DATABASE bookData");
System.out.println("删除表");
//创建数据库
state.execute("create database bookData");
System.out.println("创建表");
init();
System.out.println("重置完成");
} catch (Exception e) {
e.printStackTrace();
}
}
public String getPath() {
return path;
}
public void setPath(String path) {
this.path = path;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
扫一扫
1600
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
