mybatis 分页插件，这个很很常见，最常见的就是 pagehelp
但是不能满足我的需求
受我一个师傅的影响，在使用mybatis 分页查询的时候有以下几点是mybatis 无法满足的
- DAO层传参数的问题
public PageControlInfo findPageListMedia(@Param("entity") AdActMediaMap entity, @Param("skip") Integer skip, @Param("max") Integer max,
@Param("params") Map<String, Object> params);
上面这种写法里面，DAO传参数 传了 一个 POJO类，2个integer 参数，还有一个map 参数
因为 如果把所有的参数 都写在POJO里面 或者 一个MAP里面，会很别扭。
但是如果把参数 这样传，在 mybatis 里面取值的时候，只能通过 #{entity.id} #{params.name} #{skip}
这种方法区查，觉得很不方便，也很容易出错
- 返回值
上面的实例中，是一个分页查询，普通的返回结果集 只能是一个list 集合，但是一般的分页查询都需要返回一个 带有总记录数出去，如果查询之后，再手动拼接 总记录数，会显得很别扭。 所有我想能不能直接返回一个对象，对象里面包含 查询的结果集合和 总记录数，如下
package com.cat.common.bean;
import java.io.Serializable;
import java.util.List;
@SuppressWarnings("rawtypes")
public class PageControlInfo implements Serializable {
private static final long serialVersionUID = 6597075424155909806L;
public int start;
public int max;
public int totalNum;
public List searchData;
public PageControlInfo() {
start = 1;
max = 20;
totalNum = 0;
searchData = null;
}
public int getStart() {
return start;
}
public void setStart(int start) {
this.start = start;
}
public int getMax() {
return max;
}
public void setMax(int max) {
this.max = max;
}
public int getTotalNum() {
return totalNum;
}
public void setTotalNum(int totalNum) {
this.totalNum = totalNum;
}
public List getSearchData() {
return searchData;
}
public void setSearchData(List searchData) {
this.searchData = searchData;
}
}
基于上面2点，只能自己去写 分页插件了。
直接看代码吧
切入点是mybatis 拦截器
package com.cat.common.mybatis;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
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.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import com.cat.common.bean.PageConstants;
import com.cat.common.bean.PageControlInfo;
import com.cat.common.reflect.RReflectUtils;
/**
* 使用的限制条件
* 1. dao 层的方法 不同有同名的
* 2. 表自增主键的名称 为ID
* 3. 实体类对象的别名为 entity
* @author luoyang
*
*/
@Intercepts({
@Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,
RowBounds.class, ResultHandler.class }),
@Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,
RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class }),
@Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class }) })
public class ExecutorPlugin implements Interceptor {
private static final String Entity = "entity";
private static final String SelectKey = "selectKey";
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement) args[0];
if (SqlCommandType.INSERT.equals(ms.getSqlCommandType())) {
Object entity = hasEntity(args[1]);
if(entity != null) {
//如果是走特殊的 entity 自定义对象，还是需要将参数拆出来，单独使用，保证主键能回显
args[1] = entity;
}
//新增操作，直接不使用拦截器方式，还原参数的使用方式
return invocation.proceed();
}
if (SqlCommandType.SELECT.equals(ms.getSqlCommandType()) && checkIsSelectKey(ms.getId())) {
//是新增返回主键的方法selectKey，不需要修改入参的方式
return invocation.proceed();
}
// 不是insert 方法，修改入参数
args[1] = commandParam(args[1]);
if (SqlCommandType.SELECT.equals(ms.getSqlCommandType()) && checkIsPage(ms.getId())) {
// 分页查询请求
return handResult(invocation, ms, args[1], (ResultHandler<?>) args[3]);
}
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
/**
* 是否有自定义的 entity 对象
* @param invocation
* @param queryParam
* @return
*/
public Object hasEntity(Object queryParam) {
try {
// 原始DAO 传进来的集合。
@SuppressWarnings("unchecked")
Map<String, Object> inparam = (Map<String, Object>) queryParam;
return inparam.get(Entity);
}catch (Exception e) {
}
return null;
}
@SuppressWarnings("unchecked")
private Object handResult(Invocation invocation, MappedStatement ms, Object params, ResultHandler<?> r)
throws InvocationTargetException, IllegalAccessException, SQLException {
PageControlInfo pageInfo = new PageControlInfo();
Map<String, Object> pageParam = new HashMap<String, Object>();
Integer max = PageConstants.PAGE_MAX_COUNT;
Integer skip = 0;
if (params != null && params instanceof Map<?, ?>) {
// 强制转换
pageParam = (Map<String, Object>) params;
// 页记录数
max = (Integer) pageParam.get(PageConstants.PAGE_MAX);
if (null == max) {
max = PageConstants.PAGE_MAX_COUNT;
}
pageInfo.setMax(max);
// 当前页数
skip = (Integer) pageParam.get(PageConstants.PAGE_SKIP);
if (null == skip) {
skip = 0;
}
if (skip >= 1) {
skip = skip - 1;
}
pageInfo.setStart(skip);
}
// 总页数
Integer rowcount = getCount(invocation, ms, params, r);
if (rowcount > 0 && params instanceof Map<?, ?>) {
pageInfo.setTotalNum(rowcount);
// 起始数 skip*max， 一页总条数 max
pageParam.put(PageConstants.MYSQL_START, skip * max);
// 结束条数 max+max*skip， 开始条数 skip*max
pageParam.put(PageConstants.ORACLE_START, skip * max + 1);
pageParam.put(PageConstants.ORACLE_END, max + max * skip);
Object c = invocation.proceed();
pageInfo.setSearchData((List<?>) c);
}
List<PageControlInfo> l = new ArrayList<PageControlInfo>();
l.add(pageInfo);
return l;
}
private Integer getCount(Invocation invocation, MappedStatement ms, Object params, ResultHandler<?> r)
throws SQLException {
Executor e = (Executor) invocation.getTarget();
MappedStatement mcount = ms.getConfiguration()
.getMappedStatement(ms.getId() + "_" + PageConstants.GET_ROW_COUNT);
Object countResultList = e.query(mcount, params, RowBounds.DEFAULT, r);
Integer count = (Integer) ((List<?>) countResultList).get(0);
return count;
}
/**
* 数据格式转换
*
* @param args
* @return
*/
@SuppressWarnings("unchecked")
private Object commandParam(Object args) {
Map<String, Object> param = (Map<String, Object>) args;
Map<String, Object> r = new HashMap<String, Object>();
for (String key : param.keySet()) {
Object valueObject = param.get(key);
/*
* if (null == valueObject) { // dao层传NULL，也能兼容， continue; }
*/
if (Entity.equalsIgnoreCase(key)) {
r.putAll(RReflectUtils.getFieldMapForClass(valueObject));
} else if (valueObject instanceof Map) {
r.putAll((Map<String, Object>) valueObject);
} else {
r.put(key, valueObject);
}
}
return r;
}
/**
* 检查是否是分页查询
*
* @param id
* @return
* @throws ClassNotFoundException
*/
private boolean checkIsPage(String id) throws ClassNotFoundException {
String className = id.substring(0, id.lastIndexOf("."));
String methodName = id.substring(id.lastIndexOf(".") + 1);
Class<?> aClass = Class.forName(className);
Method[] s = aClass.getMethods();
for (Method method : s) {
if (methodName.equals(method.getName())) {
return PageControlInfo.class.equals(method.getReturnType());
}
}
return false;
}
/**
* 检查是否是新增返回主键
*
* @param id
* @return
* @throws ClassNotFoundException
*/
private boolean checkIsSelectKey(String id) throws ClassNotFoundException {
String selectKey = id.substring(id.lastIndexOf("!")+1);
if(selectKey != null && selectKey.equalsIgnoreCase(SelectKey)) {
return true;
}
return false;
}
}
配置拦截器
@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
System.out.println("sqlSessionFactory init ");
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
// 添加拦截器
sqlSessionFactoryBean.setPlugins(new Interceptor[]{new ExecutorPlugin()});
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:msq/sql/**/*.xml"));
/*
* sqlSessionFactoryBean.setPlugins(new Interceptor[]{new
* AparamsInterceptor(),new ResultInterceptor(), new ParameterInterceptor(),new
* StatementInterceptor()});
*/
return sqlSessionFactoryBean.getObject();
}
定义的分页参数
package com.cat.common.bean;
public interface PageConstants {
public static final String PAGE_TAG_FOR_CHOOSE_AUTH = "pageTag";
// 当前页
public static final String PAGE_SKIP ="skip";
// 页记录数
public static final String PAGE_MAX ="max";
// 起始记录数
public static final String PAGE_START_ROW ="startRow";
// 结束记录数
public static final String PAGE_END_ROW ="endRow";
public static final String COUNT_SUBFIX ="countSubfix";
// 合计SQL名称定义的结尾部分
public static final String GET_ROW_COUNT = "get_row_count";
public static final Integer PAGE_MAX_COUNT = 20;
public static final String ORACLE_START ="oracleStart";
public static final String ORACLE_END ="oracleEnd";
public static final String MYSQL_START ="mysqlStart";
}
xml 里面配置
<sql id="page_where_sql">
where 1=1
<if test="name != null and name !=''">
AND name like CONCAT('%',#{name},'%')
</if>
order by STATUS DESC, CREATE_TIME DESC
</sql>
<select id="findPageList" resultMap="adCompanyMap" parameterType="hashMap">
select * from ad_company t
<include refid="page_where_sql" />
<if test="mysqlStart != null">
limit #{mysqlStart}, #{max}
</if>
</select>
//分页查询语句需要自己写 添加好 limit.其实也可以在代码里 自动添加
// 查询数据总数的 申请了 语句，
<select id="findPageList_get_row_count" resultType="Integer">
SELECT count(t.id) FROM ad_company t
<include refid="page_where_sql" />
</select>
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
