Mysql实战练习之简单图书管理系统

目录

• normaluser类

一、梳理功能

1.能够表示书籍信息，针对每本书来说，序号，书名，作者，价格，类型。
2.能够表示用户信息，普通用户，管理员。
3.支持的操作：

• 对于普通用户：查看书籍列表，查询指定书籍，借书还书。
• 对于 管理员：查看书籍列表，新增删除书籍。

二、准备数据库

创建用户表和书籍表

create database if not exists java100_bookmanager;
use java100_bookmanager;
drop table if exists book;
//设置id为自增主键
create table book(id int primary  key 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);

三、构造和数据库相关的实体类

书籍

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 +
                '}';
    }

用户

有两种用户，一种为普通用户，另一种为管理员，管理员和普通用户看到的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();
        }
    }
}

针对书籍表操作

//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<books> selectall(){
list<books> 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<books> selectbyname(string name) {
list<books> 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;
}
}

针对用户表的操作

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;
}
}

编写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<books> 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> 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中多态继承等概念。

到此这篇关于mysql实战练习之简单图书管理系统的文章就介绍到这了,更多相关mysql 图书管理系统内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！