mybatis分页方式对比
ring todo mysql分页查询 语句 cau script app att
mybatis有两种分页方法(转自:http://blog.csdn.net/leozhou13/article/details/50394242)
1、内存分页,也就是假分页。本质是查出所有的数据然后根据游标的方式,截取需要的记录。如果数据量大,开销大和内存溢出。
使用方式:
利用自动生成的example类,加入mybatis的RowBounds类,在调用的接口中添加给类的参数
[java] view plain
copy
- @Override
- public List<UserVo> selectSelective(UserVo userVo) {
-
- List listVo = new ArrayList<UserVo>();
- UserPoExample example = new UserPoExample();
-
- RowBounds rowBounds = new RowBounds(0, 5);</strong>
- Criteria criteria = example.createCriteria();
- criteria.andUsernameEqualTo("123");
- criteria.andRoleEqualTo(userVo.getRole());
- example.setOrderByClause("userId desc");
- example.setStart(10);
- example.setLimit(10);
- UserPo userPo = new UserPo();
- userPo.setUsername("123");
- userPo.setRole(1);
- Page page = new Page<UserVo>();
-
- List listPo =null;
- try {
-
-
- listPo = userPoMapper.selectByExample(example,<strong>rowBounds</strong>);
-
- for(UserPo po:listPo){
- UserVo vo = new UserVo();
- BeanUtils.copyProperties(po, vo);
- listVo.add(vo);
- }
- } catch (Exception e) {
- logger.error(e);
- }
-
- return listVo;
- }
第二中是,真正的物理分页
在自动生成的example对象中,加入两个成员变量start、和limit
[java] view plain
copy
- public class UserPoExample {
- private int start;
-
- private int limit;
-
- public int getStart() {
- return start;
- }
-
- public void setStart(int start) {
- this.start = start;
- }
-
- public int getLimit() {
- return limit;
- }
-
- public void setLimit(int limit) {
- this.limit = limit;
- }
最后在对应的xml的方法中添加刚刚加入的条件,直接添加在自动生成的orderByClause后面
[html] view plain
copy
- <if test="orderByClause != null" >
- order by ${orderByClause}
- </if>
-
- <strong><if test="start !=0 or limit!=0">
- limit #{start},#{limit}</if><span style="font-family: Arial, Helvetica, sans-serif;"> </span></strong>
通过日志可以看到是真正的分页查询
还有一种是使用分页拦截器实现的
首先在spring-dao的sqlsession工厂里面配置拦截器
[html] view plain
copy
- <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
-
- <property name="dataSource" ref="dataSource" />
-
-
- <property name="typeAliasesPackage" value="ssm.po" />
-
-
- <property name="mapperLocations" value="classpath:ssm/mapper/*.xml" />
-
-
- <property name="plugins">
- <bean class="ssm.utils.MybatisSpringPageInterceptor"></bean>
- </property>
-
- </bean>
拦截器代码:
[java] view plain
copy
- package ssm.utils;
-
- import java.lang.reflect.Field;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.util.List;
- import java.util.Map;
- import java.util.Properties;
-
- import org.apache.ibatis.executor.Executor;
- import org.apache.ibatis.executor.parameter.ParameterHandler;
- import org.apache.ibatis.executor.statement.RoutingStatementHandler;
- import org.apache.ibatis.executor.statement.StatementHandler;
- import org.apache.ibatis.mapping.BoundSql;
- import org.apache.ibatis.mapping.MappedStatement;
- import org.apache.ibatis.mapping.ParameterMapping;
- 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.scripting.defaults.DefaultParameterHandler;
- import org.apache.ibatis.session.ResultHandler;
- import org.apache.ibatis.session.RowBounds;
- import org.slf4j.Logger;
- import org.slf4j.LoggerFactory;
-
- @Intercepts({ @Signature(method = "prepare", type = StatementHandler.class, args = { Connection.class }),
- @Signature(method = "query", type = Executor.class, args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class }) })
- public class MybatisSpringPageInterceptor implements Interceptor {
- private static final Logger log = LoggerFactory.getLogger(MybatisSpringPageInterceptor.class);
-
- public static final String MYSQL = "mysql";
- public static final String ORACLE = "oracle";
-
- protected String databaseType;
-
- @SuppressWarnings("rawtypes")
- protected ThreadLocal pageThreadLocal = new ThreadLocal<Page>();
-
- public String getDatabaseType() {
- return databaseType;
- }
-
- public void setDatabaseType(String databaseType) {
- if (!databaseType.equalsIgnoreCase(MYSQL) && !databaseType.equalsIgnoreCase(ORACLE)) {
- throw new PageNotSupportException("Page not support for the type of database, database type [" + databaseType + "]");
- }
- this.databaseType = databaseType;
- }
-
- @Override
- public Object plugin(Object target) {
- return Plugin.wrap(target, this);
- }
-
- @Override
- public void setProperties(Properties properties) {
- String databaseType = properties.getProperty("databaseType");
- if (databaseType != null) {
- setDatabaseType(databaseType);
- }
- }
-
- @Override
- @SuppressWarnings({ "unchecked", "rawtypes" })
- public Object intercept(Invocation invocation) throws Throwable {
- if (invocation.getTarget() instanceof StatementHandler) {
- Page page = pageThreadLocal.get();
- if (page == null) {
- return invocation.proceed();
- }
-
- RoutingStatementHandler handler = (RoutingStatementHandler) invocation.getTarget();
- StatementHandler delegate = (StatementHandler) ReflectUtil.getFieldValue(handler, "delegate");
- BoundSql boundSql = delegate.getBoundSql();
-
- Connection connection = (Connection) invocation.getArgs()[0];
- prepareAndCheckDatabaseType(connection);
-
- if (page.getTotalPage() > -1) {
- if (log.isTraceEnabled()) {
- log.trace("已经设置了总页数, 不需要再查询总数.");
- }
- } else {
- Object parameterObj = boundSql.getParameterObject();
- MappedStatement mappedStatement = (MappedStatement) ReflectUtil.getFieldValue(delegate, "mappedStatement");
- queryTotalRecord(page, parameterObj, mappedStatement, connection);
- }
-
- String sql = boundSql.getSql();
- String pageSql = buildPageSql(page, sql);
- if (log.isDebugEnabled()) {
- log.debug("分页时, 生成分页pageSql: " + pageSql);
- }
- ReflectUtil.setFieldValue(boundSql, "sql", pageSql);
-
- return invocation.proceed();
- } else {
-
- Page page = findPageObject(invocation.getArgs()[1]);
- if (page == null) {
- if (log.isTraceEnabled()) {
- log.trace("没有Page对象作为参数, 不是分页查询.");
- }
- return invocation.proceed();
- } else {
- if (log.isTraceEnabled()) {
- log.trace("检测到分页Page对象, 使用分页查询.");
- }
- }
-
- invocation.getArgs()[1] = extractRealParameterObject(invocation.getArgs()[1]);
-
- pageThreadLocal.set(page);
- try {
- Object resultObj = invocation.proceed();
- if (resultObj instanceof List) {
-
- page.setResults((List) resultObj);
- }
- return resultObj;
- } finally {
- pageThreadLocal.remove();
- }
- }
- }
-
- protected Page<?> findPageObject(Object parameterObj) {
- if (parameterObj instanceof Page<?>) {
- return (Page<?>) parameterObj;
- } else if (parameterObj instanceof Map) {
- for (Object val : ((Map<?, ?>) parameterObj).values()) {
- if (val instanceof Page<?>) {
- return (Page<?>) val;
- }
- }
- }
- return null;
- }
-
-
- protected Object extractRealParameterObject(Object parameterObj) {
- if (parameterObj instanceof Map<?, ?>) {
- Map<?, ?> parameterMap = (Map<?, ?>) parameterObj;
- if (parameterMap.size() == 2) {
- boolean springMapWithNoParamName = true;
- for (Object key : parameterMap.keySet()) {
- if (!(key instanceof String)) {
- springMapWithNoParamName = false;
- break;
- }
- String keyStr = (String) key;
- if (!"0".equals(keyStr) && !"1".equals(keyStr)) {
- springMapWithNoParamName = false;
- break;
- }
- }
- if (springMapWithNoParamName) {
- for (Object value : parameterMap.values()) {
- if (!(value instanceof Page<?>)) {
- return value;
- }
- }
- }
- }
- }
- return parameterObj;
- }
-
- protected void prepareAndCheckDatabaseType(Connection connection) throws SQLException {
- if (databaseType == null) {
- String productName = connection.getMetaData().getDatabaseProductName();
- if (log.isTraceEnabled()) {
- log.trace("Database productName: " + productName);
- }
- productName = productName.toLowerCase();
- if (productName.indexOf(MYSQL) != -1) {
- databaseType = MYSQL;
- } else if (productName.indexOf(ORACLE) != -1) {
- databaseType = ORACLE;
- } else {
- throw new PageNotSupportException("Page not support for the type of database, database product name [" + productName + "]");
- }
- if (log.isInfoEnabled()) {
- log.info("自动检测到的数据库类型为: " + databaseType);
- }
- }
- }
-
-
- protected String buildPageSql(Page<?> page, String sql) {
- if (MYSQL.equalsIgnoreCase(databaseType)) {
- return buildMysqlPageSql(page, sql);
- } else if (ORACLE.equalsIgnoreCase(databaseType)) {
- return buildOraclePageSql(page, sql);
- }
- return sql;
- }
-
-
- protected String buildMysqlPageSql(Page<?> page, String sql) {
-
- int offset = (page.getPageNo() - 1) * page.getPageSize();
- return new StringBuilder(sql).append(" limit ").append(offset).append(",").append(page.getPageSize()).toString();
- }
-
-
- protected String buildOraclePageSql(Page<?> page, String sql) {
-
- int offset = (page.getPageNo() - 1) * page.getPageSize() + 1;
- StringBuilder sb = new StringBuilder(sql);
- sb.insert(0, "select u.*, rownum r from (").append(") u where rownum < ").append(offset + page.getPageSize());
- sb.insert(0, "select * from (").append(") where r >= ").append(offset);
- return sb.toString();
- }
-
-
- protected void queryTotalRecord(Page page, Object parameterObject, MappedStatement mappedStatement, Connection connection) throws SQLException {
- BoundSql boundSql = mappedStatement.getBoundSql(page);
- String sql = boundSql.getSql();
- String countSql = this.buildCountSql(sql);
- if (log.isDebugEnabled()) {
- log.debug("分页时, 生成countSql: " + countSql);
- }
-
- List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
- BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSql, parameterMappings, parameterObject);
- ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, countBoundSql);
- PreparedStatement pstmt = null;
- ResultSet rs = null;
- try {
- pstmt = connection.prepareStatement(countSql);
- parameterHandler.setParameters(pstmt);
- rs = pstmt.executeQuery();
- if (rs.next()) {
- long totalRecord = rs.getLong(1);
- page.setTotalRecord(totalRecord);
- }
- } finally {
- <
来源: http://www.bubuko.com/infodetail-2019762.html