Terry.Li-彬

虚其心,可解天下之问;专其心,可治天下之学;静其心,可悟天下之理;恒其心,可成天下之业。

  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  143 随笔 :: 344 文章 :: 130 评论 :: 0 Trackbacks
下面的内容是基于ibatis2.2,以后的版本是否提供了类似功能不太清楚,甚至这个版本是否提供也没有细究(好像没有)。

很多时候我们需要执行select语句对应的count语句,例如分页查询时要得到结果的记录数,但在ibatis的映射文件中我们只想写一条select语句,而count语句直接由这条语句生成,这可以省去很多不必要的语句关联,下面的代码可以实现这一点。

CountStatementUtil.java

java 代码
package com.aladdin.dao.ibatis.ext;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import com.aladdin.util.ReflectUtil;
import com.ibatis.common.jdbc.exception.NestedSQLException;
import com.ibatis.sqlmap.client.event.RowHandler;
import com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient;
import com.ibatis.sqlmap.engine.mapping.parameter.ParameterMap;
import com.ibatis.sqlmap.engine.mapping.result.AutoResultMap;
import com.ibatis.sqlmap.engine.mapping.result.BasicResultMap;
import com.ibatis.sqlmap.engine.mapping.result.ResultMap;
import com.ibatis.sqlmap.engine.mapping.sql.Sql;
import com.ibatis.sqlmap.engine.mapping.statement.ExecuteListener;
import com.ibatis.sqlmap.engine.mapping.statement.MappedStatement;
import com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback;
import com.ibatis.sqlmap.engine.mapping.statement.SelectStatement;
import com.ibatis.sqlmap.engine.scope.ErrorContext;
import com.ibatis.sqlmap.engine.scope.RequestScope;

public class CountStatementUtil {

public static MappedStatement createCountStatement(MappedStatement selectStatement) {
return new CountStatement((SelectStatement) selectStatement);
}

public static String getCountStatementId(String selectStatementId) {
return "__" + selectStatementId + "Count__";
}

}

class CountStatement extends SelectStatement {

public CountStatement(SelectStatement selectStatement) {
super();
setId(CountStatementUtil.getCountStatementId(selectStatement
.getId()));
setResultSetType(selectStatement.getResultSetType());
setFetchSize(1);
setParameterMap(selectStatement.getParameterMap());
setParameterClass(selectStatement.getParameterClass());
setSql(selectStatement.getSql());
setResource(selectStatement.getResource());
setSqlMapClient(selectStatement.getSqlMapClient());
setTimeout(selectStatement.getTimeout());
List executeListeners = (List) ReflectUtil.getFieldValue(
selectStatement, "executeListeners", List.class);
if (executeListeners != null) {
for (Object listener : executeListeners) {
addExecuteListener((ExecuteListener) listener);
}
}
BasicResultMap resultMap = new AutoResultMap(
((ExtendedSqlMapClient) getSqlMapClient()).getDelegate(), false);
resultMap.setId(getId() + "-AutoResultMap");
resultMap.setResultClass(Long.class);
resultMap.setResource(getResource());
setResultMap(resultMap);

}

protected void executeQueryWithCallback(RequestScope request,
Connection conn, Object parameterObject, Object resultObject,
RowHandler rowHandler, int skipResults, int maxResults)
throws SQLException {
ErrorContext errorContext = request.getErrorContext();
errorContext
.setActivity("preparing the mapped statement for execution");
errorContext.setObjectId(this.getId());
errorContext.setResource(this.getResource());

try {
parameterObject = validateParameter(parameterObject);

Sql sql = getSql();

errorContext.setMoreInfo("Check the parameter map.");
ParameterMap parameterMap = sql.getParameterMap(request,
parameterObject);

errorContext.setMoreInfo("Check the result map.");
ResultMap resultMap = getResultMap(request, parameterObject, sql);

request.setResultMap(resultMap);
request.setParameterMap(parameterMap);

errorContext.setMoreInfo("Check the parameter map.");
Object[] parameters = parameterMap.getParameterObjectValues(
request, parameterObject);

errorContext.setMoreInfo("Check the SQL statement.");
String sqlString = getSqlString(request, parameterObject, sql);

errorContext.setActivity("executing mapped statement");
errorContext
.setMoreInfo("Check the SQL statement or the result map.");
RowHandlerCallback callback = new RowHandlerCallback(resultMap,
resultObject, rowHandler);
sqlExecuteQuery(request, conn, sqlString, parameters, skipResults,
maxResults, callback);

errorContext.setMoreInfo("Check the output parameters.");
if (parameterObject != null) {
postProcessParameterObject(request, parameterObject, parameters);
}

errorContext.reset();
sql.cleanup(request);
notifyListeners();
} catch (SQLException e) {
errorContext.setCause(e);
throw new NestedSQLException(errorContext.toString(), e
.getSQLState(), e.getErrorCode(), e);
} catch (Exception e) {
errorContext.setCause(e);
throw new NestedSQLException(errorContext.toString(), e);
}
}

private String getSqlString(RequestScope request, Object parameterObject,
Sql sql) {
String sqlString = sql.getSql(request, parameterObject);
int start = sqlString.toLowerCase().indexOf("from");
if (start >= 0) {
sqlString = "SELECT COUNT(*) AS c " + sqlString.substring(start);
}
return sqlString;
}

private ResultMap getResultMap(RequestScope request,
Object parameterObject, Sql sql) {
return getResultMap();
}

}

上面代码中的getSqlString方法可以根据自己系统select语句的复杂程度完善,这里给出的是最简单的实现。

使用上面的类即可由select语句生成count语句,下面是通过spring使用的代码:

BaseDaoiBatis.java

java 代码
//...
public abstract class BaseDaoiBatis extends SqlMapClientDaoSupport {

//...

protected long getObjectTotal(String selectQuery, Object parameterObject) {
prepareCountQuery(selectQuery);
//...
return (Long) getSqlMapClientTemplate().queryForObject(
CountStatementUtil.getCountStatementId(selectQuery),
parameterObject);
}

protected void prepareCountQuery(String selectQuery) {

String countQuery = CountStatementUtil.getCountStatementId(selectQuery);
if (logger.isDebugEnabled()) {
logger.debug("Convert " + selectQuery + " to " + countQuery);
}
SqlMapClient sqlMapClient = getSqlMapClientTemplate().getSqlMapClient();
if (sqlMapClient instanceof ExtendedSqlMapClient) {
SqlMapExecutorDelegate delegate = ((ExtendedSqlMapClient) sqlMapClient)
.getDelegate();
try {
delegate.getMappedStatement(countQuery);
} catch (SqlMapException e) {
delegate.addMappedStatement(CountStatementUtil
.createCountStatement(delegate
.getMappedStatement(selectQuery)));
}

}
}

//...
}
posted on 2008-05-03 00:31 礼物 阅读(4750) 评论(3)  编辑  收藏 所属分类: ibatis + spring

评论

# re: ibatis中由SELECT语句自动生成COUNT语句 2011-01-13 12:18 tylerLimin
Hi Terry.
按你上面写的那样可以实现分页条件中的 resultCount(记录数)统计吗?它们是共用一条查询语句?我试了试不行哦。不知你那里有没有好的建议没,有,请回复。多谢!  回复  更多评论
  

# re: ibatis中由SELECT语句自动生成COUNT语句 2011-01-17 13:44 礼物
可以实现count查询,原则上会生成两条sql语句,一条是count一条是具体分页的,这个代码已经写了很早了,你最好进行debug跟踪一下就可以知道了  回复  更多评论
  

# re: ibatis中由SELECT语句自动生成COUNT语句 2011-04-25 19:29 joliny
有没有通用的呀!!如果sql中包含子查询就不好用了  回复  更多评论