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