mysql ibatis count_ibatis中由SELECT语句自动生成COUNT语句

下面的内容是基于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 礼物 阅读(4382) 评论(3)  编辑  收藏 所属分类: ibatis + spring