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");