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

原作者: [db:作者] 来自: [db:来源] 收藏邀请

一、梳理功能

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 图书管理系统内容请搜索极客世界以前的文章或继续浏览下面的相关文章希望大家以后多多支持极客世界！