ibatis 表与表的关联查询

ibatis高级特性，处理表与表之间的关联。ibatis中，提供了Statement嵌套支持，通过Statement嵌套，我们可以实现关联数据的操作。

  用表lock和key为例,一个lock可以配多个key,一个key只能对应一个lock.

1、数据库表

 

create table lock(  
    id int,  
    lockName varchar  
);  
create table key(  
    id int,  
    lockId int,  
    keyName varchar  
);

2、定义映射类: 

  在进行表关联的时候一定要注意：关联的2个pojo之间的联系，还有就是配置文件之间的关联。 

 

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;  
    }  
      
}  
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;  
        }  
}

3、 配置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>

 

4、测试类:

 

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());  
            }  
       }  
    }  
}

 

多表关联查询大量数据时建议采用存储过程来处理。