初步使用Druid连接池+代码示例：对图书进行增删改查

呱

发布于 2020-10-23 10:34:40

9320

发布于 2020-10-23 10:34:40

文章被收录于专栏：后端JavaEE

1.创建数据库，向里面添加数据

数据库名：BookShop

#用户表 CREATE TABLE USER( id INT(11) PRIMARY KEY AUTO_INCREMENT, username VARCHAR(20) NOT NULL, PASSWORD VARCHAR(32) NOT NULL, realname VARCHAR(20), email VARCHAR(50) NOT NULL, gender VARCHAR(50) NOT NULL,
flag INT(11), role INT(11) );#书籍类别表 CREATE TABLE category( cid INT PRIMARY KEY, cname VARCHAR(30) NOT NULL );#书籍表 CREATE TABLE book( id INT PRIMARY KEY AUTO_INCREMENT , title VARCHAR(50) NOT NULL, author VARCHAR(20) NOT NULL, publicDate DATETIME, publisher VARCHAR(50), isbn VARCHAR(15) NOT NULL, price DECIMAL(8,2) NOT NULL, picture varchar(50), cid INT, CONSTRAINT FOREIGN KEY(cid) REFERENCES category(cid) );

#user添加数据 INSERT INTO USER (username,PASSWORD,realname,email,gender,flag,role) VALUES('admin','888','李明','liming@qq.com','男',1,0); INSERT INTO USER (username,PASSWORD,realname,email,gender,flag,role) VALUES('jiangjiang','123456','犟 犟','jiangjiang@163.com','男',1,1); INSERT INTO USER (username,PASSWORD,realname,email,gender,flag,role) VALUES('yitao','123456','艺 涛','shuliang@163.com','女',1,1); #categroy添加数据 INSERT INTO category (cid,cname) VALUES(10,'科技'),(11,'教育'),(12,'小说'),(13,'文艺'),(14,'经管'),(15,'成功'),(16,'生活'); #添加书籍 INSERT INTO book(title,author,publicDate,publisher,isbn,price,picture,cid) VALUES('Java核心技术 卷I 基础知识','霍斯特曼','2019-12-1','机械工业出版社','9787111636663',102.80,null,10) ,('高性能MySQL（第3版）','特卡琴科','2013-05-10','电子工业出版社','9787121198854',122.90,null,10) ,('Java从入门到精通（第5版）','明日科技','2019-03-1','清华大学出版社','9787302517597',61.40,null,10) ,('Java编程思想（第4版）','Bruce Eckel','2007-06-1','机械工业出版社','9787111213826',100.30,null,10) ,('深入理解Java虚拟机','周志明','2013-06-1','机械工业出版社','9787111421900',62.40,null,10) ,('高等数学（第七版）（上册）','同济大学数学系','2014-07-1','高等教育出版社','9787040396638',40.20,null,11) ,('管理学(第13版)','斯蒂芬·P·罗宾斯','2017-01-1','中国人民大学出版社','9787300234601',66.50,null,14) ,('红楼梦原著版（上、下册）','曹雪芹','2013-01-1','人民文学出版社','9787020002207',38.9,null,12) ,('水浒传（上下册）（全两册）','施耐庵 ,罗贯中','2004-09-1','人民文学出版社','9787020008742',32.9,null,12) ,('西游记（共两册）','吴承恩 ','2007-05-1','人民文学出版社','9787020051564',48.00,null,12);

2.搭建开发环境

 1.创建项目创建项目:bookshop、
 2.导入jar包:
 commons-dbutils-1.7.jar
 commons-logging-1.2.jar
 druid-1.1.5.jar
 mysql-connector-java-5.1.49.jar
 3.创建包结构、配置文件
 com.qf.bookshop.domain 实体类
com.qf.bookshop.utils 工具类
com.qf.bookshop.dao Dao接口
com.qf.bookshop.service Service接口
com.qf.bookshop.test 测试类

druid.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/bookshop?useSSL=false&characterEncoding=utf8
username=root
password=root

initialSize=10
maxActive=50
minIdle=5
maxWait=5000

3.项目代码

3.1domain层 —> Book

package com.qf.domain;

import java.math.BigDecimal;
import java.util.Date;

/*
Create Table

CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL,
  `author` varchar(20) NOT NULL,
  `publicDate` datetime DEFAULT NULL,
  `publisher` varchar(50) DEFAULT NULL,
  `isbn` varchar(15) NOT NULL,
  `price` decimal(8,2) NOT NULL,
  `picture` varchar(50) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cid` (`cid`),
  CONSTRAINT `book_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `category` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4

 */
public class Book {
    private int id;
    private String title;
    private String author;
    private Date publicDate;
    private String publisher;
    private String isbn;
    private BigDecimal price;
    private String picture;
    private int cid;

    public Book(int id, String title, String author, Date publicDate, String publisher, String isbn, BigDecimal price, String picture, int cid) {
        this.id = id;
        this.title = title;
        this.author = author;
        this.publicDate = publicDate;
        this.publisher = publisher;
        this.isbn = isbn;
        this.price = price;
        this.picture = picture;
        this.cid = cid;
    }

    public Book() {
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public Date getPublicDate() {
        return publicDate;
    }

    public void setPublicDate(Date publicDate) {
        this.publicDate = publicDate;
    }

    public String getPublisher() {
        return publisher;
    }

    public void setPublisher(String publisher) {
        this.publisher = publisher;
    }

    public String getIsbn() {
        return isbn;
    }

    public void setIsbn(String isbn) {
        this.isbn = isbn;
    }

    public BigDecimal getPrice() {
        return price;
    }

    public void setPrice(BigDecimal price) {
        this.price = price;
    }

    public String getPicture() {
        return picture;
    }

    public void setPicture(String picture) {
        this.picture = picture;
    }

    public int getCid() {
        return cid;
    }

    public void setCid(int cid) {
        this.cid = cid;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", author='" + author + '\'' +
                ", publicDate=" + publicDate +
                ", publisher='" + publisher + '\'' +
                ", isbn='" + isbn + '\'' +
                ", price=" + price +
                ", picture='" + picture + '\'' +
                ", cid=" + cid +
                '}';
    }
}

3.2dao层 —> BookDao

public interface BookDao {
    //查询所有
    List<Book> queryAll();
    //根据id查询
    Book queryById(int bookId);
    //根据书名查询
    List<Book> queryByTitle(String title);

    //添加
    void insert(Book book);

    //删除
    int delete(int bookId);

    //更新
    void update(Book book);

    //返回数据个数

    long getCount();

    //查询所有书名
    List<String> getTitles();
}

3.3 dao层 —> impl层 —> BookDaoImpl

public class BookDaoImpl implements BookDao {
    QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
    @Override
    public List<Book> queryAll() {
        //创建查询执行器
        try {
            return qr.query("select id,title,author,publicDate,publisher,isbn,price,picture,cid from book",new BeanListHandler<>(Book.class));
        } catch (SQLException e) {
            throw new RuntimeException("查询所有书籍失败",e);
        }
    }

    @Override
    public Book queryById(int bookId) {
        //创建查询执行器
        try {
            return qr.query("select id,title,author,publicDate,publisher,isbn,price,picture,cid from book where id=?",new BeanHandler<>(Book.class),bookId);
        } catch (SQLException e) {
            throw new RuntimeException("根据id查询所有书籍失败",e);
        }
    }

    @Override
    public List<Book> queryByTitle(String title) {
        //创建查询执行器
        try {
            return qr.query("select id,title,author,publicDate,publisher,isbn,price,picture,cid from book where title like ?",new BeanListHandler<>(Book.class),"%"+title+"%");
        } catch (SQLException e) {
            throw new RuntimeException("根据书名查询所有书籍失败",e);
        }
    }

    @Override
    public void insert(Book book) {
        String sql="insert into book(title,author,publicDate,publisher,isbn,price,picture,cid) values(?,?,?,?,?,?,?,?);";
        Object[] params={book.getTitle(),book.getAuthor(),book.getPublicDate(),book.getPublisher(),book.getIsbn(),book.getPrice(),book.getPicture(),book.getCid()};
        try {
            qr.update(sql,params);
        } catch (SQLException e) {
            throw new RuntimeException("添加书籍失败",e);
        }
    }

    @Override
    public int delete(int bookId) {
        String sql="delete from book where id=?";
        try {
            return qr.update(sql,bookId);
        } catch (SQLException e) {
            throw new RuntimeException("删除书籍失败",e);
        }
    }

    @Override
    public void update(Book book) {
        String sql="update book set title=?,author=?,publicDate=?,publisher=?,isbn=?,price=?,picture=?,cid=? where id=?;";
        Object[] params={book.getTitle(),book.getAuthor(),book.getPublicDate(),book.getPublisher(),book.getIsbn(),book.getPrice(),book.getPicture(),book.getCid(),book.getId()};
        try {
            qr.update(sql,params);
        } catch (SQLException e) {
            throw new RuntimeException("修改书籍失败",e);
        }
    }

    @Override
    public long getCount() {
        String sql="select count(*) from book";
        try {
            return qr.query(sql, new ScalarHandler<>());
        } catch (SQLException e) {
            throw new RuntimeException("查询书籍数量失败",e);
        }

    }
    @Override
    public List<String> getTitles() {
        String sql="select title from book;";
        try {
            return qr.query(sql, new ColumnListHandler<String>());
        } catch (SQLException e) {
            throw new RuntimeException("查询书籍名称失败",e);
        }

    }
}

3.4service层 —> BookService

public interface BookService {
    //查询
    List<Book> queryAll();
    Book queryById(int bookId);
    List<Book> QueryByTitle(String title);
    //添加
    void add(Book book);
    //删除
    void remove(int bookId);
    //更新
    void modify(Book book);
}

3.5service层 —> impl —> BookServiceImpl

public class BookServiceImpl implements BookService {
    private BookDao bookDao=new BookDaoImpl();
    @Override
    public List<Book> queryAll() {
        return bookDao.queryAll();
    }

    @Override
    public Book queryById(int bookId) {
        return bookDao.queryById(bookId);
    }

    @Override
    public List<Book> QueryByTitle(String title) {
        return bookDao.queryByTitle(title);
    }

    @Override
    public void add(Book book) {
        bookDao.insert(book);
    }

    @Override
    public void remove(int bookId) {

        int count=bookDao.delete(bookId);
        if(count<=0){
            throw new RuntimeException("书籍不存在");
        }
    }

    @Override
    public void modify(Book book) {
        bookDao.update(book);
    }
}

3.6Utils层

public class DruidUtils {

    private static DataSource dataSource=null;
    static {
        try {
            Properties properties=new Properties();
            InputStream is=DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            properties.load(is);
            is.close();
            dataSource= DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("连接池初始化失败");
        }

    }

    public static DataSource getDataSource(){
        return dataSource;
    }
}

3.7view层

public class BookSystem {

    private static final ThreadLocal<SimpleDateFormat> threadLocal=new ThreadLocal<SimpleDateFormat>(){
        @Override
        protected SimpleDateFormat initialValue() {
            return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        }
    };

    public static void main(String[] args) {
        Scanner input = new Scanner(System.in);
        System.out.println("=============欢迎进入千锋书籍系统=============");
        System.out.println("请登录");
        System.out.println("请输入用户名");
        String username = input.next();
        System.out.println("请输入密码");
        String password = input.next();
        UserService userService = new UserServiceImpl();
        User user = userService.login(username, password);
        //判断是否是管理员
        BookService bookService=new BookServiceImpl();
        if (user.getRole() == 0) {
            //管理员
            label:do {
                System.out.println("--------1查询所有 2 根据id查询 3根据书名查询 4 添加 5删除 0退出--------");
                System.out.println("请输入");
                int choice = input.nextInt();
                switch (choice) {
                    case 1:
                        List<Book> books = bookService.queryAll();
                        if(books!=null){
                            for (Book book : books) {
                                System.out.println(book.toString());
                            }
                        }
                        break;
                    case 2:
                        System.out.println("请输入要查询的书籍id");
                        int bookId=input.nextInt();
                        Book book = bookService.queryById(bookId);
                        if(book!=null){
                            System.out.println(book.toString());
                        }else{
                            System.out.println("没有找到");
                        }
                        break;
                    case 3:
                        System.out.println("请输入要查询的书名");
                        String title=input.next();
                        List<Book> booksList = bookService.QueryByTitle(title);
                        if(booksList!=null){
                            for (Book b : booksList) {
                                System.out.println(b.toString());
                            }
                        }
                        break;
                    case 4:
                        Book b=bookInfo();
                        bookService.add(b);
                        break;
                    case 5:
                        System.out.println("请输入删除的书籍id");
                        int bId=input.nextInt();
                        try {
                            bookService.remove(bId);
                            System.out.println("删除成功");
                        } catch (Exception e) {
                            System.out.println(e.getMessage());
                        }
                        break;
                    case 0:
                        System.out.println("欢迎下次光临");
                        break label;
                    default:
                        System.out.println("输入有误，请重新输入");
                        break;
                }
            } while (true);

        } else {
            //普通用户
            System.out.println("--------1查询所有 2 根据id查询 3根据书名查询 0退出--------");
        }
    }
    public static Book bookInfo(){
        Scanner input=new Scanner(System.in);
        System.out.println("请输入书名");
        String title=input.next();
        System.out.println("请输入作者");
        String author=input.next();
        System.out.println("请输入出版日期");
        input.nextLine();//读取换行
        String publicDate=input.nextLine();
        System.out.println("请输入出版社");
        String publisher=input.next();
        System.out.println("请输入isbn");
        String isbn=input.next();
        System.out.println("请输入价格");
        double price=input.nextDouble();
        System.out.println("请输入类别id");
        int cid=input.nextInt();
        SimpleDateFormat sdf=threadLocal.get();
        try {
            Book book=new Book(0, title, author, sdf.parse(publicDate), publisher, isbn, new BigDecimal(price), null,cid );
            return book;
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }
}