long mode 分页_springmvc mybatis怎么实现分页查询

展开全部

1.封装分32313133353236313431303231363533e4b893e5b19e31333361306338页Page类

package com.framework.common.page.impl;

import java.io.Serializable;

import com.framework.common.page.IPage;

/**

*

*

*

*/

public abstract class BasePage implements IPage, Serializable {

/**

*

*/

private static final long serialVersionUID = -3623448612757790359L;

public static int DEFAULT_PAGE_SIZE = 20;

private int pageSize = DEFAULT_PAGE_SIZE;

private int currentResult;

private int totalPage;

private int currentPage = 1;

private int totalCount = -1;

public BasePage(int currentPage, int pageSize, int totalCount) {

this.currentPage = currentPage;

this.pageSize = pageSize;

this.totalCount = totalCount;

}

public int getTotalCount() {

return this.totalCount;

}

public void setTotalCount(int totalCount) {

if (totalCount < 0) {

this.totalCount = 0;

return;

}

this.totalCount = totalCount;

}

public BasePage() {

}

public int getFirstResult() {

return (this.currentPage - 1) * this.pageSize;

}

public void setPageSize(int pageSize) {

if (pageSize < 0) {

this.pageSize = DEFAULT_PAGE_SIZE;

return;

}

this.pageSize = pageSize;

}

public int getTotalPage() {

if (this.totalPage <= 0) {

this.totalPage = (this.totalCount / this.pageSize);

if ((this.totalPage == 0) || (this.totalCount % this.pageSize != 0)) {

this.totalPage += 1;

}

}

return this.totalPage;

}

public int getPageSize() {

return this.pageSize;

}

public void setPageNo(int currentPage) {

this.currentPage = currentPage;

}

public int getPageNo() {

return this.currentPage;

}

public boolean isFirstPage() {

return this.currentPage <= 1;

}

public boolean isLastPage() {

return this.currentPage >= getTotalPage();

}

public int getNextPage() {

if (isLastPage()) {

return this.currentPage;

}

return this.currentPage + 1;

}

public int getCurrentResult() {

this.currentResult = ((getPageNo() - 1) * getPageSize());

if (this.currentResult < 0) {

this.currentResult = 0;

}

return this.currentResult;

}

public int getPrePage() {

if (isFirstPage()) {

return this.currentPage;

}

return this.currentPage - 1;

}

}

package com.framework.common.page.impl;

import java.util.List;

/**

*

*

*

*/

public class Page extends BasePage {

/**

*

*/

private static final long serialVersionUID = -970177928709377315L;

public static ThreadLocal threadLocal = new ThreadLocal();

private List> data;

public Page() {

}

public Page(int currentPage, int pageSize, int totalCount) {

super(currentPage, pageSize, totalCount);

}

public Page(int currentPage, int pageSize, int totalCount, List> data) {

super(currentPage, pageSize, totalCount);

this.data = data;

}

public List> getData() {

return data;

}

public void setData(List> data) {

this.data = data;

}

}

2.封装分页插件

package com.framework.common.page.plugin;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import java.util.Properties;

import javax.xml.bind.PropertyException;

import org.apache.commons.lang3.StringUtils;

import org.apache.ibatis.executor.ErrorContext;

import org.apache.ibatis.executor.ExecutorException;

import org.apache.ibatis.executor.statement.BaseStatementHandler;

import org.apache.ibatis.executor.statement.RoutingStatementHandler;

import org.apache.ibatis.mapping.BoundSql;

import org.apache.ibatis.mapping.MappedStatement;

import org.apache.ibatis.mapping.ParameterMapping;

import org.apache.ibatis.mapping.ParameterMode;

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.reflection.MetaObject;

import org.apache.ibatis.reflection.property.PropertyTokenizer;

import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;

import org.apache.ibatis.session.Configuration;

import org.apache.ibatis.type.TypeHandler;

import org.apache.ibatis.type.TypeHandlerRegistry;

import com.framework.common.page.impl.Page;

import com.framework.common.utils.ReflectUtil;

/**

*

*

*

*/

@Intercepts({ @org.apache.ibatis.plugin.Signature(type = org.apache.ibatis.executor.statement.StatementHandler.class, method = "prepare", args = { Connection.class }) })

public class PagePlugin implements Interceptor {

private String dialect = "";

private String pageSqlId = "";

@Override

public Object intercept(Invocation invocation) throws Throwable {

if (invocation.getTarget() instanceof RoutingStatementHandler) {

BaseStatementHandler delegate = (BaseStatementHandler) ReflectUtil

.getValueByFieldName(

(RoutingStatementHandler) invocation.getTarget(),

"delegate");

MappedStatement mappedStatement = (MappedStatement) ReflectUtil

.getValueByFieldName(delegate,

"mappedStatement");

Page page = Page.threadLocal.get();

if (page == null) {

page = new Page();

Page.threadLocal.set(page);

}

if (mappedStatement.getId().matches(".*(" + this.pageSqlId + ")$") && page.getPageSize() > 0) {

BoundSql boundSql = delegate.getBoundSql();

Object parameterObject = boundSql.getParameterObject();

String sql = boundSql.getSql();

String countSqlId = mappedStatement.getId().replaceAll(pageSqlId, "Count");

MappedStatement countMappedStatement = null;

if (mappedStatement.getConfiguration().hasStatement(countSqlId)) {

countMappedStatement = mappedStatement.getConfiguration().getMappedStatement(countSqlId);

}

String countSql = null;

if (countMappedStatement != null) {

countSql = countMappedStatement.getBoundSql(parameterObject).getSql();

} else {

countSql = "SELECT COUNT(1) FROM (" + sql + ") T_COUNT";

}

int totalCount = 0;

PreparedStatement countStmt = null;

ResultSet resultSet = null;

try {

Connection connection = (Connection) invocation.getArgs()[0];

countStmt = connection.prepareStatement(countSql);

BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject);

setParameters(countStmt, mappedStatement, countBoundSql, parameterObject);

resultSet = countStmt.executeQuery();

if(resultSet.next()) {

totalCount = resultSet.getInt(1);

}

} catch (Exception e) {

throw e;

} finally {

try {

if (resultSet != null) {

resultSet.close();

}

} finally {

if (countStmt != null) {

countStmt.close();

}

}

}

page.setTotalCount(totalCount);

ReflectUtil.setValueByFieldName(boundSql, "sql", generatePageSql(sql,page));

}

}

return invocation.proceed();

}

/**

* 对SQL参数(?)设值,参考org.apache.ibatis.executor.parameter.DefaultParameterHandler

* @param ps

* @param mappedStatement

* @param boundSql

* @param parameterObject

* @throws SQLException

*/

private void setParameters(PreparedStatement ps,MappedStatement mappedStatement,BoundSql boundSql,Object parameterObject) throws SQLException {

ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());

List parameterMappings = boundSql.getParameterMappings();

if (parameterMappings != null) {

Configuration configuration = mappedStatement.getConfiguration();

TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();

MetaObject metaObject = parameterObject == null ? null: configuration.newMetaObject(parameterObject);

for (int i = 0; i < parameterMappings.size(); i++) {

ParameterMapping parameterMapping = parameterMappings.get(i);

if (parameterMapping.getMode() != ParameterMode.OUT) {

Object value;

String propertyName = parameterMapping.getProperty();

PropertyTokenizer prop = new PropertyTokenizer(propertyName);

if (parameterObject == null) {

value = null;

} else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {

value = parameterObject;

} else if (boundSql.hasAdditionalParameter(propertyName)) {

value = boundSql.getAdditionalParameter(propertyName);

} else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX)&& boundSql.hasAdditionalParameter(prop.getName())) {

value = boundSql.getAdditionalParameter(prop.getName());

if (value != null) {

value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length()));

}

} else {

value = metaObject == null ? null : metaObject.getValue(propertyName);

}

TypeHandler typeHandler = parameterMapping.getTypeHandler();

if (typeHandler == null) {

throw new ExecutorException("There was no TypeHandler found for parameter "+ propertyName + " of statement "+ mappedStatement.getId());

}

typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());

}

}

}

}

/**

* 根据数据库方言，生成特定的分页sql

* @param sql

* @param page

* @return

*/

private String generatePageSql(String sql,Page page){

if(page!=null && StringUtils.isNotBlank(dialect)){

StringBuffer pageSql = new StringBuffer();

if("mysql".equals(dialect)){

pageSql.append(sql);

pageSql.append(" LIMIT "+page.getCurrentResult()+","+page.getPageSize());

}else if("oracle".equals(dialect)){

pageSql.append("SELECT * FROM (SELECT TMP_TB.*,ROWNUM ROW_ID FROM (");

pageSql.append(sql);

pageSql.append(") AS TMP_TB WHERE ROWNUM <= ");

pageSql.append(page.getCurrentResult()+page.getPageSize());

pageSql.append(") WHERE ROW_ID > ");

pageSql.append(page.getCurrentResult());

}

return pageSql.toString();

}else{

return sql;

}

}

@Override

public Object plugin(Object target) {

return Plugin.wrap(target, this);

}

@Override

public void setProperties(Properties properties) {

try {

if (StringUtils.isEmpty(this.dialect = properties

.getProperty("dialect"))) {

throw new PropertyException("dialect property is not found!");

}

if (StringUtils.isEmpty(this.pageSqlId = properties

.getProperty("pageSqlId"))) {

throw new PropertyException("pageSqlId property is not found!");

}

} catch (PropertyException e) {

e.printStackTrace();

}

}

}

附上出处链接：http://www.jb51.net/article/71829.htm

本回答由提问者推荐

已赞过

已踩过<

你对这个回答的评价是？

评论

收起