Mybatis基于XML配置SQL映射器(二)

2022-03-22 05:09:09

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>

View Code

同时在 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 }

View Code

增加相对应单元测试查看相关效果

 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 }

View Code

执行效果

【自动生成主键】

在上述的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>

View Code

有些数据库如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>

View Code

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>

View Code

同时在映射器接口中添加相应的方法

1     int updateUser(sysUser sysuser);

View Code

增加相对应单元测试查看相关效果

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     }

View Code

测试效果

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>

View Code

同时在映射器接口中添加相应的方法

1     int deleteByUserName(@Param("username") String username);

View Code

增加相对应单元测试查看相关效果

1  @Test
2     public void deleteByUserName() throws Exception {
3         int i=sysuserextmapper.deleteByUserName("1");
4         this.logger.info("执行成功个数："+i);
5     }

View Code

测试效果

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>

View Code

同时在映射器接口中添加相应的方法

1     sysUser selectByUsername(String username);

View Code

增加相对应单元测试查看相关效果

1   @Test
2     public void selectByUsername() throws Exception {
3         sysUser user= sysuserextmapper.selectByUsername("444");
4         System.out.println(user.getId());
5     }

View Code

测试效果

结果集映射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>

View Code

让我们来看另外一个<select>映射语句定义的例子，怎样将查询结果填充到HashMap中。如下所示：

1     <select id="selectUserByUsername"  resultType="java.util.HashMap">
2         select  username,password from sys_user where username = #{username,jdbcType=VARCHAR}
3     </select>

View Code

在上述的<select>语句中，我们将resultType配置成map，即java.util.HashMap的别名。在这种情况下，结果集的列名将会作为Map中的key值，而列值将作为Map的value值。

映射器接口中添加相应的方法

1     Map<String, String> selectUserByUsername(@Param("username") String username);

View Code

让我们再看一个使用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>

View Code

映射器接口中添加相应的方法

1 List<Map<String, String>> selectUserByOrgid(@Param("orgid") String orgid, @Param("orderSort") String orderSort, @Param("orderFiled") String orderFiled);

View Code

拓展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     }

View Code

我们可以从从另外一个<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 >

View Code

扩展方法来使用它

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>

View Code

一对一映射

使用嵌套结果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 >

View Code

扩展方法来使用它

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>

View Code

使用嵌套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>

View Code

相对应的扩展方法来使用它

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>

View Code

元素<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>

View Code

嵌套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>

View Code

相对应的扩展方法来使用它

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>

View Code

一对多映射

我们创建一张机构表一个机构包含多个用户。在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;

View Code

使用内嵌结果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>

View Code

相对应的扩展方法来使用它

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>

View Code

级联查询的时候，主表和从表有一样的字段名的时候，在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>

View Code

相对应的扩展方法来使用它

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>

View Code

GITHUB

github : https://github.com/nbfujx/learn-java-demo/tree/master/Goku.MybatisDemo.XML

码农公寓

Mybatis之XML注解

映射语句

INSERT语句

UPDATE 语句

DELETE 语句

SELECT 语句

结果集映射ResultMaps

简单ResultMap

拓展ResultMap

一对一映射

使用嵌套结果ResultMap实现一对一关系映射

使用嵌套Select语句实现一对一关系映射

一对多映射

使用内嵌结果ResultMap实现一对多映射

使用嵌套Select语句实现一对多映射

GITHUB

相关文章