Java|简单图书管理系统练习-Java+Mysql
一、梳理功能 1.能够表示书籍信息，针对每本书来说，序号，书名，作者，价格，类型。
2.能够表示用户信息，普通用户，管理员。
3.支持的操作：
对于普通用户：查看书籍列表，查询指定书籍，借书还书。
对于 管理员：查看书籍列表，新增删除书籍。
文章图片
二、准备数据库 【Java|简单图书管理系统练习-Java+Mysql】创建用户表和书籍表
create database if not exists java100_bookmanager;
use java100_bookmanager;
drop table if exists book;
//设置id为自增主键
create table book(id int primarykey auto_increment,name varchar(20),author varchar(20),price int,type varchar(20),isBorrowed int);
drop table if exists user;
//同样设置 userid为自增主键并且用户名字不重复
create table user(
userId int primary key auto_increment,
username varchar(20) unique,
password varchar(20),
isAdmin int
);
-- 插入一些书籍
insert into book values(null,'西游记','吴承恩',10000,'古典小说',0);
insert into book values(null,'三国演义','罗贯中',10000,'古典小说',0);
insert into book values(null,'水浒传','施耐庵',10000,'古典小说',0);
insert into book values(null,'金瓶梅','兰陵笑笑生',10000,'古典小说',0);
--插入一些用户
insert into user values(null,'admin','123',1);
insert into user values(null,'zhangsan','123',0);
三、构造和数据库相关的实体类 1.书籍
public class Books {private int bookId;
//书籍编号
private String name;
//书名
private String author;
//作者
private int price;
//价格
private String type;
//类型
private boolean isBorrowed;
//是否被借阅
//set get方法public int getBookId() {return bookId;
}public void setBookId(int bookId) {this.bookId = bookId;
}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 int getPrice() {return price;
}public void setPrice(int price) {this.price = price;
}public String getType() {return type;
}public void setType(String type) {this.type = type;
}public boolean isBorrowed() {return isBorrowed;
}public void setBorrowed(boolean borrowed) {isBorrowed = borrowed;
}@Override
public String toString() {return "Book{" +
"bookId=" + bookId +
", name='" + name + '\'' +
", author='" + author + '\'' +
", price=" + price +
", type='" + type + '\'' +
", isBorrowed=" + isBorrowed +
'}';
}
2.用户
有两种用户，一种为普通用户，另一种为管理员，管理员和普通用户看到的menu不同，管理员和普通 用户的类方法也不同
先定义一个抽象类User 让普通用户NoramlUser和管理员类Admin来继承User类
abstract public class user {private int userId;
private String userName;
private String passWord;
IOperation[] operations;
//方法数组，表示user类所包含的方法
abstract int menu();
//子类要重写menu方法，因为两个子类看到的menu不同
public void doOperation(int choice){
//此方法来执行一些操作，如借书还书等
operations[choice].work();
}public int getUserId() {return userId;
}public void setUserId(int userId) {this.userId = userId;
}public String getUserName() {return userName;
}public void setUserName(String userName) {this.userName = userName;
}public String getPassWord() {return passWord;
}public void setPassWord(String passWord) {this.passWord = passWord;
}@Override
public String toString() {return "user{" +
"userId=" + userId +
", userName='" + userName + '\'' +
", passWord='" + passWord + '\'' +
'}';
}
}
NormalUser类
public class NormalUser extends user{public NormalUser(){this.operations=new IOperation[]{
//之后单独开辟一个包，包里存储和实现这些方法
new ExitOperation(),//退出系统
new DisplayOperation(),//查看书籍列表
new FindOperation(),//查找书籍
new BorrowOperation(),//借阅书籍
new ReturnOperation(),//还书
};
}
@Override
public int menu() {
//重写父类menu方法
System.out.println("========================");
System.out.println("欢迎您，"+this.getUserName()+"!");
System.out.println("1.查看书籍列表");
System.out.println("2.查找指定书籍");
System.out.println("3.借阅书籍");
System.out.println("4.归还书籍");
System.out.println("0.退出系统");
System.out.println("========================");
System.out.println("请输入选项");
Scanner sc=new Scanner(System.in);
int choice=sc.nextInt();
return choice;
}
}
Admin类
public class Admin extends user {public Admin(){this.operations=new IOperation[]{new ExitOperation(),//退出系统
new DisplayOperation(),//查看书籍列表
new FindOperation(),//查找书籍
new AddOperation(),//添加书籍
new DelOperation(),//删除书籍
};
}
@Override
public int menu() {System.out.println("========================");
System.out.println("欢迎您，"+this.getUserName()+"您是管理员!");
System.out.println("1.查看书籍列表");
System.out.println("2.查找指定书籍");
System.out.println("3.新增书籍");
System.out.println("4.删除书籍");
System.out.println("0.退出系统");
System.out.println("========================");
System.out.println("请输入选项");
Scanner sc=new Scanner(System.in);
int choice=sc.nextInt();
return choice;
}
}
四、封装数据库相关操作 1.先把数据库链接的操作封装好
2.再把针对书籍表的增删查改操作封装好
3.再把针对用户表的操作封装好
数据库链接操作
//在这里封装数据库的连接操作
public class DBUtil {//设置url 账号密码 根据个人设置
private static final String URL="jdbc:mysql://127.0.0.1:3306/java100_bookmanager?characterEncoding=utf8&&useSSL=false";
private static final String USERNAME="root";
private static final String PASSWORD="q986681563";
//饿汉模式
//类加载阶段就会调用静态代码块进行实例化
/*private static DataSource dataSource=new MysqlDataSource();
static{
((MysqlDataSource)dataSource).setUrl(URL);
((MysqlDataSource)dataSource).setUser(USERNAME);
((MysqlDataSource)dataSource).setPassword(PASSWORD);
}*/
//懒汉模式
//只有首次调用getDataSource方法 才会实例化
private static DataSource dataSource=null;
public static DataSource getDataSource(){if(dataSource==null){dataSource=new MysqlDataSource();
((MysqlDataSource)dataSource).setUrl(URL);
((MysqlDataSource)dataSource).setUser(USERNAME);
((MysqlDataSource)dataSource).setPassword(PASSWORD);
}
return dataSource;
}
public static Connection getConnection() throws SQLException {return getDataSource().getConnection();
}
public static void close(ResultSet resultSet, PreparedStatement statement,Connection connection){
//释放资源
//注释掉的方式更安全
/*if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}*/
try {if(resultSet!=null) resultSet.close();
if(statement!=null) statement.close();
if(connection!=null) connection.close();
} catch (SQLException e) {e.printStackTrace();
}
}
}
2.针对书籍表操作
//DAO Data Access Object 数据访问对象
public class BookDAO {//1.新增书籍
public boolean add(Books book){Connection connection=null;
PreparedStatement statement=null;
try {connection= DBUtil.getConnection();
String sql="insert into book values(null,?,?,?,?,?)";
statement=connection.prepareStatement(sql);
statement.setString(1,book.getName());
statement.setString(2,book.getAuthor());
statement.setInt(3,book.getPrice());
statement.setString(4,book.getType());
statement.setInt(5,book.isBorrowed()?1:0);
int ret=statement.executeUpdate();
if(ret!=1) return false;
return true;
} catch (SQLException e) {e.printStackTrace();
}finally {DBUtil.close(null,statement,connection);
}
return false;
}
//2.查看所有书籍
public List selectAll(){List list=new ArrayList<>();
Connection connection=null;
PreparedStatement statement=null;
ResultSet resultSet=null;
try {connection=DBUtil.getConnection();
String sql="select*from book";
statement=connection.prepareStatement(sql);
resultSet=statement.executeQuery();
while(resultSet.next()){Books book=new Books();
book.setBookId(resultSet.getInt("id"));
book.setName(resultSet.getString("name"));
book.setAuthor(resultSet.getString("author"));
book.setPrice(resultSet.getInt("price"));
book.setType(resultSet.getString("type"));
book.setBorrowed(resultSet.getInt("isBorrowed")==1);
list.add(book);
}
} catch (SQLException e) {e.printStackTrace();
}finally {DBUtil.close(resultSet,statement,connection);
}
return list;
}
//3.根据名字找书籍
public List selectByName(String name) {List list=new ArrayList<>();
Connection connection=null;
PreparedStatement statement=null;
ResultSet resultSet=null;
try {connection=DBUtil.getConnection();
String sql="select* from book where name=?";
statement=connection.prepareStatement(sql);
statement.setString(1,name);
resultSet=statement.executeQuery();
while(resultSet.next()){Books book=new Books();
book.setBookId(resultSet.getInt("Id"));
book.setName(resultSet.getString("name"));
book.setAuthor(resultSet.getString("author"));
book.setType(resultSet.getString("type"));
book.setPrice(resultSet.getInt("price"));
book.setBorrowed(resultSet.getInt("isBorrowed")==1);
list.add(book);
}
} catch (SQLException e) {e.printStackTrace();
}finally {DBUtil.close(resultSet,statement,connection);
}
return list;
}
//4.删除书籍
public boolean delete(int bookId){Connection connection=null;
PreparedStatement statement=null;
try {connection=DBUtil.getConnection();
String sql="delete from book where id=?";
statement=connection.prepareStatement(sql);
statement.setInt(1,bookId);
int ret=statement.executeUpdate();
if(ret!=1) return false;
return true;
} catch (SQLException e) {e.printStackTrace();
}finally {DBUtil.close(null,statement,connection);
}
return false;
}
//5.借书
public boolean borrowBook(int bookId){Connection connection=null;
PreparedStatement statement=null;
PreparedStatement statement2=null;
ResultSet resultSet=null;
try {connection=DBUtil.getConnection();
String sql="select * from book where id=?";
statement=connection.prepareStatement(sql);
statement.setInt(1,bookId);
resultSet=statement.executeQuery();
if(resultSet.next()){boolean isBorrowed=(resultSet.getInt("isBorrowed")==1);
if(isBorrowed){System.out.println("书已借出，无法再次借出！ bookId="+bookId);
return false;
}
}else{System.out.println("书不存在 bookId="+bookId);
return false;
}
sql="update book set isBorrowed=1 where id=?";
statement2=connection.prepareStatement(sql);
statement2.setInt(1,bookId);
int ret = statement2.executeUpdate();
if(ret!=1) {System.out.println("借阅失败");
return false;
}
System.out.println("借阅成功");
return true;
} catch (SQLException e) {e.printStackTrace();
}finally {if(resultSet!=null) {try {connection.close();
} catch (SQLException throwables) {throwables.printStackTrace();
}
}
if(statement!=null) {try {statement.close();
} catch (SQLException throwables) {throwables.printStackTrace();
}
}
if(statement2!=null) {try {statement2.close();
} catch (SQLException throwables) {throwables.printStackTrace();
}
}
if(connection!=null){try {connection.close();
} catch (SQLException throwables) {throwables.printStackTrace();
}
}
}
return false;
}
//6.归还
public boolean returnBook(int bookId){Connection connection=null;
PreparedStatement statement=null;
PreparedStatement statement2=null;
ResultSet resultSet=null;
try {connection=DBUtil.getConnection();
String sql="select* from book where id=?";
statement=connection.prepareStatement(sql);
statement.setInt(1,bookId);
resultSet= statement.executeQuery();
if(resultSet.next()){boolean isBorrowed=(resultSet.getInt("isBorrowed")==1);
if(!isBorrowed){System.out.println("书没有被借出，不需要归还 bookId="+bookId);
return false;
}
}else{System.out.println("没有该书！ bookId="+bookId);
return false;
}
sql="update book set isBorrowed=0 where id=?";
statement2=connection.prepareStatement(sql);
statement2.setInt(1,bookId);
int ret = statement2.executeUpdate();
if(ret!=1) return false;
return true;
} catch (SQLException e) {e.printStackTrace();
}finally {if(resultSet!=null) {try {connection.close();
} catch (SQLException throwables) {throwables.printStackTrace();
}
}
if(statement!=null) {try {statement.close();
} catch (SQLException throwables) {throwables.printStackTrace();
}
}
if(statement2!=null) {try {statement2.close();
} catch (SQLException throwables) {throwables.printStackTrace();
}
}
if(connection!=null){try {connection.close();
} catch (SQLException throwables) {throwables.printStackTrace();
}
}
}
return false;
}
} 
文章图片
3.针对用户表的操作
public class UserDao {//根据用户名找密码的逻辑
//username是unique约束的
public user selectByName(String name){Connection connection=null;
PreparedStatement statement=null;
ResultSet resultSet=null;
try {connection=DBUtil.getConnection();
String sql="select* from user where username=?";
statement=connection.prepareStatement(sql);
statement.setString(1,name);
resultSet = statement.executeQuery();
if(resultSet.next()){boolean isAdmin=(resultSet.getInt("isAdmin")==1);
user users=null;
if(isAdmin){users=new Admin();
}else users=new NormalUser();
users.setPassWord(resultSet.getString("password"));
users.setUserId(resultSet.getInt("userId"));
users.setUserName(resultSet.getString("username"));
return users;
}} catch (SQLException e) {e.printStackTrace();
}finally {DBUtil.close(resultSet,statement,connection);
}
return null;
}
}编写主逻辑（main方法和login方法）
public class Main {public static void main(String[] args) {user users=login();
while(true){int choice=users.menu();
users.doOperation(choice);
}
}
private static user login(){Scanner sc=new Scanner(System.in);
System.out.println("请输入用户名");
String name=sc.next();
System.out.println("请输入密码");
String password=sc.next();
UserDao userDao=new UserDao();
user users=userDao.selectByName(name);
if(users==null){System.out.println("登陆失败！");
System.exit(0);
}
if(!users.getPassWord().equals(password)){System.out.println("密码错误");
System.exit(0);
}
return users;
}
}
6.编写operation各种细节 将所有operations操作放在一个包中，定义一个接口operations，所有操作实现这个接口并重写方法
IOperation接口
public interface IOperation {void work();
}
添加书籍操作
public class AddOperation implements IOperation{@Override
public void work() {System.out.println("新增书籍！");
Scanner sc=new Scanner(System.in);
System.out.println("请输入书名");
String name=sc.next();
System.out.println("请输入作者");
String author=sc.next();
System.out.println("请输入价格");
int price=sc.nextInt();
System.out.println("请输入类别");
String type=sc.next();
Books book=new Books();
book.setName(name);
book.setPrice(price);
book.setType(type);
book.setAuthor(author);
BookDAO bookDAO=new BookDAO();
boolean ret=bookDAO.add(book);
if(ret) System.out.println("新增成功");
else System.out.println("新增失败");
}
}
借书操作
public class BorrowOperation implements IOperation {@Override
public void work() {System.out.println("借阅书籍");
System.out.println("请输入要借阅的书籍id");
Scanner sc=new Scanner(System.in);
int id=sc.nextInt();
BookDAO bookDAO=new BookDAO();
boolean ret = bookDAO.borrowBook(id);
}
}删除书籍操作
public class DelOperation implements IOperation{@Override
public void work() {System.out.println("删除书籍！");
Scanner sc=new Scanner(System.in);
System.out.println("请输入删除书籍的id");
int id=sc.nextInt();
BookDAO bookDAO=new BookDAO();
boolean ret = bookDAO.delete(id);
if(ret) System.out.println("删除成功");
else System.out.println("删除失败");
}
}
查看书籍列表操作
public class DisplayOperation implements IOperation {@Override
public void work() {System.out.println("展示所有书籍");
BookDAO bookdao=new BookDAO();
List list=bookdao.selectAll();
for(Books book:list){System.out.println(book);
}
System.out.println("展示书籍完毕");
}
} 退出系统操作
public class ExitOperation implements IOperation{@Override
public void work() {System.out.println("退出程序");
System.exit(0);
}
}查找书籍操作
public class FindOperation implements IOperation{@Override
public void work() {System.out.println("根据名字查找书籍");
System.out.println("请输入书名");
Scanner sc=new Scanner(System.in);
String name=sc.next();
BookDAO bookDAO=new BookDAO();
List books = bookDAO.selectByName(name);
for(Books book:books){System.out.println(book);
}
System.out.println("根据名字查找书籍完毕");
}
} 还书操作
public class ReturnOperation implements IOperation{@Override
public void work() {System.out.println("归还书籍！");
System.out.println("请输入要归还的书籍的id");
Scanner sc=new Scanner(System.in);
int id=sc.nextInt();
BookDAO bookDAO=new BookDAO();
boolean ret = bookDAO.returnBook(id);
if(ret){System.out.println("归还成功");
}else{System.out.println("归还失败");
}
}
}总结：简单的图书管理系统，通过练习掌握简单JDBC语法和API，同时可以帮助理解java中多态继承等概念。
推荐阅读
- JAVA（抽象类与接口的区别&重载与重写&内存泄漏）
- 图书集合完毕
- 科学养胃，别被忽悠，其实真的很简单
- 事件代理
- opencv|opencv C++模板匹配的简单实现
- Java|Java OpenCV图像处理之SIFT角点检测详解
- java中如何实现重建二叉树
- 数组常用方法一
- 松软可口易消化，无需烤箱超简单，新手麻麻也能轻松成功~
- 【Hadoop踩雷】Mac下安装Hadoop3以及Java版本问题