ibatis一对多

1.创建数据库表：

create table team(
tid number primary key,
tname varchar2(20)
);

create table person(
pid number primary key,
pname varchar2(20),
tid number
);

alter table person
add constraint FK_team_person foreign key (tid) references team(tid);

insert into team (tid,tname) values(1,'JAVA');
insert into team (tid,tname) values(2,'C++');
insert into team (tid,tname) values(3,'.NET');

insert into person(pid,pname,tid) values(1,'曹孟德',1);
insert into person(pid,pname,tid) values(2,'刘玄德',1);
insert into person(pid,pname,tid) values(3,'孙仲谋',1);
insert into person(pid,pname,tid) values(4,'鲁子敬',2);
insert into person(pid,pname,tid) values(5,'诸葛孔明',2);
insert into person(pid,pname,tid) values(6,'司马仲达',2);
insert into person(pid,pname,tid) values(7,'周公瑾',3);
insert into person(pid,pname,tid) values(8,'关云长',3);
insert into person(pid,pname,tid) values(9,'许褚',3);

2.创建pojo类

package org.wh.ibatis.model;

import java.util.List;

/**
 * pojo类
 * @author wanghao
 *
 */
public class Team {
    private int tid;
    private String tname;
    private List<Person> persons;

　//set get...

}

package org.wh.ibatis.model;

/**
 * pojo类
 * @author wanghao
 *
 */
public class Person {
    private int pid;
    private String pname;
    private Team tid;

   //set get

}

3.配置文件

SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig     
    PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"     
    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
    <properties resource="SqlMap.properties"/>
    <settings cacheModelsEnabled="true" lazyLoadingEnabled="true" useStatementNamespaces="true" />
    <transactionManager type="JDBC">
        <dataSource type="SIMPLE">
            <property name="JDBC.Driver" value="${driver}"/>
            <property name="JDBC.ConnectionURL" value="${url}"/>
            <property name="JDBC.Username" value="${username}"/>
            <property name="JDBC.Password" value="${password}"/>
        </dataSource>
    </transactionManager>
    <sqlMap resource="org/wh/ibatis/model/Anay.xml" />
</sqlMapConfig>  

Anay.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap     
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"     
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap>
    <typeAlias alias="team" type="org.wh.ibatis.model.Team"/>
    <typeAlias alias="person" type="org.wh.ibatis.model.Person"/>
   
    <resultMap class="team" id="teamResMap">
        <result property="tid" column="tid" />
        <result property="tname" column="tname" javaType="java.lang.String" jdbcType="varchar2" />
        <result property="persons" column="tid" select="selectPersonsByTeamId"/>
    </resultMap>
    <resultMap class="person" id="personResMap">
        <result property="pid" column="pid"/>
        <result property="pname" column="pname" javaType="java.lang.String" jdbcType="varchar2"/>
        <result property="tid" column="tid" select="selectTeamById"/>
    </resultMap>
   
    <select id="selectAllTeam" resultMap="teamResMap">
        select tid,tname from team
    </select>
   
    <select id="selectAllPerson" resultMap="personResMap">
        select pid,pname,tid from person
    </select>
   
    <select id="selectTeamById" parameterClass="java.lang.Integer" resultMap="teamResMap">
        select tid,tname from team where tid = #value#
    </select>
   
    <select id="selectPersonsByTeamId" parameterClass="java.lang.Integer" resultMap="personResMap">
        select pid,pname,tid from person where tid = #value#
    </select>
   
</sqlMap>

  4.测试

    @Test
    public void test1() throws Exception{
        List<Team> teams = IbatisUtils.sqlMapClient.queryForList("selectAllTeam");
        for(Team t :teams){
            System.err.println(t);
            for(Person p : t.getPersons()){
                System.out.println("\t"+p);
            }
        }
    }

结果

tid:1    tname:JAVA
    pid:1    pname:曹孟德    team:JAVA
    pid:2    pname:刘玄德    team:JAVA
    pid:3    pname:孙仲谋    team:JAVA

tid:2    tname:C++
    pid:4    pname:鲁子敬    team:C++
    pid:5    pname:诸葛孔明    team:C++
    pid:6    pname:司马仲达    team:C++
tid:3    tname:.NET
    pid:7    pname:周公瑾    team:.NET
    pid:8    pname:关云长    team:.NET
    pid:9    pname:许褚    team:.NET

注意：此例子用于练习所用，存在N+1 问题！

解决方案1：使用联合查询

Anay.xml

<resultMap class="person" id="personResMap">
        <result property="pid" column="pid"/>
        <result property="pname" column="pname"/>
        <result property="tid.tid" column="tid"/>
        <result property="tid.tname" column="tname"/>
    </resultMap>

<select id="selectAllPerson" resultMap="personResMap">
        select * from person p,team t where p.tid=t.tid
    </select>

@Test
    public void test2() throws Exception {
        List<Person> persons = IbatisUtils.sqlMapClient.queryForList("selectAllPerson");
        for (Person p : persons) {
            System.out.println("\t" + p);
        }
    }

查询结果：

    pid:1    pname:曹孟德    team:JAVA
    pid:2    pname:刘玄德    team:JAVA
    pid:3    pname:孙仲谋    team:JAVA
    pid:4    pname:鲁子敬    team:C++
    pid:5    pname:诸葛孔明    team:C++
    pid:6    pname:司马仲达    team:C++
    pid:7    pname:周公瑾    team:.NET
    pid:8    pname:关云长    team:.NET
    pid:9    pname:许褚    team:.NET

缺点：假如很少有必要访问相关的对象，如Person的Team对象属性，则不用联合查询加载所有的Team属性可能更快

这种情况下，使用延迟加载和字节码增强的子查询，性能会更好。基本原则是：如果需要访问相关的对象，则使用联合查询

否则使用延迟加载和字节码增强的子查询。