MyBatis拦截器实现mysql与oracle共用分页

最近笔者在做公司项目从oracle到mysql的迁移，大家都知道mysql分页采用的是limit，而oracle采用的是rownum，为了不在oracle和mysql对应的mapper文件,分别写对应的分页，这里可以使用mybatis自带的拦截器插件实现。下面分一下几个步骤进行介绍：一、准备工作；二、myBatis.xml拦截器配置；三、拦截器interceptor实现；四、效果展示。

一、准备工作

首先，准备model，User.java:

package com.qiyongkang.model;public class User extends BaseEntity {    private static final long serialVersionUID = 1L;    private int Id;    private String UserName;    private int Age;    public User() {}    public int getId() {        return Id;    }    public void setId(int id) {        Id = id;    }    public String getUserName() {        return UserName;    }    public void setUserName(String userName) {        UserName = userName;    }    public int getAge() {        return Age;    }    public void setAge(int age) {        Age = age;    }    @Override    public String toString() {        // TODO Auto-generated method stub        return this.getId() + "==" + this.getUserName() + "==" + this.getAge();    }}

然后就是User类的父类，BaseEntity.java,此类是用于分页、排序用：

package com.qiyongkang.model;import java.io.Serializable;public class BaseEntity implements Serializable{    private static final long serialVersionUID = 1L;    private String sort;    private String order;    private int currentPage = 0;    public int getCurrentPage() {        return currentPage;    }    public void setCurrentPage(int currentPage) {        this.currentPage = currentPage;    }    public int getPageSize() {        return pageSize;    }    public void setPageSize(int pageSize) {        this.pageSize = pageSize;    }    private int pageSize = 5;    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;    }    @Override    public String toString() {        return "BaseEntity [sort=" + sort + ", order=" + order + ", currentPage=" + currentPage + ", pageSize="                + pageSize + "]";    }}

然后，再就是User实体对于的mapper.xml和mapper接口：UserMapper.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="com.qiyongkang.mybatis.UserMapper">  <resultMap type="User" id="userMap">    <id property="Id" column="Id"/>    <result property="UserName" column="UserName"/>    <result property="Age" column="Age"/>  </resultMap>  <select id="getUserList" resultMap="userMap" parameterType="User">    SELECT * FROM user     <where>      <if test="UserName != null and UserName != ''">userName LIKE '%%'</if>    </where>    <if test="sort != null and order != null and sort != '' and order != ''">        ORDER BY ${sort} ${order}    </if>  </select></mapper>    

UserMapper.java接口：

package com.qiyongkang.mybatis;import java.util.HashMap;import java.util.List;import com.qiyongkang.model.BaseEntity;import com.qiyongkang.model.User;public interface UserMapper {    public List<User> getUserList(BaseEntity baseEntity);}

再就是连接数据库的属性文件配置jdbc.properties:

#######MySql#########driver=com.mysql.jdbc.Driverurl=jdbc:mysql://127.0.0.1:3306/testusername=rootpassword=123456db=mysql########Oracle########driver=oracle.jdbc.driver.OracleDriver#url=jdbc:oracle:thin:@127.0.0.1:1521:ORCL#username=username#password=password    

二、myBatis.xml拦截器配置

接下来，再来看看拦截器的配置：

<?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>  <properties resource="jdbc.properties"></properties>  <typeAliases>    <package name="com.qiyongkang.model"/>  </typeAliases>  <plugins>    <plugin interceptor="com.qiyongkang.interceptor.PageSqlHandleInterceptor"></plugin>  </plugins>  <environments default="development">    <environment id="development">      <transactionManager type="JDBC"/>      <dataSource type="POOLED">        <property name="driver" value="${driver}"/>        <property name="url" value="${url}"/>        <property name="username" value="${username}"/>        <property name="password" value="${password}"/>      </dataSource>    </environment>  </environments>  <mappers>    <mapper resource="com/qiyongkang/model/UserMapper.xml"/>  </mappers></configuration>    

这里在plugins标签中配置的就是拦截器，它必须实现Interceptor接口。注意，这里笔者没有与spring集成。

三、拦截器interceptor实现

接下来，再来看看拦截器中，其实这个拦截器的功能就是在sql执行前，对sql语句进行统一的包装改变，然后再执行。

package com.qiyongkang.interceptor;import java.lang.reflect.Field;import java.util.Properties;import org.apache.ibatis.executor.statement.RoutingStatementHandler;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 com.qiyongkang.model.BaseEntity;@Intercepts({ @org.apache.ibatis.plugin.Signature(type = org.apache.ibatis.executor.statement.StatementHandler.class, method = "prepare", args = { java.sql.Connection.class }) })public class PageSqlHandleInterceptor implements Interceptor {    @Override    public Object intercept(Invocation invocation) throws Throwable {        RoutingStatementHandler statement = (RoutingStatementHandler) invocation.getTarget();        BoundSql boundSql = statement.getBoundSql();        String sql = boundSql.getSql();        System.out.println("分页前：" + sql);        BaseEntity baseEntity = (BaseEntity) boundSql.getParameterObject();        System.out.println("当前页：" + baseEntity.getCurrentPage() + "==页大小：" + baseEntity.getPageSize());        //获取currentPage, pageSize        int currentPage = baseEntity.getCurrentPage();        int pageSize = baseEntity.getPageSize();        //从jdbc.properties        Properties prop = new Properties();        prop.load(PageSqlHandleInterceptor.class.getClassLoader().getResourceAsStream("jdbc.properties"));        String db = prop.getProperty("db");        if ("mysql".equals(db)) {            sql = getLimitStringMysql(sql, (currentPage - 1) * pageSize, pageSize);        } else if ("oracle".equals(db)) {            sql = getLimitStringOracle(sql, (currentPage - 1) * pageSize, currentPage * pageSize);        }        setClassField(boundSql, "sql", sql);        System.out.println("分页前：" + sql);        return invocation.proceed();    }    @Override    public Object plugin(Object obj) {        return Plugin.wrap(obj, this);    }    @Override    public void setProperties(Properties properties) {    }    /**     * Oracle分页     * @param sql     * @param offset     * @param limit     * @return     */    public String getLimitStringOracle(String sql, int offset, int limit) {        sql = sql.trim();        boolean isForUpdate = false;        if (sql.toLowerCase().endsWith(" for update")) {            sql = sql.substring(0, sql.length() - 11);            isForUpdate = true;        }        StringBuffer pagingSelect = new StringBuffer(sql.length() + 100);        if (offset > 0) {            pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");        } else {            pagingSelect.append("select * from ( ");        }        pagingSelect.append(sql);        if (offset > 0) {            pagingSelect.append(" ) row_ ) where rownum_ <= " + limit + " and rownum_ > " + offset);        } else {            pagingSelect.append(" ) where rownum <= " + limit);        }        if (isForUpdate) {            pagingSelect.append(" for update");        }        return pagingSelect.toString();    }    /**     * mysql分页     * @param sql     * @param offset     * @param limit     * @return     */    public String getLimitStringMysql(String sql, int offset, int limit) {        sql = sql.trim();        boolean isForUpdate = false;        if (sql.toLowerCase().endsWith(" for update")) {            sql = sql.substring(0, sql.length() - 11);            isForUpdate = true;        }        StringBuffer pagingSelect = new StringBuffer(sql.length() + 100);        if (offset < 0) {            offset = 0;        }        pagingSelect.append(sql);        pagingSelect.append(" limit " + offset + "," + limit);        if (isForUpdate) {            pagingSelect.append(" for update");        }        return pagingSelect.toString();    }    /**     * 通过反射获取某个类的属性     *     * @param clazz     * @param name     * @return     * @throws Exception     */    private static Field getField(Class<?> clazz, String name) throws Exception {        Field field = null;        for (Field f : clazz.getDeclaredFields()) {            if (f.getName().equals(name)) {                f.setAccessible(true);                field = f;            }        }        return field;    }    public static void setClassField(Object obj, String name, Object value) throws Exception {        Field field = getField(obj.getClass(), name);        field.set(obj, value);    }}

四、效果展示

mysql：

oracle:

项目包结构:

最后来看看，调用测试类Test.java:

package com.qiyongkang.test;import java.io.IOException;import java.io.InputStream;import java.util.List;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import com.qiyongkang.model.User;import com.qiyongkang.mybatis.UserMapper;public class Test {    public static void main(String[] args) {        //Get the SqlSessionFactory        String resource = "myBatis.xml";        SqlSession session = null;        try {            InputStream inputStream = Resources.getResourceAsStream(resource);            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);            //Get a sqlSession            session = sqlSessionFactory.openSession();            //Get the mapper            UserMapper userMapper = session.getMapper(UserMapper.class);            //Get user list            User user3 = new User();            user3.setId(5);            user3.setUserName("1");            user3.setAge(14);            user3.setCurrentPage(2);            user3.setPageSize(5);            user3.setSort("userName");            user3.setOrder("desc");            List<User> userList = userMapper.getUserList(user3);            session.commit();            System.out.println(userList.size());        } catch (IOException e) {            e.printStackTrace();        } finally {            session.close();        }    }}

就讲到这儿啦，大家有什么疑问的或者一些好的建议，可以给我留言，谢谢！