ibatis 分页 实现 (物理分页)
来源:互联网 发布:上海巨人网络如何入职 编辑:程序博客网 时间:2024/05/17 07:54
参考http://blog.csdn.net/zhuangyan2004/archive/2007/02/01/1499915.aspx
加上自己的修改,支持分页查询,返回分页的具体信息,比如页数,当前页,结果集。
我是在SpringMvc+ibatis 实现。
其中加入一个PageInfo类,并修改了其他的4个类。附源码
package com.littleqworks.commons.db.ibatis;
import java.util.List;
public class PageInfo {
public static final int NUMBERS_PER_PAGE = 10;
// 一页显示的记录数
private int numPerPage=NUMBERS_PER_PAGE;
// 记录总数
private int totalRows;
// 总页数
private int totalPages;
// 当前页码
private int currentPage;
// 起始行数
private int startIndex;
// 结束行数
private int lastIndex;
// 结果集存放List
private List resultList;
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getNumPerPage() {
return numPerPage;
}
public void setNumPerPage(int numPerPage) {
this.numPerPage = numPerPage;
}
public List getResultList() {
return resultList;
}
public void setResultList(List resultList) {
this.resultList = resultList;
}
public int getTotalPages() {
setTotalPages();
return totalPages;
}
// 计算总页数
public void setTotalPages() {
if(totalRows % numPerPage == 0){
this.totalPages = totalRows / numPerPage;
}else{
this.totalPages= (totalRows / numPerPage) + 1;
}
}
public int getTotalRows() {
return totalRows;
}
public void setTotalRows(int totalRows) {
this.totalRows = totalRows;
}
public int getStartIndex() {
setStartIndex();
return startIndex;
}
public void setStartIndex() {
this.startIndex = (currentPage - 1) * numPerPage;
}
public int getLastIndex() {
setLastIndex();
return lastIndex;
}
// 计算结束时候的索引
public void setLastIndex() {
if( totalRows < numPerPage){
this.lastIndex = totalRows;
}else if((totalRows % numPerPage == 0)
|| (totalRows % numPerPage != 0 && currentPage < totalPages)){
this.lastIndex = currentPage * numPerPage;
}else if(totalRows % numPerPage != 0 && currentPage == totalPages){
// 最后一页
this.lastIndex = totalRows ;
}
}
}
//************************************************************************************
package com.littleqworks.commons.db.ibatis;
public interface Dialect {
public boolean supportsLimit();
public String getLimitString(String sql, boolean hasOffset);
public String getLimitString(String sql, int offset, int limit);
public String getCountSqlString(String sql);
}
//************************************************************************************
package com.littleqworks.commons.db.ibatis;
public class MySQLDialect implements Dialect {
protected static final String SQL_END_DELIMITER = ";";
public static final String RS_COLUMN = "nums";
public String getLimitString(String sql, boolean hasOffset) {
return new StringBuffer(sql.length() + 20).append(trim(sql)).append(
hasOffset ? " limit ?,?" : " limit ?")
.append(SQL_END_DELIMITER).toString();
}
public String getLimitString(String sql, int offset, int limit) {
sql = trim(sql);
StringBuffer sb = new StringBuffer(sql.length() + 20);
sb.append(sql);
if (offset > 0) {
sb.append(" limit ").append(offset).append(',').append(limit)
.append(SQL_END_DELIMITER);
} else {
sb.append(" limit ").append(limit).append(SQL_END_DELIMITER);
}
return sb.toString();
}
public String getCountSqlString(String sql){
sql = trim(sql);
StringBuffer sb = new StringBuffer(sql.length() + 10);
if(sql.toLowerCase().startsWith("select")){
int i=sql.indexOf(" ");
int j=sql.lastIndexOf("from");
sb.append(sql.subSequence(0, i));
sb.append(" ");
sb.append("count(*) as ");
sb.append(RS_COLUMN);
sb.append(" ");
sb.append(sql.subSequence(j, sql.length()));
}
return sb.toString();
}
public boolean supportsLimit() {
return true;
}
private String trim(String sql) {
sql = sql.trim();
if (sql.endsWith(SQL_END_DELIMITER)) {
sql = sql.substring(0, sql.length() - 1
- SQL_END_DELIMITER.length());
}
return sql;
}
}
import com.ibatis.sqlmap.engine.execution.SqlExecutor;
import com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient;
import com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate;
import com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback;
import com.ibatis.sqlmap.engine.scope.ErrorContext;
import com.ibatis.sqlmap.engine.scope.RequestScope;
import com.ibatis.sqlmap.engine.scope.SessionScope;
public class LimitSqlExecutor extends SqlExecutor {
protected final static Logger logger = LoggerFactory.getLogger(LimitSqlExecutor.class);
private Dialect dialect;
private int totalRowsCount;
private boolean enableLimit = true;
public Dialect getDialect() {
return dialect;
}
public void setDialect(Dialect dialect) {
this.dialect = dialect;
}
public boolean isEnableLimit() {
return enableLimit;
}
public void setEnableLimit(boolean enableLimit) {
this.enableLimit = enableLimit;
}
@Override
public void executeQuery(RequestScope request, Connection conn, String sql,
Object[] parameters, int skipResults, int maxResults,
RowHandlerCallback callback) throws SQLException {
if ((skipResults != NO_SKIPPED_RESULTS || maxResults != NO_MAXIMUM_RESULTS)
&& supportsLimit()) {
int rowsCount = 0;
String csql = dialect.getCountSqlString(sql);
if(parameters!=null){
rowsCount = executeQuery(request, parameters, conn, csql, callback);
setTotalRowsCount(rowsCount);
}
if(skipResults>=rowsCount){
skipResults=rowsCount-skipResults;
}
sql = dialect.getLimitString(sql, skipResults, maxResults);
if(logger.isDebugEnabled()){
logger.debug(sql);
}
skipResults = NO_SKIPPED_RESULTS;
maxResults = NO_MAXIMUM_RESULTS;
}
super.executeQuery(request, conn, sql, parameters, skipResults,
maxResults, callback);
}
private int executeQuery(RequestScope request, Object[] parameters, Connection conn, String sql,
RowHandlerCallback callback) {
int rowsCount = 0;
ErrorContext errorContext = request.getErrorContext();
errorContext.setActivity("executing query procedure");
errorContext.setObjectId(sql);
PreparedStatement ps = null;
ResultSet rs = null;
try {
errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
// Integer rsType = request.getStatement().getResultSetType();
ps = prepareStatement(request.getSession(), conn, sql);
if(parameters!=null){
for(int i=0;i<parameters.length;i++)
ps.setObject(i+1, parameters[i]);
}
errorContext.setMoreInfo("Check the parameters (set parameters failed).");
ps.execute();
errorContext.setMoreInfo("Check the results (failed to retrieve results).");
rs = ps.getResultSet();
// Begin ResultSet Handling
if(rs!=null){
while(rs.next()){
rowsCount=rs.getInt(MySQLDialect.RS_COLUMN);
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// ignore
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
// ignore
}
}
}
return rowsCount;
}
public boolean supportsLimit() {
if (enableLimit && dialect != null) {
return dialect.supportsLimit();
}
return false;
}
private static PreparedStatement prepareStatement(SessionScope session,
Connection conn, String sql) throws SQLException {
SqlMapExecutorDelegate delegate = ((ExtendedSqlMapClient) session
.getSqlMapExecutor()).getDelegate();
if (session.hasPreparedStatementFor(sql)) {
return session.getPreparedStatement((sql));
} else {
PreparedStatement ps = conn.prepareStatement(sql);
session.putPreparedStatement(delegate, sql, ps);
return ps;
}
}
public int getTotalRowsCount() {
return totalRowsCount;
}
public void setTotalRowsCount(int totalRowsCount) {
this.totalRowsCount = totalRowsCount;
}
}
//************************************************************************************
package com.littleqworks.commons.db.ibatis;
import java.sql.SQLException;
import java.util.List;
import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.engine.execution.SqlExecutor;
import com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient;
public abstract class BaseDaoiBatis extends SqlMapClientDaoSupport {
private SqlExecutor sqlExecutor;
public SqlExecutor getSqlExecutor() {
return sqlExecutor;
}
public void setSqlExecutor(SqlExecutor sqlExecutor) {
this.sqlExecutor = sqlExecutor;
}
public void setEnableLimit(boolean enableLimit) {
if (sqlExecutor instanceof LimitSqlExecutor) {
((LimitSqlExecutor) sqlExecutor).setEnableLimit(enableLimit);
}
}
public void initialize() throws Exception {
if (sqlExecutor != null) {
SqlMapClient sqlMapClient = getSqlMapClientTemplate()
.getSqlMapClient();
if (sqlMapClient instanceof ExtendedSqlMapClient) {
ReflectUtil.setFieldValue(((ExtendedSqlMapClient) sqlMapClient)
.getDelegate(), "sqlExecutor", SqlExecutor.class,
sqlExecutor);
}
}
}
public PageInfo queryForPage(final String selectStatement,PageInfo page){
try {
List list=getSqlMapClientTemplate().getSqlMapClient().queryForList(selectStatement,
null,
page.getStartIndex(), page.getNumPerPage());
page.setTotalRows(((LimitSqlExecutor)getSqlExecutor()).getTotalRowsCount());
page.setResultList(list);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return page;
}
}
可以这样调用改方法
PageInfo resultPage=baseDao.queryForPage("sqlId",object,page);
方法
public PageInfo queryForPage(String selectStatement,Object param,PageInfo page){
try {
List list=super.getSqlClient().queryForList(selectStatement,
param,
page.getStartIndex(), page.getNumPerPage());
page.setTotalRows(super.getSqlExecutor().getTotalRowsCount());
page.setResultList(list);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return page;
}
- ibatis 分页 实现 (物理分页)
- IBatis物理分页实现
- 修改ibatis源码实现物理分页
- Ibatis.net物理分页
- ibatis的物理分页
- ibatis物理分页
- 修改 ibatis 分页机制(ORACLE物理分页)
- ibatis 实现 物理级别的 分页 兼容多种数据库(转载)
- Mybatis实现物理分页
- Mybatis 实现物理分页
- Mybatis实现物理分页
- mybatis实现物理分页
- mybatis实现物理分页
- ibatis实现分页查询
- IBatis.Net 之路进阶 --- 物理分页
- 实现对ibatis原生SQL的拦截改造-可以实现物理分页等(咋个办呢 zgbn)
- mybatis物理分页的实现
- [Java][MyBatis]物理分页实现
- 最简单的页面自动刷新&跳转
- android传感器摇一摇功能
- 从塞班手机中的GPS模块获取高度和经纬度信息
- [ZT]50个Windows 8 应用小技巧集锦
- .NET 里操作Excel 出现有些列的数据取不到的问题
- ibatis 分页 实现 (物理分页)
- HttpWebResponse判断一个网页是否出现404错误
- Simple Factory (简单工厂模式)
- JSP传递参数至另一个JSP页面时中文乱码问题的解决
- SQL 收藏
- Mp3播放器(C写的)
- ORA-12899: value too large for column "SOAU"."SJQY_QTSBSPEC"."PROPERTY_6" (actual: 566, maximum: 500
- unix/linux文件系统结构标准
- Android自定义组合控件