项目阶段三：图书模块的数据库准备与dao、Service层

2023-12-16 21:28:21

1、编写图书模块的数据库表

drop table if exists t_book;
create table t_book(
    `id` int primary key auto_increment,
    `bookname` varchar (100),
    `author` varchar (100),
    `price` decimal (11,2),
    `sales` int,
    `stock` int,
    `img_path` varchar (200)
);

插入初始化测试数据

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , 'java从入门到放弃' , '国哥' , 80 , 9999 , 9 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , '数据结构与算法' , '严敏君' , 78.5 , 6 , 13 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , '怎样拐跑别人的媳妇' , '龙伍' , 68, 99999 , 52 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , '木虚肉盖饭' , '小胖' , 16, 1000 , 50 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , 'C++编程思想' , '刚哥' , 45.5 , 14 , 95 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , '蛋炒饭' , '周星星' , 9.9, 12 , 53 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , '赌神' , '龙伍' , 66.5, 125 , 535 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , 'Java编程思想' , '阳哥' , 99.5 , 47 , 36 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , 'JavaScript从入门到精通' , '婷姐' , 9.9 , 85 , 95 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , 'cocos2d-x游戏编程入门' , '国哥' , 49, 52 , 62 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , 'C语言程序设计' , '谭浩强' , 28 , 52 , 74 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , 'Lua语言程序设计' , '雷丰阳' , 51.5 , 48 , 82 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , '西游记' , '罗贯中' , 12, 19 , 9999 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , '水浒传' , '华仔' , 33.05 , 22 , 88 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , '操作系统原理' , '刘优' , 133.05 , 122 , 188 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , '数据结构 java版' , '封大神' , 173.15 , 21 , 81 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , 'UNIX高级环境编程' , '乐天' , 99.15 , 210 , 810 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , 'javaScript高级编程' , '国哥' , 69.15 , 210 , 810 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , '大话设计模式' , '国哥' , 89.15 , 20 , 10 , 'static/img/default.jpg');

insert into t_book(`id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path`)
values(null , '人月神话' , '刚哥' , 88.15 , 20 , 80 , 'static/img/default.jpg');

2、编写图书模块的 JavaBean

private Integer id;
private String name;
private String author;
private BigDecimal price;
private Integer sales;
private Integer stock;
private String imgPath = "static/img/default.jpg";
public Book(Integer id, String bookName, String author, BigDecimal price, Integer sales, Integer stock, String imgPath) {
    this.id = id;
    this.bookName = bookName;
    this.author = author;
    this.price = price;
    this.sales = sales;
    this.stock = stock;
    // 要求给定的图书封面图书路径不能为空
    if(imgPath != null && ! "".equals(imgPath)){
        this.imgPath = imgPath;
    }
}
public void setImgPath(String imgPath) {
    // 要求给定的图书封面图书路径不能为空
    if(imgPath != null && ! "".equals(imgPath)){
        this.imgPath = imgPath;
    }
}

3、编写图书模块的 Dao 和测试 Dao

Dao 接口：

public interface BookDao {

    /**
     * 添加图书
     *
     * @param book 要添加的图书的信息
     * @return -1：添加失败，其它数字表示返回添加图书的本数
     */
    int addBook(Book book);

    /**
     * 修改图书信息
     * @param book 要修改的图书的信息
     * @return -1：修改失败，其它数字表示返回添加图书的本数
     */
    int updateBook(Book book);

    /**
     * 通过ID删除图书
     * @param id 要删除图书的ID
     * @return -1：删除失败，其它数字表示返回添加图书的本数
     */
    int deleteBookById(Integer id);

    /**
     * 通过ID查找图书
     * @param id 要查找图书的ID
     * @return 所查找的图书的信息
     */
    Book queryBookById(Integer id);

    /**
     * 查看所图书信息
     * @return 数据库里所图书信息集合
     */
    List<Book>  queryBooks();
}

BookDaoImpl 实现类：

public class BookDaoImpl extends BaseDao implements BookDao {
    @Override
    public int addBook(Book book) {
        String sql = "insert into t_book (`bookname`,`author`,`price`,`sales`,`stock`,`img_path`)" +
                " values(?,?,?,?,?,?)";
        return upDate(sql,book.getBookName(),book.getAuthor(),book.getPrice(),book.getSales(),book.getStock(),book.getImgPath());
    }

    @Override
    public int updateBook(Book book) {
        String sql = "update t_book set `bookname`=?,`author`=?,`price`=?,`sales`=?,`stock`=?,`img_path`=? where id=?";
        return upDate(sql,book.getBookName(),book.getAuthor(),book.getPrice(),book.getSales(),book.getStock(),book.getImgPath(),book.getId());
    }

    @Override
    public int deleteBookById(Integer id) {
        String sql = "delete from t_book where id = ?";

        return upDate(sql,id);
    }

    @Override
    public Book queryBookById(Integer id) {
        String sql = "select `id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path` imgPath from t_book where id = ?";
        return queryForOne(Book.class,sql,id);
    }

    @Override
    public List<Book> queryBooks() {
        String sql = "select `id` , `bookname` , `author` , `price` , `sales` , `stock` , `img_path` imgPath from t_book";
        return queryForList(Book.class,sql);
    }
}

BookDao 的测试：

public class BookDaoTest {

    private BookDao bookDao = new BookDaoImpl();

    @Test
    public void addBook() {
        bookDao.addBook(new Book(null,"安徒生童话","安徒生",new BigDecimal(22.2),99,8,null));
    }

    @Test
    public void updateBook() {
        bookDao.updateBook(new Book(21,"安徒生童话大全","安徒生",new BigDecimal(33.3),99,8,null));
    }

    @Test
    public void deleteBookById() {
        bookDao.deleteBookById(21);
    }

    @Test
    public void queryBookById() {
        System.out.println(bookDao.queryBookById(12));
    }

    @Test
    public void queryBooks() {
        List<Book> books = bookDao.queryBooks();
        for(Book book : books){
            System.out.println(book);
        }
    }
}

4、编写图书模块的 Service 和测试 Service

BookService 接口：

public interface BookService {

    /**
     * 添加图书
     * @param book 要添加的图书的信息
     */
    void addBook(Book book);

    /**
     * 通过ID删除图书
     * @param id 要删除图书的ID
     */
    void deleteBookById(Integer id);

    /**
     *  修改图书信息
     * @param book 要修改的图书的信息
     */
    void updateBook(Book book);

    /**
     * 通过ID查找图书
     * @param id 要查找图书的ID
     * @return 所查找的图书的信息
     */
    Book queryBookById(Integer id);

    /**
     * 查看所图书信息
     * @return 数据库里所图书信息集合
     */
    List<Book> queryBooks();
}

BookServiceImpl 实现类：

public class BookServiceImpl implements BookService {

    BookDao bookDao = new BookDaoImpl();
    @Override
    public void addBook(Book book) {
        bookDao.addBook(book);
    }

    @Override
    public void deleteBookById(Integer id) {
        bookDao.deleteBookById(id);
    }

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

    @Override
    public Book queryBookById(Integer id) {
        return bookDao.queryBookById(id);
    }

    @Override
    public List<Book> queryBooks() {
        return bookDao.queryBooks();
    }
}

BookService 的测试：

public class BookServiceTest {

    private BookService bookService = new BookServiceImpl();
    @Test
    public void addBook() {
        bookService.addBook(new Book(null,"安徒生童话","安徒生",new BigDecimal(22.2),99,8,null));
    }

    @Test
    public void deleteBookById() {
        bookService.deleteBookById(22);
    }

    @Test
    public void updateBook() {
        bookService.updateBook(new Book(22,"安徒生","安徒生",new BigDecimal(22.2),99,8,null));
    }

    @Test
    public void queryBookById() {
        System.out.println(bookService.queryBookById(12));
    }

    @Test
    public void queryBooks() {
        List<Book> books = bookService.queryBooks();
        for(Book book : books){
            System.out.println(book);
        }
    }
}
上一篇：(精华)2020年9月6日 C#基础知识点 Linq的使用场景


下一篇：gin框架用go-redis+redsync实现分布式锁