/**
* 模糊查询(分页)
*
* @project apqp
* @author liud JIRA:APQP-23
* @date 2013-04-16
* @param flow
* @param page
* 分页参数包装器
* @return pageData 分页结果集
* @history
*/
@Override
public PageData queryFlow(Attach attach, Pagination page) {
if (page.isReadTotal())
page.setTotal(this.selectOneByTotalCount("flow.queryList", attach));
RowBounds rowbounds = new RowBounds(page.getStart(), page.getLimit());
List result = this.sqlSession.selectList("flow.queryList",
attach, rowbounds);
PageData pageData = new PageData();
pageData.setResult(result);
pageData.setPagination(page);
return pageData;
}
/**
* 获得分页的总条数
*
* @author liud
* @date 2013-2-18
* @param s
* mybatis对应的命名空间
* @param obj
* 参数
* @return
*/
protected int selectOneByTotalCount(String s, Object obj) {
final BaseEntity baseEntity = new BaseEntity();
RowBounds rowbounds = new RowBounds(-1, -1);
this.sqlSession.select(s, obj, rowbounds, new ResultHandler() {
@Override
public void handleResult(ResultContext context) {
Object object = context.getResultObject();
if (object != null) {
BaseEntity base = (BaseEntity) object;
baseEntity.setTotal(base.getTotal());
}
}
});
return baseEntity.getTotal();
}
-- mybatis.xml flow.queryList
parameterType="com.eman.flow.entity.Attach">
SELECT f1.fileID
, f1.fileName
, f1.fileType
, f1.fileSize
, f1.uploadUser
, f1.uploadTime
, f2.userName uploadUserName
FROM
t_attachmentOfAPQPFlowPhase p INNER
JOIN t_attachment f1
ON p.fileID =
f1.fileID
INNER JOIN t_user f2 ON f1.uploadUser =
f2.userID
WHERE
p.phaseID = #{phaseID}
order by f1.uploadTime desc
-- mybatis.config.xml
/p>
"http://mybatis.org/dtd/mybatis-3-config.dtd">
package com.eman.core.interceptor;
import java.sql.Connection;
import java.util.Properties;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.eman.core.dialect.Dialect;
import com.eman.core.dialect.SQLServer2005Dialect;
import com.eman.core.dialect.SQLServer2008Dialect;
/**
* 拦截mybatis执行sql,以用于转换分页sql
*
* @author liud
*
*/
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })
public class PaginationInterceptor implements Interceptor {
private static final Logger log = LoggerFactory.getLogger("controller");
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler sh = (StatementHandler) invocation.getTarget();
MetaObject mo = MetaObject.forObject(sh);
RowBounds rb = (RowBounds) mo.getValue("delegate.rowBounds");
if (rb == RowBounds.DEFAULT
|| (rb.getLimit() <= 0 && rb.getLimit() != -1)
|| rb.getLimit() > RowBounds.NO_ROW_LIMIT)
return invocation.proceed();
Configuration conf = (Configuration) mo
.getValue("delegate.configuration");
Dialect dialect = this.invokeDialect(conf.getVariables()
.getProperty("jdbc.db.type").toUpperCase());
if (dialect == null)
return invocation.proceed();
String bSql = (String) mo.getValue("delegate.boundSql.sql");
if (rb.getLimit() == -1 && rb.getOffset() == -1)
bSql = dialect.getTotalString(bSql);
else
bSql = dialect.getLimitString(bSql, rb.getOffset(), rb.getLimit());
mo.setValue("delegate.boundSql.sql", bSql);
mo.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);
mo.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
/**
* 通过配置获得sql数据库对象
*
* @author liud
* @date 2013-2-18
* @param param
* @return
*/
private Dialect invokeDialect(String param) {
Dialect.Type type = null;
try {
type = Dialect.Type.valueOf(param);
} catch (Exception e) {
log.debug(
"the value of the dialect property in mybatis-config.xml is not defined:{}",
param);
}
Dialect dialect = null;
switch (type) {
case SQLSERVER:
break;
case SQLSERVER2005:
dialect = new SQLServer2005Dialect();
break;
case SQLSERVER2008:
dialect = new SQLServer2008Dialect();
break;
case ORACLE9I:
break;
case ORACLE10G:
break;
case ORACLE11G:
break;
case MYSQL5:
break;
}
return dialect;
}
}
package com.eman.core.dialect;
/**
* sql2008数据库的实现
*
* @author liud
*
*/
public class SQLServer2008Dialect implements Dialect {
private static final String SELECT = "select";
private static final String FROM = "from";
private static final String DISTINCT = "distinct";
@Override
public String getTotalString(String sql) {
StringBuilder sb = new StringBuilder(sql.trim().toLowerCase());
int orderbyIndex = sb.indexOf("order by");
if (orderbyIndex != -1) {
sb.delete(orderbyIndex, sb.length());
}
sb.insert(0, "WITH query AS (").append(
") SELECT count(*) total FROM query ");
return sb.toString();
}
@Override
public String getLimitString(String sql, int offset, int limit) {
if (offset > 1 || limit > 1)
return getLimitString(sql, offset, limit, true);
return sql;
}
/**
* Add a LIMIT clause to the given SQL SELECT (HHH-2655: ROW_NUMBER for
* Paging)
*
* The LIMIT SQL will look like:
*
*
* WITH query AS (
* SELECT ROW_NUMBER() OVER (ORDER BY orderby) as __liud_row_nr__,
* original_query_without_orderby
* )
* SELECT * FROM query WHERE __liud_row_nr__ BEETWIN offset AND offset + last
*
*
*
* @param querySqlString
* The SQL statement to base the limit query off of.
* @param offset
* Offset of the first row to be returned by the query
* (zero-based)
* @param limit
* Maximum number of rows to be returned by the query
*
* @return A new SQL statement with the LIMIT clause applied.
*/
private String getLimitString(String querySqlString, int offset, int limit,
boolean hasOffset) {
StringBuilder sb = new StringBuilder(querySqlString.trim()
.toLowerCase());
int orderByIndex = sb.indexOf("order by");
CharSequence orderby = orderByIndex > 0 ? sb.subSequence(orderByIndex,
sb.length()) : "ORDER BY CURRENT_TIMESTAMP";
// Delete the order by clause at the end of the query
if (orderByIndex > 0) {
sb.delete(orderByIndex, orderByIndex + orderby.length());
}
// HHH-5715 bug fix
replaceDistinctWithGroupBy(sb);
insertRowNumberFunction(sb, orderby);
// Wrap the query within a with statement:
sb.insert(0, "WITH query AS (").append(") SELECT * FROM query ");
sb.append("WHERE __liud_row_nr__ BETWEEN ").append(offset + 1)
.append(" AND ").append(offset + limit);
return sb.toString();
}
/**
* Right after the select statement of a given query we must place the
* row_number function
*
* @param sql
* the initial sql query without the order by clause
* @param orderby
* the order by clause of the query
*/
protected static void insertRowNumberFunction(StringBuilder sql,
CharSequence orderby) {
// Find the end of the select statement
int selectEndIndex = sql.indexOf(SELECT) + SELECT.length();
// Insert after the select statement the row_number() function:
sql.insert(selectEndIndex, " ROW_NUMBER() OVER (" + orderby
+ ") as __liud_row_nr__,");
}
/**
* Utility method that checks if the given sql query is a select distinct
* one and if so replaces the distinct select with an equivalent simple
* select with a group by clause. See
* {@link SQLServer2005DialectTestCase#testReplaceDistinctWithGroupBy()}
*
* @param sql
* an sql query
*/
protected static void replaceDistinctWithGroupBy(StringBuilder sql) {
int distinctIndex = sql.indexOf(DISTINCT);
if (distinctIndex > 0) {
sql.delete(distinctIndex, distinctIndex + DISTINCT.length() + 1);
sql.append(" group by").append(getSelectFieldsWithoutAliases(sql));
}
}
/**
* This utility method searches the given sql query for the fields of the
* select statement and returns them without the aliases. See
* {@link SQLServer2005DialectTestCase#testGetSelectFieldsWithoutAliases()}
*
* @param an
* sql query
* @return the fields of the select statement without their alias
*/
protected static CharSequence getSelectFieldsWithoutAliases(
StringBuilder sql) {
String select = sql.substring(sql.indexOf(SELECT) + SELECT.length(),
sql.indexOf(FROM));
// Strip the as clauses
return stripAliases(select);
}
/**
* Utility method that strips the aliases. See
* {@link SQLServer2005DialectTestCase#testStripAliases()}
*
* @param a
* string to replace the as statements
* @return a string without the as statements
*/
protected static String stripAliases(String str) {
return str.replaceAll("\\sas[^,]+(,?)", "$1");
}
}
以上是集成了mybatis的分页功能,只需要调用第一方法即可以实现。实现方式采用了hibernate分页方式。
2013年6月09日 14:57