Mybatis分页解决方案

来源：互联网发布：第三世界首都知乎编辑：程序博客网时间：2024/05/29 21:36

转自： http://blog.csdn.net/fover717/article/details/8334209

http://www.cnblogs.com/zemliu/archive/2013/08/07/3242966.html

http://fhd001.iteye.com/blog/1121189

参考：http://blog.csdn.net/isea533/article/details/23831273

http://blog.csdn.net/hupanfeng/article/details/9238127

一，在Spring3中使用MyBatis

1.MyBatis 例子

首先，单独使用MyBatis时：

[java] view plaincopy

import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MyBatisUtil {
// 每一个MyBatis的应用程序都以一个SqlSessionFactory对象的实例为核心
// 使用SqlSessionFactory的最佳实践是在应用运行期间不要重复创建多次,最佳范围是应用范围
private final static SqlSessionFactory sqlSessionFactory;
static {
String resource = "configuration.xml";
Reader reader = null;
try {
reader = Resources.getResourceAsReader(resource);
} catch (IOException e) {
System.out.println(e.getMessage());
}
// SqlSessionFactory对象的实例可以通过SqlSessionFactoryBuilder对象来获得
// SqlSessionFactoryBuilder实例的最佳范围是方法范围（也就是本地方法变量）。
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
}
public static SqlSessionFactory getSqlSessionFactory() {
return sqlSessionFactory;
}
}

配置文件：

[html] view plaincopy

<?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="Mapper.UserMapper">
<insert id="insertUser" parameterType="User">
insert into vincent_user(name,age) values(#{name},#{age})
</insert>
<select id="getUser" resultType="User" parameterType="java.lang.String">
select * from vincent_user where name=#{name}
</select>
</mapper>

使用的测试类为：

[java] view plaincopy

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;
public class TestMapper {
static SqlSessionFactory sqlSessionFactory = null;
static {
sqlSessionFactory = MyBatisUtil.getSqlSessionFactory();
}
@Test
public void testAdd() {
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User("tom",new Integer(5));
userMapper.insertUser(user);
sqlSession.commit();//这里一定要提交，不然数据进不去数据库中
} finally {
sqlSession.close();
}
}
@Test
public void getUser() {
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.getUser("jun");
System.out.println("name: "+user.getName()+"|age: "+user.getAge());
} finally {
sqlSession.close();
}
}
}

2.整合Spring3后，单独使用Mybatis

首先，Spring3配置文件中（applicationContext.xml）有：

[html] view plaincopy

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="configLocation" value="classpath:ibatis-config.xml" />
<property name="dataSource" ref="dataSource" />
<property name="mapperLocations">
<span style="white-space:pre"> </span><list>
<span style="white-space:pre"> </span><value>classpath:com/log/bean/mapper/*.xml</value>
<span style="white-space:pre"> </span></list>
</property>
</bean>

[html] view plaincopy

<!-- 通过扫描的模式，扫描目录在com/log/bean/mapper目录下，所有的mapper都继承
SQLMapper接口的接口，这样一个bean就可以了 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.log.bean.mapper"/>
<property name="markerInterface" value="com.log.bean.mapper.SQLMapper"/>
</bean>

工具类：

[java] view plaincopy

import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
public class MyBatisUtil {
private final static SqlSessionFactory sqlSessionFactory;
static {
ApplicationContext ac = new FileSystemXmlApplicationContext("classpath:applicationContext.xml");
sqlSessionFactory = (SqlSessionFactory)ac.getBean("sqlSessionFactory");
}
public static SqlSessionFactory getSqlSessionFactory() {
return sqlSessionFactory;
}
}

myBatis3之从SqlSessionFactory中获取SqlSession

----------

现在，我们已经知道如何获取SqlSessionFactory对象了，基于同样的启示，我们就可以获得SqlSession的实例了。SqlSession对象完全包含以数据库为背景的所有执行SQL操作的方法。你可以用SqlSession实例来直接执行已映射的SQL 语句。例如：

Java代码

SqlSession session = sqlMapper.openSession();
try{
Blog blog = (Blog)session.selectOne("org.mybatis.example.BlogMapper.selectBlog",101);
}finally{
session.close();
}

现在有一种更简洁的方法。使用合理描述参数和SQL语句返回值的接口(比如BlogMapper.class)，这样现在就更简单，更安全的代码，没有容易发生的字符串文字和转换的错误。例如：

Java代码

SqlSession session = sqlSessionFactory.openSession();
try {
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog = mapper.selectBlog(101);
}finally{
session.close();
}

探究已映射的SQL语句

这里给出一个基于XML映射语句的示例，这些语句应该可以满足上述示例中SqlSession对象的调用。

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="org.mybatis.example.BlogMapper">
<select id="selectBlog" parameterType="int" resultType="Blog">
select * from Blog where id = #{id}
</select>
</mapper>

在命名空间“com.mybatis.example.BlogMapper”中，它定义了一个名为“selectBlog”的映射语句，这样它允许你使用完全限定名“org.mybatis.example.BlogMapper.selectBlog”来调用映射语句，我们下面示例中的写法也就是这样的。

Java代码

Blog blog = (Blog)session.selectOne("org.mybatis.example.BlogMapper.selectBlog", 101);

但下面的调用更有优势：

映射接口对应映射xml文件的命令空间，接口方法对应映射xml文件中定义的SQL映射的ID。???????????

Java代码

BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog = mapper.selectBlog(101);

首先它不是基于文字的，那就更安全了。第二，如果你的IDE有代码补全功能，那么你可以利用它来操纵已映射的SQL语句。第三，不需要强制类型转换，同时BlogMapper接口可以保持简洁，返回值类型很安全(参数类型也很安全);

MyBatis 物理分页

MyBatis使用RowBounds实现的分页是逻辑分页,也就是先把数据记录全部查询出来,然在再根据offset和limit截断记录返回

为了在数据库层面上实现物理分页,又不改变原来MyBatis的函数逻辑,可以编写plugin截获MyBatis Executor的statementhandler,重写SQL来执行查询

参考资料: http://blog.csdn.net/hupanfeng/article/details/9265341

下面的插件代码只针对MySQL

plugin代码

package plugin;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Properties;import org.apache.ibatis.executor.parameter.ParameterHandler;import org.apache.ibatis.executor.statement.StatementHandler;import org.apache.ibatis.logging.Log;import org.apache.ibatis.logging.LogFactory;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.apache.ibatis.reflection.factory.DefaultObjectFactory;import org.apache.ibatis.reflection.factory.ObjectFactory;import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;import org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory;import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;import org.apache.ibatis.session.Configuration;import org.apache.ibatis.session.RowBounds;/** * 通过拦截<code>StatementHandler</code>的<code>prepare</code>方法，重写sql语句实现物理分页。 * 老规矩，签名里要拦截的类型只能是接口。 * */@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})public class PaginationInterceptor implements Interceptor {    private static final Log logger = LogFactory.getLog(PaginationInterceptor.class);    private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();    private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();    private static String DEFAULT_PAGE_SQL_ID = ".*Page$"; // 需要拦截的ID(正则匹配)    @Override    public Object intercept(Invocation invocation) throws Throwable {        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();        MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT_OBJECT_FACTORY,                DEFAULT_OBJECT_WRAPPER_FACTORY);        RowBounds rowBounds = (RowBounds) metaStatementHandler.getValue("delegate.rowBounds");        // 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过下面的两次循环可以分离出最原始的的目标类)        while (metaStatementHandler.hasGetter("h")) {            Object object = metaStatementHandler.getValue("h");            metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);        }        // 分离最后一个代理对象的目标类        while (metaStatementHandler.hasGetter("target")) {            Object object = metaStatementHandler.getValue("target");            metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);        }        // property在mybatis settings文件内配置        Configuration configuration = (Configuration) metaStatementHandler.getValue("delegate.configuration");        // 设置pageSqlId        String pageSqlId = configuration.getVariables().getProperty("pageSqlId");        if (null == pageSqlId || "".equals(pageSqlId)) {            logger.warn("Property pageSqlId is not setted,use default '.*Page$' ");            pageSqlId = DEFAULT_PAGE_SQL_ID;        }        MappedStatement mappedStatement = (MappedStatement)                metaStatementHandler.getValue("delegate.mappedStatement");        // 只重写需要分页的sql语句。通过MappedStatement的ID匹配，默认重写以Page结尾的MappedStatement的sql        if (mappedStatement.getId().matches(pageSqlId)) {            BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");            Object parameterObject = boundSql.getParameterObject();            if (parameterObject == null) {                throw new NullPointerException("parameterObject is null!");            } else {                String sql = boundSql.getSql();                // 重写sql                String pageSql = sql + " LIMIT " + rowBounds.getOffset() + "," + rowBounds.getLimit();                metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);                // 采用物理分页后，就不需要mybatis的内存分页了，所以重置下面的两个参数                metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);                metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);            }        }        // 将执行权交给下一个拦截器        return invocation.proceed();    }    @Override    public Object plugin(Object target) {        // 当目标类是StatementHandler类型时，才包装目标类，否者直接返回目标本身,减少目标被代理的次数        if (target instanceof StatementHandler) {            return Plugin.wrap(target, this);        } else {            return target;        }    }    @Override    public void setProperties(Properties properties) {        //To change body of implemented methods use File | Settings | File Templates.    }}

配置plugin

    <plugins>        <plugin interceptor="plugin.PaginationInterceptor" />    </plugins>

查询SQL

    <!-- 测试分页查询 -->    <select id="selectUserByPage" resultMap="dao.base.userResultMap">        <![CDATA[        SELECT * FROM user        ]]>    </select>

调用示例

    @Override    public List<User> selectUserByPage(int offset, int limit) {        RowBounds rowBounds = new RowBounds(offset, limit);        return getSqlSession().selectList("dao.userdao.selectUserByPage", new Object(), rowBounds);    }

另外,结合Spring MVC,编写翻页和生成页码代码

页码类

package util;/** * Created with IntelliJ IDEA. * User: zhenwei.liu * Date: 13-8-7 * Time: 上午10:29 * To change this template use File | Settings | File Templates. */public class Pagination {    private String url; // 页码url    private int pageSize = 10;  // 每页显示记录数    private int currentPage = 1;    // 当前页码    private int maxPage = Integer.MAX_VALUE;    // 最大页数    // 获取offset    public int getOffset() {        return (currentPage - 1) * pageSize;    }    // 获取limit    public int getLimit() {        return getPageSize();    }    public String getUrl() {        return url;    }    public void setUrl(String url) {        this.url = url;    }    public int getPageSize() {        return pageSize;    }    public void setPageSize(int pageSize) {        this.pageSize = pageSize;    }    public int getCurrentPage() {        return currentPage;    }    public void setCurrentPage(int currentPage) {        if (currentPage < 1)            currentPage = 1;        if (currentPage > maxPage)            currentPage = maxPage;        this.currentPage = currentPage;    }    public int getMaxPage() {        return maxPage;    }    public void setMaxPage(int maxPage) {        this.maxPage = maxPage;    }}

为了计算最大页码,需要知道数据表的总记录数,查询SQL如下

    <!-- 记录总数 -->    <select id="countUser" resultType="Integer">        <![CDATA[        SELECT COUNT(*) FROM user        ]]>    </select>

    @Override    public Integer countTable() {        return getSqlSession().selectOne("dao.userdao.countUser");    }

Controller中的使用

    @RequestMapping("/getUserByPage")    public String getUserByPage(@RequestParam                                    int page, Model model) {        pagination.setCurrentPage(page);        pagination.setUrl(getCurrentUrl());        pagination.setMaxPage(userDao.countTable() / pagination.getPageSize() + 1);        List<User> userList = userDao.selectUserByPage(                pagination.getOffset(), pagination.getLimit());        model.addAttribute(pagination);        model.addAttribute(userList);        return "index";    }

0 0