mybatis插件的使用,新增了两个类,一个是Interceptor,负责拦截并处理数据,一个是PageParams,作为入参
使用方式:
1.配置mybatis的插件
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"></property>
<property name="mapperLocations" value="classpath:sqlConfig/*.xml"></property><!-- 扫描所有的xml文件 -->
<property name="plugins">
<array>
<bean class="com.*.*.*.PagePlugin"></bean>
</array>
</property>
</bean>
2. 在dao中将入参设置为PageParams,例如
List<UserPwd> selectUserPwdByPage(PageParams<UserPwd> param);
3.调用
PageParams<UserPwd> pp = new PageParams<UserPwd>();
pp.setPage(1);
pp.setPageSize(10);
UserPwd userPwd = new UserPwd();
userPwd.setUserId("123");
pp.setParam(userPwd);
List<UserPwd> list = userPwdDao.selectUserPwdByPage(pp);
System.out.println("查出数据"+list.size());
System.out.println(pp);
查询结果为list,总数和总页数在pp中
4.sql
查询参数放在param中,sql中通过param取出
select <include refid="fields" /> from user_pwd
<where>
<if test="param.userId != null">
user_id = #{param.userId}
</if>
</where>
5. 代码
Interceptor
@Intercepts({
@Signature(type = StatementHandler.class,
method = "prepare",
args = {Connection.class})
})
public class PagePlugin implements Interceptor {
private Integer defaultPage;
private Integer defaultPageSize;
private Boolean defaultUseFlag;
private Boolean defaultCheckFlag;
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler stmtHandler = getUnProxyObject(invocation);
MetaObject metaStatementHandler = SystemMetaObject.forObject(stmtHandler);
String sql = (String) metaStatementHandler.getValue("delegate.boundSql.sql");
//不是select语句
if(!checkSelect(sql)) {
return invocation.proceed();
}
BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
Object parameterObject = boundSql.getParameterObject();
PageParams pageParams = getPageParams(parameterObject);
//没有分页参数,不启用插件
if(pageParams == null) {
return invocation.proceed();
}
//获取分页参数,获取不到时候使用默认值
Integer pageNum = pageParams.getPage() == null ?
this.defaultPage : pageParams.getPage();
Integer pageSize = pageParams.getPageSize() == null ?
this.defaultPageSize : pageParams.getPageSize();
Boolean useFlag = pageParams.getUseFlag() == null ?
this.defaultUseFlag : pageParams.getUseFlag();
Boolean checkFlag = pageParams.getCheckFlag() == null ?
this.defaultCheckFlag : pageParams.getCheckFlag();
//不适用分页插件
if(!useFlag) {
return invocation.proceed();
}
int total = getTotal(invocation, metaStatementHandler, boundSql);
//回填总数到分页参数里
setTotalToPageParams(pageParams, total, pageSize);
//检查当前页码的有效性
checkPage(checkFlag, pageNum, pageParams.getTotalPage());
//修改sql
return changeSql(invocation, metaStatementHandler, boundSql, pageNum, pageSize);
}
@Override
public Object plugin(Object statementHandler) {
return Plugin.wrap(statementHandler, this);
}
@Override
public void setProperties(Properties props) {
String strDefaultPage = props.getProperty("default.page", "1");
String strDefaultPageSize = props.getProperty("default.pageSize", "50");
String strDefaultUseFlag = props.getProperty("default.useFlag", "false");
String strDefaultCheckFlag = props.getProperty("default.checkFlag", "false");
this.defaultPage = Integer.parseInt(strDefaultPage);
this.defaultPageSize = Integer.parseInt(strDefaultPageSize);
this.defaultUseFlag = Boolean.parseBoolean(strDefaultUseFlag);
this.defaultCheckFlag = Boolean.parseBoolean(strDefaultCheckFlag);
}
private StatementHandler getUnProxyObject(Invocation ivt) {
StatementHandler statementHandler = (StatementHandler) ivt.getTarget();
MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);
Object object = null;
while(metaStatementHandler.hasGetter("h")) {
object = metaStatementHandler.getValue("h");
}
if(object == null) {
return statementHandler;
}
return (StatementHandler) object;
}
private boolean checkSelect(String sql) {
String trimSql = sql.trim();
int idx = trimSql.toLowerCase().indexOf("select");
return idx == 0;
}
/*
* **********分解分页参数,这里支持使用Map和@Param注解传递参数,或者pojo继承PageParams,这三种方式都可以
* 只拦截入参是PageParams的
*/
private PageParams getPageParams(Object parameterObject) {
if(parameterObject == null) {
return null;
}
PageParams pageParams = null;
// if(parameterObject instanceof Map) {
// Map<String, Object> paramMap = (Map<String, Object>) parameterObject;
// Set<String> keySet = paramMap.keySet();
// Iterator<String> it = keySet.iterator();
// while(it.hasNext()) {
// String key = it.next();
// Object value = paramMap.get(key);
// if(value instanceof PageParams) {
// return (PageParams) value;
// }
// }
// }else if(parameterObject instanceof PageParams) {
// pageParams = (PageParams) parameterObject;
// }
if(parameterObject instanceof PageParams) {
pageParams = (PageParams) parameterObject;
}
return pageParams;
}
private int getTotal(Invocation invocation, MetaObject metaStatementHandler, BoundSql boundSql) throws SQLException {
MappedStatement mappedStatement = (MappedStatement)
metaStatementHandler.getValue("delegate.mappedStatement");
Configuration cfg = mappedStatement.getConfiguration();
String sql = (String) metaStatementHandler.getValue("delegate.boundSql.sql");
String countSql = "select count(*) as total from ("+sql+") a";
Connection connection = (Connection) invocation.getArgs()[0];
PreparedStatement ps = null;
int total = 0;
try {
ps = connection.prepareStatement(countSql);
BoundSql countBoundSql = new BoundSql(cfg, countSql,
boundSql.getParameterMappings(),
boundSql.getParameterObject());
ParameterHandler handler = new DefaultParameterHandler(mappedStatement,
boundSql.getParameterObject(), countBoundSql);
handler.setParameters(ps);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
total = rs.getInt("total");
}
}finally {
if(ps != null) {
ps.close();
}
}
return total;
}
private void setTotalToPageParams(PageParams pageParams, int total, Integer pageSize) {
pageParams.setTotal(total);
int totalPage = total % pageSize == 0 ? total / pageSize : total / pageSize +1;
pageParams.setTotalPage(totalPage);
}
private void checkPage(Boolean checkFlag, Integer pageNum, Integer totalPage) throws Exception {
if(checkFlag) {
if(pageNum > totalPage) {
//throw new Exception("查询时报,查询页码【"+pageNum+"】大于总页数【"+totalPage+"】!");
pageNum = totalPage;
}
}
}
private Object changeSql(Invocation invocation, MetaObject metaStatementHandler, BoundSql boundSql, Integer pageNum,
Integer pageSize) throws Exception {
String sql = (String) metaStatementHandler.getValue("delegate.boundSql.sql");
String newSql = "select a.* from ("+sql+") a limit ?, ?";
metaStatementHandler.setValue("delegate.boundSql.sql", newSql);
PreparedStatement ps = (PreparedStatement) invocation.proceed();
int count = ps.getParameterMetaData().getParameterCount();
ps.setInt(count-1, (pageNum-1)*pageSize);
ps.setInt(count, pageSize);
return ps;
}
}
PageParams
public class PageParams <T>{
private Integer page;//当前页码
private Integer pageSize;//每页条数
private Boolean useFlag = true;//是否启用插件
private Boolean CheckFlag = true;//是否检测当前页码的有效性
private Integer total;//当前sql返回总数,插件回填
private Integer totalPage;//sql以当前分页的总页数,插件回填
private Object param;//查询参数
private List<T> resultList;//返回参数,可以把结果直接设置进来,直接返回
public Object getParam() {
return param;
}
public void setParam(Object param) {
this.param = param;
}
public List<T> getResultList() {
return resultList;
}
public void setResultList(List<T> resultList) {
this.resultList = resultList;
}
public Integer getPage() {
return page;
}
public void setPage(Integer page) {
this.page = page;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Boolean getUseFlag() {
return useFlag;
}
public void setUseFlag(Boolean useFlag) {
this.useFlag = useFlag;
}
public Boolean getCheckFlag() {
return CheckFlag;
}
public void setCheckFlag(Boolean checkFlag) {
CheckFlag = checkFlag;
}
public Integer getTotal() {
return total;
}
public void setTotal(Integer total) {
this.total = total;
}
public Integer getTotalPage() {
return totalPage;
}
public void setTotalPage(Integer totalPage) {
this.totalPage = totalPage;
}
@Override
public String toString() {
return "PageParams [page=" + page + ", pageSize=" + pageSize + ", useFlag=" + useFlag + ", CheckFlag="
+ CheckFlag + ", total=" + total + ", totalPage=" + totalPage + "]";
}
}