mybatis-plus-join
在项目中添加依赖
<dependency> <groupId>com.github.yulichang</groupId> <artifactId>mybatis-plus-join</artifactId> <version>1.2.2</version> </dependency>
或者clone代码到本地,执行mvn install,再引入以上依赖
注意: mybatis plus version >= 3.4.0
MPJLambdaWrapper示例
简单的3表查询
class test { @Resource private UserMapper userMapper; void testJoin() { List<UserDTO> list = userMapper.selectJoinList(UserDTO.class, new MPJLambdaWrapper<UserDO>() .selectAll(UserDO.class) .select(UserAddressDO::getTel) .selectAs(UserAddressDO::getAddress, UserDTO::getUserAddress) .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)); } }
对应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 > ?)
说明:
分页查询
class test { @Resource private UserMapper userMapper; void testJoin() { IPage<UserDTO> iPage = userMapper.selectJoinPage(new Page<>(2, 10), UserDTO.class, new MPJLambdaWrapper<UserDO>() .selectAll(UserDO.class) .select(UserAddressDO::getTel) .selectAs(UserAddressDO::getAddress, UserDTO::getUserAddress) .select(AreaDO::getProvince, AreaDO::getCity) .leftJoin(UserAddressDO.class, UserAddressDO::getUserId, UserDO::getId) .leftJoin(AreaDO.class, AreaDO::getId, UserAddressDO::getAreaId)); } }
对应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 LIMIT ?,?
简单的3表查询
class test { @Resource private UserMapper userMapper; void testJoin() { List<UserDTO> list = userMapper.selectJoinList(UserDTO.class, new MPJQueryWrapper<UserDO>() .selectAll(UserDO.class) .select("addr.tel", "addr.address", "a.province") .leftJoin("user_address addr on t.id = addr.user_id") .rightJoin("area a on addr.area_id = a.id") .like("addr.tel", "1") .le("a.province", "1")); } }
对应sql
SELECT t.id, t.name, t.sex, t.head_img, addr.tel, addr.address, a.province FROM user t LEFT JOIN user_address addr on t.id = addr.user_id RIGHT JOIN area a on addr.area_id = a.id WHERE ( addr.tel LIKE ? AND a.province <= ?)
说明:
分页查询
class test { @Resource private UserMapper userMapper; void testJoin() { IPage<UserDTO> page = userMapper.selectJoinPage(new Page<>(1, 10), UserDTO.class, new MPJQueryWrapper<UserDO>() .selectAll(UserDO.class) .select("addr.tel", "addr.address") .select("a.province") .leftJoin("user_address addr on t.id = addr.user_id") .rightJoin("area a on addr.area_id = a.id")); } }
对应sql
SELECT t.id, t.name, t.sex, t.head_img, addr.tel, addr.address, a.province FROM user t LEFT JOIN user_address addr on t.id = addr.user_id RIGHT JOIN area a on addr.area_id = a.id LIMIT ?,?
还可以这么操作,但不建议
class test { @Resource private UserMapper userMapper; void testJoin() { List<UserDTO> list = userMapper.selectJoinList(UserDTO.class, new MPJQueryWrapper<UserDO>() .selectAll(UserDO.class) .select("addr.tel", "addr.address") //行列转换 .select("CASE t.sex WHEN '男' THEN '1' ELSE '0' END AS sex") //求和函数 .select("sum(a.province) AS province") //自定义数据集 .leftJoin("(select * from user_address) addr on t.id = addr.user_id") .rightJoin("area a on addr.area_id = a.id") .like("addr.tel", "1") .le("a.province", "1") .orderByDesc("addr.id")); } }
对应sql
SELECT t.id, t.name, t.sex, t.head_img, addr.tel, addr.address, CASE t.sex WHEN '男' THEN '1' ELSE '0' END AS sex, sum(a.province) AS province FROM user t LEFT JOIN (select * from user_address) addr on t.id = addr.user_id RIGHT JOIN area a on addr.area_id = a.id WHERE ( addr.tel LIKE ? AND a.province <= ?) ORDER BY addr.id DESC