Mybatis之XML注解
之前已经讲到通过 mybatis-generator 生成mapper映射接口和相关的映射配置文件:
下面我们将详细的讲解具体内容
首先我们新建映射接口文档 sysUserExtMapper.java,同时新增相关配置文件sysUserExtMapper.xml。通过操作这两个类我们来讲解具体内容。
映射语句
MyBatis提供了多种元素来配置不同类型的语句，如SELECT，INSERT，UPDATE，DELETE。接下来让我们看看如何具体配置映射语句。
INSERT语句
一个INSERT SQL语句可以在<insert>元素在sysUserExtMapper.xml中配置，如下所示：
1 <insert id="insertsysUser" parameterType="com.goku.mybatis.model.sysUser"> 2 insert into sys_user (id, username, password, 3 name, sex, status, org_id, 4 email, idcard, is_admin, 5 sort, mobile, stationid 6 ) 7 values (#{id,jdbcType=VARCHAR}, #{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, 8 #{name,jdbcType=VARCHAR}, #{sex,jdbcType=VARCHAR}, #{status,jdbcType=CHAR}, #{orgId,jdbcType=VARCHAR}, 9 #{email,jdbcType=VARCHAR}, #{idcard,jdbcType=VARCHAR}, #{isAdmin,jdbcType=VARCHAR}, 10 #{sort,jdbcType=BIGINT}, #{mobile,jdbcType=VARCHAR}, #{stationid,jdbcType=LONGVARCHAR} 11 ) 12 </insert>
同时在 sysUserExtMapper.java接口中添加相应的方法
1 package com.goku.mybatis.mapper.ext; 2 3 import com.goku.mybatis.model.sysUser; 4 import org.apache.ibatis.annotations.Param; 5 6 import java.util.List; 7 import java.util.Map; 8 9 /** 10 * Created by nbfujx on 2017/10/14. 11 */ 12 public interface sysUserExtMapper { 13 14 int insertsysUser(sysUser sysuser)); 15 }
增加相对应单元测试查看相关效果
1 package test.com.goku.mybatis.mapper.ext; 2 3 import com.goku.mybatis.WebapiApplication; 4 import com.goku.mybatis.mapper.ext.sysUserExtMapper; 5 import com.goku.mybatis.model.sysUser; 6 import com.goku.mybatis.service.impl.sysUserServiceImpl; 7 import org.junit.Test; 8 import org.junit.runner.RunWith; 9 import org.slf4j.Logger; 10 import org.slf4j.LoggerFactory; 11 import org.springframework.beans.factory.annotation.Autowired; 12 import org.springframework.boot.test.context.SpringBootTest; 13 import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; 14 15 import static org.junit.Assert.*; 16 17 /** 18 * Created by nbfujx on 2017/10/23. 19 */ 20 @RunWith(SpringJUnit4ClassRunner.class) 21 @SpringBootTest(classes = WebapiApplication.class) 22 public class sysUserExtMapperTest { 23 24 private Logger logger = LoggerFactory.getLogger(sysUserExtMapperTest.class); 25 26 @Autowired 27 private sysUserExtMapper sysuserextmapper; 28 29 @Test 30 public void insertsysUser() throws Exception { 31 sysUser sysuser=new sysUser(); 32 sysuser.setId("222"); 33 sysuser.setOrgId("2"); 34 int i=sysuserextmapper.insertsysUser(sysuser); 35 this.logger.info("执行成功个数："+i); 36 } 37 38 }
执行效果
【自动生成主键】
在上述的INSERT语句中，我们为可以自动生成（auto-generated）主键的列 id 插入值。
我们可以使用useGeneratedKeys 和 keyProperty属性让数据库生成AUTO_INCREMENT列的值，并将生成的值设置到其中一个输入对象属性内，如下所示：
1 <insert id="insertsysUser2" parameterType="com.goku.mybatis.model.sysUser" useGeneratedKeys="true" 2 keyProperty="id"> 3 insert into sys_user ( username, password, 4 name, sex, status, org_id, 5 email, idcard, is_admin, 6 sort, mobile, stationid 7 ) 8 values ( #{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, 9 #{name,jdbcType=VARCHAR}, #{sex,jdbcType=VARCHAR}, #{status,jdbcType=CHAR}, #{orgId,jdbcType=VARCHAR}, 10 #{email,jdbcType=VARCHAR}, #{idcard,jdbcType=VARCHAR}, #{isAdmin,jdbcType=VARCHAR}, 11 #{sort,jdbcType=BIGINT}, #{mobile,jdbcType=VARCHAR}, #{stationid,jdbcType=LONGVARCHAR} 12 ) 13 </insert>
有些数据库如Oracle并不支持 AUTO_INCREMENT 列，其使用序列（SEQUENCE），或者其他查询（uuid）来生成主键值，如下所示：
1 <insert id="insertsysUser3" parameterType="com.goku.mybatis.model.sysUser"> 2 <selectKey keyProperty="id" resultType="java.lang.String" order="BEFORE"> 3 SELECT replace(uuid(),'-','') AS id 4 </selectKey> 5 insert into sys_user (id, username, password, 6 name, sex, status, org_id, 7 email, idcard, is_admin, 8 sort, mobile, stationid 9 ) 10 values (#{id,jdbcType=VARCHAR}, #{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, 11 #{name,jdbcType=VARCHAR}, #{sex,jdbcType=VARCHAR}, #{status,jdbcType=CHAR}, #{orgId,jdbcType=VARCHAR}, 12 #{email,jdbcType=VARCHAR}, #{idcard,jdbcType=VARCHAR}, #{isAdmin,jdbcType=VARCHAR}, 13 #{sort,jdbcType=BIGINT}, #{mobile,jdbcType=VARCHAR}, #{stationid,jdbcType=LONGVARCHAR} 14 ) 15 </insert>
UPDATE 语句
一个UPDATE SQL语句可以在<update>元素在映射器XML配置文件中配置，如下所示：
1 <update id="updateUser" parameterType="com.goku.mybatis.model.sysUser"> 2 <!-- 3 WARNING - @mbg.generated 4 This element is automatically generated by MyBatis Generator, do not modify. 5 --> 6 update sys_user 7 set username = #{username,jdbcType=VARCHAR}, 8 password = #{password,jdbcType=VARCHAR}, 9 name = #{name,jdbcType=VARCHAR}, 10 sex = #{sex,jdbcType=VARCHAR}, 11 status = #{status,jdbcType=CHAR}, 12 org_id = #{orgId,jdbcType=VARCHAR}, 13 email = #{email,jdbcType=VARCHAR}, 14 idcard = #{idcard,jdbcType=VARCHAR}, 15 is_admin = #{isAdmin,jdbcType=VARCHAR}, 16 sort = #{sort,jdbcType=BIGINT}, 17 mobile = #{mobile,jdbcType=VARCHAR}, 18 stationid = #{stationid,jdbcType=LONGVARCHAR} 19 where id = #{id,jdbcType=VARCHAR} 20 </update>
同时在映射器接口中添加相应的方法
1 int updateUser(sysUser sysuser);
增加相对应单元测试查看相关效果
1 @Test 2 public void updateUser() throws Exception { 3 sysUser sysuser=sysuserextmapper.selectByUsername("1"); 4 sysuser.setPassword("3"); 5 sysuser.setOrgId("2"); 6 int i=sysuserextmapper.updateUser(sysuser); 7 this.logger.info("执行成功个数："+i); 8 }
测试效果
DELETE 语句
一个DELETE SQL语句可以在<delete>元素在映射器XML配置文件中配置，如下所示：
1 <delete id="deleteByUserName" parameterType="java.lang.String"> 2 <!-- 3 WARNING - @mbg.generated 4 This element is automatically generated by MyBatis Generator, do not modify. 5 --> 6 delete from sys_user 7 where username = #{username,jdbcType=VARCHAR} 8 </delete>
同时在映射器接口中添加相应的方法
1 int deleteByUserName(@Param("username") String username);
增加相对应单元测试查看相关效果
1 @Test 2 public void deleteByUserName() throws Exception { 3 int i=sysuserextmapper.deleteByUserName("1"); 4 this.logger.info("执行成功个数："+i); 5 }
测试效果
SELECT 语句
一个SELECT SQL语句可以在<select>元素在映射器XML配置文件中配置，如下所示：
1 <select id="selectByUsername" parameterType="java.lang.String" resultMap="BaseResultExtMap"> 2 select 3 <include refid="Base_Ext_Column_List" /> 4 from sys_user 5 where username = #{username,jdbcType=VARCHAR} 6 </select>
同时在映射器接口中添加相应的方法
1 sysUser selectByUsername(String username);
增加相对应单元测试查看相关效果
1 @Test 2 public void selectByUsername() throws Exception { 3 sysUser user= sysuserextmapper.selectByUsername("444"); 4 System.out.println(user.getId()); 5 }
测试效果
结果集映射ResultMaps
简单ResultMap
在<select>语句中，我们使用了resultMap属性，而不是resultType来引用映射。
当<select>语句中配置了resutlMap属性，MyBatis会使用此数据库列名与对象属性映射关系来填充JavaBean中的属性。
1 <select id="selectByUsername" parameterType="java.lang.String" resultMap="BaseResultExtMap"> 2 select 3 <include refid="Base_Ext_Column_List" /> 4 from sys_user 5 where username = #{username,jdbcType=VARCHAR} 6 </select>
让我们来看另外一个<select>映射语句定义的例子，怎样将查询结果填充到HashMap中。如下所示：
1 <select id="selectUserByUsername" resultType="java.util.HashMap"> 2 select username,password from sys_user where username = #{username,jdbcType=VARCHAR} 3 </select>
在上述的<select>语句中，我们将resultType配置成map，即java.util.HashMap的别名。在这种情况下，结果集的列名将会作为Map中的key值，而列值将作为Map的value值。
映射器接口中添加相应的方法
1 Map<String, String> selectUserByUsername(@Param("username") String username);
让我们再看一个 使用resultType=”map”,返回多行结果的例子：
1 <select id="selectUserByOrgid" resultType="java.util.HashMap"> 2 select username,password from sys_user where org_id = #{orgid,jdbcType=VARCHAR} 3 <if test="orderFiled != null" > 4 order by ${orderFiled} 5 <if test="orderSort != null" > 6 ${orderSort} 7 </if> 8 </if> 9 </select>
映射器接口中添加相应的方法
1 List<Map<String, String>> selectUserByOrgid(@Param("orgid") String orgid, @Param("orderSort") String orderSort, @Param("orderFiled") String orderFiled);
拓展ResultMap
首先我们要在 sys_User中增加 sys_user_info对象。下面同理
1 private sysUserInfo sysuserinfo; 2 3 public sysUserInfo getSysuserinfo() { 4 return sysuserinfo; 5 } 6 7 public void setSysuserinfo(sysUserInfo sysuserinfo) { 8 this.sysuserinfo = sysuserinfo; 9 }
我们可以从从另外一个<resultMap>，拓展出一个新的<resultMap>，这样，原先的属性映射可以继承过来，以实现。
1 <resultMap id="UserinfoBaseResultExtMap" type="com.goku.mybatis.model.sysUser" extends="BaseResultExtMap"> 2 <!-- 3 WARNING - @mbg.generated 4 This element is automatically generated by MyBatis Generator, do not modify. 5 --> 6 <result column="address" jdbcType="VARCHAR" property="sysuserinfo.address" /> 7 <result column="post_code" jdbcType="VARCHAR" property="sysuserinfo.postCode" /> 8 <result column="height" jdbcType="VARCHAR" property="sysuserinfo.height" /> 9 <result column="weight" jdbcType="VARCHAR" property="sysuserinfo.weight" /> 10 <result column="birthday" jdbcType="DATE" property="sysuserinfo.birthday" /> 11 <result column="blood" jdbcType="VARCHAR" property="sysuserinfo.blood" /> 12 <result column="culture" jdbcType="VARCHAR" property="sysuserinfo.culture" /> 13 <result column="finish_school_date" jdbcType="DATE" property="sysuserinfo.finishSchoolDate" /> 14 <result column="folk" jdbcType="VARCHAR" property="sysuserinfo.folk" /> 15 <result column="government" jdbcType="VARCHAR" property="sysuserinfo.government" /> 16 <result column="homepage" jdbcType="VARCHAR" property="sysuserinfo.homepage" /> 17 <result column="householder" jdbcType="VARCHAR" property="sysuserinfo.householder" /> 18 <result column="marriage" jdbcType="VARCHAR" property="sysuserinfo.marriage" /> 19 <result column="msn" jdbcType="VARCHAR" property="sysuserinfo.msn" /> 20 <result column="nativity_address" jdbcType="VARCHAR" property="sysuserinfo.nativityAddress" /> 21 <result column="qq" jdbcType="VARCHAR" property="sysuserinfo.qq" /> 22 <result column="speciality" jdbcType="VARCHAR" property="sysuserinfo.speciality" /> 23 <result column="description" jdbcType="VARCHAR" property="sysuserinfo.description" /> 24 <result column="version" jdbcType="BIGINT" property="sysuserinfo.version" /> 25 </resultMap >
扩展方法来使用它
1 <select id="selectextends" parameterType="java.lang.String" resultMap="UserinfoBaseResultExtMap"> 2 select 3 u.id, username, password, name, sex, status, org_id, email, idcard, is_admin, sort, 4 mobile,address, post_code, height, weight, birthday, blood, culture, finish_school_date, 5 folk, government, homepage, householder, marriage, msn, nativity_address, qq, speciality, 6 description, version 7 from sys_user u LEFT OUTER JOIN sys_user_info ui ON u.id=ui.id 8 where username = #{username,jdbcType=VARCHAR} 9 </select>
一对一映射
使用嵌套结果ResultMap实现一对一关系映射
在我们的域模型样例中，每一个用户都有一个与之关联的用户扩展信息。表sys_user有一个id列，是sys_user_info表的外键。我们定义一个resultMap中，sys_user_info的属性使用了圆点记法被赋上了对应列的值。
1 <resultMap id="BaseResultExtMapext" type="com.goku.mybatis.model.sysUser"> 2 <id column="id" jdbcType="VARCHAR" property="id" /> 3 <result column="username" jdbcType="VARCHAR" property="username" /> 4 <result column="password" jdbcType="VARCHAR" property="password" /> 5 <result column="name" jdbcType="VARCHAR" property="name" /> 6 <result column="sex" jdbcType="VARCHAR" property="sex" /> 7 <result column="status" jdbcType="CHAR" property="status" /> 8 <result column="org_id" jdbcType="VARCHAR" property="orgId" /> 9 <result column="email" jdbcType="VARCHAR" property="email" /> 10 <result column="idcard" jdbcType="VARCHAR" property="idcard" /> 11 <result column="is_admin" jdbcType="VARCHAR" property="isAdmin" /> 12 <result column="sort" jdbcType="BIGINT" property="sort" /> 13 <result column="mobile" jdbcType="VARCHAR" property="mobile" /> 14 <result column="address" jdbcType="VARCHAR" property="sysuserinfo.address" /> 15 <result column="height" jdbcType="VARCHAR" property="sysuserinfo.height" /> 16 <result column="weight" jdbcType="VARCHAR" property="sysuserinfo.weight" /> 17 <result column="birthday" jdbcType="DATE" property="sysuserinfo.birthday" /> 18 <result column="blood" jdbcType="VARCHAR" property="sysuserinfo.blood" /> 19 <result column="culture" jdbcType="VARCHAR" property="sysuserinfo.culture" /> 20 <result column="finish_school_date" jdbcType="DATE" property="sysuserinfo.finishSchoolDate" /> 21 <result column="folk" jdbcType="VARCHAR" property="sysuserinfo.folk" /> 22 <result column="government" jdbcType="VARCHAR" property="sysuserinfo.government" /> 23 <result column="homepage" jdbcType="VARCHAR" property="sysuserinfo.homepage" /> 24 <result column="householder" jdbcType="VARCHAR" property="sysuserinfo.householder" /> 25 <result column="marriage" jdbcType="VARCHAR" property="sysuserinfo.marriage" /> 26 <result column="msn" jdbcType="VARCHAR" property="sysuserinfo.msn" /> 27 <result column="nativity_address" jdbcType="VARCHAR" property="sysuserinfo.nativityAddress" /> 28 <result column="qq" jdbcType="VARCHAR" property="sysuserinfo.qq" /> 29 <result column="speciality" jdbcType="VARCHAR" property="sysuserinfo.speciality" /> 30 <result column="description" jdbcType="VARCHAR" property="sysuserinfo.description" /> 31 <result column="version" jdbcType="BIGINT" property="sysuserinfo.version" /> 32 </resultMap >
扩展方法来使用它
1 <select id="selectextends2" parameterType="java.lang.String" resultMap="BaseResultExtMapext"> 2 select 3 u.id, username, password, name, sex, status, org_id, email, idcard, is_admin, sort, 4 mobile,address, post_code, height, weight, birthday, blood, culture, finish_school_date, 5 folk, government, homepage, householder, marriage, msn, nativity_address, qq, speciality, 6 description, version 7 from sys_user u LEFT OUTER JOIN sys_user_info ui ON u.id=ui.id 8 where username = #{username,jdbcType=VARCHAR} 9 </select>
使用嵌套Select语句实现一对一关系映射
上述样例展示了一对一关联映射的一种方法。然而，使用这种方式映射，如果sysuserinfo结果需要在其他的SELECT映射语句中映射成Address对象，我们需要为每一个语句重复这种映射关系。MyBatis提供了更好地实现一对一关联映射的方法：嵌套结果ResultMap和嵌套select查询语句。
嵌套结果ResultMap
1 <resultMap id="userinfoBaseResultMap" type="com.goku.mybatis.model.sysUserInfo"> 2 <!-- 3 WARNING - @mbg.generated 4 This element is automatically generated by MyBatis Generator, do not modify. 5 --> 6 <id column="id" jdbcType="VARCHAR" property="id" /> 7 <result column="address" jdbcType="VARCHAR" property="address" /> 8 <result column="post_code" jdbcType="VARCHAR" property="postCode" /> 9 <result column="height" jdbcType="VARCHAR" property="height" /> 10 <result column="weight" jdbcType="VARCHAR" property="weight" /> 11 <result column="birthday" jdbcType="DATE" property="birthday" /> 12 <result column="blood" jdbcType="VARCHAR" property="blood" /> 13 <result column="culture" jdbcType="VARCHAR" property="culture" /> 14 <result column="finish_school_date" jdbcType="DATE" property="finishSchoolDate" /> 15 <result column="folk" jdbcType="VARCHAR" property="folk" /> 16 <result column="government" jdbcType="VARCHAR" property="government" /> 17 <result column="homepage" jdbcType="VARCHAR" property="homepage" /> 18 <result column="householder" jdbcType="VARCHAR" property="householder" /> 19 <result column="marriage" jdbcType="VARCHAR" property="marriage" /> 20 <result column="msn" jdbcType="VARCHAR" property="msn" /> 21 <result column="nativity_address" jdbcType="VARCHAR" property="nativityAddress" /> 22 <result column="qq" jdbcType="VARCHAR" property="qq" /> 23 <result column="speciality" jdbcType="VARCHAR" property="speciality" /> 24 <result column="description" jdbcType="VARCHAR" property="description" /> 25 <result column="version" jdbcType="BIGINT" property="version" /> 26 </resultMap> 27 <resultMap type="com.goku.mybatis.model.sysUser" id="BaseResultExtMapext3"> 28 <id column="id" jdbcType="VARCHAR" property="id" /> 29 <result column="username" jdbcType="VARCHAR" property="username" /> 30 <result column="password" jdbcType="VARCHAR" property="password" /> 31 <result column="name" jdbcType="VARCHAR" property="name" /> 32 <result column="sex" jdbcType="VARCHAR" property="sex" /> 33 <result column="status" jdbcType="CHAR" property="status" /> 34 <result column="org_id" jdbcType="VARCHAR" property="orgId" /> 35 <result column="email" jdbcType="VARCHAR" property="email" /> 36 <result column="idcard" jdbcType="VARCHAR" property="idcard" /> 37 <result column="is_admin" jdbcType="VARCHAR" property="isAdmin" /> 38 <result column="sort" jdbcType="BIGINT" property="sort" /> 39 <result column="mobile" jdbcType="VARCHAR" property="mobile" /> 40 <association property="sysuserinfo" resultMap="userinfoBaseResultMap" /> 41 </resultMap>
相对应的扩展方法来使用它
1 <select id="selectextends5" parameterType="java.lang.String" resultMap="BaseResultExtMapext3"> 2 select 3 u.id, username, password, name, sex, status, org_id, email, idcard, is_admin, sort, 4 mobile,address, post_code, height, weight, birthday, blood, culture, finish_school_date, 5 folk, government, homepage, householder, marriage, msn, nativity_address, qq, speciality, 6 description, version 7 from sys_user u LEFT OUTER JOIN sys_user_info ui ON u.id=ui.id 8 where username = #{username,jdbcType=VARCHAR} 9 </select>
元素<association>被用来导入“有一个”(has-one)类型的关联。在上述的例子中，我们使用了<association>元素引用了另外的在同一个XML文件中定义的<resultMap>。也可以使用<association>定义内联的resultMap。
1 <resultMap type="com.goku.mybatis.model.sysUser" id="BaseResultExtMapext4"> 2 <id column="id" jdbcType="VARCHAR" property="id" /> 3 <result column="username" jdbcType="VARCHAR" property="username" /> 4 <result column="password" jdbcType="VARCHAR" property="password" /> 5 <result column="name" jdbcType="VARCHAR" property="name" /> 6 <result column="sex" jdbcType="VARCHAR" property="sex" /> 7 <result column="status" jdbcType="CHAR" property="status" /> 8 <result column="org_id" jdbcType="VARCHAR" property="orgId" /> 9 <result column="email" jdbcType="VARCHAR" property="email" /> 10 <result column="idcard" jdbcType="VARCHAR" property="idcard" /> 11 <result column="is_admin" jdbcType="VARCHAR" property="isAdmin" /> 12 <result column="sort" jdbcType="BIGINT" property="sort" /> 13 <result column="mobile" jdbcType="VARCHAR" property="mobile" /> 14 <association property="sysuserinfo" javaType="com.goku.mybatis.model.sysUserInfo"> 15 <id column="id" jdbcType="VARCHAR" property="id" /> 16 <result column="address" jdbcType="VARCHAR" property="address" /> 17 <result column="post_code" jdbcType="VARCHAR" property="postCode" /> 18 <result column="height" jdbcType="VARCHAR" property="height" /> 19 <result column="weight" jdbcType="VARCHAR" property="weight" /> 20 <result column="birthday" jdbcType="DATE" property="birthday" /> 21 <result column="blood" jdbcType="VARCHAR" property="blood" /> 22 <result column="culture" jdbcType="VARCHAR" property="culture" /> 23 <result column="finish_school_date" jdbcType="DATE" property="finishSchoolDate" /> 24 <result column="folk" jdbcType="VARCHAR" property="folk" /> 25 <result column="government" jdbcType="VARCHAR" property="government" /> 26 <result column="homepage" jdbcType="VARCHAR" property="homepage" /> 27 <result column="householder" jdbcType="VARCHAR" property="householder" /> 28 <result column="marriage" jdbcType="VARCHAR" property="marriage" /> 29 <result column="msn" jdbcType="VARCHAR" property="msn" /> 30 <result column="nativity_address" jdbcType="VARCHAR" property="nativityAddress" /> 31 <result column="qq" jdbcType="VARCHAR" property="qq" /> 32 <result column="speciality" jdbcType="VARCHAR" property="speciality" /> 33 <result column="description" jdbcType="VARCHAR" property="description" /> 34 <result column="version" jdbcType="BIGINT" property="version" /> 35 </association> 36 </resultMap>
嵌套select查询语句
1 <resultMap type="com.goku.mybatis.model.sysUser" id="BaseResultExtMapext2"> 2 <id column="id" jdbcType="VARCHAR" property="id" /> 3 <result column="username" jdbcType="VARCHAR" property="username" /> 4 <result column="password" jdbcType="VARCHAR" property="password" /> 5 <result column="name" jdbcType="VARCHAR" property="name" /> 6 <result column="sex" jdbcType="VARCHAR" property="sex" /> 7 <result column="status" jdbcType="CHAR" property="status" /> 8 <result column="org_id" jdbcType="VARCHAR" property="orgId" /> 9 <result column="email" jdbcType="VARCHAR" property="email" /> 10 <result column="idcard" jdbcType="VARCHAR" property="idcard" /> 11 <result column="is_admin" jdbcType="VARCHAR" property="isAdmin" /> 12 <result column="sort" jdbcType="BIGINT" property="sort" /> 13 <result column="mobile" jdbcType="VARCHAR" property="mobile" /> 14 <association property="sysuserinfo" column="id" select="findsysUserInfoById"/> 15 </resultMap>
相对应的扩展方法来使用它
1 <select id="findsysUserInfoById" parameterType="String" 2 resultMap="userinfoBaseResultMap"> 3 SELECT * FROM sys_user_info where id = #{id,jdbcType=VARCHAR} 4 </select> 5 <select id="selectextends4" parameterType="java.lang.String" resultMap="BaseResultExtMapext2"> 6 select * from sys_user where username = #{username,jdbcType=VARCHAR} 7 </select>
一对多映射
我们创建一张机构表一个机构包含多个用户。在sys_org中增加sys_user对象。
1 public List<sysUser> getSysuser() { 2 return sysuser; 3 } 4 5 public void setSysuser(List<sysUser> sysuser) { 6 this.sysuser = sysuser; 7 } 8 9 private List<sysUser> sysuser;
使用内嵌结果ResultMap实现一对多映射
1 <resultMap id="OrgBaseResultMapext1" type="com.goku.mybatis.model.sysOrg"> 2 <!-- 3 WARNING - @mbg.generated 4 This element is automatically generated by MyBatis Generator, do not modify. 5 --> 6 <id column="id" jdbcType="VARCHAR" property="id" /> 7 <result column="version" jdbcType="BIGINT" property="version" /> 8 <result column="level" jdbcType="BIGINT" property="level" /> 9 <result column="type" jdbcType="CHAR" property="type" /> 10 <result column="description" jdbcType="VARCHAR" property="description" /> 11 <result column="image" jdbcType="VARCHAR" property="image" /> 12 <result column="isparent" jdbcType="VARCHAR" property="isparent" /> 13 <result column="name" jdbcType="VARCHAR" property="name" /> 14 <result column="sort" jdbcType="BIGINT" property="sort" /> 15 <result column="url" jdbcType="VARCHAR" property="url" /> 16 <result column="parent_id" jdbcType="VARCHAR" property="parentId" /> 17 <result column="operate" jdbcType="VARCHAR" property="operate" /> 18 <collection property="sysuser" resultMap="userBaseResultMap" /> 19 </resultMap>
相对应的扩展方法来使用它
1 <select id="selectextend1" parameterType="java.lang.String" resultMap="OrgBaseResultMapext1"> 2 select o.id, version, level, type, description, image, isparent, o.name, o.sort, url, parent_id, 3 u.id as uid, username, password, u.name, sex, status, org_id, email, idcard, is_admin, u.sort, 4 mobile from sys_org o LEFT OUTER JOIN sys_user u 5 on o.id=u.org_id where o.id = #{id,jdbcType=VARCHAR} 6 </select>
级联查询的时候，主表和从表有一样的字段名的时候，在mysql上命令查询是没问题的。但在mybatis中主从表需要为相同字段名设置别名。
<collection>元素被用来将多行结果映射成一个对象的一个集合。
使用嵌套Select语句实现一对多映射
1 <resultMap id="OrgBaseResultMapext2" type="com.goku.mybatis.model.sysOrg"> 2 <!-- 3 WARNING - @mbg.generated 4 This element is automatically generated by MyBatis Generator, do not modify. 5 --> 6 <id column="id" jdbcType="VARCHAR" property="id" /> 7 <result column="version" jdbcType="BIGINT" property="version" /> 8 <result column="level" jdbcType="BIGINT" property="level" /> 9 <result column="type" jdbcType="CHAR" property="type" /> 10 <result column="description" jdbcType="VARCHAR" property="description" /> 11 <result column="image" jdbcType="VARCHAR" property="image" /> 12 <result column="isparent" jdbcType="VARCHAR" property="isparent" /> 13 <result column="name" jdbcType="VARCHAR" property="name" /> 14 <result column="sort" jdbcType="BIGINT" property="sort" /> 15 <result column="url" jdbcType="VARCHAR" property="url" /> 16 <result column="parent_id" jdbcType="VARCHAR" property="parentId" /> 17 <result column="operate" jdbcType="VARCHAR" property="operate" /> 18 <collection property="sysuser" column="id" select="findsysUserByOrgId" /> 19 </resultMap>
相对应的扩展方法来使用它
1 <select id="findsysUserByOrgId" parameterType="String" 2 resultMap="userBaseResultMap"> 3 SELECT * FROM sys_user where org_id = #{id,jdbcType=VARCHAR} 4 </select> 5 <select id="selectextend2" parameterType="java.lang.String" resultMap="OrgBaseResultMapext2"> 6 select * from sys_org where id = #{id,jdbcType=VARCHAR} 7 </select>
GITHUB
github : https://github.com/nbfujx/learn-java-demo/tree/master/Goku.MybatisDemo.XML
