iBATIS多表

流程：

1、创建pojo类（在一对多关联关系中，应该在“一”这端的pojo有一个“多”这一端的引用，而多这端有一这端的集合引用（即聚合关联）

2、创建pojo对应的数据库表，并插入数据

3、在ibatis配置文件中配置pojo类（配置方法：SqlMappingConfig.xml文件中添加如<typeAlias alias="Key" type="com.longsky.ibatis.lock.model.Key"/>配置，下文会看到。）

4、在pojo对应的xml配置文件中配置resultMap，比如在Lock.xml文件中添加

 

<resultMap id="LockResult" class="Lock">     <result property="id" column="id"/> <result property="lockName" column="lockName"/>     <result property="keys" column="id" select="getKeysByLockId"/>     </resultMap>

5、在需要级联查询的查询语句中使用resultMap，比如

<select id="selectAllLocks" resultMap="LockResult">     <![CDATA[     select id,lockName from lock  ]]>     </select>   

上面的resultMap="LockResult"就是使用resultMap

 

下面进行详细介绍：

这里用表lock和key为例,一个lock可以配多个key,一个key只能对应一个lock.
数据库表

创建lock表

create table tlock(id varchar(2) not null,lockName varchar(10),primary key(id));

 

创建key表

create table tkey(id varchar(2) not null primary key,lockid varchar(2) not null,keyName varchar(10),foreign key(lockid) references tlock(id));

 

插入数据到两个表：

insert into tlock values('1','drawer1');insert into tlock values('2','drawer2');insert into tkey values('1','1','key1');insert into tkey values('2','1','key2');insert into tkey values('1','2','key3');insert into tkey values('2','2','key4');

 

定义映射类:

key持久化类：

public class Key {   private int id; private int lockId;  private String keyName;  private Lock lock; public int getId() {  return id; } public void setId(int id) {  this.id = id; } public int getLockId() {  return lockId; } public void setLockId(int lockId) {  this.lockId = lockId; } public String getKeyName() {  return keyName; } public void setKeyName(String keyName) {  this.keyName = keyName; } public Lock getLock() {  return lock; } public void setLock(Lock lock) {  this.lock = lock; } }

 

Lock持久化类:

public class Lock {private int id;private String lockName;private List<Key> keys;  public int getId() {   return id;  }  public void setId(int id) {   this.id = id;  }  public String getLockName() {   return lockName;  }  public void setLockName(String lockName) {   this.lockName = lockName;  }  public List<Key> getKeys() {   return keys;  }  public void setKeys(List<Key> keys) {   this.keys = keys;  }}

 

配置ibatis的 SqlMapping文件

<?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 namespace="lock">     <typeAlias alias="Key" type="com.longsky.ibatis.lock.model.Key"/>     <typeAlias alias="Lock" type="com.longsky.ibatis.lock.model.Lock"/>         <resultMap id="KeyResult" class="Key">     <result property="id" column="id"/>     <result property="keyName" column="keyName"/>     <result property="lock" column="lockId" select="getLockById"/>     </resultMap>  <!-- 通过key表中保存的lock的id实现的一对一关联,ibatis会使用getLockById(lockId)的结果填充lock属性 -->      <resultMap id="LockResult" class="Lock">     <result property="id" column="id"/>     <result property="lockName" column="lockName"/>     <result property="keys" column="id" select="getKeysByLockId"/>     </resultMap>  <!-- 通过lock表中的id实现的一对多关联,ibatis会使用getKeysByLockId(id)得到的List填充keys属性 -->    <select id="selectAllkeys" resultMap="KeyResult">     <!--[CDATA[     select id,lockId,keyName from lock_key_table  ]]>     </select>          <select id="getLockById" parameterClass="int" resultClass="Lock">     <![CDATA[     select id,lockName from lock where id = #value#     ]]>     </select>    <select id="selectAllLocks" resultMap="LockResult">     <![CDATA[     select id,lockName from lock  ]]>     </select>         <select id="getKeysByLockId" parameterClass="int" resultClass="key">     <![CDATA[     select id,lockId,keyName from lock_key_table where lockId = #value#     ]]-->     </select>   </sqlMap>  

 

写测试类:

import java.io.IOException;import java.io.Reader;import java.sql.SQLException;import java.util.List;import com.ibatis.common.resources.Resources;import com.ibatis.sqlmap.client.SqlMapClient;import com.ibatis.sqlmap.client.SqlMapClientBuilder;import com.longsky.ibatis.lock.model.Key;import com.longsky.ibatis.lock.model.Lock;public class Test { @SuppressWarnings("unchecked") public static void main(String[] args) throws SQLException {  SqlMapClient sqlMapper=null ;  try{         Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");         sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);         reader.close();          }     catch(IOException e){         throw new RuntimeException("Something bad happened while building the SqlMapClient instance." + e, e);         }      List<Lock> locks=(List<Lock>)sqlMapper.queryForList("selectAllLocks");      //取Lock对象。      for(int i=0;i<locks.size();i++){      Lock lock=(Lock)locks.get(i);      System.out.println("lock name = ["+lock.getLockName()+"] has keys as follows:");      //从Lock对象取List<Key>。      List<Key> keys=lock.getKeys();      for(int j=0;j<keys.size();j++){       System.out.println("key"+j+":"+keys.get(i).getKeyName());      }    } }}