ibatis批量处理+多表关联查询

 ibatis批量操作

ibatis批量操作存在两种方式：
一种是直接在代码中进行循环操作，另一种是在配置文件中进行循环操作。
(1)在配置文件中循环：
1.情况一：多个输入参数循环次数不对称：
  处理方式：新建一个JAVABEAN，将各个参数作为其属性进行赋值。在配置文件中，获取其值，进行各自循环。
  如下例：要更新的字段Opr的值只有一个，而ID的值却有多个。
代码：
    view plaincopy to clipboardprint?
public void batchClientAppOperation(String[] appDevIds,String operation) throws Exception  
    {  
        try 
        {  
            AppOperation appOpr=new AppOperation();  
            appOpr.setOperation(operation);  
            appOpr.setAppDevIds(appDevIds);  
              
            this.getSqlMapClientTemplate().update("Device.ClientAppOperation", appOpr);  
        }  
        catch (DataAccessException ex)  
        {  
            throw new Exception(  
                    Constants.ERROR_CODE_DELETE_USER_BY_ID.getLongValue(),  
                    ex);  
        }  
    } 
public void batchClientAppOperation(String[] appDevIds,String operation) throws Exception
    {
        try
        {
            AppOperation appOpr=new AppOperation();
            appOpr.setOperation(operation);
            appOpr.setAppDevIds(appDevIds);
           
            this.getSqlMapClientTemplate().update("Device.ClientAppOperation", appOpr);
        }
        catch (DataAccessException ex)
        {
            throw new Exception(
                    Constants.ERROR_CODE_DELETE_USER_BY_ID.getLongValue(),
                    ex);
        }
    }
ibatis配置文件：
 view plaincopy to clipboardprint?
<update id="Device.ClientAppOperation" parameterClass="AppOperation"> 
        update T_Device_App_R_Info  
         set Opr=#operation#  
         where ID in  
        <iterate conjunction="," open="(" close=")" property="appDevIds"> 
            #appDevIds[]#  
        </iterate> 
 </update> 
<update id="Device.ClientAppOperation" parameterClass="AppOperation">
        update T_Device_App_R_Info
         set Opr=#operation#
         where ID in
        <iterate conjunction="," open="(" close=")" property="appDevIds">
            #appDevIds[]#
        </iterate>
 </update>
 
2.情况二：多个输入参数循环次数是对称的：
  处理方式：新建一个hashmap，将各个参数名作为key，参数值作为value。在配置文件中，获取各key-value，进行各自循环。
  如下例：将循环插入/更新的列名作为key，列值作为value放入hashmap中.(列名与列值是一一对应的，即循环次数对等)
view plaincopy to clipboardprint?
<!--  
插入一条新纪录  
$与#获取值的方式是一样的，只是$获取是对应参数的值，#会将获取的值加上引号，变为字符串类型。所以，一般用$来获取表名，列名，用#用获取要插入的值。  
--> 
<insert id="customPO_insert" parameterClass="customPO"> 
INSERT INTO $moduleTable$ (parentID  
<iterate property="fieldValueList" prepend="," conjunction=",">  --fieldValueList是customPO一属性  
$fieldValueList[].key$   --循环fieldValueList[]这个数组，因为此数组每个对象是map，可获得map的key.  
</iterate> 
)  
VALUES (#parentID#  
<iterate property="fieldValueList" prepend="," conjunction=","> 
#fieldValueList[].value#  
</iterate> 
)  
<selectKey resultClass="int" keyProperty="id"> 
SELECT last_insert_id()  
</selectKey> 
</insert> 
   
<!--更新--> 
<update id="customPO_update" parameterClass="customPO"> 
UPDATE $moduleTable$ SET  
<iterate property="fieldValueList" conjunction=","> 
$fieldValueList[].key$ = #fieldValueList[].value#  
</iterate> 
WHERE id = #id#  
</update> 
<!--
插入一条新纪录
$与#获取值的方式是一样的，只是$获取是对应参数的值，#会将获取的值加上引号，变为字符串类型。所以，一般用$来获取表名，列名，用#用获取要插入的值。
-->
<insert id="customPO_insert" parameterClass="customPO">
INSERT INTO $moduleTable$ (parentID
<iterate property="fieldValueList" prepend="," conjunction=",">  --fieldValueList是customPO一属性
$fieldValueList[].key$   --循环fieldValueList[]这个数组，因为此数组每个对象是map，可获得map的key.
</iterate>
)
VALUES (#parentID#
<iterate property="fieldValueList" prepend="," conjunction=",">
#fieldValueList[].value#
</iterate>
)
<selectKey resultClass="int" keyProperty="id">
SELECT last_insert_id()
</selectKey>
</insert>
 
<!--更新-->
<update id="customPO_update" parameterClass="customPO">
UPDATE $moduleTable$ SET
<iterate property="fieldValueList" conjunction=",">
$fieldValueList[].key$ = #fieldValueList[].value#
</iterate>
WHERE id = #id#
</update>
 
(2)
在代码中批量处理：
即是在startBatch()与executeBatch()之间循环调用操作数据库。如下:
   view plaincopy to clipboardprint?
public void batchUpdate(final String[] ids,final String appId) throws IEPGMException  
    {  
        try 
        {  
            final HashMap<String, String> map = new HashMap<String, String>();  
              
              
            this.getSqlMapClientTemplate().execute(new SqlMapClientCallback()  
            {  
                  
              public Object doInSqlMapClient( SqlMapExecutor executor) throws SQLException  
                {  
                    executor.startBatch();  
                    executor.delete("Application.deleteAppDevTypeMapById", appId);  
                    for(int i=0;i<ids.length;i++)  
                    {  
//由于map中key都是appId,ids,所以每次循环赋值后，map中也只有二个key，即是appId,ids，其值会被不断更新.  
                     map.put("appId",appId);  
                     map.put("ids",ids[i]);  
                     executor.insert("Application.insertAppDevTypeMap", map);  
                    }  
                    executor.executeBatch();  
                    return null;  
                }  
            });  
        }  
        catch (DataAccessException ex)  
        {  
            throw new IEPGMException(  
                    Constants.ERROR_CODE_DELETE_USER_BY_ID.getLongValue(),  
                    ex);  
        }  
    } 
public void batchUpdate(final String[] ids,final String appId) throws IEPGMException
    {
        try
        {
            final HashMap<String, String> map = new HashMap<String, String>();
           
           
            this.getSqlMapClientTemplate().execute(new SqlMapClientCallback()
            {
               
              public Object doInSqlMapClient( SqlMapExecutor executor) throws SQLException
                {
                    executor.startBatch();
                    executor.delete("Application.deleteAppDevTypeMapById", appId);
                    for(int i=0;i<ids.length;i++)
                    {
//由于map中key都是appId,ids,所以每次循环赋值后，map中也只有二个key，即是appId,ids，其值会被不断更新.
                     map.put("appId",appId);
                     map.put("ids",ids[i]);
                     executor.insert("Application.insertAppDevTypeMap", map);
                    }
                    executor.executeBatch();
                    return null;
                }
            });
        }
        catch (DataAccessException ex)
        {
            throw new IEPGMException(
                    Constants.ERROR_CODE_DELETE_USER_BY_ID.getLongValue(),
                    ex);
        }
    }
 
ibatis配置文件中：
view plaincopy to clipboardprint?
<delete id="Application.deleteAppDevTypeMapById" parameterClass="java.lang.String"> 
    DELETE FROM T_App_Spce_R_Info WHERE App_ID=#appId#;  
</delete> 
 
<insert id="Application.insertAppDevTypeMap" parameterClass="java.util.HashMap"> 
 INSERT INTO T_App_Spce_R_Info(App_ID,Spec_Code) VALUES(#appId#,#ids#);  
</insert> 
 <delete id="Application.deleteAppDevTypeMapById" parameterClass="java.lang.String">
     DELETE FROM T_App_Spce_R_Info WHERE App_ID=#appId#;
 </delete>
 
 <insert id="Application.insertAppDevTypeMap" parameterClass="java.util.HashMap">
  INSERT INTO T_App_Spce_R_Info(App_ID,Spec_Code) VALUES(#appId#,#ids#);
 </insert>
 
如何定义多表查询
1)
添加Guojia.java类，只是一个普通的javabean，对应guojia表中各字段，及其setter,getter，无须像hibernate一样设置对应关系。
2)
添加guojia.xml，只须添加：
 
view plaincopy to clipboardprint?
<sqlMap namespace="Guo"> 
       <typeAlias type="com.coship.dhm.iepgm.admin.entity.Guojia" alias="Guo" /> 
       <resultMap id="GuoResult" class="Guo" > 
              <result column="gid" property="gid" /> 
              <result column="userid" property="userid" /> 
              <result column="gname" property="gname" /> 
       </resultMap> 
</sqlMap> 
<sqlMap namespace="Guo">
       <typeAlias type="com.coship.dhm.iepgm.admin.entity.Guojia" alias="Guo" />
       <resultMap id="GuoResult" class="Guo" >
              <result column="gid" property="gid" />
              <result column="userid" property="userid" />
              <result column="gname" property="gname" />
       </resultMap>
</sqlMap>
 
无须添加如查询之类的东西。
3)
在当前类hwj.java添加连合查询的表guojia的list属性,如下：
   view plaincopy to clipboardprint?
private List<Guojia> guoList;  
  public List<Guojia> getGuoList() {  
            return guoList;  
     }  
     public void setGuoList(List<Guojia> guoList) {  
            this.guoList = guoList;  
     } 
  private List<Guojia> guoList;
    public List<Guojia> getGuoList() {
              return guoList;
       }
       public void setGuoList(List<Guojia> guoList) {
              this.guoList = guoList;
       }
 
4)
在当前表hwj.xml的resultMap中，添加<result property="guoList" resultMap="Guo.GuoResult"/>，如下：
     view plaincopy to clipboardprint?
<resultMap id="HwjResult" class="Hwj"> 
            <result column="staff_id" property="id" /> 
            <result column="longinName" property="loginName" /> 
            <result column="password" property="password" /> 
            <result column="staff_name" property="username" /> 
            <result column="status" property="status" /> 
            <result column="phone" property="phone" /> 
            <result column="email" property="email" /> 
            <result property="guoList" resultMap="Guo.GuoResult"/> 
     </resultMap> 
  <resultMap id="HwjResult" class="Hwj">
              <result column="staff_id" property="id" />
              <result column="longinName" property="loginName" />
              <result column="password" property="password" />
              <result column="staff_name" property="username" />
              <result column="status" property="status" />
              <result column="phone" property="phone" />
              <result column="email" property="email" />
              <result property="guoList" resultMap="Guo.GuoResult"/>
       </resultMap>
 
 
注意：resultMap="Guo.GuoResult"中的Guo对应上面guojia.xml中的namespace，GuoResult对应其resultMap的名.
5)
在sqlMapClient.xml导入guojia.xml
注意：
在resultMap中列出的各列，只能是select的各字段。不能多，也不能少。否则出错。
如：A表中有a,b两列。现select a from A。则resultMap只能列了关于a的列名—属性，不能多列b的东西，否则出错。
 
6)
若为三表连合查询，
1.对于第三个表的设置，跟上面设置第二个表一样。
2.同样，也只须在hwj.java,hwj.xml中进行相同的设置即可。
说明：
在页面显示时：
在hwj循环记录的iterator中，添加guojia,address各种的循环即可。
无须将address内嵌在guojia中：因为实际上hwj的查询语句查询出来是所有记录，guojia,address添加的循环，也只能循环一次，取一个值。
设置如下：
view plaincopy to clipboardprint?
<%-- guojia    --%>         
                                    <td  width="20%"> 
                                    <s:iterator value="#user.guoList" status="index1" id="l"> 
                                       <%-- <s:property value="#index1.index"/> --%> 
                                          <s:property value="gname" /> 
                                   </s:iterator></td> 
<%-- address        --%>         
                                    <td  width="20%"> 
                                    <s:iterator value="#user.addressList" status="index1" id="l"> 
                                       <%-- <s:property value="#index1.index"/> --%> 
                                          <s:property value="address" /> 
                                   </s:iterator></td> 
<%-- guojia    --%>      
                                    <td  width="20%">
                                    <s:iterator value="#user.guoList" status="index1" id="l">
                                       <%-- <s:property value="#index1.index"/> --%>
                                          <s:property value="gname" />
                                   </s:iterator></td>
<%-- address        --%>      
                                    <td  width="20%">
                                    <s:iterator value="#user.addressList" status="index1" id="l">
                                       <%-- <s:property value="#index1.index"/> --%>
                                          <s:property value="address" />
                                   </s:iterator></td>
 
 
（3）
resultMap中的的列与javaBean属性的对应关系,只能列出sql语句中select的字段,不能多也不能少,否则出错.----不关心sql语句中关联几个表.如下:
   view plaincopy to clipboardprint?
<resultMap id="DevTypeResult" class="DevType"> 
      <result column="Spec_Code" property="specCode" /> 
      <result column="Spec_Name" property="specName" /> 
      <result column="Vendor" property="vendor" /> 
      <result column="Brand" property="brand" /> 
      <result column="Model" property="model" /> 
   </resultMap> 
     
   <select id="DevType.findNoChoiceDevTypeById" parameterClass="java.lang.String" resultMap="DevTypeResult"> 
      SELECT distinct d.*   
      FROM T_App_Info a, T_Device_Spec d, T_App_Spce_R_Info r   
      WHERE a.App_ID=r.App_ID   
            AND r.Spec_Code!=d.Spec_Code  
            AND a.App_ID=#appId#  
      ORDER BY  d.Spec_Code;  
   </select> 
 <resultMap id="DevTypeResult" class="DevType">
       <result column="Spec_Code" property="specCode" />
       <result column="Spec_Name" property="specName" />
       <result column="Vendor" property="vendor" />
       <result column="Brand" property="brand" />
       <result column="Model" property="model" />
    </resultMap>
   
    <select id="DevType.findNoChoiceDevTypeById" parameterClass="java.lang.String" resultMap="DevTypeResult">
       SELECT distinct d.*
       FROM T_App_Info a, T_Device_Spec d, T_App_Spce_R_Info r
       WHERE a.App_ID=r.App_ID
             AND r.Spec_Code!=d.Spec_Code
             AND a.App_ID=#appId#
       ORDER BY  d.Spec_Code;
    </select>
 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/w40338544/archive/2010/06/28/5699568.aspx