1, One to one mapping

Suppose that in the permission system, a user can only have one role, that is, the relationship between users and roles is limited to one-to-one relationship.

1.1 handling one-to-one relationships using automatic mapping

A user has a role. We add the role class SysRole field in the user class SysUser. As follows:

public class SysUser {

    ...Original field

    /**
     * User role
     */
    private  SysRole role;

    public SysRole getRole() {
        return role;
    }

    public void setRole(SysRole role) {
        this.role = role;
    }
}

Using automatic mapping is to let MyBatis automatically match the value to the corresponding space segment through alias. Simple alias mapping, such as user_name corresponds to userName. In addition, MyBatis also supports complex attribute mapping, which can be nested at multiple levels, such as role role_name is mapped to role On roleName. MyBatis will first find the role attribute. If there is a role attribute, create a role object, and then continue to find the roleName in the role object_ The value of name is bound to the roleName property of the role object.

Example: in usermapper Add the following code to the XML Mapping File:

    <select id="selectUserAndRoleByid" resultType="com.wyf.mybaties.model.SysUser">
		SELECT u.id,
		       u.user_name userName,
		       u.user_password userPassword,
		       u.user_email userEmail,
		       u.user_info userInfo,
		       u.head_img headImg,
		       u.create_time createTime,
		       r.id "role.id",
			   r.role_name "role.roleName",
			   r.enabled "role.enabled",
			   r.create_by "role.createBy",
			   r.create_time "role.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 = #{id}
	</select>

Add a method to the corresponding interface file:

    /**
     * Obtain user information and user role information according to user id
     *
     * ＠param id
     * ＠return
     */
    SysUser selectUserAndRoleByid(Long id);

Test code:

    /**
     * One to one relationship
     */
    @Test
    public void selectUserAndRoleById(){
        //Get SqlSession
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            SysUser user = userMapper.selectUserAndRoleByid(1001L);
            System.out.println(user.getRole().getRoleName());
        }finally {
            sqlSession.close();
        }
    }

1.2} use resultMap to configure one-to-one mapping

In addition to using MyBatis's automatic mapping to handle one-to-one nesting, you can also configure the result mapping in the XML Mapping file.

In usermapper There is already a mapping configuration of userMap in the XML Mapping file, so userRoleMap only needs to inherit userMap and add the unique configuration of role. The modified code of userRoleMap is as follows:

	<resultMap id="userMap" type="com.wyf.mybaties.model.SysUser">
		<id property="id" column="id"/>
		<result property="userName" column="user_name"/>
		<result property="userPassword" column="user_password"/>
		<result property="userEmail" column="user_email"/>
		<result property="userInfo" column="user_info"/>
		<result property="headImg" column="head_img" jdbcType="BLOB"/>
		<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
	</resultMap>

	<resultMap id="userRoleMap" extends="userMap" type="com.wyf.mybaties.model.SysUser">
		<result property="role.id" column="role_id"/>
		<result property="role.roleName" column="role_name"/>
		<result property="role.enabled" column="enabled"/>
		<result property="role.createBy" column="create_by"/>
		<result property="role.createTime" column="role_create_time" jdbcType="TIMESTAMP"/>
	</resultMap>

Using inheritance not only makes the configuration easier, but also requires only one modification when modifying the main table userMap.

1.3 # use the association tag of resultMap to configure one-to-one mapping

In the resultMap, the association tag is used to associate with a complex type, that is, for one-to-one association configuration. Modify the code of the above example as follows:

	<resultMap id="userRoleMap" extends="userMap" type="com.wyf.mybaties.model.SysUser">
		<association property="role" columnPrefix="role_" javaType="com.wyf.mybaties.model.SysRole">
			<result property="id" column="id"/>
			<result property="roleName" column="role_name"/>
			<result property="enabled" column="enabled"/>
			<result property="createBy" column="create_by"/>
			<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
		</association>
	</resultMap>

The association tag contains the following attributes:

• Property: the property name in the corresponding entity class; required.
• javaType: the Java type corresponding to the attribute.
• resultMap: you can directly use the existing resultMap without configuring it here.
• columnPrefix: query the prefix of the column. After configuring the prefix, configure the result in the sub tag Column, you can omit the prefix.

Configure columnprefix = "role". When writing SQL, the alias of query column related to sys_role table must have "role" prefix. When configuring column in internal result, it needs to be configured to remove the prefix column name. MyBatis will automatically use the combination of prefix and column value to take values in the result of SQL query when mapping the result.

Because the prefix of the column is configured, you also need to modify the SQL. The code is as follows.

	<select id="selectUserAndRoleByid2" resultMap="userRoleMap">
		SELECT u.id,
			   u.user_name,
			   u.user_password,
			   u.user_email,
			   u.user_info,
			   u.head_img,
			   u.create_time,
			   r.id role_id,
			   r.role_name "role_role_name",
			   r.enabled "role_enabled",
			   r.create_by "role_create_by",
			   r.create_time "role_create_time"
		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 = #{id}
	</select>

Attention and sys_ The aliases (database table column names) of role related columns have been changed to the prefix "role". Pay special attention to role_name: role after adding prefix_ role_name.

When using association configuration, you can also use the resultMap property to configure an existing resultMap mapping, as follows:

We're at rolemapper Add the following resultMap to the XML Mapping File:

 
	<resultMap id="roleMap" type="com.wyf.mybaties.model.SysRole">
		<result property="id" column="id"/>
		<result property="roleName" column="role_name"/>
		<result property="enabled" column="enabled"/>
		<result property="createBy" column="create_by"/>
		<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
	</resultMap>
    

In usermapper Add the following code to the XML file:

    <resultMap id="userRoleMap" extends="userMap" type="com.wyf.mybaties.model.SysUser">
		<association property="role" columnPrefix="role_" resultMap="com.wyf.mybaties.mapper.RoleMapper.roleMap"/>
	</resultMap>

roleMap is defined in rolemapper XML file, so we are in usermapper XML, when specifying resultMap, you should specify its correct address.

1.4 nested query of association tag

In addition to the above three kinds of multi table queries, we can also use simple SQL to convert the results we need through multiple queries. This method is similar to the method of executing multiple SQL automatically according to business logic, Finally, the results are combined into one object.

1) We're at rolemapper Add the following query to the XML Mapping File:

	<select id="selectRoleByid" resultMap="roleMap">
		select * from sys_role where id = #{id}
	</select>

2) In usermapper Add the following code to the XML Mapping File:

	<resultMap id="userRoleMapSelect" extends="userMap" type="com.wyf.mybaties.model.SysUser">
		<association property="role"
					 fetchType="lazy"
					 select="com.wyf.mybaties.mapper.RoleMapper"
					 column="{id=role_id}"/>
	</resultMap>

The common attributes of nested query of association tag are as follows:

• select: the ID of another mapping query. Mybatis will additionally execute this query to obtain the results of nested objects.
• fetch Type: data loading method. The optional values are lazy and eager, which are delayed loading and active loading respectively. This configuration will override the global lazy loading enabled configuration.  
• Column: column name (or alias), which takes the result of the column in the main query as the parameter of the nested query. The configuration method is as follows:
  Column = {PROP1 = col1, prop2 = col2}, PROP1 and prop2 will be used as parameters of nested query.

	<select id="selectUserAndRoleByidSelect" resultMap="userRoleMapSelect">
		select
			u.id,
			u.user_name,
			u.user_password,
			u.user_email,
			u.user_info,
			u.head_img,
			u.create_time,
			ur.role_id
		from sys_user u
				 inner join sys_user_role ur on u.id = ur.user_id
		where u.id = #{id}
	</select>

3) Add a method to the corresponding interface:

    /**
     * Obtain user information and user role information according to user id, and use nested query method
     *
     * @param id
     * @return
     */
    SysUser selectUserAndRoleByIdSelect(Long id);

The test code is as follows:

    @Test
    public void selectUserAndRoleByIdSelect(){
        //Get SqlSession
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            SysUser user = userMapper.selectUserAndRoleByIdSelect(1001L);
            System.out.println(user.getRole().getRoleName());
        }finally {
            sqlSession.close();
        }
    }

2, End

This article is a study note of MyBatis, which mainly introduces the related content of one-to-one mapping.