As an enhancement tool for mybatis, mybatis-plus has greatly simplified database operations in development, but for a long time, its conjunction table query capabilities have been criticized by everyone. Once you encounter a left join or right join connection, you still have to open the xml file honestly and write a large paragraph of SQL statements by hand.
I accidentally came across such a tool called mybatis-plus-join (hereinafter referred to as mpj). After using it, I have to say it is really delicious! I was completely liberated from the hell of xml and finally I can conduct joint table query in a way similar to QueryWrapper in mybatis-plus. Without further ado, let's start to experience it.
Plugin Documentation
Plugin Github repository/yulichang/mybatis-plus-join
1. Add dependencies
Add mybatis plus join dependency in pom
<!-- mpj rely -->
<dependency>
<groupId></groupId>
<artifactId>mybatis-plus-join-boot-starter</artifactId>
<version>1.4.4.1</version>
</dependency>
<!-- mp rely -->
<dependency>
<groupId></groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.3.1</version>
</dependency>2. Create an entity
Add two database entity classes User and Address and result class UserDTO
Here is a simple code for lombok
@Data
@ToString
@TableName("area")
public class User {
@TableId
private Long id;
private String name;
private Integer age;
private String email;
}@Data
@ToString
@TableName("address")
public class Address {
@TableId
private Long id;
private Long userId;
private String city;
private String address;
}@Data
@ToString
public class UserDTO{
private Long id;
private Long userId;
private String city;
private String address;
//Address list for the next one-to-many mapping query private List<Address> addressList;
//Address for the next one-to-one mapping query private Address address;
}3. Create a mapper
Add mapper and inherit MPJBaseMapper
@Mapper
public interface UserMapper extends MPJBaseMapper<User> {
}@Mapper
public interface AddressMapper extends MPJBaseMapper<Address> {
}4. Continuous table query test
The entity and mapper are built and can be used directly~~
@SpringBootTest
public class SampleTest {
@Autowired
private UserMapper userMapper;
@Test
public void testSelect() {
MPJLambdaWrapper<User> wrapper = new MPJLambdaWrapper<User>()
.selectAll()//Query all fields of the user table .select(Address::getCity, Address::getAddress)
.leftJoin(, Address::getUserId, User::getId);
List<UserDTO> userList = (, wrapper);
(::println);
}
}sql printing
SELECT ,,,,, FROM user t LEFT JOIN address t1 ON t1.user_id =
Console output
User(id=1, name=Jone, age=18, email=test1@,city=Beijing, address=People's Square)
User(id=2, name=Jack, age=20, email=test2@,city=Shanghai, address=People's Square)
User(id=3, name=Tom, age=28, email=test3@,city=Guangzhou,address=People's Square)
User(id=4, name=Sandy, age=21, email=test4@,city=Shanghai, address=People's Square)
User(id=5, name=Billie, age=24, email=test5@,city=Beijing,address=People's Square)
Even table paging is also a very common function, and MPJ also supports it. Just call selectJoinPage()
@SpringBootTest
public class SampleTest {
@Autowired
private UserMapper userMapper;
@Test
public void testSelect() {
MPJLambdaWrapper<User> wrapper = new MPJLambdaWrapper<User>()
.selectAll()//Query all fields of the user table .select(Address::getCity, Address::getAddress)
.leftJoin(, Address::getUserId, User::getId);
Page<UserDTO> page= (new Page(1,10), , wrapper);
}
}Printing SQL can see more pagination dialects
SELECT ,,,,, FROM user t LEFT JOIN address t1 ON t1.user_id = LIMIT ?
summary:
Through the above simple steps, we have implemented the table-connecting function of the User table, and we don’t even need to write XML files!
From the above steps, we can see that integrating MyBatis-Plus-Join is very simple, and you only need to introduce the starter project.
But MyBatis-Plus-Join is much more powerful than these features.
You can check the plug-in documentation/
Next test one-to-many and one-to-one mapping queries
5. One-to-many, one-to-one mapping
One-to-many selectCollection
@SpringBootTest
public class SampleTest {
@Autowired
private UserMapper userMapper;
@Test
public void testSelect() {
MPJLambdaWrapper<User> wrapper = new MPJLambdaWrapper<User>()
.selectAll()//Query all fields of the user table .selectCollection(Address::getCity, UserDTO::getAddressList)
.leftJoin(, Address::getUserId, User::getId);
List<UserDTO> userList = (, wrapper);
}
}One-to-one selectAssociation
@SpringBootTest
public class SampleTest {
@Autowired
private UserMapper userMapper;
@Test
public void testSelect() {
MPJLambdaWrapper<User> wrapper = new MPJLambdaWrapper<User>()
.selectAll()//Query all fields of the user table .selectAssociation(Address::getCity, UserDTO::getAddress)
.leftJoin(, Address::getUserId, User::getId);
List<UserDTO> userList = (, wrapper);
}
}6. Summary
Through the above simple steps, we have implemented the table-connecting function of the User table, and we don’t even need to write XML files!
From the above steps, we can see that integrating MyBatis-Plus-Join is very simple, and you only need to introduce the starter project.
But MyBatis-Plus-Join is much more powerful than these features. Want to learn more about the power of MyBatis-Plus-Join?
You can check the plug-in documentation/
This is the article about mybatisplus's table enhancement plugin mybatis plus join. For more related contents of mybatis plus join, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!