ibatis 多表关联查询

修改ibator代码生成的代码，实现多表关联查询（实现方法）

1.      public static class Criteria{}静态内部类的修改

增加属性criteriaJoinTableName、criteriaJoinTableColumn

修改isValid方法

增加方法：public void addJoinTable(String tableName)；

          public void addJoinTableColumn(String expression)；

具体如下：

public List criteriaWithoutValue;     public List criteriaWithListValue;     public List criteriaWithSingleValue;     public List criteriaWithBetweenValue;     public List criteriaJoinTableName;     public List criteriaJoinTableColumn;       public boolean isValid(){        return criteriaWithoutValue.size() > 0               || criteriaWithSingleValue.size() > 0               || criteriaWithListValue.size() > 0               || criteriaWithBetweenValue.size() > 0               || criteriaJoinTableColumn.size() > 0;     } /**      * <p>方法名称: addJoinTable|描述: 增加关联表</p>      * @param tableName 关联表名      */     public void addJoinTable(String tableName){        if(criteriaJoinTableName==null){            criteriaJoinTableName = new ArrayList();        }        criteriaJoinTableName.add(tableName);     }     /**      * <p>方法名称: addJoinTableColumn|描述: 增加关联关系</p>      * @param expression 关联表达式      */     public void addJoinTableColumn(String expression){        if(criteriaJoinTableColumn == null){            criteriaJoinTableColumn = new ArrayList();        }        criteriaJoinTableColumn.add(expression);     }

 

2.       sqlMap配置文件的修改

主要修改查询条件字句：ibatorgenerated_Example_Where_Clause

<sql id="ibatorgenerated_Example_Where_Clause">     <!--        WARNING - This element is automatically generated by Apache iBATIS ibator, do not modify.     -->     <iterate property="oredCriteria">        <isNotEmpty            property="oredCriteria[].criteriaJoinTableName">            <iterate conjunction="," prepend=","               property="oredCriteria[].criteriaJoinTableName">               $oredCriteria[].criteriaJoinTableName[]$            </iterate>        </isNotEmpty>     </iterate>     <iterate property="oredCriteria" conjunction="or"        prepend="where" removeFirstPrepend="iterate">        <isEqual property="oredCriteria[].valid"            compareValue="true">            (            <iterate conjunction="and" prepend="and"               property="oredCriteria[].criteriaJoinTableColumn">               $oredCriteria[].criteriaJoinTableColumn[]$            </iterate>            <iterate prepend="and"               property="oredCriteria[].criteriaWithoutValue" conjunction="and">               $oredCriteria[].criteriaWithoutValue[]$            </iterate>            <iterate prepend="and"               property="oredCriteria[].criteriaWithSingleValue"               conjunction="and">               $oredCriteria[].criteriaWithSingleValue[].condition$               #oredCriteria[].criteriaWithSingleValue[].value#            </iterate>            <iterate prepend="and"               property="oredCriteria[].criteriaWithListValue"               conjunction="and">               $oredCriteria[].criteriaWithListValue[].condition$               <iterate                   property="oredCriteria[].criteriaWithListValue[].values" open="("                   close=")" conjunction=",">                   #oredCriteria[].criteriaWithListValue[].values[]#               </iterate>            </iterate>            <iterate prepend="and"               property="oredCriteria[].criteriaWithBetweenValue"               conjunction="and">               $oredCriteria[].criteriaWithBetweenValue[].condition$               #oredCriteria[].criteriaWithBetweenValue[].values[0]#               and               #oredCriteria[].criteriaWithBetweenValue[].values[1]#            </iterate>            )        </isEqual>     </iterate> </sql>

 

使用配置

1.       配置对应的的sqlMap配置文件，仿照【sqlMap配置文件的修改】示例，将那两段代码拷贝到相应的位置（只是增加，没有修改！！！！）

2.       调用addJoinTable和addJoinTableColumn方法，进行表连接查询。

示例：关联V_ORGAN_USER_INFO表进行查询。关联关系为，主表指定字段和V_ORGAN_USER_INFO表的ORGAN_ID进行关联

public void addCriterionWithUserId(String user_id, String organKey){     if(user_id == null){        throw new RuntimeException("Value for condition cannot be null");     }     this.addJoinTable("V_ORGAN_USER_INFO"); //关联表名     this.addJoinTableColumn(organKey +" = V_ORGAN_USER_INFO.ORGAN_ID"); //关联关系     this.addCriterion("V_ORGAN_USER_INFO.USER_ID = '" + user_id + "'"); //其他查询条件 }

 

注意问题：表之间有重名的字段。

1.       如果重名的字段出现在查询结果列中，则需要修改sqlMap中的select字句，给字段增加所属表名（最好使用表的全名，而不要给表定义别名，这样查询总数时就不用管，也会避免其它的一些问题），具体如下：

<select id="ibatorgenerated_selectByExample"     resultMap="ibatorgenerated_BaseResultMap"     parameterClass="com.resoft.web.bean.T00SysLogCriteria">     <!--         WARNING - This element is automatically generated by Apache iBATIS ibator, do not modify.     -->     select T00_SYS_LOG.USER_ID, USER_ENAME, BUZI_NAME,     EXEC_TIME,ORGAN_KEY from T00_SYS_LOG     <isParameterPresent>         <include            refid="T00_SYS_LOG.ibatorgenerated_Example_Where_Clause" />         <isNotNull property="orderByClause">            order by $orderByClause$         </isNotNull>     </isParameterPresent>   </select>

 

2.       如果重名的字段出现在页面的查询条件中，

则需要对XXXCriteria进行修改

public Criteria andUserIdIsNull(){     addCriterion("T00_SYS_LOG.USER_ID is null");     return this; }   public Criteria andUserIdIsNotNull(){     addCriterion("T00_SYS_LOG.USER_ID is not null");     return this; }   public Criteria andUserIdEqualTo(String value){     addCriterion("T00_SYS_LOG.USER_ID =", value, "userId");     return this; } 。。。。。。。。。。。。。。。

3.       关联字段有重名，则要带上表名

 如：this.addJoinTableColumn(organKey +" = V_ORGAN_USER_INFO.ORGAN_ID");