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

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

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

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

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

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

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

 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("归还失败"); } } }