As we all know, the mapper encapsulated by mybatis plus does not support join. If you need support, you must implement it yourself. But for most business scenarios, multi-table join is required, otherwise there is no need to use a relational database.
So is there a way to provide join capabilities through the framework without using hard SQL? The answer is, yes. After a period of eye insertion and eye row operation, a jar package is successfully packaged. This article talks about its usage.
How to use
Install
Maven
<dependency>
<groupId>com.github.yulichang</groupId>
<artifactId>mybatis-plus-join</artifactId>
<version>1.2.4</version>
</dependency>
Gradle
implementation 'com.github.yulichang:mybatis-plus-join:1.2.4'
Or clone the code to execute mvn install locally, and then introduce the above dependencies
Note: mybatis plus version >= 3.4.0
Use
-
mapper inherits MPJBaseMapper (required)
-
service inherits from MPJBaseService (optional)
-
serviceImpl inherits from MPJBaseServiceImpl (optional)
Core classes MPJLambdaWrapper and MPJQueryWrapper
MPJLambdaWrapper Usage
Simple three-table query
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));
}
}
corresponding to 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 > ?)
Description:
-
UserDTO.classquery result return class (resultType) -
selectAll()Query all fields of the specified entity class -
select()Query the specified field, support variable parameters, the same select can only query the fields of the same table -
So separate
UserAddressDOand AreaDO into twoselect() -
selectAs()Field alias query, used when the database field is inconsistent with the attribute name of the business entity class -
leftJoin()parameter description-
The first parameter: The entity class involved in the link table
-
The second parameter: The ON field of the link table, this attribute must be the attribute of the first parameter entity class
-
The third parameter: Another entity class attribute that participates in the ON of the linked table
-
The default main table alias is t, and other table aliases use t1, t2, t3 in the order of invocation….
Conditional query, you can query the fields of the main table and all tables participating in the connection, all call the native method of mp, there is no risk of sql injection in normal use
Paging queries
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));
}
}
corresponding to 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 ?,?
MPJQueryWrapper
Simple 3 table query
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"));
}
}
corresponding to 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 <= ?)
Description:
–UserDTO.class query result class (resultType)
-
selectAll(UserDO.class)Query all fields of the main table (main table entity class) default main table alias “t” -
select()The select strategy of mp is coverage, whichever is the last time, the strategy here is to add, you can always select -
The main table field can use lambda, and the table alias will be added automatically. The main table alias is t by default, and the non-main table fields must be queried with an alias
-
leftJoin() rightJoin() innerJoin()pass sql fragment format (table + alias + association condition) -
Conditional query, you can query the fields of the main table and all tables participating in the connection, all call the native method of mp, there is no risk of sql injection in normal use
Paging queries
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"));
}
}
corresponding to 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 ?,?
You can still do this, not recommended
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")
// row and column conversion
.select("CASE t.sex WHEN 'male' THEN '1' ELSE '0' END AS sex")
// summation function
.select("sum(a.province) AS province")
//custom data set
.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"));
}
}
corresponding to sql
SELECT
t.id,
t.name,
t. sex,
t.head_img,
addr.tel,
addr. address,
CASE t.sex WHEN 'Male' 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
If you are interested in the above jars, you can download the corresponding source code for further study!