代码驿站移动版

当前位置：主页 > 数据库 > Mysql >

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

时间：2022-08-29 09:36:43 | 栏目：Mysql | 点击：次

一、梳理功能

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

四、封装数据库相关操作

数据库链接操作

//在这里封装数据库的连接操作
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中多态继承等概念。

您可能感兴趣的文章:

相关文章