簡單圖書管理系統練習-Java+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);

三、構造和資料庫相關的物體類

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

在這里插入圖片描述

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<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中多型繼承等概念，

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/303036.html

標籤：java

上一篇：JavaScipt 面試題+知識點

下一篇：SpringMVC這篇文章吃透了，最少最少漲5000