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

深藏阁楼爱情的钟 2023-01-12 04:08 193阅读 0赞

下面的内容是基于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

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关详解MySQL查询语句：SELECT语句一网打尽

相关 mysql自动生成sql语句

相关 mysql中排查语句,关于mysql：如何从“ SELECT *”语句中排除列？

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

相关 mysql 原生语句count_重新认识MySQL中的COUNT语句

相关 ibatis mysql iterate_ibatis中动态语句的iterate标签

相关 mysql触发器中包含select语句

相关 mysql-select语句小结

相关 MySQL中SELECT语句简单使用

相关 ASP中获得Select Count语句返回值的方法