Mybatis-plus连表查询_mybatisplus连表查询

Mybatis-plus连表查询

mybatis-plus-join解决了mybatis-plus只能进行单表查询的问题；该插件学习成本低，使用方便，为mybatis-plus多表查询提供了高效且优雅的解决方案。

mybatis-plus-join插件

github地址：: mybatis-plus-join
pom依赖：

		<dependency>
            <groupId>com.github.yulichang</groupId>
            <artifactId>mybatis-plus-join</artifactId>
            <version>1.2.4</version>
        </dependency>
1
2
3
4
5

注意：注意: mybatis plus version >= 3.3.0！！！！！

使用配置

• mapper继承MPJBaseMapper (必选)

@Repository
public interface MdaImageMapper extends MPJBaseMapper<MdaImage> {

}
1
2
3
4

• service继承MPJBaseService (可选)
• serviceImpl继承MPJBaseServiceImpl (可选)

用例

简单的连表查询：

class test {
    @Resource
    private UserMapper userMapper;

    void testJoin() {
        //和Mybatis plus一致，MPJLambdaWrapper的泛型必须是主表的泛型，并且要用主表的Mapper来调用
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>()
                .selectAll(UserDO.class)//查询user表全部字段
                .select(UserAddressDO::getTel)//查询user_address tel 字段
                .selectAs(UserAddressDO::getAddress, UserDTO::getUserAddress)//别名 t.address AS userAddress
                .select(AreaDO::getProvince, AreaDO::getCity)
                .leftJoin(UserAddressDO.class, UserAddressDO::getUserId, UserDO::getId)
                .leftJoin(AreaDO.class, AreaDO::getId, UserAddressDO::getAreaId)
                .eq(UserDO::getId, 1)
                .like(UserAddressDO::getTel, "1")
                .gt(UserDO::getId, 5);

        //连表查询 返回自定义ResultType
        List<UserDTO> list = userMapper.selectJoinList(UserDTO.class, wrapper);

        //分页查询 （需要启用 mybatis plus 分页插件）
        Page<UserDTO> listPage = userMapper.selectJoinPage(new Page<>(2, 10), UserDTO.class, wrapper);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

对应的sql语句：

SELECT  
    t.id, t.name, t.sex, t.head_img, 
    t1.tel, t1.address AS userAddress,
    t2.province, t2.city 
FROM 
    user t 
    LEFT JOIN user_address t1 ON t1.user_id = t.id 
    LEFT JOIN area t2 ON t2.id = t1.area_id 
WHERE (
    t.id = ? 
    AND t1.tel LIKE ? 
    AND t.id > ?)
1
2
3
4
5
6
7
8
9
10
11
12

说明

• 条件查询与mybatis-plus相同。

• leftJoin() 参数说明

  • 第一个参数: 参与连表的实体类class
  • 第二个参数: 连表的ON字段,这个属性必须是第一个参数实体类的属性
  • 第三个参数: 参与连表的ON的另一个实体类属性

使用

对Mdapatient表与MdaImage表进行连表查询，先建立所需数据结构PatientImageVo;
1

@Data
@ToString
public class PatientsImageVo {
    private String date;
    @TableId
    private String ctId;
    private Integer patientId;
    private String name;
    private String sex;
    private int age;
    private String ct_type;
    private int status;
}
1
2
3
4
5
6
7
8
9
10
11
12
13

修改MdaImageMapper

@Repository
public interface MdaImageMapper extends MPJBaseMapper<MdaImage> {

}
1
2
3
4

将查询方法写入MdaPatientServiceImpl

public class MdaPatientServiceImpl extends ServiceImpl<MdaPatientMapper, MdaPatient>
    implements MdaPatientService{

    @Autowired
    MdaImageMapper mdaImageMapper;

    @Override
    public List<PatientsImageVo> patientImageList(String doctorId) {
        MPJLambdaWrapper<MdaImage> mdaPatientMPJQueryWrapper=new MPJLambdaWrapper<MdaImage>()
        		//查询需要的字段
                .select(MdaImage::getDate,MdaImage::getCtId,MdaImage::getPatientId)
                .select(MdaPatient::getName,MdaPatient::getSex,MdaPatient::getAge)
                .select(MdaImage::getCtType,MdaImage::getStatus)
                //连表，匹配字段
                .leftJoin(MdaPatient.class, MdaPatient::getId,MdaImage::getPatientId)
                //查询条件
                .eq(MdaPatient::getDoctorId, doctorId);
        //返回list
        List<PatientsImageVo> list=mdaImageMapper.selectJoinList(PatientsImageVo.class, mdaPatientMPJQueryWrapper);
        return list;
    }
 }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22