ibatis-2.3.4.726学习(二) 多表关联查询

     

 

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

      数据库表

 

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

 

 

 

      (待续)

 

 

      定义映射类:

 

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

 

 

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