图书管理系统
- 一、Oracle数据库
- 二、java代码
- 1.Book类
- 2.LibrarySystemInterface接口
- 3.JdbcUtils类[自定义工具类]
- 4.LibrarySystem(核心类)
- 三、实现效果
- 1.管理员登录
- 2.增加图书
- 3.删除图书
- 4.修改图书信息
- 5.查看所有图书信息
- 6.根据价格区间查找图书
- 7.根据类别查找图书
一、Oracle数据库
create table library(
id number(10) check(id > 0) primary key,
name varchar2(20) not null,
price number(4,1) check(price between 0 and 999.9),
cotegary varchar2(20) not null,
description varchar2(600)
);
表已创建
create sequence library_seq start with 10006;
序列已创建
insert into library values(10000,'基督山伯爵',10.0,'小说','法国作家大仲马的杰出作品。本书讲述了十九世纪一位名叫爱德蒙·邓蒂斯的大副受到陷害后的悲惨遭遇以及日后以基督山伯爵身份成功复仇的故事。');
insert into library values(10001,'红楼梦',20.0,'小说','又名石头记,中国古典四大名著之首,清代作家曹雪芹创作的章回体长篇小说。');
insert into library values(10002,'水浒传',30.0,'小说','中国古典四大文学名著之一,全书描写北宋末年,以宋江为首的108位好汉在梁山起义以及聚义之后接受招安,四处征战的故事。');
insert into library values(10003,'高等数学',40.0,'数学','中国现代四大自闭书籍之一,无数学生的噩梦');
insert into library values(10004,'大学英语',40.0,'英语','中国现代四大自闭书籍之一,万恶四六级的源泉');
insert into library values(10005,'奥数',40.0,'数学','和java并列中国两大秃头神器');
6条被插入
二、java代码
1.Book类
描述:用来存放从数据库中的获取的图书信息
package cn.com.library;
public class Book {
private Integer bookId;
private String name;
private Double price;
private String category;
private String description;
public Book() {
super();
}
public Book(Integer bookId, String name, Double price, String category, String description) {
super();
this.bookId = bookId;
this.name = name;
this.price = price;
this.category = category;
this.description = description;
}
public void showMessage() {
System.out.println("[" + bookId + " " + name + " " + price + "¥ " + category + " " + description + "]");
}
public Integer getBookId() {
return bookId;
}
public void setBookId(Integer bookId) {
this.bookId = bookId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public String getCategory() {
return category;
}
public void setCategory(String category) {
this.category = category;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
}
2.LibrarySystemInterface接口
描述:声明一些需要用到的方法
package cn.com.library;
import java.util.List;
/**
* 图书管理系统功能标准(接口)
*/
public interface LibrarySystemInterface {
/**
* 描述:TODO 通过判断参数的usenrame,和password是否和数据仓库类中管理员信息一致,决定是否登陆成功
*/
public boolean login(String username, String password);
/**
* 描述:TODO 将参数book对象添加到数据仓库类中
*/
public void addBook(Book book);
/**
* 描述:TODO 根据参数bookId在数据仓库中找到对应的图书,然后删除。
* 提示:1.遍历数据仓库中的books属性中所有的图书,找到图书编号和bookId相同的图书下标
* 2.在遍历的循环外面,通过list的remove(index)方法删除图书
*/
public void removeBook(Integer bookId);
/**
* 描述:TODO 根据bookId找到对应的图书,将图书的其余属性分别修改为参数中传递过来的值
*/
public void updateBook(Integer bookId);
/**
* 描述:TODO 获得所有图书
*/
public List<Book> getAllBooks();
/**
* 描述:TODO 根据价格区间找到 minPrice<=price<=maxPrice的所有图书
*/
public List<Book> getBooksByPriceRange(double minPrice, double maxPrice);
/**
* 描述:TODO 根据类名查找所有的图书
*/
public List<Book> getBooksByCategory(String category);
}
3.JdbcUtils类[自定义工具类]
描述:里面封装了一些方法: 获取getConnection(),closeAll(),closeOne()
package cn.com.library;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class JdbcUtils {
private static Properties p = new Properties();
//读取jdbc.properties文件中的数据并存入到p中
static {
InputStream path = null;
try {
try {
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
path = JdbcUtils.class.getResourceAsStream("/jdbc.properties");
p.load(path);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (path != null)
path.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
//如果name和pwd输入正确,则返回对应的Connection,否则返回null
public static Connection getConnection(String name, String pwd) {
try {
return (p.getProperty("name").equals(name) && p.getProperty("pwd").equals(pwd))
? DriverManager.getConnection(p.getProperty("url"), name, pwd)
: null;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
//返回一个默认的Connection,方便使用
public static Connection getConnection() {
try {
return DriverManager.getConnection(p.getProperty("url"), "hr", "hr");
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
//关闭conn,ps,rs资源
public static void closeAll(Connection conn, PreparedStatement ps, ResultSet rs) {
try {
if (rs != null)
rs.close();
if (ps != null)
ps.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//关闭conn资源
public static void closeOne(Connection conn) {
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
4.LibrarySystem(核心类)
描述:LibrarySystem的实现类,整个系统的核心代码
package cn.com.library;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
/**
* 图书管理系统
*/
public class LibrarySystem2 implements LibrarySystemInterface {
private static Scanner s = new Scanner(System.in);
private static LibrarySystem2 t = new LibrarySystem2();
private static List<Book> list = new ArrayList<Book>();
/**
* 描述:TODO 测试类
*/
public static void main(String[] args) {
t.menu();
}
public void menu() {
boolean bn;
do {
bn = false;
System.out.println("图书管理员登录");
System.out.print("用户名:");
String username = s.next();
System.out.print("密码:");
String password = s.next();
if (t.login(username, password)) {
int num;
do {
t.libraryView();
System.out.print("请选择你要操作的编号");
num = s.nextInt();
switch (num) {
case (1):
System.out.print("增加图书");
System.out.print("编号:");
int bookId = s.nextInt();
System.out.print("名称:");
String name = s.next();
System.out.print("价格:");
double price = s.nextDouble();
System.out.print("种类");
String category = s.next();
System.out.print("简介:");
String description = s.next();
t.addBook(new Book(bookId, name, price, category, description));
break;
case (2):
boolean bl;
do {
System.out.println("删除图书");
bl = false;
System.out.print("请输入你要删除的编号");
bookId = s.nextInt();
if (!ifIdExist(bookId)) {
System.out.println("该编号不存在!");
System.out.println("重新输入-y 退出-n");
char c = s.next().charAt(0);
bl = (c == 'y' || c == 'Y') ? true : false;
} else {
t.removeBook(bookId);
}
} while (bl);
break;
case (3):
System.out.println("修改图书");
System.out.print("请输入你要修改的编号");
bookId = s.nextInt();
updateBook(bookId);
break;
case (4):
System.out.println("查看所有图书信息");
list = getAllBooks();
if (list == null) {
System.out.println("暂无图书信息~~");
} else {
System.out.println("[图书编号 图书名称 价格(¥) 图书类别 图书简介]");
for (Book book : list) {
book.showMessage();
}
}
break;
case (5):
System.out.println("根据价格区间查找图书");
System.out.print("最低价格:");
double minPrice = s.nextDouble();
System.out.print("最高价格:");
double maxPrice = s.nextDouble();
list = t.getBooksByPriceRange(minPrice, maxPrice);
if (list == null) {
System.out.println("暂无图书信息~~");
} else {
System.out.println("[图书编号 图书名称 价格(¥) 图书类别 图书简介]");
for (Book book : list) {
book.showMessage();
}
}
break;
case (6):
System.out.println("根据类别查找图书");
System.out.print("请输入你要查找的书籍类别:");
list = getBooksByCategory(s.next());
if (list == null) {
System.out.println("暂无图书信息~~");
} else {
System.out.println("[图书编号 图书名称 价格(¥) 图书类别 图书简介]");
for (Book book : list) {
book.showMessage();
}
}
break;
case (7):
System.out.println("欢迎下次使用!");
break;
default:
System.out.println("输入有误!");
}
} while (num != 7);
} else {
System.out.println("账号或密码不存在!");
System.out.println("重新输入-y 退出-n");
char c = s.next().charAt(0);
bn = (c == 'y' || c == 'Y') ? true : false;
}
} while (bn);
}
public void libraryView() {
System.out.println("-------欢迎使用百知图书管理系统-------");
System.out.println(" 1-增加图书");
System.out.println(" 2-删除图书");
System.out.println(" 3-修改图书信息");
System.out.println(" 4-查看所有图书信息");
System.out.println(" 5-根据价格区间查找图书");
System.out.println(" 6-根据类别查找图书");
System.out.println(" 7-退出");
}
/**
* 描述:TODO 通过判断参数的usenrame,和password是否和数据仓库类中管理员信息一致,决定是否登陆成功
*/
public boolean login(String name, String pwd) {
Connection conn = JdbcUtils.getConnection(name, pwd);
return conn != null;
}
/**
* 描述:TODO 将参数book对象添加到数据仓库类中
*/
public void addBook(Book book) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement("insert into library values(?,?,?,?,?)");
ps.setInt(1, book.getBookId());
ps.setString(2, book.getName());
ps.setDouble(3, book.getPrice());
ps.setString(4, book.getCategory());
ps.setString(5, book.getDescription());
ps.executeUpdate();
System.out.println("添加成功!");
} catch (SQLException e) {
System.out.println("添加失败!");
System.out.println("重新添加-y 退出-n");
char c = s.next().charAt(0);
if (c == 'y' || c == 'Y') {
boolean bn;
do {
bn = false;
System.out.print("名称:");
String name = s.next();
do {
bn = false;
System.out.print("价格:");
double price = s.nextDouble();
if (price <= 0) {
System.out.println("价格输入不合法!");
System.out.println("重新输入-y 退出-n");
c = s.next().charAt(0);
bn = (c == 'y' || c == 'Y') ? true : false;
} else {
System.out.print("类型");
String category = s.next();
System.out.print("简介:");
String description = s.next();
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement("insert into library values(library_seq.nextVal,?,?,?,?");
ps.setString(1, name);
ps.setDouble(2, price);
ps.setString(3, category);
ps.setString(4, description);
ps.executeUpdate();
System.out.println("添加成功!");
} catch (SQLException ee) {
System.out.println("添加失败,程序意外终止!");
ee.printStackTrace();
}
}
} while (bn);
} while (bn);
}
} finally {
JdbcUtils.closeAll(conn, ps, null);
}
}
/**
* 描述:TODO 根据参数bookId在数据仓库中找到对应的图书,然后删除。
* 提示:1.遍历数据仓库中的books属性中所有的图书,找到图书编号和bookId相同的图书下标
* 2.在遍历的循环外面,通过list的remove(index)方法删除图书
*/
public void removeBook(Integer bookId) {
boolean bn = false;
do {
if (bn) {
boolean bl;
do {
System.out.println("删除图书");
bl = false;
System.out.print("请输入你要删除的编号");
bookId = s.nextInt();
if (!ifIdExist(bookId)) {
System.out.println("该编号不存在!");
System.out.println("重新输入-y 退出-n");
char c = s.next().charAt(0);
bl = (c == 'y' || c == 'Y') ? true : false;
}
} while (bl);
}
bn = false;
System.out.println("请确认这是否为你要删除的图书信息");
System.out.println("[图书编号 图书名称 价格(¥) 图书类别 图书简介]");
getBook(bookId).showMessage();
System.out.println("1-确认 2-重新输入 3-退出");
System.out.print("请选择你要操作的编号");
int num = s.nextInt();
if (num == 1) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement("delete library where id = " + bookId);
ps.executeUpdate();
System.out.println("删除成功!");
} catch (SQLException e) {
System.out.println("删除失败,程序意外终止!");
e.printStackTrace();
} finally {
JdbcUtils.closeAll(conn, ps, null);
}
} else if (num == 2) {
bn = true;
}
} while (bn);
}
/**
* 描述:TODO 根据bookId找到对应的图书,将图书的其余属性分别修改为参数中传递过来的值
*/
public void updateBook(Integer bookId) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtils.getConnection();
System.out.println("修改图书信息");
boolean bn = false;
do {
if (bn) {
System.out.println("修改图书");
bn = false;
System.out.print("请输入你要修改的编号");
bookId = s.nextInt();
}
bn = false;
if (!ifIdExist(bookId)) {
System.out.println("该编号不存在!");
System.out.println("重新输入-y 退出-n");
char c = s.next().charAt(0);
bn = (c == 'y' || c == 'Y') ? true : false;
} else {
bn = false;
System.out.println("请确认这是否为你要修改的图书信息");
System.out.println("[图书编号 图书名称 价格(¥) 图书类别 图书简介]");
getBook(bookId).showMessage();
System.out.println("1-确认 2-重新输入 3-退出");
System.out.print("请选择你要操作的编号");
int num = s.nextInt();
if (num == 1) {
do {
bn = false;
System.out.println("1-修改编号 2-修改名称 3-修改价格 4-修改类型 5-修改简介");
System.out.println("请选择你要操作的编号");
num = s.nextInt();
switch (num) {
case (1):
do {
bn = false;
System.out.print("新编号:");
int newBookId = s.nextInt();
// 判断新编号是否存在,如果不存在 则修改成功
if (ifIdExist(newBookId) == false) {
// 创建一个执行器对象ps
ps = conn.prepareStatement("update library set id = ? where id = ?");
ps.setInt(1, newBookId);
ps.setInt(2, bookId);
ps.executeUpdate();
System.out.println("修改成功!");
} else {
boolean bool = false;
do {
if (bool) {
bool = false;
System.out.print("新编号:");
newBookId = s.nextInt();
} else {
System.out.println("该编号已存在!");
System.out.println("1-重新输入 2-修改其它内容 3-退出");
System.out.println("请选择你要操作的编号");
num = s.nextInt();
if (num == 1) {
bool = true;
} else if (num == 2) {
bn = true;
}
}
} while (bool);
}
} while (bn);
break;
case (2):
boolean boo = false;
do {
if (boo) {
System.out.print("修改的书籍编号:");
bookId = s.nextInt();
}
boo = false;
if (ifIdExist(bookId)) {
System.out.print("新名称:");
String newName = s.next();
ps = conn.prepareStatement("update library set name = ? where id = ?");
ps.setString(1, newName);
ps.setInt(2, bookId);
ps.executeUpdate();
System.out.println("修改成功!");
} else {
System.out.println("该编号不存在!");
System.out.println("1-重新输入 2-修改其它内容 3-退出");
System.out.println("请选择你要操作的编号");
num = s.nextInt();
if (num == 1) {
boo = true;
} else if (num == 2) {
bn = true;
}
}
} while (boo);
break;
case (3):
boo = false;
do {
if (boo) {
System.out.print("修改的书籍编号:");
bookId = s.nextInt();
}
boo = false;
System.out.print("新价格:");
double newPrice = s.nextDouble();
if (newPrice > 0) {
ps = conn.prepareStatement("update library set price = ? where id = ?");
ps.setDouble(1, newPrice);
ps.setInt(2, bookId);
ps.executeUpdate();
System.out.println("修改成功!");
} else {
System.out.println("价格输入不合法!");
System.out.println("重新输入-y 退出-n");
char c = s.next().charAt(0);
boo = (c == 'y' || c == 'Y') ? true : false;
}
} while (boo);
break;
case (4):
boo = false;
do {
boo = false;
if (ifIdExist(bookId)) {
System.out.println("修改类别:");
String category = s.next();
ps = conn.prepareStatement("update library set category = ? where id = ?");
ps.setString(1, category);
ps.setInt(2, bookId);
ps.executeUpdate();
System.out.println("修改成功!");
} else {
System.out.println("该编号不存在!");
System.out.println("1-重新输入 2-修改其它内容 3-退出");
System.out.println("请选择你要操作的编号");
num = s.nextInt();
if (num == 1) {
boo = true;
} else if (num == 2) {
bn = true;
}
}
} while (boo);
break;
case (5):
boo = false;
do {
if (boo) {
System.out.print("修改的书籍编号:");
bookId = s.nextInt();
}
boo = false;
if (ifIdExist(bookId)) {
System.out.print("修改简介:");
String description = s.next();
ps = conn.prepareStatement("update library set description = ? where id = ?");
ps.setString(1, description);
ps.setInt(2, bookId);
ps.executeUpdate();
System.out.println("修改成功!");
} else {
System.out.println("该编号不存在!");
System.out.println("1-重新输入 2-修改其它内容 3-退出");
System.out.println("请选择你要操作的编号");
num = s.nextInt();
if (num == 1) {
boo = true;
} else if (num == 2) {
bn = true;
}
}
} while (boo);
break;
default:
System.out.println("输入有误!");
System.out.println("重新输入-y 退出-n");
char c = s.next().charAt(0);
bn = (c == 'y' || c == 'Y') ? true : false;
}
} while (bn);
} else if (num == 2) {
bn = true;
}
}
} while (bn);
} catch (SQLException e) {
System.out.println("修改失败!");
} finally {
JdbcUtils.closeAll(conn, ps, null);
}
}
/**
* 描述:TODO 获得所有图书
*/
public List<Book> getAllBooks() {
List<Book> list = new ArrayList<>();
boolean bn = false;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement("select * from library order by id");
rs = ps.executeQuery();
while (rs.next()) {
bn = true;
Integer id = rs.getInt("id");
String name = rs.getString("name");
double price = rs.getDouble("price");
String category = rs.getString("category");
String description = rs.getString("description");
list.add(new Book(id, name, price, category, description));
}
return bn ? list : null;
} catch (SQLException e) {
System.out.println("程序意外终止!");
return null;
} finally {
JdbcUtils.closeAll(conn, ps, rs);
}
}
/**
* 描述:TODO 根据价格区间找到 minPrice<=price<=maxPrice的所有图书
*/
public List<Book> getBooksByPriceRange(double minPrice, double maxPrice) {
List<Book> list = new ArrayList<>();
boolean bn = false;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement("select * from library where price between ? and ? order by id");
ps.setDouble(1, minPrice);
ps.setDouble(2, maxPrice);
rs = ps.executeQuery();
while (rs.next()) {
bn = true;
Integer id = rs.getInt("id");
String name = rs.getString("name");
double price = rs.getDouble("price");
String category = rs.getString("category");
String description = rs.getString("description");
list.add(new Book(id, name, price, category, description));
}
return bn ? list : null;
} catch (SQLException e) {
System.out.println("程序意外终止!");
return null;
} finally {
JdbcUtils.closeAll(conn, ps, rs);
}
}
/**
* 描述:TODO 根据类名查找所有的图书
*/
public List<Book> getBooksByCategory(String category) {
List<Book> list = new ArrayList<>();
boolean bn = false;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr");
ps = conn.prepareStatement("select * from library where category = ? order by id");
ps.setString(1, category);
rs = ps.executeQuery();
while (rs.next()) {
bn = true;
Integer id = rs.getInt("id");
String name = rs.getString("name");
double price = rs.getDouble("price");
String description = rs.getString("description");
list.add(new Book(id, name, price, category, description));
}
return bn ? list : null;
} catch (SQLException e) {
System.out.println("程序意外终止!");
return null;
} finally {
JdbcUtils.closeAll(conn, ps, rs);
}
}
/**
* @TODO 判断编号在数据库中是否存在,如果存在 返回true,不存在 返回false
*/
public boolean ifIdExist(int bookId) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement("select * from library where id = " + bookId);
rs = ps.executeQuery();
boolean bn = rs.next();
return bn;
} catch (SQLException e) {
System.out.println("程序意外终止!");
return false;
} finally {
JdbcUtils.closeAll(conn, ps, rs);
}
}
/**
* @TODO 根据编号返回该编号在数据库中对应的书籍信息
*/
public Book getBook(int bookId) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement("select * from library where id = " + bookId);
rs = ps.executeQuery();
if (rs.next()) {
String name = rs.getString("name");
double price = rs.getDouble("price");
String category = rs.getString("category");
String description = rs.getString("description");
return new Book(bookId, name, price, category, description);
} else {
return null;
}
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
JdbcUtils.closeAll(conn, ps, rs);
}
}
}
三、实现效果
1.管理员登录
2.增加图书
3.删除图书
4.修改图书信息
5.查看所有图书信息
6.根据价格区间查找图书
7.根据类别查找图书