iBATIS SQL Maps（三）

看看 iBATIS SQL Maps 是怎样从数据库按照 one-to-many 关系查询数据的：

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap     PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"     "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap>     <resultMap id="get-autoInfo-result" class="bo.AutoInfo">         <result property="id" column="auto_id"/>         <result property="licensePlate" column="license_plate"/>     </resultMap>     <resultMap id="get-people-result" class="bo.People">         <result property="id" column="owner_id"/>         <result property="name" column="name"/>         <result property="address" column="address"/>         <result property="autoInfoList" column="owner_id" select="getAutoInfo"/>     </resultMap>   <select id="getPeople" resultMap="get-people-result" parameterClass="bo.People">        <![CDATA[        select * from people where owner_id=#id#         ]]>   </select>   <select id="getAutoInfo" resultMap="get-autoInfo-result" parameterClass="int">        <![CDATA[        select * from auto_info where owner_no=#id#         ]]>   </select> </sqlMap>

 

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap     PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"     "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap>     <resultMap id="get-autoInfo-result" class="bo.AutoInfo">         <result property="id" column="auto_id"/>         <result property="licensePlate" column="license_plate"/>     </resultMap>     <resultMap id="get-people-result" class="bo.People">         <result property="id" column="owner_id"/>         <result property="name" column="name"/>         <result property="address" column="address"/>         <result property="autoInfoList" column="owner_id" select="getAutoInfo"/>     </resultMap>   <select id="getPeople" resultMap="get-people-result" parameterClass="bo.People">        <![CDATA[        select * from people where owner_id=#id#         ]]>   </select>   <select id="getAutoInfo" resultMap="get-autoInfo-result" parameterClass="int">        <![CDATA[        select * from auto_info where owner_no=#id#         ]]>   </select> </sqlMap>

 

resultMap id="get-autoInfo-result" class="bo.AutoInfo"

    resultMap 是 iBATIS SQL Maps 框架中重要组件之一，你也许还记得 resultClass 吧？两者概念基本一致。resultMap 则是可定制 Mapped Statement 返回对象的。可定制表现在：比如我有一张数据表包含10个字段，但我的 POJO 只定义了5个属性，这时，只要取出我关心的字段就行。和 Hibernate 类似，resultMap 的 result 元素定义了 POJO 属性到数据表字段的映射。

需要说明的是 result 元素 select 属性，该属性类似于加载了一个子查询，并自动载入，如果这个子查询包含多条结果，就自动把结果装载进 List 类型。该 result 元素对应 People 中的属性为”autoInfoList”，而由 Hibernate 生成的 People 却是 Set 类型”autoInfoSet”，这两种类型无法直接互转，所以我修改了 POJO 属性。

select id="getPeople" resultMap="get-people-result" parameterClass="bo.People"

此为 select 类型 Mapped Statement。传入 People 实例，返回为自己定制、包含”autoInfoList”的 People 实例。传入People 类型参数是为了便于多字段匹配查询，今天也许我只需利用 People.id 单字段匹配就能得到结果，但下次的新需求也许是 People.address 和 People.name 联合匹配！

相应程序代码：

package test; import java.io.Reader; import java.util.List; import com.ibatis.sqlmap.client.*; import com.ibatis.common.resources.*; import bo.*; public class AutoMag {  private Reader reader;  private People people=new People();  private SqlMapClient sqlMap;  private String resource = "SqlMapConfig.xml";    public People findPeople() throws Exception{   reader = Resources.getResourceAsReader(resource);    sqlMap=SqlMapClientBuilder.buildSqlMapClient(reader);       people.setId(new Integer("1"));    people=(People)sqlMap.queryForObject("getPeople",people);    return people;  } }

 

package test; import java.io.Reader; import java.util.List; import com.ibatis.sqlmap.client.*; import com.ibatis.common.resources.*; import bo.*; public class AutoMag {  private Reader reader;  private People people=new People();  private SqlMapClient sqlMap;  private String resource = "SqlMapConfig.xml";    public People findPeople() throws Exception{   reader = Resources.getResourceAsReader(resource);    sqlMap=SqlMapClientBuilder.buildSqlMapClient(reader);       people.setId(new Integer("1"));    people=(People)sqlMap.queryForObject("getPeople",people);    return people;  } }

 

sqlMap.queryForObject(String arg0, Object arg1)

此方法类似 Hibernate 的 session.load(…)，传入 Mapped Statement id，再传入包含主键的对象实例。除我上面列举的 sqlMap.queryForObject(String arg0, Object arg1) 外，还有重载方法：

people=(People)sqlMap.queryForObject("getPeople",people); 替换成 sqlMap.queryForObject("getPeople", people, people);

 

people=(People)sqlMap.queryForObject("getPeople",people); 替换成 sqlMap.queryForObject("getPeople", people, people);

 

第一个”people”是传入参数，第二个”people”是返回的结果。要表达的意思一样，只不过换了种表现形式。

下面我会讲到如何以集合类持有多个 People 对象实例。

在车辆管理应用中，需要把人员一一列出，选中某个再显示详细内容。类似于这样的需求，iBATIS SQL Maps 引入 sqlMap.queryForList(String arg0, Object arg1) 来满足。

      还记得我们的映射文件怎么写的？对了，传入主键值再查询！

但是，新需求不要任何条件，直接列出人员啊！难道还要再添加新的 Mapped Statement 来满足？动态 Mapped Statement 能满足在不改变映射文件的前提下提供有参数和无差数查询：

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap     PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"     "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap>     <resultMap id="get-autoInfo-result" class="bo.AutoInfo">         <result property="id" column="auto_id"/>         <result property="licensePlate" column="license_plate"/>     </resultMap>     <resultMap id="get-people-result" class="bo.People">         <result property="id" column="owner_id"/>         <result property="name" column="name"/>         <result property="address" column="address"/>         <result property="autoInfoList" column="owner_id" select="getAutoInfo"/>     </resultMap>   <select id="getPeople" resultMap="get-people-result" parameterClass="bo.People">        <![CDATA[        select * from people        ]]>        <dynamic prepend="where">          <isNotNull property="id">            <![CDATA[            owner_id=#id#            ]]>          </isNotNull>        </dynamic>   </select>   <select id="getAutoInfo" resultMap="get-autoInfo-result" parameterClass="int"                     resultClass="bo.AutoInfo">        <![CDATA[        select * from auto_info where owner_no=#id#         ]]>   </select> </sqlMap>

 

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap     PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"     "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap>     <resultMap id="get-autoInfo-result" class="bo.AutoInfo">         <result property="id" column="auto_id"/>         <result property="licensePlate" column="license_plate"/>     </resultMap>     <resultMap id="get-people-result" class="bo.People">         <result property="id" column="owner_id"/>         <result property="name" column="name"/>         <result property="address" column="address"/>         <result property="autoInfoList" column="owner_id" select="getAutoInfo"/>     </resultMap>   <select id="getPeople" resultMap="get-people-result" parameterClass="bo.People">        <![CDATA[        select * from people        ]]>        <dynamic prepend="where">          <isNotNull property="id">            <![CDATA[            owner_id=#id#            ]]>          </isNotNull>        </dynamic>   </select>   <select id="getAutoInfo" resultMap="get-autoInfo-result" parameterClass="int"                     resultClass="bo.AutoInfo">        <![CDATA[        select * from auto_info where owner_no=#id#         ]]>   </select> </sqlMap>

 

dynamic prepend="where"

select 类型 Mapped Statement 的本质并无改变，只是通过 dynamic 元素定义了动态 where 子句。dynamic 元素属性 prepend=”where”在一元判定元素 isNotNull 返回”true”时有效。isNotNull 元素属性 property="id" 用于判定 People.id 是否为 null，假如未传入包含主键值的 People 实例，dynamic 元素将会无效，反之则亦然，这样就达到了在不改变映射文件的前提下实现动态 SQL 的目的。关于一元判定元素和二元判定元素的详细资料请查阅官方文档，如能恰当利用这些判定元素，动态 SQL 将会更灵活。

相应程序代码：

package test; import java.io.Reader; import java.util.List; import com.ibatis.sqlmap.client.*; import com.ibatis.common.resources.*; import bo.*; public class AutoMag {  private Reader reader;  private People people=new People();  private SqlMapClient sqlMap;  private String resource = "SqlMapConfig.xml";    public List findPeople() throws Exception{   reader = Resources.getResourceAsReader(resource);   sqlMap=SqlMapClientBuilder.buildSqlMapClient(reader);         List list=sqlMap.queryForList("getPeople",null);   return list;  } }

 

package test; import java.io.Reader; import java.util.List; import com.ibatis.sqlmap.client.*; import com.ibatis.common.resources.*; import bo.*; public class AutoMag {  private Reader reader;  private People people=new People();  private SqlMapClient sqlMap;  private String resource = "SqlMapConfig.xml";    public List findPeople() throws Exception{   reader = Resources.getResourceAsReader(resource);   sqlMap=SqlMapClientBuilder.buildSqlMapClient(reader);         List list=sqlMap.queryForList("getPeople",null);   return list;  } }

 

传入 Mapped Statement id，再传入未包含主键的对象实例，也可以干脆传 null 进去。重载方法 sqlMap.queryForList(String arg0, Object arg1, int arg2, int arg3) 用于分页，arg2、arg3分别代表页码和每页个数。

好了，使用动态 Mapped Statement，再试试 queryForObject(String arg0, Object arg1) 吧！

   

（请注意！引用、转贴本文应注明原作者：Rosen Jiang 以及出处：http://blog.csdn.net/rosen）

   

（请注意！引用、转贴本文应注明原作者：Rosen Jiang 以及出处：http://blog.csdn.net/rosen）