MyBatis应用开发(14)映射的嵌套查询

1.1.1. 嵌套查询

采用association结点的select属性可以起嵌套查询。前文介绍的莫采用select的点子以主表结果记录集包含N条记录,每个记录对承诺1漫漫子表记录的状下,总共执行同一条SQL语句就好满足所有查询操作的要求,而本文介绍的select属性建立嵌套查询的计,则

用履行1+N长SQL语句(单为关系),或者1+N*2漫长语句(双向关联)。

 

Mapper配置:

<!-- 主表resultMap:Person和IdCard的一对一关联:双向关联,嵌套查询 -->

<resultMap type="com.test.mybatis3.pojo.Person" id="personAndIdCardEmbeddedResultMap">

<id property="id" column="id" />

<result  property="name" column="name" />

<result  property="status" column="status" />



<!-- Person通过idCard属性导航到IdCard -->

<association property="idCard" javaType="com.test.mybatis3.pojo.IdCard" column="id" select="findIdCardByCardId">

</association>



</resultMap>



<!-- 子表resultMap:IdCard的映射 -->

<resultMap type="com.test.mybatis3.pojo.IdCard" id="idCardResultMap">

<id property="cardid" column="cardid" />

<result property="personid" column="personid" />



<!-- IdCard通过person属性导航到Person -->

<association property="person" column="personid" select="findPersonByPersonId">

</association>

</resultMap>



<!-- 主表:查找 Person和对应的IdCard:双向关联-->

<select id="findPersonAndIdCardEmbeded" resultMap="personAndIdCardEmbeddedResultMap">

select * from t_person

</select>



<!-- 子表:通过personid查找IdCard -->

<select id="findIdCardByCardId" parameterType="string" resultMap="idCardResultMap">

select * from t_idcard where personid=#{personid}

</select>



<!-- 用于IdCard的映射中从personid查找Person -->

<select id="findPersonByPersonId" parameterType="string" resultType="com.test.mybatis3.pojo.Person" >

select * from t_person where id=#{id}

</select>

 

 

 

Mapper接口:

/**

 * 嵌套查询:Person和IdCard.

 * @return

 * @throws Exception

 */

List<Person> findPersonAndIdCardEmbeded() throws Exception;

 

 

单元测试代码:

//打开Session。

session = sessionBuilder.openSession();



//找到MyBatis自动实现的PersonMapper接口的代理对象。

PersonMapper  personMapper = session.getMapper(PersonMapper.class);



//嵌套查询。

List<Person> persons = personMapper.findPersonAndIdCardEmbeded();

//输出查询结果。

for(Person person : persons){

System.out.println(person);

System.out.println(person.getIdCard());

//查看person和idCard的person是否同一个对象。

System.out.println(person == person.getIdCard().getPerson());

}

 

运转结果如下:

 

1    [main] DEBUG
com.test.mybatis3.mapper.PersonMapper.findPersonAndIdCardEmbeded  –
==>  Preparing: select * from t_person

43   [main] DEBUG
com.test.mybatis3.mapper.PersonMapper.findPersonAndIdCardEmbeded  –
==> Parameters:

67   [main] TRACE
com.test.mybatis3.mapper.PersonMapper.findPersonAndIdCardEmbeded  –
<==    Columns: id, name, status

67   [main] TRACE
com.test.mybatis3.mapper.PersonMapper.findPersonAndIdCardEmbeded  –
<==        Row: lisi, li si, 0

70   [main] DEBUG
com.test.mybatis3.mapper.PersonMapper.findIdCardByCardId  – ====>
 Preparing: select * from t_idcard where personid=?

70   [main] DEBUG
com.test.mybatis3.mapper.PersonMapper.findIdCardByCardId  – ====>
Parameters: lisi(String)

71   [main] TRACE
com.test.mybatis3.mapper.PersonMapper.findIdCardByCardId  – <====
   Columns: personid, cardid

72   [main] TRACE
com.test.mybatis3.mapper.PersonMapper.findIdCardByCardId  – <====
       Row: lisi, 222222222222222222

72   [main] DEBUG
com.test.mybatis3.mapper.PersonMapper.findPersonByPersonId  – ======>
 Preparing: select * from t_person where id=?

72   [main] DEBUG
com.test.mybatis3.mapper.PersonMapper.findPersonByPersonId  – ======>
Parameters: lisi(String)

72   [main] TRACE
com.test.mybatis3.mapper.PersonMapper.findPersonByPersonId  – <======
   Columns: id, name, status

73   [main] TRACE
com.test.mybatis3.mapper.PersonMapper.findPersonByPersonId  – <======
       Row: lisi, li si, 0

74   [main] DEBUG
com.test.mybatis3.mapper.PersonMapper.findPersonByPersonId  – <======
     Total: 1

74   [main] DEBUG
com.test.mybatis3.mapper.PersonMapper.findIdCardByCardId  – <====
     Total: 1

74   [main] TRACE
com.test.mybatis3.mapper.PersonMapper.findPersonAndIdCardEmbeded  –
<==        Row: zhangsan, zhang san, 0

75   [main] DEBUG
com.test.mybatis3.mapper.PersonMapper.findIdCardByCardId  – ====>
 Preparing: select * from t_idcard where personid=?

75   [main] DEBUG
com.test.mybatis3.mapper.PersonMapper.findIdCardByCardId  – ====>
Parameters: zhangsan(String)

75   [main] TRACE
com.test.mybatis3.mapper.PersonMapper.findIdCardByCardId  – <====
   Columns: personid, cardid

77   [main] TRACE
com.test.mybatis3.mapper.PersonMapper.findIdCardByCardId  – <====
       Row: zhangsan, 1111111111111111

77   [main] DEBUG
com.test.mybatis3.mapper.PersonMapper.findPersonByPersonId  – ======>
 Preparing: select * from t_person where id=?

78   [main] DEBUG
com.test.mybatis3.mapper.PersonMapper.findPersonByPersonId  – ======>
Parameters: zhangsan(String)

78   [main] TRACE
com.test.mybatis3.mapper.PersonMapper.findPersonByPersonId  – <======
   Columns: id, name, status

78   [main] TRACE
com.test.mybatis3.mapper.PersonMapper.findPersonByPersonId  – <======
       Row: zhangsan, zhang san, 0

78   [main] DEBUG
com.test.mybatis3.mapper.PersonMapper.findPersonByPersonId  – <======
     Total: 1

79   [main] DEBUG
com.test.mybatis3.mapper.PersonMapper.findIdCardByCardId  – <====
     Total: 1

79   [main] DEBUG
com.test.mybatis3.mapper.PersonMapper.findPersonAndIdCardEmbeded  –
<==      Total: 2

Person [id=lisi, name=li si, status=0,address=null]

IdCard [cardid=222222222222222222, personid=lisi, person=Person
[id=lisi, name=li si, status=0,address=null]]

false

Person [id=zhangsan, name=zhang san, status=0,address=null]

IdCard [cardid=1111111111111111, personid=zhangsan, person=Person
[id=zhangsan, name=zhang san, status=0,address=null]]

false

 

着眼运行结果好窥见,在双向关联的情形下,主表2长条记下(N=2),总共执行了1+N+N=5长SQL语句。如果换成被MyBatis做单向关系,在Service方法中编代码实现反朝关系,则独自待实行1+N条SQL语句。

 

网站地图xml地图