</pre><pre name="code" class="html">
Mybatis实现自定义分页内容返回需要2个插件(相当于struts的拦截器),一个拦截StatementHandler接口,用于对查询的sql做分页封装和分页对象的一些参数设置,另一个拦截ResultSetHandler接口,在mybatis执行完查询之后返回的数据进行封装到分页对象中。整个分页就自动完成了。
</pre><pre name="code" class="html">
PageInterceptor.java(用于拦截 StatementHandler接口)
package com.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;
import org.apache.ibatis.executor.parameter.DefaultParameterHandler;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.PreparedStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
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.reflection.MetaObject;
import org.springframework.util.StringUtils;
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args=Connection.class)})
public class PageInterceptor implements Interceptor {
private String DBType;
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
MetaObject delegateMetaObject =MetaObject.forObject(statementHandler);
PreparedStatementHandler delegate = (PreparedStatementHandler)delegateMetaObject.getValue("delegate");
BoundSql boundSql = delegate.getBoundSql();
Object parame = boundSql.getParameterObject();
Connection connection = (Connection)invocation.getArgs()[0];
if(parame instanceof Map){
Map<?, ?> parameMap = (Map<?, ?>)parame;
Iterator<?> iterator = parameMap.entrySet().iterator();
boolean flag = true;
while(iterator.hasNext()){
Object object = ((Entry<?, ?>)iterator.next()).getValue();
if(object instanceof Page && flag){
String pageSql = this.getPageSql(boundSql.getSql(), (Page<?>)object);
this.countTotal(parameMap, delegate,connection);
flag = false;
MetaObject boundSqlMetaObject = MetaObject.forObject(boundSql);
boundSqlMetaObject.setValue("sql", pageSql);
}
}
}
return invocation.proceed();
}
public Object plugin(Object target) {
if(target instanceof StatementHandler){
return Plugin.wrap(target, this);
}else {
return target;
}
}
public void setProperties(Properties properties) {
this.DBType = properties.getProperty("DBType");
}
private void countTotal(Map<?, ?> Map,PreparedStatementHandler statementHandler,Connection connection){
Page<?> page = null;
Iterator<?> iterator = Map.values().iterator();
while(iterator.hasNext()){
Object object = iterator.next();
if(object instanceof Page){
page = (Page<?>)object;
}
}
MetaObject metaObject = MetaObject.forObject(statementHandler);
MappedStatement mappedStatement = (MappedStatement)metaObject.getValue("mappedStatement");
BoundSql boundSql = statementHandler.getBoundSql();
String sql = boundSql.getSql();
sql = this.getCountSql(sql);
BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), sql, boundSql.getParameterMappings(), Map);
ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, Map, countBoundSql);
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt =connection.prepareStatement(sql);
parameterHandler.setParameters(pstmt);
rs = pstmt.executeQuery();
if (rs.next()) {
int totalRecord = rs.getInt(1);
page.setTotal(totalRecord);
page.setPageCount((totalRecord-1)/page.getPageSize()+1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private String getCountSql(String sql){
int beginIndex = sql.indexOf("from");
sql = sql.substring(beginIndex);
sql = "select count(1)"+sql;
return sql;
}
private String getPageSql(String sql,Page<?> page){
StringBuffer sqlBuffer = new StringBuffer(sql);
if(DBType.equalsIgnoreCase("mysql")){
return getMysqlPageSql(sqlBuffer, page);
}else if(DBType.equalsIgnoreCase("oralce")){
return getOralcePageSql(sqlBuffer, page);
}else{
return sql.toString();
}
}
private String getMysqlPageSql(StringBuffer sql,Page<?> page){
if(null!=page.getOrder() && !page.getOrder().equals("0")){
String[] order = page.getOrder().split(",");
String[] sorts = page.getSort().split(",");
int i=0;
sql.append(" order by ");
for(String sort :sorts){
sql.append(sort).append(" ").append(order[i]).append(",");
i++;
}
sql.deleteCharAt(sql.length()-1);
}
sql.append(" limit ").append(page.getStartIndex()).append(",").append(page.getEndIndex());
return sql.toString();
}
private String getOralcePageSql(StringBuffer sql,Page<?> page){
if(null!=page.getOrder() && !page.getOrder().equals("0")){
String[] order = StringUtils.split(page.getOrder(),",");
int i=0;
sql.append(" order by ");
for(String sort :StringUtils.split(page.getSort(), ",") ){
sql.append(sort).append(" ").append(order[i]);
i++;
}
}
sql.insert(0, "select u.*, rownum r from (").append(") u where rownum <= ").append(page.getEndIndex());
sql.insert(0, "select * from (").append(") where r > ").append(page.getStartIndex());
return sql.toString();
}
}
ResultInterceptor,java( 拦截 ResultSetHandler 接口,将查询结果进行封装到分页对象中)
package com.util;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.ibatis.executor.resultset.FastResultSetHandler;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
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.reflection.MetaObject;
@Intercepts({@Signature(args = { Statement.class }, method = "handleResultSets", type = ResultSetHandler.class)})
public class ResultInterceptor implements Interceptor {
public Object intercept(Invocation invocation) throws Throwable {
FastResultSetHandler resultSetHandler = (FastResultSetHandler)invocation.getTarget();
MetaObject metaObject = MetaObject.forObject(resultSetHandler);
BoundSql boundSql = (BoundSql)metaObject.getValue("boundSql");
Object parameterObject = boundSql.getParameterObject();
List result = new ArrayList();
if(parameterObject instanceof Map<?, ?>){
Iterator<?> iterator = ((Map<?, ?>) parameterObject).values().iterator();
while(iterator.hasNext()){
Object object = iterator.next();
if(object instanceof Page<?>){
List<?> list = (List<?>) invocation.proceed();
Page<?> page = (Page<?>)object;
page.setResult(list);
result.add(page);
break;
}
}
}else{
result = (List)invocation.proceed();
}
return result;
}
public Object plugin(Object target) {
if(target instanceof ResultSetHandler){
return Plugin.wrap(target, this);
}else{
return target;
}
}
public void setProperties(Properties properties) {
}
}
package com.util;
import java.util.List;
public class Page<T> {
private final int PAGE_SIZE_DEFAULT=10;
private int page=1;
private int pageSize=PAGE_SIZE_DEFAULT;
private int total;
private int pageCount;
private int startIndex;
private int endIndex;
private String sort;
private String order;
private List<?> result;
public List<?> getRows() {
return result;
}
public void setRows(List<?> rows) {
//this.rows = rows;
pageSize = Integer.valueOf((String)rows.get(0));
}
public String toString() {
return "Page [PAGE_SIZE_DEFAULT=" + PAGE_SIZE_DEFAULT + ", pageNum="
+ page + ", pageSize=" + pageSize + ", total=" + total
+ ", pageCount=" + pageCount + ", startIndex=" + startIndex
+ ", endIndex=" + endIndex + ", result=" + result + "]";
}
public int getPage() {
return page;
}
public void setPage(int pageNum) {
this.page = pageNum;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotal() {
return total;
}
public void setTotal(int total) {
this.total = total;
}
public int getStartIndex() {
this.startIndex = (this.page-1)*this.pageSize;
return startIndex;
}
public int getEndIndex() {
this.endIndex = this.page*this.pageSize;
return endIndex;
}
public List<?> getResult() {
return result;
}
public void setResult(List<?> result) {
this.result = result;
}
public int getPageCount() {
return pageCount;
}
public void setPageCount(int pageCount) {
this.pageCount = pageCount;
}
public void setRows(int rows) {
this.pageSize = rows;
}
public String getSort() {
return sort;
}
public void setSort(String sort) {
this.sort = sort;
}
public String getOrder() {
return order;
}
public void setOrder(String order) {
this.order = order;
}
}
conf.xml(mybatis配置插件)
<?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>
<plugins>
<plugin interceptor="com.util.PageInterceptor">
<property name="DBType" value="mysql"/>
</plugin>
<plugin interceptor="com.util.ResultInterceptor"></plugin>
</plugins>
</configuration>
applicationContext.xml(mybatis自动扫描dao与实现对接)
<?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:mvc="http://www.springframework.org/schema/mvc"
xmlns:security="http://www.springframework.org/schema/security"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-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/mvc
http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd
http://www.springframework.org/schema/security
http://www.springframework.org/schema/security/spring-security-3.1.xsd"
>
<!--创建jdbc数据源 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/sushe" />
<property name="username" value="root" />
<property name="password" value="123456" />
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="mapperLocations" value="classpath:com/dao/Impl/*.xml" />
<property name="configLocation" value="classpath:conf.xml"></property>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.dao"></property>
</bean>
</beans>
testDao.java
package com.dao;
import com.bean.Student;
import com.util.Page;
public interface TestDao {
public Student getStudentById(int id);
public Page<Student> getStudentsByClassId(int classId,Page<Student> page);
}
testDao.xml
<?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,namespace的值习惯上设置成包名+sql映射文件名,这样就能够保证namespace的值是唯一的
例如namespace="me.gacl.mapping.userMapper"就是me.gacl.mapping(包名)+userMapper(userMapper.xml文件去除后缀)
-->
<mapper namespace="com.dao.TestDao">
<!-- 在select标签中编写查询的SQL语句, 设置select标签的id属性为getUser,id属性值必须是唯一的,不能够重复
使用parameterType属性指明查询时使用的参数类型,resultType属性指明查询返回的结果集类型
resultType="me.gacl.domain.User"就表示将查询结果封装成一个User类的对象返回
User类就是users表所对应的实体类
-->
<!--
根据id查询得到一个user对象
-->
<resultMap type="com.bean.Student" id="student">
<id property="id" column="ID"/>
<result column="Student_Name" property="name"/>
<result column="Student_Sex" property="sex"/>
</resultMap>
<select id="getStudentById" parameterType="int"
resultMap="student">
select *,Student_ID id from student where student_id=#{id}
</select>
<select id="getStudentsByClassId" resultMap="student">
select * from student where class_id=#{0}
</select>
</mapper>
<?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:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-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/mvc
http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd
">
<context:annotation-config />
<!-- 自动扫面com目录及其子目录下面所有类文件,自动注入所有带注解的类 -->
<context:component-scan base-package="com.*" />
<bean class="org.springframework.web.servlet.mvc.annotation.DefaultAnnotationHandlerMapping"/>
<!-- 处理请求response返回值,如下配置能正确返回字符串型返回值,如返回值为对象,则自动转为json -->
<bean id="handleAdapter" class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter">
<property name="messageConverters">
<list>
<ref bean="mappingJacksonHttpMessageConverter" /><!-- json转换器 -->
<ref bean="mappingStringHttpMessageConverter" />
</list>
</property>
</bean>
<bean id="mappingJacksonHttpMessageConverter" class="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter" />
<bean id="mappingStringHttpMessageConverter" class="org.springframework.http.converter.StringHttpMessageConverter" />
<!-- 对模型视图名称的解析,即在模型视图名称添加前后缀 -->
<bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/jsp/" />
<property name="suffix" value=".jsp"></property>
<property name="order" value="1"></property>
</bean>
</beans>