MyBatis拦截器实现分页功能实例

MyBatis拦截器实现分页功能实例

由于业务关系 巴拉巴拉巴拉

好吧 简单来说就是

原来的业务是 需要再实现类里写 selectCount 和selectPage两个方法才能实现分页功能

现在想要达到效果是 只通过一个方法就可以实现 也就是功能合并 所以就有了下面的实践

既然是基于MyBatis 所以就先搭建一个Mybatis的小项目

1.01导入 mybatis和mysql的包

1.02.配置文件 Configuration.xml 中添加

2.01.然后创建一个模块user  创建user表

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` char(32) NOT NULL,

`t1` char(32) DEFAULT NULL,

`t2` char(32) DEFAULT NULL,

`t3` char(32) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

3.01.写对应bean：User.java

package lqb.bean;

public class User extends Common{

private String id;

private String name;

private String t1;

private String t2;

private String t3;

//省略get set

}

3.02.对应的mapper： UserMapper.java和UserMapper.xml

简单实现下CRUD

public interface UserMapper {

public User selectByID(int id);

public List select();

public int insert(User u);

public int update(User u);

public int delete(User u);

}

select * from `user` where id = #{id}

select * from `user`

insert into user (id,name,t1,t2,t3) values (#{id},#{name},#{t1},#{t2},#{t3})

update user set name=#{name},t1=#{t1},t2=#{t2},t3=#{t3} where id=#{id}

delete from user where id=#{id}

3.03.然后 在配置文件Configuration.xml中添加user的配置

3.04.然后是实现：UserService.java

public class UserService {

private static SqlSessionFactory sqlSessionFactory;

private static Reader reader;

static{

try{

reader = Resources.getResourceAsReader("Configuration.xml");

sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);

}catch(Exception e){

e.printStackTrace();

}

}

public static SqlSessionFactory getSession(){

return sqlSessionFactory;

}

}

4.01 好 然后是重点了

思路： 截获查询的sql 然后拼成 sqlPage和sqlCount 再进行查找取值 然后赋传入对象

所以我们就需要创建一个基础类来让user.java来继承

public class Common {

private int pagesize;

private int pageid;

private int pagebegin;

private int count;

//省略 get set

}

4.02 然后 让User继承Common

public class User extends Common{

4.03 那怎么截获sql呢 我们就要写一个mybatis的拦截器 用来拦截sql请求 PageInterceptor

@Intercepts({

@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class}),

@Signature(type = ResultSetHandler.class, method = "handleResultSets", args = {Statement.class})

})

public class PageInterceptor implements Interceptor {

　　//插件运行的代码，它将代替原有的方法

@Override

public Object intercept(Invocation invocation) throws Throwable {

}

// 拦截类型StatementHandler

@Override

public Object plugin(Object target) {

}

@Override

public void setProperties(Properties properties) {

}

4.04 首先 设置拦截类型 重写plugin方法

@Override

public Object plugin(Object target) {

if (target instanceof StatementHandler) {

return Plugin.wrap(target, this);

} else {

return target;

}

}

4.05 然后 就要重写最重要的intercept了

这里我们有一个设定  如果查询方法含有searchpage 就进行分页 其他方法无视

所以就要获取方法名

StatementHandler statementHandler = (StatementHandler) invocation.getTarget();

MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);

MappedStatement mappedStatement=(MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");

String selectId=mappedStatement.getId();

4.06 然后判断下 如果含有searchpage 就获取sql

BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");

// 分页参数作为参数对象parameterObject的一个属性

String sql = boundSql.getSql();

Common co=(Common)(boundSql.getParameterObject());

4.07 然后 根据这个sql 重新拼写countsql和pagesql

String countSql=concatCountSql(sql);

String pageSql=concatPageSql(sql,co);

...

public String concatCountSql(String sql){

StringBuffer sb=new StringBuffer("select count(*) from ");

sql=sql.toLowerCase();

if(sql.lastIndexOf("order")>sql.lastIndexOf(")")){

sb.append(sql.substring(sql.indexOf("from")+4, sql.lastIndexOf("order")));

}else{

sb.append(sql.substring(sql.indexOf("from")+4));

}

return sb.toString();

}

public String concatPageSql(String sql,Common co){

StringBuffer sb=new StringBuffer();

sb.append(sql);

sb.append(" limit ").append(co.getPagebegin()).append(" , ").append(co.getPagesize());

return sb.toString();

}

4.08 然后 通过jdbc查询count 然后把值绑定给common

Connection connection = (Connection) invocation.getArgs()[0];

PreparedStatement countStmt = null;

ResultSet rs = null;

int totalCount = 0;

try {

countStmt = connection.prepareStatement(countSql);

rs = countStmt.executeQuery();

if (rs.next()) {

totalCount = rs.getInt(1);

}

} catch (SQLException e) {

System.out.println("Ignore this exception"+e);

} finally {

try {

rs.close();

countStmt.close();

} catch (SQLException e) {

System.out.println("Ignore this exception"+ e);

}

}

//绑定count

co.setCount(totalCount);

4.09 再把pagesql赋给元BoundSql

metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);

4.10 最后在配置文件中添加拦截器配置

4.11 好然后 在UserMapper.java和UserMapper.xml中添加分页代码

select * from `user` where id in(3,4,6,8) order by id

public List selectPage(User u);

5.01 最后是测试了

main...请允许本人的懒 就姑且在main方法测下吧

User u=new User();

u.setPagebegin(2);

u.setPagesize(3);

System.out.println("-u.getCount()------"+u.getCount());

List l=userService.selectPage(u);

System.out.println(l.size());

System.out.println("-u.getCount()------"+u.getCount());

5.02 结果 略   然后就成功了

下面附上拦截器的代码

package lqb.interceptor;

import java.util.Properties;

import org.apache.ibatis.executor.resultset.ResultSetHandler;

import org.apache.ibatis.executor.statement.StatementHandler;

import org.apache.ibatis.mapping.BoundSql;

import org.apache.ibatis.mapping.MappedStatement;

import org.apache.ibatis.plugin.*;

import org.apache.ibatis.reflection.MetaObject;

import org.apache.ibatis.reflection.SystemMetaObject;

import java.sql.*;

import lqb.bean.Common;

@Intercepts({

@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class}),

@Signature(type = ResultSetHandler.class, method = "handleResultSets", args = {Statement.class})

})

public class PageInterceptor implements Interceptor {

private static final String SELECT_ID="selectpage";

//插件运行的代码，它将代替原有的方法

@Override

public Object intercept(Invocation invocation) throws Throwable {

System.out.println("PageInterceptor -- intercept");

if (invocation.getTarget() instanceof StatementHandler) {

StatementHandler statementHandler = (StatementHandler) invocation.getTarget();

MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);

MappedStatement mappedStatement=(MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");

String selectId=mappedStatement.getId();

if(SELECT_ID.equals(selectId.substring(selectId.lastIndexOf(".")+1).toLowerCase())){

BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");

// 分页参数作为参数对象parameterObject的一个属性

String sql = boundSql.getSql();

Common co=(Common)(boundSql.getParameterObject());

// 重写sql

String countSql=concatCountSql(sql);

String pageSql=concatPageSql(sql,co);

System.out.println("重写的 count sql :"+countSql);

System.out.println("重写的 select sql :"+pageSql);

Connection connection = (Connection) invocation.getArgs()[0];

PreparedStatement countStmt = null;

ResultSet rs = null;

int totalCount = 0;

try {

countStmt = connection.prepareStatement(countSql);

rs = countStmt.executeQuery();

if (rs.next()) {

totalCount = rs.getInt(1);

}

} catch (SQLException e) {

System.out.println("Ignore this exception"+e);

} finally {

try {

rs.close();

countStmt.close();

} catch (SQLException e) {

System.out.println("Ignore this exception"+ e);

}

}

metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);

//绑定count

co.setCount(totalCount);

}

}

return invocation.proceed();

}

/**

* 拦截类型StatementHandler

*/

@Override

public Object plugin(Object target) {

if (target instanceof StatementHandler) {

return Plugin.wrap(target, this);

} else {

return target;

}

}

@Override

public void setProperties(Properties properties) {

}

public String concatCountSql(String sql){

StringBuffer sb=new StringBuffer("select count(*) from ");

sql=sql.toLowerCase();

if(sql.lastIndexOf("order")>sql.lastIndexOf(")")){

sb.append(sql.substring(sql.indexOf("from")+4, sql.lastIndexOf("order")));

}else{

sb.append(sql.substring(sql.indexOf("from")+4));

}

return sb.toString();

}

public String concatPageSql(String sql,Common co){

StringBuffer sb=new StringBuffer();

sb.append(sql);

sb.append(" limit ").append(co.getPagebegin()).append(" , ").append(co.getPagesize());

return sb.toString();

}

public void setPageCount(){

}

}

最后是下载地址：mybatisResolve_jb51.rar

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。