Mysql+Mybatis+spring 后台分页

一、mybatis分页是基于mybatis plugin，用拦截器实现，上代码。

1、生产sql语句：

package com.csair.interceptor;

import java.sql.Connection;
import java.util.HashMap;
import java.util.Properties;

import org.apache.ibatis.executor.statement.PreparedStatementHandler;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
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.session.RowBounds;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


@Intercepts({@Signature(type=StatementHandler.class, method="prepare", args={Connection.class})})
public class StatementHandlerInterceptor implements Interceptor {

	private static String MYSQL_DIALECT = "com.csair.interceptor.MysqlDialectPPT";
	private Logger log = LoggerFactory.getLogger(StatementHandlerInterceptor.class);
	
	@SuppressWarnings("unchecked")
	public Object intercept(Invocation invocation) throws Throwable {
		RoutingStatementHandler statetment = (RoutingStatementHandler)invocation.getTarget();
		PreparedStatementHandler handler = (PreparedStatementHandler)ReflectUtil.getFieldValue(statetment,"delegate");
		RowBounds rowBounds = (RowBounds)ReflectUtil.getFieldValue(handler, "rowBounds");

                if(rowBounds.getLimit() == RowBounds.NO_ROW_LIMIT) {
                    return invocation.proceed();
                }		

                BoundSql boundSql = statetment.getBoundSql();
		String sql = boundSql.getSql();
//		HashMap<String, Object> map = (HashMap<String, Object>)boundSql.getParameterObject();
		MysqlDialectPPT dialect = (MysqlDialectPPT)Class.forName(MYSQL_DIALECT).newInstance();
		sql = dialect.getLimitString(sql, rowBounds.getOffset(), rowBounds.getLimit());
		log.info(sql);
		ReflectUtil.setFieldValue(boundSql, "sql", sql);
		return invocation.proceed();
	}

	public Object plugin(Object target) {
		return Plugin.wrap(target, this);
	}

	public void setProperties(Properties properties) {
		SqlSessionFactoryBean c;
	}
	

}

2、结果处理：

package com.csair.interceptor;

import java.sql.Statement;
import java.util.Properties;

import org.apache.ibatis.executor.resultset.FastResultSetHandler;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
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.session.RowBounds;

@Intercepts({@Signature(type=ResultSetHandler.class, method="handleResultSets", args={Statement.class})})
public class ResultSetHandlerInterceptor implements Interceptor {

	public Object intercept(Invocation invocation) throws Throwable {
		FastResultSetHandler resultSet = (FastResultSetHandler)invocation.getTarget();
		RowBounds rowBounds = (RowBounds)ReflectUtil.getFieldValue(resultSet, "rowBounds");
		if(rowBounds.getLimit() > 0 && rowBounds.getLimit() < RowBounds.NO_ROW_LIMIT) {
			ReflectUtil.setFieldValue(resultSet, "rowBounds", new RowBounds());
		}
		return invocation.proceed();
	}

	public Object plugin(Object target) {
		return Plugin.wrap(target, this);
	}

	public void setProperties(Properties properties) {

	}

}

3、mysql分页语句：

package com.csair.interceptor;

import org.hibernate.dialect.MySQLDialect;

public class MysqlDialectPPT extends MySQLDialect {

	public String getLimitString(String queryString, int offset, int pageSize) {
		StringBuffer buffer = new StringBuffer(queryString);
		buffer.append(" limit ");
		buffer.append(offset);
		buffer.append(",");
		buffer.append(pageSize);
		return buffer.toString();
	}
}

4、用反射机制赋值：

package com.csair.interceptor;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;

public class ReflectUtil {

	public static void setFieldValue(Object object, String fieldName,Object value){
        Field field = getDeclaredField(object, fieldName);
        if (field == null)
            throw new IllegalArgumentException("Could not find field ["
                    + fieldName + "] on target [" + object + "]");
        makeAccessible(field);
        try {
            field.set(object, value);
        } catch(IllegalAccessException e) {
            e.printStackTrace();
        }
    }
	
	public static Object getFieldValue(Object object, String fieldName) {
		Field field = getDeclaredField(object, fieldName);
		if (field == null)
			throw new IllegalArgumentException("Could not find field ["
					+ fieldName + "] on target [" + object + "]");
		makeAccessible(field);
		Object result = null;
		try {
			result = field.get(object);
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	private static Field getDeclaredField(Object object, String filedName) {
		for (Class<?> superClass = object.getClass(); superClass != Object.class; superClass = superClass
				.getSuperclass()) {
			try {
				return superClass.getDeclaredField(filedName);
			} catch (NoSuchFieldException e) {
				// Field 不在当前类定义, 继续向上转型
			}
		}
		return null;
	}
	
	private static void makeAccessible(Field field) {
		if (!Modifier.isPublic(field.getModifiers())) {
			field.setAccessible(true);
		}
	}
}

5、spring配置

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="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-3.0.xsd
    http://www.springframework.org/schema/aop
    http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">
    
    <context:annotation-config/>
    <context:component-scan base-package="com.csair.dao"/>
	
    <bean id="dataSource_dbcp" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
		<property name="password" value="root"/>
		<property name="username" value="root"/>
		<property name="url" value="jdbc:mysql://localhost:3306/ppt?useUnicode=true&characterEncoding=UTF-8"/>
		<property name="maxActive" value="20"/>
		<property name="maxIdle" value="15"/>
		<property name="minIdle" value="5"/>
		<property name="maxWait" value="60000"/>
		<property name="initialSize" value="1"/>
		<property name="removeAbandoned" value="true"/>
		<property name="removeAbandonedTimeout" value="180"/>
		<property name="logAbandoned" value="true"/>
		<property name="timeBetweenEvictionRunsMillis" value="3600000"/>
		<property name="minEvictableIdleTimeMillis" value="3600000"/>
		<property name="defaultAutoCommit" value="false"/>
	</bean> 
    
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    	<property name="dataSource">
    		<ref local="dataSource_dbcp"/>
    	</property>
    	<property name="typeAliasesPackage" value="com.csair.dao"/>
    	<property name="mapperLocations" value="mybatis/*"/>
    	<property name="plugins">
    		<array>
    			<ref local="statementHandlerInterceptor"/>
    			<ref local="resultSetHandlerInterceptor"/>
    		</array>
    	</property>
    </bean>
    <bean id="statementHandlerInterceptor" class="com.csair.interceptor.StatementHandlerInterceptor"/>
    <bean id="resultSetHandlerInterceptor" class="com.csair.interceptor.ResultSetHandlerInterceptor"/>
    
   <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    	<property name="basePackage" value="com.csair.dao"></property> <!-- 扫描接口定义的方式，自动帮实现 -->
    </bean> 
    
    <bean id="mybatisDemo" class="com.csair.dao.MybatisDemo">
    </bean>
    
</beans>

6、接口和实现

package com.csair.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;
@Component("mybatisDemoInterface")
public interface MybatisDemoInterface {
	List<UserAnotation> selectUser();
	void addUser();
	
	List<UserAnotation> getPage(Map<String, String> parameters, RowBounds rowBounds);
}

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper  
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.csair.dao.MybatisDemoInterface">
<cache />
	<select id="selectUser" resultType="UserAnotation">
		select * from user
	</select>
	
	<insert id="addUser">
		insert into user(username, password) value('mybatis', 'mybatis')
	</insert>
	
	<select id="getPage" resultType="UserAnotation" parameterType="map">
		select * from user where username = #{username} order by id desc
	</select>
</mapper>

7、测试

@Autowired
	private MybatisDemoInterface mybatisDemoInterface;
	public static void main(String[] args) {
		String configLocation = "applicationContext-mybatis.xml";
		ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext(configLocation);
		MybatisDemo demo = context.getBean("mybatisDemo", MybatisDemo.class);
		demo.queryPage();
	}
	public void queryPage() {
		Map<String, String> map = new HashMap<String, String>();
		map.put("username", "okok");
		List<UserAnotation> user = mybatisDemoInterface.getPage(map, new RowBounds(10,5));
		System.out.println(user.size());
		System.out.println(user.toString());
	}