mybatis框架下物理分页的实现（整个工程采用的是springmvc、spring、mybatis框架，数据库是mysql数据库）

缺乏、安全感 2022-07-14 00:42 21阅读 0赞

整个工程采用的是springmvc、spring、mybatis框架，数据库是mysql数据库，本工程采用的是拦截器机制实现物理分页。

**因为文章最后会有完整的工程资源文件，这里就不把详细代码贴出来了，只是把拦截器和相关的配置文件的代码跟大家详细探讨下。**

###     (一）关于分页拦截器的简单理解 ###

首先，要开发MyBatis的插件需要实现org.apache.ibatis.plugin.Interceptor接口，这个接口将会要求实现几个方法：intercept（）、plugin（）及setProperties（），intercept方法是开发人员所要执行的操作，plugin是将你插件放入到MyBatis的插件集合中去，而setProperties这是在你配置你插件的时候将plugins/plugin/properties的值设置到该插件中。  
    该方法的第一句话就是获得Intercepts注解，接下来将获得在Intercepts里面的参数@Signature注解内容，在该注解中包含三个参数，分别是type，method，args。Type指定要拦截的类对象，method是指明要拦截该类的哪个方法，第三个是指明要拦截的方法参数集合。在Intercepts中可以配置多个@Signature。那么便对这写值进行遍历，已获得对应的type、method以及args。最终是获得一个HashMap对象，这些对象里面的键是类对象，而值是指定的类中方法对象。执行该端程序之后，更具target的classLoader和接口，来创建一个代理，并且，InvocationHandler是创建一个新的Plugin对象，同时将target，interceptor以及signatureMap传递给Plugin对象，当然，这里的Plugin也实现了Invocation接口。那么target对象所有的方法调用都会触发Plugin中的invoke方法，那么这里将执行开发者所有插入的操作。

另外对拦截器类里面几个关键的类做出解释：

（1）BoundSql类 ，封装mybatis最终产生sql的类，包括sql语句，参数，参数源数据等。  
（2）MappedStatement类，MappedStatement类在Mybatis框架中用于表示XML文件中一个sql语句节点，即一个<select />、<update />或者<insert />标签。Mybatis框架在初始化阶段会对XML配置文件进行读取，将其中的sql语句节点对象化为一个个MappedStatement对象。

总结，本拦截器实现的目标就是在进行数据库查询操作之前，从配置文件读出相应的sql语句，将相应的参数拼接到其中，然后再进行查询。当然在拼接sql语句之前，先查询了一下数据库中相应记录的总数。

### （二）拦截器类PageIntercepter.java： ###

package cn.zyy.paging.intercepter;
    
    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 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.mapping.SqlSource;
    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.session.Configuration;
    import org.apache.ibatis.session.RowBounds;
    import org.apache.ibatis.type.TypeHandler;
    import org.apache.ibatis.type.TypeHandlerRegistry;
    
    import cn.zyy.paging.vo.PageObject;
    
    @Intercepts({@org.apache.ibatis.plugin.Signature(method="query", type=org.apache.ibatis.executor.Executor.class, args={MappedStatement.class, Object.class, RowBounds.class, org.apache.ibatis.session.ResultHandler.class})})
    public class PageIntercepter implements Interceptor{
    
    	@Override
    	public Object intercept(Invocation invocation) throws Throwable {
    		MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
    		Object object = invocation.getArgs()[1];
    		if(object instanceof PageObject){
    			
    			PageObject pageObject = (PageObject) object;
    			BoundSql boundSql = mappedStatement.getBoundSql(object);
    			String sql = boundSql.getSql();
    			
    			int count = getCount(mappedStatement,boundSql);
    			pageObject.setCount(count);
    			int pages = (pageObject.getCount()+pageObject.getNumber()-1)/pageObject.getNumber();
    			pageObject.setPages(pages>0?pages:1);
    			
    			int offset = (pageObject.getPage() - 1) * pageObject.getNumber();
    			int limit = pageObject.getNumber();
    			String pageSql = pageSql(sql, offset, limit);
    			BoundSql pageBoundSql = new BoundSql(mappedStatement.getConfiguration(), pageSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
    			MappedStatement pageMappedStatement = pageMappedStatement(mappedStatement, new PageSqlSource(pageBoundSql));
    			invocation.getArgs()[0] = pageMappedStatement;
    		    invocation.getArgs()[2] = RowBounds.DEFAULT;
    		}
    		return invocation.proceed();
    	}
    
    	@Override
    	public Object plugin(Object object) {
    		// TODO Auto-generated method stub
    		return Plugin.wrap(object, this);
    	}
    
    	@Override
    	public void setProperties(Properties properties) {
    		// TODO Auto-generated method stub
    		
    	}
    
    	private int getCount(MappedStatement mappedStatement, BoundSql boundSql) throws SQLException {
    		
    		Connection connection = null;
    		PreparedStatement ps = null;
    		ResultSet rs = null;
    		
    		try {
    			String countSql = countSql(boundSql.getSql());
    			connection = mappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection();
    			ps = connection.prepareStatement(countSql);
    			BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
    			setCountParameters(ps, mappedStatement, countBoundSql);
    			rs = ps.executeQuery();
    			int count = 0;
    			 if (rs.next())
    			  {
    			    count = rs.getInt(1);
    			  }
    			return count;
    		} catch (Exception e) {
    			return 1000;
    		}finally{
    			 try {
    		        rs.close();
    		      } catch (Exception localException4) {
    		      }
    		      try {
    		        ps.close();
    		      } catch (Exception localException5) {
    		      }
    		      try {
    		        connection.close();
    		      }
    		      catch (Exception localException6) {
    		      }
    		}
    	}
    	
    	private static String countSql(String sql){
    		sql = sql.toUpperCase();
    		StringBuffer countSql = new StringBuffer();
    		countSql.append("SELECT COUNT(1) FROM (");
    		countSql.append(sql.substring(0, sql.indexOf("ORDER BY")==-1?sql.length():sql.indexOf("ORDER BY")-1));
    		countSql.append(") PAY_PAGE_T");
    		return countSql.toString();
    	}
    	
    	private static void setCountParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql) throws SQLException {
    		List<ParameterMapping> parameterMappingList = boundSql.getParameterMappings();
    		if (parameterMappingList != null)
    	    {
    	      Configuration configuration = mappedStatement.getConfiguration();
    	      TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
    	      Object parameterObject = boundSql.getParameterObject();
    	      MetaObject metaObject = parameterObject == null ? null : configuration.newMetaObject(parameterObject);
    
    	      int n = 1;
    	      for (ParameterMapping parameterMapping : parameterMappingList)
    	      {
    	        if ((parameterMapping.getMode() == ParameterMode.IN) || (parameterMapping.getMode() == ParameterMode.INOUT))
    	        {
    	          String property = parameterMapping.getProperty();
    	          Object value = null;
    	          if (parameterObject != null)
    	          {
    	            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass()))
    	            {
    	              value = parameterObject;
    	            }
    	            else
    	            {
    	              value = metaObject == null ? null : metaObject.getValue(property);
    	            }
    	          }
    
    	          TypeHandler typeHandler = parameterMapping.getTypeHandler();
    	          typeHandler.setParameter(ps, n, value, parameterMapping.getJdbcType());
    	        }
    
    	        n++;
    	      }
    	    }
    	}
    	
    	private String pageSql(String sql, int offset, int limit) {
    	    sql = sql.toUpperCase();
    	    StringBuffer pageSql = new StringBuffer();
    	    pageSql.append(sql);
    	    pageSql.append(" LIMIT ");
    	    pageSql.append(offset);
    	    pageSql.append(", ");
    	    pageSql.append(limit);
    	    return pageSql.toString();
    	  }
    	
    	private MappedStatement pageMappedStatement(MappedStatement mappedStatement, SqlSource sqlSource)
    	  {
    	    MappedStatement.Builder builder = new MappedStatement.Builder(
    	      mappedStatement.getConfiguration(), 
    	      mappedStatement.getId(), 
    	      sqlSource, 
    	      mappedStatement.getSqlCommandType());
    
    	    builder.resource(mappedStatement.getResource());
    	    builder.fetchSize(mappedStatement.getFetchSize());
    	    builder.statementType(mappedStatement.getStatementType());
    	    builder.keyGenerator(mappedStatement.getKeyGenerator());
    	    builder.timeout(mappedStatement.getTimeout());
    	    builder.parameterMap(mappedStatement.getParameterMap());
    	    builder.resultMaps(mappedStatement.getResultMaps());
    	    builder.cache(mappedStatement.getCache());
    	    builder.resultSetType(mappedStatement.getResultSetType());
    	    builder.flushCacheRequired(mappedStatement.isFlushCacheRequired());
    	    builder.useCache(mappedStatement.isUseCache());
    	    builder.resultOrdered(mappedStatement.isResultOrdered());
    	    builder.databaseId(mappedStatement.getDatabaseId());
    	    builder.lang(mappedStatement.getLang());
    	    if (mappedStatement.getKeyProperties() != null)
    	    {
    	      for (String keyProperty : mappedStatement.getKeyProperties())
    	      {
    	        builder.keyProperty(keyProperty);
    	      }
    	    }
    	    if (mappedStatement.getKeyColumns() != null)
    	    {
    	      for (String keyColumn : mappedStatement.getKeyColumns())
    	      {
    	        builder.keyColumn(keyColumn);
    	      }
    	    }
    
    	    return builder.build();
    	  }
    	
    	 public static class PageSqlSource implements SqlSource {
    	    private BoundSql boundSql;
    
    	    public PageSqlSource(BoundSql boundSql) {
    	      this.boundSql = boundSql;
    	    }
    
    	    public BoundSql getBoundSql(Object parameterObject)
    	    {
    	      return this.boundSql;
    	    }
    	  }
    
    }

### （三）spring配置文件和mybatis配置文件 ###

mybatis配置文件中主要是配置相关的vo类和拦截器

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration PUBLIC 
        "-//mybatis.org//DTD Config 3.0//EN" 
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
        
    <configuration>
        <typeAliases>
            <typeAlias alias="page" type="cn.zyy.paging.vo.PageObject"/>
        </typeAliases>
    	<plugins>
    	   <plugin interceptor="cn.zyy.paging.intercepter.PageIntercepter"/>
    	</plugins>
    </configuration>

spring的配置文件中主要就是实现mybatis和spring框架的整合

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
    	xmlns:tx="http://www.springframework.org/schema/tx" xmlns:mvc="http://www.springframework.org/schema/mvc"
    	xmlns:aop="http://www.springframework.org/schema/aop"
    	xsi:schemaLocation="
    		http://www.springframework.org/schema/aop 
    		http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
    		http://www.springframework.org/schema/mvc 
    		http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd
    		http://www.springframework.org/schema/beans 
    		http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    		http://www.springframework.org/schema/tx 
    		http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
    		http://www.springframework.org/schema/context 
    		http://www.springframework.org/schema/context/spring-context-2.5.xsd">
    		
    		
    		<bean class = "org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    			<property name = "location" value = "classpath:db.properties"></property>
    		</bean>
    		
    		<bean id = "dataSource" class = "org.springframework.jdbc.datasource.DriverManagerDataSource">
    			<property name = "driverClassName" value = "${driverClass}"></property>
    			<property name = "url" value = "${url}"></property>
    			<property name = "username" value = "${username}"></property>
    			<property name = "password" value = "${password}"></property>
    		</bean>
    		
    		<bean id = "sqlSessionFactory" class = "org.mybatis.spring.SqlSessionFactoryBean">
    			<property name = "dataSource" ref = "dataSource"></property>
    			<property name = "configLocation" value = "classpath:mybatis-config.xml"></property>
    			<property name = "mapperLocations" value = "classpath:cn/zyy/paging/xml/*.xml"></property>
    			<property name = "typeAliasesPackage" value = "cn.zyy.paging.vo"></property>
    		</bean>
    		
    		<bean class = "org.mybatis.spring.mapper.MapperScannerConfigurer">
    			<property name = "sqlSessionFactoryBeanName" value = "sqlSessionFactory"></property>
    			<property name = "basePackage" value = "cn.zyy.paging.dao"></property>
    		</bean>
    		
    		
    		<bean id = "txManage" class = "org.springframework.jdbc.datasource.DataSourceTransactionManager">
    			<property name = "dataSource" ref = "dataSource"></property>
    		</bean>
    		
    		
    		<tx:advice id = "txAdvice" transaction-manager="txManage">
    			<tx:attributes>
    				<tx:method name="*" propagation="REQUIRED"/>
    			</tx:attributes>
    		</tx:advice>
    		
    		<aop:config>
    			<aop:pointcut expression="execution(* cn.zyy.paging.service.*.*(..))" id="pointcut"/>
    			<aop:advisor advice-ref="txAdvice" pointcut-ref="pointcut"/>
    		</aop:config>
    		
    		<context:component-scan base-package="cn.zyy.paging.service"></context:component-scan>
    </beans>

整个工程的下载地址是：http://download.csdn.net/detail/u011991249/9689207。

注：使用的数据库表很简单，表名称是user，表字段是id（int）、name（varchar）、pwd（varchar）。