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");
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值