pagehelper / mybatis-pagehelper Goto Github PK
View Code? Open in Web Editor NEWMybatis通用分页插件
Home Page: https://mybatis.io
License: MIT License
Mybatis通用分页插件
Home Page: https://mybatis.io
License: MIT License
在一个循环中,从第一页查询到最后一页,在这个过程中,将查询出来的结果执行update操作,导致分页查询的记录数不准确。如果不执行update操作,就能准确查出所有记录,请问这种情况只能手动写分页了吗?
想支持Teradata数据库的分页,如何自定义一个Parser
我遇到当使用 PageHelper.startPage(1, 10); 的方式进行分页查询时,会爆出 :
数据库是 SQL Server 2014,helperDialect 设置为 sqlserver2012
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid usage of the option NEXT in the FETCH statement.
PageHelper自动生成的:
SELECT
[num]
FROM vw_app_query_1
WHERE (num LIKE '%' + ? + '%')
ORDER BY [num]
FETCH NEXT 10 ROWS ONLY;
可以正确执行的:
SELECT
[num]
FROM vw_app_query_1
WHERE (num LIKE '%' + '32' + '%')
ORDER BY [num]
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
主要是缺少了 OFFSET 0 ROWS
使用javaconfig方式配置的话,应该怎么配置,不用xml
错误信息
org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'distinct' in 'class com.dhcc.mis.pub.product.entity.ProductEntity'
at org.apache.ibatis.reflection.Reflector.getGetInvoker(Reflector.java:381)
at org.apache.ibatis.reflection.MetaClass.getGetInvoker(MetaClass.java:164)
at org.apache.ibatis.reflection.wrapper.BeanWrapper.getBeanProperty(BeanWrapper.java:162)
at org.apache.ibatis.reflection.wrapper.BeanWrapper.get(BeanWrapper.java:49)
at org.apache.ibatis.reflection.MetaObject.getValue(MetaObject.java:122)
at org.apache.ibatis.scripting.xmltags.DynamicContext$ContextMap.get(DynamicContext.java:94)
at org.apache.ibatis.scripting.xmltags.DynamicContext$ContextAccessor.getProperty(DynamicContext.java:108)
at org.apache.ibatis.ognl.OgnlRuntime.getProperty(OgnlRuntime.java:2420)
at org.apache.ibatis.ognl.ASTProperty.getValueBody(ASTProperty.java:114)
at org.apache.ibatis.ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212)
at org.apache.ibatis.ognl.SimpleNode.getValue(SimpleNode.java:258)
at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:494)
at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:458)
at org.apache.ibatis.scripting.xmltags.OgnlCache.getValue(OgnlCache.java:44)
at org.apache.ibatis.scripting.xmltags.ExpressionEvaluator.evaluateBoolean(ExpressionEvaluator.java:32)
at org.apache.ibatis.scripting.xmltags.IfSqlNode.apply(IfSqlNode.java:34)
at org.apache.ibatis.scripting.xmltags.MixedSqlNode.apply(MixedSqlNode.java:33)
at org.apache.ibatis.scripting.xmltags.DynamicSqlSource.getBoundSql(DynamicSqlSource.java:41)
at org.apache.ibatis.mapping.MappedStatement.getBoundSql(MappedStatement.java:279)
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:80)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy77.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:120)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:113)
at sun.reflect.GeneratedMethodAccessor360.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:354)
at com.sun.proxy.$Proxy22.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:194)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:122)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:64)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
at com.sun.proxy.$Proxy37.selectByExample(Unknown Source)
at com.dhcc.mis.pub.product.service.ProductService.getProductList(ProductService.java:35)
调用:
PageHelper.offsetPage(prod.getStart(),prod.getLength());
List<ProductEntity> list = mapper.selectByExample(prod);
public String selectByExample(MappedStatement ms) {
Class<?> entityClass = getEntityClass(ms);
//将返回值修改为实体类型
setResultType(ms, entityClass);
StringBuilder sql = new StringBuilder("SELECT ");
sql.append("<if test=\"distinct\">distinct</if>");
//支持查询指定列
这里 我的ProductEntity没有distinct 这里会报错
使用版本:
pagehelper-5.0.0.jar
mybatis-3.3.1.jar
mapper-3.3.6.jar
该方法未使用分页。打断点看了下类PageInterceptor中intercept方法被调用了2次
第一次的时候 dialect 的值为PageHelper
第二次dialet的值就为空了。所以在finaly里面调用afterAll 时报空指针
Hi
I am trying to add PageHelper to my [Sping - SpringMVC - MyBatis] project.
But some errors happened. Can you tell me why. Thanks in advance.
五月 02, 2017 11:40:35 下午 org.apache.catalina.core.StandardContext listenerStart
严重: Exception sending context initialized event to listener instance of class org.springframework.web.context.ContextLoaderListener
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sqlSessionFactory' defined in file [D:\Software\apache-tomcat-8.0.36\webapps\joker\WEB-INF\classes\config\spring\applicationContext-dao.xml]: Invocation of init method failed; nested exception is org.springframework.core.NestedIOException: Failed to parse config resource: class path resource [config/mybatis/SqlMapConfig.xml]; nested exception is org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: java.lang.ClassCastException: com.github.pagehelper.PageHelper cannot be cast to org.apache.ibatis.plugin.Interceptor
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1583)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:545)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:732)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:861)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:541)
at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:444)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:326)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:107)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4842)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5303)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:147)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:725)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:701)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717)
at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1092)
at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1834)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:744)
Caused by: org.springframework.core.NestedIOException: Failed to parse config resource: class path resource [config/mybatis/SqlMapConfig.xml]; nested exception is org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: java.lang.ClassCastException: com.github.pagehelper.PageHelper cannot be cast to org.apache.ibatis.plugin.Interceptor
at org.mybatis.spring.SqlSessionFactoryBean.buildSqlSessionFactory(SqlSessionFactoryBean.java:499)
at org.mybatis.spring.SqlSessionFactoryBean.afterPropertiesSet(SqlSessionFactoryBean.java:381)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1642)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1579)
... 25 more
Caused by: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: java.lang.ClassCastException: com.github.pagehelper.PageHelper cannot be cast to org.apache.ibatis.plugin.Interceptor
at org.apache.ibatis.builder.xml.XMLConfigBuilder.parseConfiguration(XMLConfigBuilder.java:120)
at org.apache.ibatis.builder.xml.XMLConfigBuilder.parse(XMLConfigBuilder.java:98)
at org.mybatis.spring.SqlSessionFactoryBean.buildSqlSessionFactory(SqlSessionFactoryBean.java:493)
... 28 more
Caused by: java.lang.ClassCastException: com.github.pagehelper.PageHelper cannot be cast to org.apache.ibatis.plugin.Interceptor
at org.apache.ibatis.builder.xml.XMLConfigBuilder.pluginElement(XMLConfigBuilder.java:182)
at org.apache.ibatis.builder.xml.XMLConfigBuilder.parseConfiguration(XMLConfigBuilder.java:109)
... 30 more
五月 02, 2017 11:40:35 下午 org.apache.catalina.core.ApplicationContext log
this is mybatis config file:
this is the DAO config file for spring
<!-- 数据库连接池 -->
<!-- 加载配置文件 -->
<context:property-placeholder location="classpath:config/resource/database.properties" />
<!-- 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
destroy-method="close">
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="driverClassName" value="${jdbc.driver}" />
<property name="maxActive" value="10" />
<property name="minIdle" value="5" />
</bean>
<!-- 配置sqlsessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="configLocation" value="classpath:config/mybatis/SqlMapConfig.xml"></property>
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- 配置扫描包,加载mapper代理对象 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.joker.mapper"></property>
</bean>
Mybatis-PageHelper 怎么可以使用AOP监控到执行的SQL语句?
public PageInfo<T> byPage(Integer pageNo, Integer pageSize, List<T> llist)
{
pageNo = pageNo == null ? 1 : pageNo;
pageSize = pageSize == null ? 10 : pageSize;
PageHelper.startPage(pageNo, pageSize);
List<T> list = llist ;
return new PageInfo<T>(list);
}
使用版本pagehelper-4.1.3
重现:
1.使用分页查询某条数据 假设在Mapper里面 id="selectA" (此处会改变 id="selectA" 的sqlSource 为PageDynamicSqlSource)
2.使用分页查询另外一条数据,但是其resultMap里面包含了selectA
如:
<resultMap id="ResultMap" type="XXXForm" >
<collection property="pros" column="Id=prosID" select="selectA" />
</resultMap>
在mybatis在查出主表的数据之后,会执行查询属性pros的语句selectA,在 PageSqlSource.getBoundSql方法里面获取SQL的时候就会将selectA也当作是需要分页查询的语句
原因是getCount()获取的是线程变量Page的countSignal属性(count信号,3种情况,null的时候执行默认BoundSql,true的时候执行count,false执行分页)
然后这里实际上是不需要分页查询的,所以再获取 First_PageHelper 的时候会报找不到 First_PageHelper 属性的异常。
请教一下,这个bug在后续的版本有修复吗?
数据库:SQLSERVER2008
Pagehelper版本:4和5
SQL:select a.id from table1 a with(nolock) left join table2 b with(nolock) on a.id = b.rid where a.name = 'xxx'
执行问题:
执行SQL会解析成:select count(0) from table1 a with(nolock) left join table2 b with(nolock) where a.name = 'xxx'
报错信息:where附近语法错误
补充,解析后的SQL中丢失了on 及后面的关联条件
请问如何解决该bug,谢谢!万分感谢!
支持人数达到一定程度后,我会考虑。
不需要或者不支持的不建议留言!
谢谢大家的回复,计划在5.1版本增加以前的排序功能,在这中间可能还有1,2个5.0.x的小版本。
Caused by: java.lang.NoSuchMethodError: net.sf.jsqlparser.statement.select.Top.setRowCount(J)V
at com.github.pagehelper.parser.SqlServerParser.(SqlServerParser.java:77)
at com.github.pagehelper.dialect.helper.SqlServerDialect.(SqlServerDialect.java:44)
at com.github.pagehelper.dialect.helper.SqlServer2012Dialect.(SqlServer2012Dialect.java:33)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
This is my pom.xml:
com.github.pagehelper
pagehelper
<!--5.0.0 Paging failure,4.1.0 Paging success-->
<version>4.1.0</version>
<!--<version>5.0.0</version>-->
</dependency>
Of cause , pagehelper-spring-boot-starter is ok!Only version 5.0.0 is failure.
val p = PageRowBounds(0, 10)
val users = userDao.pageSearch(p)
users.forEach { println(it.username) }
println(p)
val total = p.total
if (total == null) {
println(0)
} else {
println(total)
}
println("+++++++++++++++++++++++++++++")
输出如下
test1395990298
test-181769175
com.github.pagehelper.PageRowBounds@232bdc3d
0
+++++++++++++++++++++++++++++
why?
使用的版本是4.2.1
在同一个工程的测试类里边运行没问题,用maven的tomcat插件运行的时候会报如下错误
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sqlSessionFactory' defined in file [E:\JAVA\eclipse2017workplace\vcgo-manager\vcgo-manager-service\target\classes\spring\applicationContext-dao.xml]: Invocation of init method failed; nested exception is org.springframework.core.NestedIOException: Failed to parse config resource: class path resource [mybatis/SqlMapConfig.xml]; nested exception is org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.builder.BuilderException: Error resolving class. Cause: org.apache.ibatis.type.TypeException: Could not resolve type alias 'com.github.pagehelper.PageInterceptor'. Cause: java.lang.ClassNotFoundException: Cannot find class: com.github.pagehelper.PageInterceptor
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1578)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:545)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:753)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:839)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:538)
at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:446)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:328)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:107)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4939)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5434)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1559)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1549)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.core.NestedIOException: Failed to parse config resource: class path resource [mybatis/SqlMapConfig.xml]; nested exception is org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.builder.BuilderException: Error resolving class. Cause: org.apache.ibatis.type.TypeException: Could not resolve type alias 'com.github.pagehelper.PageInterceptor'. Cause: java.lang.ClassNotFoundException: Cannot find class: com.github.pagehelper.PageInterceptor
at org.mybatis.spring.SqlSessionFactoryBean.buildSqlSessionFactory(SqlSessionFactoryBean.java:434)
at org.mybatis.spring.SqlSessionFactoryBean.afterPropertiesSet(SqlSessionFactoryBean.java:340)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1637)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1574)
... 21 more
Caused by: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.builder.BuilderException: Error resolving class. Cause: org.apache.ibatis.type.TypeException: Could not resolve type alias 'com.github.pagehelper.PageInterceptor'. Cause: java.lang.ClassNotFoundException: Cannot find class: com.github.pagehelper.PageInterceptor
at org.apache.ibatis.builder.xml.XMLConfigBuilder.parseConfiguration(XMLConfigBuilder.java:109)
at org.apache.ibatis.builder.xml.XMLConfigBuilder.parse(XMLConfigBuilder.java:92)
at org.mybatis.spring.SqlSessionFactoryBean.buildSqlSessionFactory(SqlSessionFactoryBean.java:428)
... 24 more
Caused by: org.apache.ibatis.builder.BuilderException: Error resolving class. Cause: org.apache.ibatis.type.TypeException: Could not resolve type alias 'com.github.pagehelper.PageInterceptor'. Cause: java.lang.ClassNotFoundException: Cannot find class: com.github.pagehelper.PageInterceptor
at org.apache.ibatis.builder.BaseBuilder.resolveClass(BaseBuilder.java:103)
at org.apache.ibatis.builder.xml.XMLConfigBuilder.pluginElement(XMLConfigBuilder.java:142)
at org.apache.ibatis.builder.xml.XMLConfigBuilder.parseConfiguration(XMLConfigBuilder.java:100)
... 26 more
Caused by: org.apache.ibatis.type.TypeException: Could not resolve type alias 'com.github.pagehelper.PageInterceptor'. Cause: java.lang.ClassNotFoundException: Cannot find class: com.github.pagehelper.PageInterceptor
at org.apache.ibatis.type.TypeAliasRegistry.resolveAlias(TypeAliasRegistry.java:117)
at org.apache.ibatis.builder.BaseBuilder.resolveAlias(BaseBuilder.java:130)
at org.apache.ibatis.builder.BaseBuilder.resolveClass(BaseBuilder.java:101)
... 28 more
Caused by: java.lang.ClassNotFoundException: Cannot find class: com.github.pagehelper.PageInterceptor
at org.apache.ibatis.io.ClassLoaderWrapper.classForName(ClassLoaderWrapper.java:190)
at org.apache.ibatis.io.ClassLoaderWrapper.classForName(ClassLoaderWrapper.java:89)
at org.apache.ibatis.io.Resources.classForName(Resources.java:256)
at org.apache.ibatis.type.TypeAliasRegistry.resolveAlias(TypeAliasRegistry.java:113)
... 30 more
求助,找不到原因啊
问题会出在哪
我在开发API的时候,需要给客户端返回json,而且要包括分页信息,但是PageInfo类的信息太多了,我并不想全部给客户端显示,而Page类又无法被转化为json,有没有什么办法解决下?还是说我只能重新写一个类来包装一下
在mapper.xml中已经对相应的枚举类型设置了
typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler
page自动查询count的语句正常输出,但是查询list结果的语句就会提示Object does not represent an enum type.
的异常。dubug的时候,我的两个枚举两次正常进入,不明白的是为什么又进入了第三次传入了一个Object类型的Class对象,理应来说只有两个参数设置了typeHandler,应该进入两次就完了。
如果注释掉分页,直接查询list结果集也是可以正常查询的,所以我斗胆猜测是否是分页引起的bug,麻烦作者检查一下(PS:我用mybatis的时间不是很长,也许是我使用方法有误,也麻烦请告知,请包涵)
我在使用静态方法使下一个select方法分页生效后,得到的list中是po对象,将它们转换为了适用于页面的vo对象list后,放入PageInfo中,也可以分页,但是pageinfo的属性失效了,所以我现在调用两种Service方法,一种返回po,一种封装为vo,因为我只想要PageInfo的分页属性,请问有没有什么好的解决方案?我也在思考中,感谢!
<bean class="com.github.pagehelper.PageInterceptor">
<property name="properties">
<value>
helperDialect=mysql
</value>
</property>
</bean>
必须上面那样写,但是在你文档里面说。不写也可以。默认自动分析来着。。。。。。。。。。大天坑
有时候需要count,有时候又不用,能否PageRowBounds 增加个属性,好控制
5.0 版本 不支持 PageHelper.setOrderBy 方法了?升级后要改好多代码啊
打印出来的sql语句,其中的LIMIT1,5有两次,导致sql报错
select * from t_weike
WHERE `published` = ?
AND `publish_user_id` = ?
AND `deleted` = ?
ORDER BY id desc LIMIT 1,5 LIMIT 1,5
<!--mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>
<!--mybatis 分页插件-->
<!--mapper-->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>1.1.0</version>
</dependency>
<!--pagehelper-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.1.0</version>
</dependency>
<!--mybatis 分页插件 end -->
<!-- mybatis 配置 start -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="mapperLocations" value="classpath:mybatis/*.xml"/>
<property name="plugins">
<array>
<bean class="com.github.pagehelper.PageInterceptor">
<property name="properties">
<value>reasonable=true</value>
</property>
</bean>
</array>
</property>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.benmei.weike.dao"/>
</bean>
<!-- mybatis 配置 end/ -->
PageRowBounds pageRowBounds = new PageRowBounds(1, 5);
List<Weike> list = weikeDao.searchWeike(weike,pageRowBounds);
long total = pageRowBounds.getTotal();
没搞懂是select全部数据自己循环呢还是
有用到SQL的limit,
毕竟没用到的话效率实在是......担心
配置了两个数据源,只有primary的pagehelper有效,另一个无效,应该如何处理?
请教一下,当通用Mapper和通用分页组件同时使用时,如何实现模糊查询?
表里面一共 5 条记录, 我按照每页 2 条分页, 查第一页
{
"endRow": 2,
"firstPage": 1,
"hasNextPage": true,
"hasPreviousPage": false,
"isFirstPage": true,
"isLastPage": false,
"lastPage": 3,
"list": [
...//省略
],
"navigateFirstPage": 1,
"navigateLastPage": 3,
"navigatePages": 8, //?????????为什么是8
"navigatepageNums": [
1,
2,
3
],
"nextPage": 2,
"pageNum": 1,
"pageSize": 2,
"pages": 3,
"prePage": 0,
"size": 2,
"startRow": 1,
"total": 5
}
int offset = 0;
boolean isLast = false;
while (!isLast) {
PageHelper.offsetPage(offset, LIMIT);
PageInfo<Gjc> pageInfo = new PageInfo<>(gjcMapper.getGjcs());
for (Gjc gjc : pageInfo.getList()) {
redisService.set(GJC.concat(gjc.getGjc()), String.valueOf(gjc.getBh()));
}
isLast = pageInfo.isIsLastPage();
offset = offset + LIMIT;
}
代码如上
gjcMapper.getGjcs()
数据库一条数据都没有,取回的时候
isLast = pageInfo.isIsLastPage(); 的标志位始终为false
返回给前端的字段能否配置下,加入我不想返回orderBy这个字段,但是我又不想重新封装下,能不能通过配置解决
在使用mysql5.7版本的fulltext查询时 count语句变成了
select count(0) from (select * from table WHERE MATCH (text
) AGAINST (? IN BOOLEAN MODE)) tmp_count
未使用fulltext全文检索查询时count语句为
SELECT count(0) FROM table
为什么fulltext全文检索的count语句不能直接是
SELECT count(0) FROM table WHERE MATCH (text
) AGAINST (? IN BOOLEAN MODE)) 感觉这样效率更高
PageHelper.startPage(1,5);
mapper.executeSql(String sqlStr);//此sql语句是动态传入的,mapper.xml文件写法是${sqlStr}。执行后确实分页了。
PageHelper.clearPage();
mapper.select1(pagram1);//但是这个普通的select 语句都也被分页了。mapper.xml文件写法是select * from **
mapper.select2(pagram1);//这个普通的select 语句都也被分页了。
mapper.select3(pagram1);//这个普通的select 语句都也被分页了。
而我只想分页一次,即让executeSql分页,不想让select1,select2,select3分页。怎么办?
加上PageHelper.clearPage();也不行。
IDEA 2016.3.3 + JRebel 7.0.2 + SpringBoot 1.4.3 + SpringBoot的插件
这个模式下,Jrebel模式重新编译代码,会导致PageHelper的分页查询直接失效。
目前在多台机器上都100%存在这个问题。
您好,最近在做一个项目,采用Sping+SpringMVC+MyBatis,数据库采用informix,其中也引入了您的开源项目PageHelper。
调用时我观察了一下总条数的查询语句,SELECT count(0) FROM company WHERE 1 = 1 AND companyid = ? 。count(0)的写法在informix数据里是不支持的,所以我在测试时,SQL部分直接报错,
==> Preparing: SELECT count(0) FROM company WHERE 1 = 1 AND companyid = ?
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@ea04cb]
[org.springframework.beans.factory.xml.XmlBeanDefinitionReader] - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
[org.springframework.jdbc.support.SQLErrorCodesFactory] - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
五月 08, 2017 3:28:59 下午 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet rpReport threw exception
java.sql.SQLException
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3523)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2353)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2269)
at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1153)
at com.informix.jdbc.IfxPreparedStatement.e(IfxPreparedStatement.java:318)
at com.informix.jdbc.IfxPreparedStatement.a(IfxPreparedStatement.java:298)
at com.informix.jdbc.IfxPreparedStatement.(IfxPreparedStatement.java:168)
at com.informix.jdbc.IfxSqliConnect.h(IfxSqliConnect.java:5918)
at com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java:1999)
at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:248)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:302)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:54)
at com.sun.proxy.$Proxy34.prepareStatement(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:75)
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:85)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:57)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:73)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:59)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:112)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:60)
at com.sun.proxy.$Proxy33.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)
at com.sun.proxy.$Proxy21.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
at com.sun.proxy.$Proxy31.select(Unknown Source)
at com.picc.service.impl.CompanyServiceImpl.select(CompanyServiceImpl.java:21)
at com.picc.controller.CompanyController.select(CompanyController.java:24)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:690)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:945)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:876)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:108)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:745)
例如:List query(@param("params") PaymentRequestParams paymentRequestParams);
需要在SQL异常的后处理中,进行其他SQL查询操作等, 所以需要在每个startPage时,通过try finally调用clearPage,如果Page本身实现AutoCloseable接口(调用clearPage),则直接使用ty(){}就可以了。
这是POM:
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.1.0</version> </dependency>
`<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>orderby-helper</artifactId>
<version>0.0.1</version>
</dependency>`
分页+排序代码片段:
PageHelper.startPage(pageBean.getPage(),pageBean.getRows()); //添加排序字段 OrderByHelper.orderBy(baseEntity.getSort()+" "+baseEntity.getOrder());
还有 MyBatis.xml的配置: <plugins> <!-- 分页插件或其他插件,OrderBy 一定要在分页插件下面(主要是为了避免 count 也被增加排序) --> <plugin interceptor="tk.mybatis.orderbyhelper.OrderByHelper"/> </plugins>
结果是没排序
使用版本:4.1.6
说明:查看5.0.1版本源码,该部分代码没变,应该也可以重现该bug
异常现场:
### Error querying database. Cause: java.lang.RuntimeException: 无法处理该类型[class com.github.pagehelper.sqlsource.PageDynamicSqlSource]的SqlSource
### Cause: java.lang.RuntimeException: 无法处理该类型[class com.github.pagehelper.sqlsource.PageDynamicSqlSource]的SqlSource
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:79)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)
at com.sun.proxy.$Proxy72.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:231)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:128)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:68)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53
重现:
在启一个线程池,进行同一个分页查询操作时,出现RuntimeException,无法处理该类型PageDynamicSqlSource
所用代码如下:
@Test
public void search() throws Exception {
ExecutorService executorService = Executors.newFixedThreadPool(10);
for(int i = 0;i<10;i++) {
executorService.submit(new Runnable() {
@Override
public void run() {
Object result = testService.search(1, new Random().nextInt(1000));
logger.info(JSONObject.toJSONString(result));
}
});
}
executorService.shutdown();
while(true){
if(executorService.isTerminated()){
break;
}
Thread.sleep(100);
}
}
异常分析:
在SqlUtils方法中有以下处理:
//判断并处理为PageSqlSource
if (!isPageSqlSource(ms)) {
processMappedStatement(ms);
}
其中:
processMappedStatement方法为:
public void processMappedStatement(MappedStatement ms) throws Throwable {
SqlSource sqlSource = ms.getSqlSource();
MetaObject msObject = SystemMetaObject.forObject(ms);
SqlSource pageSqlSource;
if (sqlSource instanceof StaticSqlSource) {
pageSqlSource = new PageStaticSqlSource((StaticSqlSource) sqlSource);
} else if (sqlSource instanceof RawSqlSource) {
pageSqlSource = new PageRawSqlSource((RawSqlSource) sqlSource);
} else if (sqlSource instanceof ProviderSqlSource) {
pageSqlSource = new PageProviderSqlSource((ProviderSqlSource) sqlSource);
} else if (sqlSource instanceof DynamicSqlSource) {
pageSqlSource = new PageDynamicSqlSource((DynamicSqlSource) sqlSource);
} else if(sqlSource instanceof PageDynamicSqlSource) {
pageSqlSource = sqlSource;
}else {
throw new RuntimeException("无法处理该类型[" + sqlSource.getClass() + "]的SqlSource");
}
msObject.setValue("sqlSource", pageSqlSource);
//由于count查询需要修改返回值,因此这里要创建一个Count查询的MS
msCountMap.put(ms.getId(), MSUtils.newCountMappedStatement(ms));
}
由于processMappedStatement是非线程安全的方法,虽然前面进行过if判断,进入这个方法体后sqlSource对象还是有可能是处理过的PageSqlSource,此时instanceof 判断失败,抛出RuntimeException
建议订正:
在条件判断最后,再进行一次PageSqlSource的判断,如果是,直接返回:
else if(sqlSource instanceof PageSqlSource){
pageSqlSource = sqlSource ;
}else {
throw new RuntimeException("无法处理该类型[" + sqlSource.getClass() + "]的SqlSource");
}
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.