由于MyBatis没有相关的分页查询，因此抽取出hibernate的分页查询功能，通过过滤器，对本地sql方言进行区分，对sql语句进行拼接和封装
步骤分为：1、先查询出总数
2、通过拼接的sql再去查询相关信息
/**
* 带有分页信息的查询
* @param sqlMapId mybatis映射id
* @param pageRequest 分页请求参数信息
* @return
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public Page findForPage(String sqlMapId, PageRequest pageRequest) {
// 查询总数
pageRequest.getFilters().put(SelectCountSqlInterceptor.COUNT, null); // 设置是否需要将sql转换成总数查询sql
Number totalCount = (Number) findForObject(sqlMapId, pageRequest
.getFilters());
if (totalCount == null || totalCount.intValue() <= 0) {
return new Page(pageRequest, 0);
}
if(totalCount != null && totalCount.intValue() <= (pageRequest.getPageNumber()-1) * pageRequest.getPageSize()){
return new Page(pageRequest.getPageNumber(), pageRequest.getPageSize(), totalCount.intValue(), new ArrayList(0));
}
pageRequest.getFilters().remove(SelectCountSqlInterceptor.COUNT);
Map filters = new HashMap();
filters.putAll(pageRequest.getFilters());
Page page = new Page(pageRequest, totalCount.intValue());
List list = findForList(sqlMapId, filters, page.getFirstResult(), page.getPageSize());
page.setResult(list);
return page;
}
/**
* 分页请求信息
*/
public class PageRequest implements Serializable {
private static final long serialVersionUID = 9092186838918641382L;
/**
* 过滤参数
*/
private Map filters;
/**
* 页号码,页码从1开始
*/
private int pageNumber;
/**
* 分页大小
*/
private int pageSize;
}
/**
* 将查询SQL转成查询总数SQL<br>
*
* 配置文件内容:
*
* <pre>
* <plugins>
* <plugin interceptor="ewell.nis.common.ibatis.plugin.SelectCountSqlInterceptor"/>
* </plugins>
* </pre>
*/
@Intercepts( { @Signature(type = Executor.class, method = "query", args = {
MappedStatement.class, Object.class, RowBounds.class,
ResultHandler.class }) })
public class SelectCountSqlInterceptor implements Interceptor {
public static String COUNT = "_count";
private static int MAPPED_STATEMENT_INDEX = 0;
private static int PARAMETER_INDEX = 1;
@Override
public Object intercept(Invocation invocation) throws Throwable {
processCountSql(invocation.getArgs());
return invocation.proceed();
}
private void processCountSql(final Object[] queryArgs) {
if (queryArgs[PARAMETER_INDEX] instanceof Map) {
Map parameter = (Map) queryArgs[PARAMETER_INDEX];
if (parameter.containsKey(COUNT)) {
MappedStatement ms = (MappedStatement) queryArgs[MAPPED_STATEMENT_INDEX];
BoundSql boundSql = ms.getBoundSql(parameter);
String sql = ms.getBoundSql(parameter).getSql().trim();
BoundSql newBoundSql = new BoundSql(ms.getConfiguration(),
getCountSQL(sql), boundSql.getParameterMappings(),
boundSql.getParameterObject());
MappedStatement newMs = copyFromMappedStatement(ms,
new BoundSqlSqlSource(newBoundSql));
queryArgs[MAPPED_STATEMENT_INDEX] = newMs;
}
}
}
// see: MapperBuilderAssistant
private MappedStatement copyFromMappedStatement(MappedStatement ms,
SqlSource newSqlSource) {
Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms
.getId(), newSqlSource, ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
builder.keyProperty(getKeyProperty(ms.getKeyProperties()));
// setStatementTimeout()
builder.timeout(ms.getTimeout());
// setParameterMap()
builder.parameterMap(ms.getParameterMap());
// setStatementResultMap()
List<ResultMap> resultMaps = new ArrayList<ResultMap>();
String id = "-inline";
if (ms.getResultMaps() != null) {
id = ms.getResultMaps().get(0).getId() + "-inline";
}
ResultMap resultMap = new ResultMap.Builder(null, id, Long.class,
new ArrayList()).build();
resultMaps.add(resultMap);
builder.resultMaps(resultMaps);
builder.resultSetType(ms.getResultSetType());
// setStatementCache()
builder.cache(ms.getCache());
builder.flushCacheRequired(ms.isFlushCacheRequired());
builder.useCache(ms.isUseCache());
return builder.build();
}
private String getKeyProperty(String[] keyProperties) {
StringBuilder builder = new StringBuilder();
if (keyProperties != null && keyProperties.length > 0) {
int length = keyProperties.length;
for (int i = 0; i < length; i++) {
builder.append(keyProperties[i]);
if (i < length -1) {
builder.append(",");
}
}
return builder.toString();
}
return null;
}
private String getCountSQL(String sql) {
String lowerCaseSQL = sql.toLowerCase().replace("
", " ").replace(
" ", " ");
int index = lowerCaseSQL.indexOf(" order ");
if (index != -1) {
sql = sql.substring(0, index);
}
if (lowerCaseSQL.indexOf(" group ") != -1) {
return "SELECT COUNT(*) FROM (SELECT COUNT(*) AS COUNT_" + sql.substring(lowerCaseSQL.indexOf(" from ")) + ") TABLE_";
}
return "SELECT COUNT(*) AS COUNT_" + sql.substring(lowerCaseSQL.indexOf(" from "));
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
/**
* 为ibatis3提供基于方言(Dialect)的分页查询的插件<br>
*
* 将拦截Executor.query()方法实现分页方言的插入<br>
*
* 配置文件内容:
*
* <pre>
* <plugins>
* <plugin interceptor="ewell.nis.common.ibatis.plugin.OffsetLimitInterceptor">
* <property name="dialectClass" value="ewell.nis.common.ibatis.dialect.MySQLDialect"/>
* </plugin>
* </plugins>
* </pre>
*
*
*/
@Intercepts( { @Signature(type = Executor.class, method = "query", args = {
MappedStatement.class, Object.class, RowBounds.class,
ResultHandler.class }) })
public class OffsetLimitInterceptor implements Interceptor {
private static int MAPPED_STATEMENT_INDEX = 0;
private static int PARAMETER_INDEX = 1;
private static int ROWBOUNDS_INDEX = 2;
private Dialect dialect;
@Override
public Object intercept(Invocation invocation) throws Throwable {
processIntercept(invocation.getArgs());
return invocation.proceed();
}
void processIntercept(final Object[] queryArgs) {
// queryArgs = query(MappedStatement ms, Object parameter, RowBounds
// rowBounds, ResultHandler resultHandler)
MappedStatement ms = (MappedStatement) queryArgs[MAPPED_STATEMENT_INDEX];
Object parameter = queryArgs[PARAMETER_INDEX];
final RowBounds rowBounds = (RowBounds) queryArgs[ROWBOUNDS_INDEX];
int offset = rowBounds.getOffset();
int limit = rowBounds.getLimit();
if (dialect.supportsLimit()
&& (offset != RowBounds.NO_ROW_OFFSET || limit != RowBounds.NO_ROW_LIMIT)) {
BoundSql boundSql = ms.getBoundSql(parameter);
String sql = boundSql.getSql().trim();
if (dialect.supportsLimitOffset()) {
sql = dialect.getLimitString(sql, offset, limit);
offset = RowBounds.NO_ROW_OFFSET;
}
else {
sql = dialect.getLimitString(sql, 0, offset+limit);
}
limit = RowBounds.NO_ROW_LIMIT;
queryArgs[ROWBOUNDS_INDEX] = new RowBounds(offset, limit);
BoundSql newBoundSql = new BoundSql(ms.getConfiguration(), sql,
boundSql.getParameterMappings(), boundSql
.getParameterObject());
MappedStatement newMs = copyFromMappedStatement(ms,
new BoundSqlSqlSource(newBoundSql));
queryArgs[MAPPED_STATEMENT_INDEX] = newMs;
}
}
// see: MapperBuilderAssistant
private MappedStatement copyFromMappedStatement(MappedStatement ms,
SqlSource newSqlSource) {
Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms
.getId(), newSqlSource, ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
builder.keyProperty(getKeyProperty(ms.getKeyProperties()));
// setStatementTimeout()
builder.timeout(ms.getTimeout());
// setStatementResultMap()
builder.parameterMap(ms.getParameterMap());
// setStatementResultMap()
builder.resultMaps(ms.getResultMaps());
builder.resultSetType(ms.getResultSetType());
// setStatementCache()
builder.cache(ms.getCache());
builder.flushCacheRequired(ms.isFlushCacheRequired());
builder.useCache(ms.isUseCache());
return builder.build();
}
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
public void setProperties(Properties properties) {
String dialectClass = new PropertiesHelper(properties)
.getRequiredString("dialectClass");
try {
dialect = (Dialect) Class.forName(dialectClass).newInstance();
} catch (Exception e) {
throw new RuntimeException(
"cannot create dialect instance by dialectClass:"
+ dialectClass, e);
}
System.out.println(OffsetLimitInterceptor.class.getSimpleName()
+ ".dialect=" + dialectClass);
}
private String getKeyProperty(String[] keyProperties) {
StringBuilder builder = new StringBuilder();
if (keyProperties != null && keyProperties.length > 0) {
int length = keyProperties.length;
for (int i = 0; i < length; i++) {
builder.append(keyProperties[i]);
if (i < length -1) {
builder.append(",");
}
}
return builder.toString();
}
return null;
}
public static class BoundSqlSqlSource implements SqlSource {
private BoundSql boundSql;
public BoundSqlSqlSource(BoundSql boundSql) {
this.boundSql = boundSql;
}
public BoundSql getBoundSql(Object parameterObject) {
return boundSql;
}
}
}