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