MyBatis-Plus 多表分页动态拼接SQL

参考文章：https://www.jianshu.com/p/759b6430ed5b 一、pom.xml

<dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.0.7.1</version> </dependency> <dependency> <build> <resources> <!--引入静态文件--> <resource> <directory>src/main/resources</directory> <filtering>true</filtering> </resource> <!--引入mapper对应的xml文件--> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> </includes> </resource> </resources> </build>

二、配置文件

yml: mybatis-plus: type-aliases-package: com.harmonycloud.sfc.web.patrol.model.entity #扫描xml文件 mapper-locations: classpath*:com/harmonycloud/sfc/web/patrol/mapper/xml/*.xml global-config: db-config: logic-not-delete-value: 1 logic-delete-value: 0 配置类： @Configuration @MapperScan("com.harmonycloud.sfc.web.patrol.mapper") public class MybatisPlusConfig { /** *分页插件 */ @Bean public PaginationInterceptor paginationInterceptor(){ return new PaginationInterceptor(); } /** * 逻辑删除 */ @Bean public ISqlInjector iSqlInjector(){ return new LogicSqlInjector(); } /** * 打印 sql */ @Bean public PerformanceInterceptor performanceInterceptor() { PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor(); //格式化sql语句 Properties properties = new Properties(); properties.setProperty("format", "true"); performanceInterceptor.setProperties(properties); return performanceInterceptor; } }

三、Service

@Override public Page<Constant> getConstantByDtoAndPage(ConstantDto dto) { #构建分页查询对象 Page<Constant> queryPage = new Page<>(dto.getPageNum(),dto.getPageSize()); #构建查询条件 QueryWrapper wrapper = new QueryWrapper(dto); #调用Mapper自定义接口 List<Constant> list = baseMapper.getConstantByContentAndCategory(queryPage,wrapper); queryPage.setRecords(list); return queryPage; }

四、Mapper

public interface ConstantMapper extends BaseMapper<Constant> { #此处Page<Constant> 也可不指定泛型，直接为Page List<Constant> getConstantByContentAndCategory(Page<Constant> page, @Param("qw") QueryWrapper wrapper); }

五、XML

<select id="getConstantByContentAndCategory" parameterType="com.harmonycloud.sfc.web.patrol.model.dto.ConstantDto" resultType="com.harmonycloud.sfc.web.patrol.model.entity.Constant"> SELECT a.ConstID, a.Content, a.CategoryID, b.Name as Category FROM (SELECT t.ConstID, t.Content, t.CategoryID FROM siconstant t WHERE t.Active = 1 <if test="qw.entity.Content!=null and qw.entity.Content!=''"> and t.Content LIKE CONCAT('%',#{qw.entity.Content,jdbcType=VARCHAR},'%') </if> ) a LEFT JOIN siconstantcategory b ON a.CategoryID = b.CategoryId <if test="qw.entity.Categroy!=null and qw.entity.Categroy!=''"> WHERE Name LIKE CONCAT('%',#{qw.entity.Categroy,jdbcType=VARCHAR},'%') </if> </select>

注意：动态SQL中字符串参数时： 当只有一个参数的时候，可以使用_parameter，它就代表了这个参数，如果使用@Param的话，会使用指定的参数值代替