由于业务关系 巴拉巴拉巴拉
好吧 简单来说就是
原来的业务是 需要再实现类里写 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 NULLAUTO_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
packagelqb.bean;public class User extendsCommon{privateString id;privateString name;privateString t1;privateString t2;privateString t3;//省略get set
}
3.02.对应的mapper: UserMapper.java和UserMapper.xml
简单实现下CRUD
public interfaceUserMapper {public User selectByID(intid);public Listselect();public intinsert(User u);public intupdate(User u);public intdelete(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 classUserService {private staticSqlSessionFactory sqlSessionFactory;private staticReader reader;static{try{
reader= Resources.getResourceAsReader("Configuration.xml");
sqlSessionFactory= newSqlSessionFactoryBuilder().build(reader);
}catch(Exception e){
e.printStackTrace();
}
}public staticSqlSessionFactory getSession(){returnsqlSessionFactory;
}
}
4.01 好 然后是重点了
思路: 截获查询的sql 然后拼成 sqlPage和sqlCount 再进行查找取值 然后赋传入对象
所以我们就需要创建一个基础类来让user.java来继承
public classCommon {private intpagesize;private intpageid;private intpagebegin;private intcount;//省略 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 implementsInterceptor {//插件运行的代码,它将代替原有的方法
@Overridepublic Object intercept(Invocation invocation) throwsThrowable {
}//拦截类型StatementHandler
@OverridepublicObject plugin(Object target) {
}
@Overridepublic voidsetProperties(Properties properties) {
}
4.04 首先 设置拦截类型 重写plugin方法
@OverridepublicObject plugin(Object target) {if (target instanceofStatementHandler) {return Plugin.wrap(target, this);
}else{returntarget;
}
}
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);
...publicString 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));
}returnsb.toString();
}publicString concatPageSql(String sql,Common co){
StringBuffer sb=newStringBuffer();
sb.append(sql);
sb.append(" limit ").append(co.getPagebegin()).append(" , ").append(co.getPagesize());returnsb.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 ListselectPage(User u);
5.01 最后是测试了
main...请允许本人的懒 就姑且在main方法测下吧
User u=newUser();
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 结果 略 然后就成功了
下面附上拦截器的代码
packagelqb.interceptor;importjava.util.Properties;importorg.apache.ibatis.executor.resultset.ResultSetHandler;importorg.apache.ibatis.executor.statement.StatementHandler;importorg.apache.ibatis.mapping.BoundSql;importorg.apache.ibatis.mapping.MappedStatement;import org.apache.ibatis.plugin.*;importorg.apache.ibatis.reflection.MetaObject;importorg.apache.ibatis.reflection.SystemMetaObject;import java.sql.*;importlqb.bean.Common;
@Intercepts({
@Signature(type= StatementHandler.class, method = "prepare", args = {Connection.class}),
@Signature(type= ResultSetHandler.class, method = "handleResultSets", args = {Statement.class})
})public class PageInterceptor implementsInterceptor {private static final String SELECT_ID="selectpage";//插件运行的代码,它将代替原有的方法
@Overridepublic Object intercept(Invocation invocation) throwsThrowable {
System.out.println("PageInterceptor -- intercept");if (invocation.getTarget() instanceofStatementHandler) {
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);
}
}returninvocation.proceed();
}/*** 拦截类型StatementHandler*/@OverridepublicObject plugin(Object target) {if (target instanceofStatementHandler) {return Plugin.wrap(target, this);
}else{returntarget;
}
}
@Overridepublic voidsetProperties(Properties properties) {
}publicString 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));
}returnsb.toString();
}publicString concatPageSql(String sql,Common co){
StringBuffer sb=newStringBuffer();
sb.append(sql);
sb.append(" limit ").append(co.getPagebegin()).append(" , ").append(co.getPagesize());returnsb.toString();
}public voidsetPageCount(){
}
}
View Code
最后是下载地址