数据库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>