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" 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" 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" parameterresultresultMap="LockResult"> <![CDATA[ select id,lockName from lock ]]> </select> <select id="getKeysByLockId" parameterresultname="code">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()); } } } }?
多表关联查询大量数据时建议采用存储过程来处理。
?
1 楼 java_ganbin 2011-09-01 写得不够详细，lock_key_table哪来的？没看到你创建