spring + mybatis 进行拦截分页、分库
来源:互联网 发布:云计算管理系统 编辑:程序博客网 时间:2024/05/16 13:42
http://zxlaiye.iteye.com/blog/1344703
悲催的Bug
与spring结合时,org.mybatis.spring.mapper.MapperScannerConfigurer不支持 <context:property-placeholder />
(mybatis 3.1.1, mybatis-spring 1.1.1)
●结合Spring的配置
●传递多个参数
●分页插件。 有以下缺点:
Page.java
PagingPlugin.java
(mybatis 3.1.1, mybatis-spring 1.1.1)
●结合Spring的配置
- <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
- <property name="dataSource" ref="dataSource"/>
- <property name="typeAliasesPackage" value="xx.xx.xx.entity" />
- <property name="plugins">
- <list>
- <!-- 配置自己实现的分页插件 -->
- <bean class="xx.xx.xx.mybatis.PagingPlugin">
- <property name="dialect" value="mysql"/>
- </bean>
- </list>
- </property>
- </bean>
- <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
- <property name="basePackage" value="xx.xx.xx.dao" />
- </bean>
●传递多个参数
- //使用@Param来注解,例如:
- List queryXX(@Param("arg1") int arg1, @Param("arg2") String arg2);
●分页插件。 有以下缺点:
- 暂时只实现mysql和oracle
- oracle未测试
- 未考虑取总数的性能
- 未考虑排序
- 查找的结果集未能自动放到分页对象(Page)中
Page.java
- public class Page{
- private int limit = 20; //每页显示条数
- private int start = 0; //起始行号
- private long total = -1; //总数
- private List result = new ArrayList(); //结果集
- //---- 省略get set ----//
- }
PagingPlugin.java
- /**
- * Mybatis的分页查询插件,通过拦截StatementHandler的prepare方法来实现。
- * 只有在参数列表中包括Page类型的参数时才进行分页查询。
- * 在多参数的情况下,只对第一个Page类型的参数生效。
- * 另外,在参数列表中,Page类型的参数无需用@Param来标注
- * @author linzongxue 2012-1-16(修改)
- *
- */
- @Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})
- public class PagingPlugin implements Interceptor {
- private String dialect;
- @SuppressWarnings("unchecked")
- @Override
- public Object intercept(Invocation invocation) throws Throwable {
- if(!(invocation.getTarget() instanceof RoutingStatementHandler))
- return invocation.proceed();
- RoutingStatementHandler statementHandler = (RoutingStatementHandler)invocation.getTarget();
- BoundSql boundSql = statementHandler.getBoundSql();
- //分析是否含有分页参数,如果没有则不是分页查询
- //注意:在多参数的情况下,只处理第一个分页参数
- Page page = null;
- Object paramObj = boundSql.getParameterObject();
- if (paramObj instanceof Page){ //只有一个参数的情况
- page = (Page)paramObj;
- }
- else if (paramObj instanceof Map){ //多参数的情况,找到第一个Page的参数
- for (Map.Entry<String, Object> e : ((Map<String, Object>)paramObj).entrySet()){
- if (e.getValue() instanceof Page){
- page = (Page)e.getValue();
- break;
- }
- }
- }
- if (page == null) return invocation.proceed();
- //查找总记录数,并设置Page的相关参数
- long total = this.getTotal(invocation);
- page.setTotal(total);
- //生成分页SQL
- String pageSql = generatePageSql(boundSql.getSql(), page);
- //强制修改最终要执行的SQL
- setFieldValue(boundSql, "sql", pageSql);
- return invocation.proceed();
- }
- /**
- * 获取记录总数
- */
- @SuppressWarnings("unchecked")
- private long getTotal(Invocation invocation) throws Exception{
- RoutingStatementHandler statementHandler = (RoutingStatementHandler)invocation.getTarget();
- BoundSql boundSql = statementHandler.getBoundSql();
- /*
- * 为了设置查找总数SQL的参数,必须借助MappedStatement、Configuration等这些类,
- * 但statementHandler并没有开放相应的API,所以只好用反射来强行获取。
- */
- BaseStatementHandler delegate = (BaseStatementHandler)getFieldValue(statementHandler, "delegate");
- MappedStatement mappedStatement = (MappedStatement)getFieldValue(delegate, "mappedStatement");
- Configuration configuration = mappedStatement.getConfiguration();
- TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
- Object param = boundSql.getParameterObject();
- MetaObject metaObject = configuration.newMetaObject(param);
- long total = 0;
- String sql = boundSql.getSql();
- String countSql = "select count(1) from (" + sql+ ") as t"; //记录统计 (mysql要求必须添加 最后的as t)
- try{
- Connection conn = (Connection)invocation.getArgs()[0];
- PreparedStatement ps = conn.prepareStatement(countSql);
- int i = 1;
- for (ParameterMapping pm : boundSql.getParameterMappings()) {
- Object value = null;
- String propertyName = pm.getProperty();
- PropertyTokenizer prop = new PropertyTokenizer(propertyName);
- if (typeHandlerRegistry.hasTypeHandler(param.getClass())) {
- value = param;
- }
- else if (boundSql.hasAdditionalParameter(propertyName)) {
- value = boundSql.getAdditionalParameter(propertyName);
- }
- else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX)&& boundSql.hasAdditionalParameter(prop.getName())) {
- value = boundSql.getAdditionalParameter(prop.getName());
- if (value != null) {
- value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length()));
- }
- } else {
- value = metaObject.getValue(propertyName);
- }
- pm.getTypeHandler().setParameter(ps, i++, value, pm.getJdbcType());
- }
- ResultSet rs = ps.executeQuery();
- rs.next();
- total = rs.getLong(1);
- rs.close();
- ps.close();
- }
- catch (Exception e){
- throw new RuntimeException("分页查询无法获取总记录数", e);
- }
- return total;
- }
- /**
- * 生成分页SQL
- */
- private String generatePageSql(String sql, Page page){
- StringBuilder pageSql = new StringBuilder();
- if("mysql".equals(dialect)){
- pageSql.append(sql);
- pageSql.append(" limit ").append(page.getStart()).append(",").append(page.getLimit());
- }
- else if("oracle".equals(dialect)){
- pageSql.append("select * from (select t.*, ROWNUM num from (")
- .append(sql).append(") as t where ROWNUM <= ")
- .append(page.getStart() + page.getLimit())
- .append(") where num > ").append(page.getStart());
- }
- else{
- throw new RuntimeException("分页插件还不支持数据库类型:" + dialect);
- }
- return pageSql.toString();
- }
- /**
- * 用反射取对象的属性值
- */
- private Object getFieldValue(Object obj, String fieldName) throws Exception{
- for (Class<?> superClass = obj.getClass(); superClass != Object.class; superClass = superClass.getSuperclass()) {
- try{
- Field field = superClass.getDeclaredField(fieldName);
- field.setAccessible(true);
- return field.get(obj);
- }
- catch(Exception e){}
- }
- return null;
- }
- /**
- * 用反射设置对象的属性值
- */
- private void setFieldValue(Object obj, String fieldName, Object fieldValue) throws Exception{
- Field field = obj.getClass().getDeclaredField(fieldName);
- field.setAccessible(true);
- field.set(obj, fieldValue);
- }
- @Override
- public Object plugin(Object target) {
- return Plugin.wrap(target, this);
- }
- @Override
- public void setProperties(Properties props) {
- }
- public void setDialect(String dialect){
- this.dialect = dialect.toLowerCase();
- }
- public String getDialect(){
- return this.dialect;
- }
- }
maven笔记 | 夜半闲聊
- 2012-01-13 13:53
- 浏览 5686
- 评论(2)
- 收藏
- 分类:编程语言
- 相关推荐
0 0
- spring + mybatis 进行拦截分页、分库
- mybatis分页拦截器
- Mybatis - 分页拦截器
- MyBatis分页拦截
- Mybatis拦截器分页
- mybatis分页拦截器
- MyBatis拦截器分页
- Mybatis分页拦截器
- Mybatis分页拦截器
- Mybatis拦截器,分页
- mybatis分页拦截
- Mybatis拦截器分页
- MyBatis拦截器分页
- mybatis 拦截器分页
- Mybatis拦截器分页
- Spring Boot 集成mybatis的分页拦截器:PageHelper
- Spring+SpringMVC+mybatis+easyui整合实例(五)使用mybatis拦截器分页
- Spring+SpringMVC+mybatis+easyui整合实例----使用mybatis拦截器分页
- Oracle11g数据库初始化脚本
- poj1416 Shredding Company dfs 记录路径
- Linux Supervisor
- 学习笔记之JavaSE(8)--Java基础语法7
- Unable to instantiate application com.android.tools.fd.runtime.BootstrapApplication的错误解决
- spring + mybatis 进行拦截分页、分库
- linux进程高cpu问题分析
- lua 继承、重新定义
- 小试Android中使用MVC框架模式
- linux物理内存一直占满的问题
- 腾讯云环境搭建:Centos6.6+jdk1.8+mysql5.1+tomcat7
- getsockname函数与getpeername函数的使用
- https
- cocos2d-x-2.1.4 调用tidy html库