1. Query multiple tables
In the previous blog, the two queries in our example were single-table queries, but a real business scenario would definitely require multi-table queries. For example, here is a requirement:
Query all roles owned by a user. This requirement involves three tables: sys_USER, sys_USER_ROLE, and sys_ROLE.
First, define the following methods in the SysUserMapper interface.
/** * Get role information based on user ID **@param userId
* @return* /
List<SysRole> selectRolesByUserId(Long userId);
Copy the codeThen open the corresponding sysusermapper. XML file and add the following SELECT statement:
<select id="selectRolesByUserId" resultType="com.zwwhnly.mybatisaction.model.SysRole">
SELECT r.id,
r.role_name roleName,
r.enabled,
r.create_by createBy,
r.create_time createTime
FROM sys_user u
INNER JOIN sys_user_role ur ON u.id = ur.user_id
INNER JOIN sys_role r ON ur.role_id = r.id
WHERE u.id = #{userId}
</select>
Copy the codeCareful readers may notice that although we use a multi-table query, the resultType is still set to a single table, which only contains information for the role table.
How do I set resultType if I want this query to return the user_NAME field of the SysUser table as well?
Method 1: Add the userName field directly to the SysRole entity class.
private String userName;
public String getUserName(a) {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
Copy the codeIn this case, the resultType does not need to be changed.
** Method 2: ** Create an extension class and add the userName field to the extension class.
package com.zwwhnly.mybatisaction.model;
public class SysRoleExtend extends SysRole {
private String userName;
public String getUserName(a) {
return userName;
}
public void setUserName(String userName) {
this.userName = userName; }}Copy the codeAt this point need to amend the resultType: com. Zwwhnly. Mybatisaction. Model. SysRoleExtend.
This approach is suitable for scenarios that require a small number of additional fields. If you need a large number of fields from another table, you can use mode 3 or mode 4. Personally, I recommend mode 4.
** Method 3: ** Add a field of type SysUser to the SysRole entity class.
private SysUser sysUser;
public SysUser getSysUser(a) {
return sysUser;
}
public void setSysUser(SysUser sysUser) {
this.sysUser = sysUser;
}
Copy the codeIn this case, the resultType does not need to be changed.
Method 4(recommended) : Create an extension class and add a field of type SysUser to the extension class.
Method 3 is recommended in the book, and method 4 is better in my opinion, because entity classes are generally generated automatically by tools, and it is easy to forget and be overwritten after adding fields.
package com.zwwhnly.mybatisaction.model;
public class SysRoleExtend extends SysRole {
private SysUser sysUser;
public SysUser getSysUser(a) {
return sysUser;
}
public void setSysUser(SysUser sysUser) {
this.sysUser = sysUser; }}Copy the codeAt this point need to amend the resultType: com. Zwwhnly. Mybatisaction. Model. SysRoleExtend.
The query statement in the XML is as follows.
<select id="selectRolesByUserId" resultType="com.zwwhnly.mybatisaction.model.SysRoleExtend">
SELECT r.id,
r.role_name roleName,
r.enabled,
r.create_by createBy,
r.create_time createTime,
u.user_name "sysUser.userName",
u.user_email "sysUser.userEmail"
FROM sys_user u
INNER JOIN sys_user_role ur ON u.id = ur.user_id
INNER JOIN sys_role r ON ur.role_id = r.id
WHERE u.id = #{userId}
</select>
Copy the codeAdd the test code to SysUserMapperTest as follows.
@Test
public void testSelectRolesByUserId(a) {
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
List<SysRole> sysRoleList = sysUserMapper.selectRolesByUserId(1L);
Assert.assertNotNull(sysRoleList);
Assert.assertTrue(sysRoleList.size() > 0);
} finally{ sqlSession.close(); }}Copy the codeTo run the test method, enter the log as follows.
DEBUG [main] – ==> Preparing: SELECT r.id, r.role_name roleName, r.enabled, r.create_by createBy, r.create_time createTime, u.user_name “sysUser.userName”, u.user_email “sysUser.userEmail” FROM sys_user u INNER JOIN sys_user_role ur ON u.id = ur.user_id INNER JOIN sys_role r ON ur.role_id = r.id WHERE u.id = ?
DEBUG [main] – ==> Parameters: 1(Long)
TRACE [main] – <== Columns: id, roleName, enabled, createBy, createTime, sysUser.userName, sysUser.userEmail
TRACE [main] – <== Row: 1, admin, admin, [email protected]
TRACE [main] – <== Row: 2, 1, 1, 2019-06-27 18:21:12.0, admin, [email protected]
DEBUG [main] – <== Total: 2
2. Usage of multiple interface parameters
2.1 Parameter Types are basic types
So far, we’ve defined methods that take only one argument, or one primitive type, such as selectById(Long ID); .
Either there is only one object as a parameter, that is, multiple parameters are merged into one object.
However, in some scenarios, for example, there are only two parameters, it is not necessary to create a new object for these two parameters. For example, we now need to obtain all the roles of the user based on the user ID and role state, so how to use it?
First, add the following methods to interface SysUserMapper.
/** * Get the user role ** based on the user ID and the enabled status of the role@param userId
* @param enabled
* @return* /
List<SysRole> selectRolesByUserIdAndRoleEnabled(Long userId,Integer enabled);
Copy the codeThen, open the corresponding sysusermapper.xml file and add the following code.
<select id="selectRolesByUserIdAndRoleEnabled" resultType="com.zwwhnly.mybatisaction.model.SysRole">
SELECT r.id,
r.role_name roleName,
r.enabled,
r.create_by createBy,
r.create_time createTime
FROM sys_user u
INNER JOIN sys_user_role ur ON u.id = ur.user_id
INNER JOIN sys_role r ON ur.role_id = r.id
WHERE u.id = #{userId} AND r.enabled = #{enabled}
</select>
Copy the codeAdd the following test methods to the SysUserMapperTest test class.
@Test
public void testselectRolesByUserIdAndRoleEnabled(a) {
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
List<SysRole> sysRoleList = sysUserMapper.selectRolesByUserIdAndRoleEnabled(1L.1);
Assert.assertNotNull(sysRoleList);
Assert.assertTrue(sysRoleList.size() > 0);
} finally{ sqlSession.rollback(); sqlSession.close(); }}Copy the codeRun the test method and the following error is reported.
Error: userId not found, available parameter is [0,1,param1,param2]
WHERE u.id = #{0} AND r.enabled = #{1}
Copy the codeOr change to:
WHERE u.id = #{param1} AND r.enabled = #{param2}
Copy the codeThis will pass the test, but it is not recommended because the code is not friendly to read.
It is recommended to add @param annotations to the interface method arguments as follows:
/** * Get the user role ** based on the user ID and the enabled status of the role@param userId
* @param enabled
* @return* /
List<SysRole> selectRolesByUserIdAndRoleEnabled(@Param("userId") Long userId, @Param("enabled") Integer enabled);
Copy the codeRun the test method you just added, the test passes, output the following log:
DEBUG [main] – ==> Preparing: SELECT r.id, r.role_name roleName, r.enabled, r.create_by createBy, r.create_time createTime FROM sys_user u INNER JOIN sys_user_role ur ON u.id = ur.user_id INNER JOIN sys_role r ON ur.role_id = r.id WHERE u.id = ? AND r.enabled = ?
DEBUG [main] – ==> Parameters: 1(Long), 1(Integer)
TRACE [main] – <== Columns: id, roleName, enabled, createBy, createTime
TRACE [main] – <== Row: 1, 1, 1, 2019-06-27 18:21:12.0
TRACE [main] – <== Row: 2, 1, 1, 2019-06-27 18:21:12.0
DEBUG [main] – <== Total: 2
2.2 Parameter Types are objects
To demonstrate how argument types can be used as objects, we add the following method to the interface SysUserMapper:
/** * Get the user role ** based on the user ID and the enabled status of the role@param user
* @param role
* @return* /
List<SysRole> selectRolesByUserAndRole(@Param("user") SysUser user, @Param("role") SysRole role);
Copy the codeThe corresponding XML statement is:
<select id="selectRolesByUserAndRole" resultType="com.zwwhnly.mybatisaction.model.SysRole">
SELECT r.id,
r.role_name roleName,
r.enabled,
r.create_by createBy,
r.create_time createTime
FROM sys_user u
INNER JOIN sys_user_role ur ON u.id = ur.user_id
INNER JOIN sys_role r ON ur.role_id = r.id
WHERE u.id = #{user.id} AND r.enabled = #{role.enabled}
</select>
Copy the code3. Source code and reference
Source code address: github.com/zwwhnly/myb… Welcome to download.
MyBatis from Entry to Mastery by Liu Zenghui