编程宝库 - 技术改变世界

编程宝库

Search

Mysql知识

MySQL时间类型和模式详情

MySQL深分页问题解决的实战记录

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

四、封装数据库相关操作

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 图书管理系统内容请搜索编程宝库以前的文章，希望大家多多支持编程宝库！

下一节：MySQL深分页问题解决的实战记录

MySQL教程

前言我们日常做分页需求时，一般会用limit实现，但是当偏移量特别大的时候，查询效率就变得低下。本文将分4个方案，讨论如何优化MySQL百万数据的深分页问题，并附上最近优化生产慢SQL的实战案 ...

MySQL时间类型和模式详情

MySQL深分页问题解决的实战记录