mybatis高级映射之一对一映射
一、最基本的一对一映射方法
1. UserMapper.xml中代码如下：
<select id="selectUserAndRoleById" resultType="com.example.simple.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>
接口方法：
/* * 根据用户id获取用户信息和角色信息 * */ SysUser selectUserAndRoleById(Long id);
测试方法：
@Test public void testSelectUserAndRoleById(){ SqlSession sqlSession = getSqlSession(); try{ UserMapper userMapper = sqlSession.getMapper(UserMapper.class); SysUser sysUser = userMapper.selectUserAndRoleById(1001L); Assert.assertNotNull(sysUser); }finally { sqlSession.close(); } }
二、使用 resultMap 配置一对一映射
这种配置和上一节相似的地方在于，role 中的 property 配置部分使用“ role ”前缀，在colum 部分，为了避免不同表中存在相同的列，所有可能重名的列都增加了“ role”前缀。使用这种方式配置时还需要在查询时设置不同的别名，针对该方法在UserMapper.xml中增加 selectUserAndRoleById2 方法
UserMapper.xml中代码如下：
<resultMap id="userRoleMap" type="com.example.simple.model.SysUser"> <id property="id" column="id"/> <result property="userName" column="user_name"/> <result property="userPassword" column="user_password"/> <result property="userMail" column="user_mail"/> <result property="userInfo" column="user_info"/> <result property="headImg" column="head_img" jdbcType="BLOB"/> <result property="createTime" column="creat_time" jdbcType="TIMESTAMP"/> <result property="role.id" column="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="create_time"/> </resultMap>
<select id="selectUserAndRoleById2" resultMap="userRoleMap2"> select u.id, u.user_name, u.user_password, u.user_email , u.user_info , u.head_img, u.create_time , r.id , r.role_name , r.enabled , r.create_by , r.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>
接口处：
/* * 根据用户id获取用户信息和角色信息（resultMap实现） * */ SysUser selectUserAndRoleById2(Long id);
测试代码：
@Test public void testSelectUserAndRoleById2(){ SqlSession sqlSession = getSqlSession(); try{ UserMapper userMapper = sqlSession.getMapper(UserMapper.class); SysUser sysUser = userMapper.selectUserAndRoleById2(1001L); Assert.assertNotNull(sysUser); }finally { sqlSession.close(); } }
三、使用继承extends实现代码简洁
1. UserMapper.xml中代码如下：
<resultMap id="userMap" type="com.example.simple.model.SysUser"> <id property="id" column="id"/> <result property="userName" column="user_name"/> <result property="userPassword" column="user_password"/> <result property="userMail" column="user_mail"/> <result property="userInfo" column="user_info"/> <result property="headImg" column="head_img" jdbcType="BLOB"/> <result property="createTime" column="creat_time" jdbcType="TIMESTAMP"/> </resultMap>
<resultMap id="userRoleMap" extends="userMap" type="com.example.simple.model.SysUser"> <result property="role.id" column="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="create_time"/> </resultMap>
接口和测试如上实现。
四、使用resultMap的 association标签实现一对一映射
UserMapper.xml中：
<resultMap id="userRoleMap1" extends="userMap" type="com.example.simple.model.SysUser"> <association property="role" columnPrefix="role_" javaType="com.example.simple.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"/> </association> </resultMap>
或者如下写法也能达到同样效果：
1. UserMapper.xml中：
<resultMap id="userRoleMap2" extends="userMap" type="com.example.simple.model.SysUser"> <association property="role" columnPrefix="role_" resultMap="com.example.simple.mapper.RoleMapper.roleMap"/> </resultMap>
<select id="selectUserAndRoleById2" resultMap="userRoleMap2"> select u.id, u.user_name, u.user_password, u.user_email , u.user_info , u.head_img, u.create_time , r.id , r.role_name , r.enabled , r.create_by , r.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>
2. RoleMapper.xml中：
<resultMap id="roleMap" type="com.example.simple.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"/> </resultMap>
测试代码和接口如上。
五、association 标签的嵌套查询
1. UserMapper.xml中：
<resultMap id="userRoleMapSelect" extends="userMap" type="com.example.simple.model.SysUser"> <association property="role" column="{id = role_id}" select="com.example.simple.mapper.RoleMapper.selectRoleById"/> </resultMap> <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>
2. RoleMapper.xml中：
<select id="selectRoleById" resultMap="roleMap"> select * from sys_role where id = #{id} </select>
测试代码和 接口如上。
