谷粒商城-全栈-31 商品服务-Mybatis-plus 分页插件及自定义 SQL 语句

一、添加Mybatis-plus分页配置

src/main/java/com/atguigu/gulimall/product/config/MyBatisConfig.java

package com.atguigu.gulimall.product.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * 分页类配置
 *
 * @doc: https://baomidou.com/guide/page.html
 *
 * @author: kaiyi
 * @create: 2020-08-23 01:36
 */
@Configuration
@EnableTransactionManagement // 开启事务
@MapperScan("com.atguigu.gulimall.product.dao")
public class MyBatisConfig {
  @Bean
  public PaginationInterceptor paginationInterceptor() {
    PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
    // 设置请求的页面大于最大页后操作, true调回到首页,false 继续请求  默认false
    paginationInterceptor.setOverflow(true);
    // 设置最大单页限制数量,默认 500 条,-1 不受限制
    paginationInterceptor.setLimit(1000);
    // 开启 count 的 join 优化,只针对部分 left join
    paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
    return paginationInterceptor;
  }

}

配置好之后,重启服务即可自动分页。

二、自定义SQL

product/service/impl/CategoryBrandRelationServiceImpl.java

/**
   * 更新分类
   *
   * @param catId
   * @param name
   */
    public void updateCategory(Long catId, String name){
      this.baseMapper.updateCategory(catId, name);
    }

Dao层(Mapper层):com/atguigu/gulimall/product/dao/CategoryBrandRelationDao.java

package com.atguigu.gulimall.product.dao;

import com.atguigu.gulimall.product.entity.CategoryBrandRelationEntity;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

/**
 * 品牌分类关联
 * 
 * @author kaiyi
 * @email corwienwong@gmail.com
 * @date 2020-08-10 15:45:20
 */
@Mapper
public interface CategoryBrandRelationDao extends BaseMapper<CategoryBrandRelationEntity> {

 // 这里的参数需要使用注解@Param(),否则
  void updateCategory(@Param("catId") Long catId, @Param("name") String name);
}

SQL 语句:
mapper/product/CategoryBrandRelationDao.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.atguigu.gulimall.product.dao.CategoryBrandRelationDao">

    <!-- 可根据自己的需求,是否要使用 -->
    <resultMap type="com.atguigu.gulimall.product.entity.CategoryBrandRelationEntity" id="categoryBrandRelationMap">
        <result property="id" column="id"/>
        <result property="brandId" column="brand_id"/>
        <result property="catelogId" column="catelog_id"/>
        <result property="brandName" column="brand_name"/>
        <result property="catelogName" column="catelog_name"/>
    </resultMap>
  <update id="updateCategory">
   UPDATE `pms_category_brand_relation` SET catelog_name=#{name} WHERE catelog_id=#{catId}
  </update>

</mapper>

为者常成,行者常至