mybatis-plus实现自定义SQL、多表查询、多表分页查询_mybatisplus自定义查询

查看此文章前强烈建议先看这篇文章：Java江湖路 | 专栏目录

前言

本文介绍了在mybatis-plus中如何实现：自定义SQL语句，多表查询语句，多表分页查询语句

在说怎么实现之前我们要先明白一个概念，就是mybatis-plus是在mybatis的基础上进行增强，并不做改变，所以mybatis的操作在mybatis-plus中也是一样可以使用的，咱们直接上代码

---

1、自定义SQL

在mapper中自定义一个方法即可

@Repository
public interface EduTeacherMapper extends BaseMapper<EduTeacher> {
    @Select("select * from edu_teacher")
    EduTeacherMapper selectTeacherList();
}
1
2
3
4
5

然后要调用该方法的话，通过注入mapper，然后通过mapper调用方法即可

@Service
public class EduTeacherServiceImpl extends ServiceImpl<EduTeacherMapper, EduTeacher> implements EduTeacherService {

    @Autowired
    private EduTeacherMapper teacherMapper;

    public EduTeacher selectTheacher(){
        return teacherMapper.selectTheacher();
    }
}

1
2
3
4
5
6
7
8
9
10
11

2、多表查询

多表查询和自定义SQL是一样写的，唯一区别就是需要创建一个 vo类来接收数据即可。

@Repository
public interface EduTeacherMapper extends BaseMapper<EduTeacher> {
   @Select("SELECT * FROM edu_teacher t1 INNER JOIN edu_course t2 ON t1.id = t2.teacher_id")
   EduTeacherVo selectTheacher();
}
1
2
3
4
5

3、多表分页查询

分页查询的话就需要用到Page了
1、需要先创建一个配置类，然后在配置类中引入分页插件（固定代码）

@Configuration                  
public class MpConfig {
    /**
     * 分页插件
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }
}
1
2
3
4
5
6
7
8
9
10

2、mapper代码如下：

public interface EduTeacherMapper extends BaseMapper<EduTeacher> {
    @Select("select * from edu_teacher")
    IPage<EduTeacher> selectTheacher(Page<EduTeacher> page);
}
1
2
3
4
5

3、serviceImpl实现类代码如下：

@Service
public class EduTeacherServiceImpl extends ServiceImpl<EduTeacherMapper, EduTeacher> implements EduTeacherService {

    @Autowired
    private EduTeacherMapper teacherMapper;

    public void selectTheacher(){
        //1代表当前页数，10代表每页数据的数量
        Page<EduTeacher> page = new Page<>(1,10);
        IPage<EduTeacher> eduTeacherIPage = teacherMapper.selectTheacher(page);

        System.out.println(eduTeacherIPage.getCurrent());  //当前页
        System.out.println(eduTeacherIPage.getPages());    //每页数据 是一个List集合
        System.out.println(eduTeacherIPage.getSize());     //每页显示记录数
        System.out.println(eduTeacherIPage.getTotal());    //总记录数
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

4、多表分页条件查询

多表分页条件查询只是在多表分页查询的基础上增加了条件筛选而已
其它步骤都和多表分页查询一样，只要在mapper中写sql的时候多加条件参数就可以了。注意：如果使用了动态SQL，那么该sql语句一定要使用script标签包裹

@Select("<script>
  SELECT t1.id,t1.qiye_id,t1.member_tag,t1.name,t1.logo,t1.images,t1.video,t1.contacts_mobile,t1.contacts_name,t1.address,t1.sort,t1.sort,t1.click_num,t1.is_put,t1.company_type,t1.is_attestation,t1.is_recommend,t1.introduce,t1.create_time,t2.laoa_product_type_id AS productId,t3.laoa_style_id AS styleId FROM laoa_ad_logistics t1 LEFT JOIN laoa_product_middle t2 ON t1.qiye_id = t2.company_key LEFT JOIN laoa_style_middle t3 ON t1.qiye_id = t3.company_key
  <where>
    t1.company_type = 1
    <if test="adLogisticsVo.isOk == true">
      AND t1.is_put = 1
    </if>
    <if test="adLogisticsVo.name != null">
      AND t1.name LIKE CONCAT('%',#{adLogisticsVo.name},'%')
    </if>
    <if test="adLogisticsVo.styleId != null">
      AND t3.laoa_style_id = #{adLogisticsVo.styleId}
    </if>
    <if test="adLogisticsVo.productId != null">
      AND t2.laoa_product_type_id = #{adLogisticsVo.productId}
    </if>
  </where>
  ORDER BY t1.sort
</script>")
IPage<AdLogisticsVo> selectAdLogisticsList(@Param("page") IPage<AdLogisticsVo> page, @Param("adLogisticsVo") AdLogisticsVo adLogisticsVo);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

---