SpringBoot的增删改查、分页(Book书籍管理)

数据库sql：

create database bookmis;
​
use bookmis;
​
SET FOREIGN_KEY_CHECKS=0;
 
 --编号，书名，价格，出版日期，作者
 --商品信息表
CREATE TABLE `book` (
    `bid` INT(20) NOT NULL AUTO_INCREMENT COMMENT '主键编号',
    `bookname` varchar(30) COLLATE utf8_bin NOT NULL COMMENT '书名',
    `price` decimal(10,2) COLLATE utf8_bin NOT NULL COMMENT '价格',
    `publicdate` DATE  NOT NULL COMMENT '出版日期',
    `author` varchar(30) COLLATE utf8_bin NOT NULL COMMENT '作者',
    PRIMARY KEY (`bid`)
)AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

​
INSERT INTO `book`(`bookname`,`price`,`publicdate`,`author`)
 VALUES('红楼梦','68.00','1993-03-21','曹雪芹');
INSERT INTO `book`(`bookname`,`price`,`publicdate`,`author`)
 VALUES('老人与海','58.00','2008-06-25','海明威');
 INSERT INTO `book`(`bookname`,`price`,`publicdate`,`author`)
 VALUES('活着','55.00','2005-05-15','余华');
INSERT INTO `book`(`bookname`,`price`,`publicdate`,`author`)
 VALUES('永别了，武器','89.00','2003-07-14','海明威');
INSERT INTO `book`(`bookname`,`price`,`publicdate`,`author`)
 VALUES(' 太阳照样升起','67.90','1996-06-11','海明威');
 INSERT INTO `book`(`bookname`,`price`,`publicdate`,`author`)
 VALUES('人间告白','66.00','2009-11-21','金鱼酱');
 INSERT INTO `book`(`bookname`,`price`,`publicdate`,`author`)
 VALUES('简爱','58.00','2000-12-11','勃朗特');
SELECT * from `book`; --查询表
DROP  TABLE `book`; --删除表

一、pom.xml添加分页依赖

 <!-- 分页 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.7</version>
        </dependency>

二、在application.yaml添加配置

spring:
  datasource:
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/bookmis?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  type-aliases-package: com.nf.dityss.entity
  mapper-locations: classpath:/mappers/*.xml

三、在entity包写实体类

@NoArgsConstructor
@AllArgsConstructor
@Data
public class Book {
    private Integer bid;// INT(20) NOT NULL AUTO_INCREMENT COMMENT '主键编号',
    private String bookname;// varchar(30) COLLATE utf8_bin NOT NULL COMMENT '书名',
    private BigDecimal price;// decimal(10,2) COLLATE utf8_bin NOT NULL COMMENT '价格',
    @DateTimeFormat(pattern="yyyy-MM-dd")
    @JsonFormat(pattern="yyyy-MM-dd",timezone = "GMT+8")
    private Date publicdate;// DATE  NOT NULL COMMENT '出版日期',
    private String author;// varchar(30) COLLATE utf8_bin NOT NULL COMMENT '作者',
​
}

四、mapper包

@Mapper
public interface BookMapper {
    /**查询 显示所有图书信息 */
    public List<Book> getBook(Book book);
    /**删除*/
    public int deletebookById(String bid);
    /** 添加学生 */
    public int BookAdd(Book book);
    /** 单条查询学生 */
    public Book getBookId(String bid);
    /** 修改学生 */
    public Boolean updateBook(Book book);
    /** 多条件模糊查询 学生 */
    public List<Book> getBookbyId(Book book);
    /** 批量删除 */
    public Boolean bookDelete(@Param("ids") String ids[]);
    /** 获取学生并分页 */
    public List<Book> selectPager();
}

五、service包

public interface BookService {
    /**查询 显示所有图书信息 */
    public List<Book> getBook(Book book);
    /**删除*/
    public int deletebookById(String bid);
    /** 添加学生 */
    public int BookAdd(Book book);
    /** 单条查询学生 */
    public Book getBookId(String bid);
    /** 修改学生 */
    public Boolean updateBook(Book book);
    /** 多条件模糊查询 学生 */
    public List<Book> getBookbyId(Book book);
​
    /** 批量删除 */
    public Boolean bookDelete(@Param("ids") String ids[]);
​
    /** 获取学生并分页 */
    public PageResult selectPager(int pageNum, int pageSize, Book book);
​
}

六、service包 --> impl包

@Service
public class BookServiceImpl implements BookService {
    @Autowired
    BookMapper bookMapper;
​
    @Override
    public List<Book> getBook(Book book) {
        return bookMapper.getBook(book);
    }
​
    @Override
    public int deletebookById(String bid) {
        return bookMapper.deletebookById(bid);
    }
​
    @Override
    public int BookAdd(Book book) {
        return bookMapper.BookAdd(book);
    }
​
    @Override
    public Book getBookId(String bid) {
        return bookMapper.getBookId(bid);
    }
​
    @Override
    public Boolean updateBook(Book book) {
        return bookMapper.updateBook(book);
    }
​
    @Override
    public List<Book> getBookbyId(Book book) {
        return bookMapper.getBookbyId(book);
    }
​
    @Override
    public Boolean bookDelete(String[] ids) {
        return bookMapper.bookDelete(ids);
    }
​
    @Override
    public PageResult selectPager(int pageNum, int pageSize,Book book) {
        //开始分页，指定第几页，每页多少记录
        PageHelper.startPage(pageNum,pageSize);
        //查询学生列表
        List<Book> books = bookMapper.getBook(book);
        //返回分页对象
        return PageResult.getPageResult(new PageInfo<Book>(books));
    }

七、config包

跨域请求:

@Configuration
public class GlobalCorsConfig {
    @Bean
    public CorsFilter corsFilter() {
        //1. 添加 CORS配置信息
        CorsConfiguration config = new CorsConfiguration();
        //放行哪些原始域
        config.addAllowedOrigin("http://localhost:8081/");
        config.addAllowedOrigin("http://localhost:5173/");
        config.addAllowedOrigin("http://127.0.0.1:5501/");
        config.addAllowedOrigin("http://localhost:63342/");
        //是否发送 Cookie
        config.setAllowCredentials(true);
        //放行哪些请求方式
        config.addAllowedMethod("*");
        //放行哪些原始请求头部信息
        config.addAllowedHeader("*");
        //暴露哪些头部信息
        config.addExposedHeader("*");
        //2. 添加映射路径
        UrlBasedCorsConfigurationSource corsConfigurationSource = new UrlBasedCorsConfigurationSource();
        corsConfigurationSource.registerCorsConfiguration("/**",config);
        //3. 返回新的CorsFilter
        return new CorsFilter(corsConfigurationSource);
    }
}

分页请求:

/**
 * 分页请求
 */
public class PageRequest {
    /**
     * 当前页码
     */
    private int pageNum;
    /**
     * 每页数量
     */
    private int pageSize;
​
    public int getPageNum() {
        return pageNum;
    }
​
    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }
​
    public int getPageSize() {
        return pageSize;
    }
​
    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }
}

分页信息：

public class PageResult {
    /**
     * 当前页码
     */
    private int pageNum;
    /**
     * 每页数量
     */
    private int pageSize;
    /**
     * 记录总数
     */
    private long pageTotal;
    /**
     * 页码总数
     */
    private int totalPages;
    /**
     * 数据模型
     */
    private List<?> list;
​
    public int getPageNum() {
        return pageNum;
    }
​
    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }
​
    public int getPageSize() {
        return pageSize;
    }
​
    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }
​
    public long getPageTotal() {
        return pageTotal;
    }
​
    public void setPageTotal(long pageTotal) {
        this.pageTotal = pageTotal;
    }
​
    public int getTotalPages() {
        return totalPages;
    }
​
    public void setTotalPages(int totalPages) {
        this.totalPages = totalPages;
    }
​
    public List<?> getList() {
        return list;
    }
​
    public void setList(List<?> list) {
        this.list = list;
    }
    /**
     * 将分页信息封装到统一的接口
     * @return
     */
    public static PageResult getPageResult(PageInfo<?> pageInfo) {
        PageResult pageResult = new PageResult();
        pageResult.setPageNum(pageInfo.getPageNum());
        pageResult.setPageSize(pageInfo.getPageSize());
        pageResult.setPageTotal(pageInfo.getTotal());
        pageResult.setTotalPages(pageInfo.getPages());
        pageResult.setList(pageInfo.getList());
        return pageResult;
    }

八、Controller控制层

@RestController
@RequestMapping("/api/book")
public class BookController {
    @Autowired
    BookService bookService;
​
    @PostMapping("/findAll")
    public List<Book> getBook(@RequestBody Book book){
        return bookService.getBook(book);
    }
    @DeleteMapping("/deleteBookById")
    public int deletebookById(String bid){
        return bookService.deletebookById(bid);
    }
​
    @PostMapping("/bookAdd")
    public int BookAdd(@RequestBody Book book){
        return bookService.BookAdd(book);
    }
​
    @GetMapping("/getBookId")
    public Book getBookId(String bid){
        System.out.println("查询信息"+bid);
        return bookService.getBookId(bid);
    }
    @PostMapping("/updateBook")
    public Boolean updateBook(@RequestBody Book book){
        System.out.println("修改信息"+book);
        return bookService.updateBook(book);
    }
    @PostMapping("/getBookbyId")
    public List<Book> getBookbyId(@RequestBody Book book){
        System.out.println("查询信息"+book);
        return bookService.getBookbyId(book);
    }
    @PostMapping("/bookDelete")
    public Boolean bookDelete(@RequestBody String ids[]){
        return bookService.bookDelete(ids);
    }
​
    @GetMapping("/pager")
    public PageResult selectPager(@RequestParam(name="pageNum",required = false,defaultValue = "1") int pageNum,
                                  @RequestParam(name="pageSize",required = false,defaultValue = "2")int pageSize,
​
                                  @RequestParam(required = false,defaultValue = "") Integer bid,
                                  @RequestParam(required = false,defaultValue = "") String bookname,
                                  @RequestParam(required = false,defaultValue = "") BigDecimal price,
                                  @RequestParam(required = false,defaultValue = "") Date publicdate,
                                  @RequestParam(required = false,defaultValue = "") String author
    ){
        //获取所有学生信息
        Book book = new Book();
        book.setBid(bid);
        book.setBookname(bookname);
        book.setPrice(BigDecimal.valueOf(Long.parseLong(String.valueOf(price))));
        book.setPublicdate(publicdate);
//        book.setAuthor(author);
        if(!author.equals("")){
            book.setAuthor(String.valueOf(DateUtil.parse(author,"yyyy-MM-dd")));
        }
​
//        List<Student> allDitys = studentService.getStudent(student);
​
        return bookService.selectPager(pageNum,pageSize,book);
    }
}

九、BookMapper.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.nf.dityss.mapper.BookMapper">
​
    <resultMap id="BookMap" type="Book">
        <result property="bid" column="bid"></result>
        <result property="bookname" column="bookname"></result>
        <result property="price" column="price"></result>
        <result property="publicdate" column="publicdate"></result>
        <result property="author" column="author"></result>
    </resultMap>
​
    <select id="getBook" resultMap="BookMap">
        select * from book
        <where>
            <if test="bid!=null and bid!=''">
                and book.bid like concat('%',concat(#{bid},'%'))
            </if>
​
            <if test="bookname!=null and bookname!=''">
                and book.bookname like concat('%',concat(#{bookname},'%'))
            </if>
​
            <if test="price!=null and price!=''">
                and book.price like concat('%',concat(#{price},'%'))
            </if>
​
            <if test="publicdate!=null">
                and book.publicdate=#{publicdate}
            </if>
​
            <if test="author!=null and author!=''">
                and book.author like concat('%',concat(#{author},'%'))
            </if>
        </where>
    </select>
​
    <delete id="deletebookById">
        delete from book where bid=#{bid}
    </delete>
​
    <insert id="BookAdd">
        insert into book(book.bookname,
                         book.price,
                         book.publicdate,
                         book.author)
        values(#{bookname},#{price},#{publicdate},#{author})
    </insert>
​
    <select id="getBookId" resultMap="BookMap">
        select * from book where bid =#{bid}
    </select>
​
    <update id="updateBook">
        update book set
                        book.bid=#{bid},
                        book.bookname=#{bookname},
                        book.price=#{price},
                        book.publicdate=#{publicdate},
                        book.author=#{author}
        where bid=#{bid}
    </update>
​
    <select id="getBookbyId" resultMap="BookMap">
        select * from book
​
<!--        <trim prefix="WHERE" prefixOverrides="AND|OR">-->
<!--            <if test="bid !=null">-->
<!--                and bid=#{bid}-->
<!--            </if>-->
<!--            <if test="bookname !=null">-->
<!--                bookname Like CONCAT(#{bookname},'%')-->
<!--            </if>-->
<!--            <if test="publicdate !=null">-->
<!--                and publicdate=#{publicdate}-->
<!--                and publicdate=#{publicdate}-->
<!--                and publicdate=#{publicdate}-->
<!--            </if>-->
<!--            <if test="author !=null">-->
<!--                and author=#{author}-->
<!--            </if>-->
​
<!--        </trim>-->
    </select>
​
    <delete id="bookDelete">
        delete from book where bid in
        <foreach collection="ids" item="bid" open="(" close=")" separator=",">
            #{bid}
        </foreach>
    </delete>
​
    <!--    分页-->
    <select id="selectPager" resultMap="BookMap">
        SELECT
            book.bid,
            book.bookname,
            book.price,
            book.publicdate,
            book.author
        FROM
            book
    </select>
</mapper>