mybatis 分页 使用拦截器sql重写

 

package cn.util;

import java.sql.Connection;
import java.util.Properties;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.reflect.FieldUtils;
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;

/**
 * 分页拦截器
 * @author
 *
 */
@Intercepts(@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class}))
public class PageInterceptor implements Interceptor {
 
 private final String SQL_SELECT = "(?is)^\\s*SELECT.*$";
 private final String SQL_COUNT  = "(?is)^\\s*SELECT\\s+COUNT\\s*\\(\\s*(?:\\*|\\w+)\\s*\\).*$";

 public Object intercept(Invocation inv) throws Throwable {

  StatementHandler target = (StatementHandler) inv.getTarget();
  BoundSql boundSql = target.getBoundSql();
  String sql = boundSql.getSql();
  if(StringUtils.isBlank(sql)){
   return inv.proceed();
  }
  
  System.out.println("SQL: "+ sql);
  
  //只有select语句下一步
  if(sql.matches(SQL_SELECT) && !Pattern.matches(SQL_COUNT, sql)){
   Object obj = FieldUtils.readField(target, "delegate", true);
   //反射获取RowBounds对象
   RowBounds rowBounds = (RowBounds) FieldUtils.readField(obj, "rowBounds", true);
   //分页参数存在且不为默认值时进行分页SQL构造
   if(rowBounds != null && rowBounds != RowBounds.DEFAULT){
    FieldUtils.writeField(boundSql, "sql", newSql(sql, rowBounds), true);
    System.out.println("new Sql: "+boundSql.getSql());
    FieldUtils.writeField(rowBounds ,"offset", RowBounds.NO_ROW_OFFSET, true);
    FieldUtils.writeField(rowBounds, "limit", RowBounds.NO_ROW_LIMIT, true);
   }
  }
  return inv.proceed();
 }

 public Object plugin(Object target) {

  return Plugin.wrap(target, this);
 }

 public void setProperties(Properties arg0) {

  System.out.println("PageInterceptor setProperties arg0="+ arg0);
 }
 
 /**Oracle 分页
 public String newSql(String oldSql, RowBounds rowBounds){
  StringBuffer sb = new StringBuffer(128);
  sb.append(" SELECT * FROM (SELECT　row_.*, ROWNUM rownum_ FROM (");
  sb.append(oldSql);
  sb.append(") row_ WHERE ROWNUM <=");
  sb.append(rowBounds.getLimit());
  sb.append(") WHERE rownum_ > ");
  sb.append(rowBounds.getOffset());
  return sb.toString();
 }
 */
 
 /**mysql 分页*/
 public String newSql(String oldSql, RowBounds rowBounds){
  StringBuffer sb = new StringBuffer(128);
  sb.append(oldSql);
  sb.append(" limit ");
  sb.append(rowBounds.getOffset());
  sb.append(",");
  sb.append(rowBounds.getLimit());
  
  return sb.toString();
 }

}

<select id="findUsersOfPage" resultMap="userMap" parameterType="userDomain">
  select
  <include refid="userFields"></include>
  from user
  where 1=1
  <if test="userName !=null">
   and USERNAME like '%' || #{userName} || '%'
  </if>
 </select>

 

@Repository
public interface UserMapper {

  
  //分页（使用拦截器sql重写）
 public List<User> findUsersOfPage(User user, RowBounds rowBounds);
}

 

@Service
public class UserServiceImpl implements UserService {
 
 @Autowired
 private UserMapper userMapper;

 public List<User> findUsers() {

  return userMapper.select();
 }
 
 @Transactional
 public void insert(User user){
  userMapper.insert(user);
 }

 //事务传播行为：REQUIRED、REQUIRES_NEW、NESTED 作为testTran方法的一个整体，要么执行成功，要么失败，抛出异常则回滚。
 //@Transactional(propagation=Propagation.REQUIRED, rollbackForClassName={"Exception"})
 //@Transactional(propagation=Propagation.REQUIRES_NEW, rollbackForClassName={"Exception"})
 //@Transactional(propagation=Propagation.NESTED, rollbackForClassName={"Exception"})
 
 //无事务
 //@Transactional(propagation=Propagation.SUPPORTS, rollbackForClassName={"Exception"})
 //@Transactional(propagation=Propagation.NOT_SUPPORTED, rollbackForClassName={"Exception"})
 //@Transactional(propagation=Propagation.NEVER, rollbackForClassName={"Exception"})
 
 @Transactional(propagation=Propagation.REQUIRED, rollbackForClassName={"Exception"})
 public List<User> testTran(User user)throws Exception{
  insert(user);
  List<User> users = findUsers();
  if(true)
   throw new Exception("transaction");
  return users;
 }
 
 
 //分页（使用拦截器sql重写）
 public List<User> findUsersOfPage(User user, int offset, int limit){
  return userMapper.findUsersOfPage(user, new RowBounds(offset, limit));
 }
 
}