hibernate mysql 分页时报错 显示的代码和sql server 类似 select top 1……

最新推荐文章于 2021-09-21 09:10:56 发布
最新推荐文章于 2021-09-21 09:10:56 发布
阅读量182 收藏
点赞数
原文链接：http://www.cnblogs.com/roychenyi/p/5384470.html
版权 
[ERROR][com.alibaba.druid.filter.stat.StatFilter]merge sql error, dbType mysql, sql : 
select top 1 ddiary0_.diary_id as diary_id1_9_, ddiary0_.site_id as site_id26_9_, ddiary0_.catalog_id as catalog27_9_, ddiary0_.user_id as user_id28_9_, ddiary0_.annex_id as annex_id2_9_, ddiary0_.client_agent as client_a3_9_, ddiary0_.client_ip as client_i4_9_, ddiary0_.client_type as client_t5_9_, ddiary0_.content as content6_9_, ddiary0_.contribute as contribu7_9_, ddiary0_.diary_size as diary_si8_9_, ddiary0_.ext1 as ext9_9_, ddiary0_.ext2 as ext10_9_, ddiary0_.ext3 as ext11_9_, ddiary0_.ext4 as ext12_9_, ddiary0_.last_read_time as last_re13_9_, ddiary0_.last_reply_time as last_re14_9_, ddiary0_.locked as locked15_9_, ddiary0_.modify_time as modify_16_9_, ddiary0_.mood_level as mood_le17_9_, ddiary0_.reply_count as reply_c18_9_, ddiary0_.reply_notify as reply_n19_9_, ddiary0_.role as role20_9_, ddiary0_.type_id as type_id29_9_, ddiary0_.tag as tag21_9_, ddiary0_.title as title22_9_, ddiary0_.txt as txt23_9_, ddiary0_.view_count as view_co24_9_, ddiary0_.write_time as write_t25_9_ from wxs.d_diary ddiary0_ where ddiary0_.diary_id<? and ddiary0_.user_id=? order by ddiary0_.diary_id desc1068481ncom.alibaba.druid.sql.parser.ParserException: syntax error, error in :' top 1 ddiary0_.diary_id as diary_i',expect LITERAL_INT, actual LITERAL_INT top
	at com.alibaba.druid.sql.parser.SQLParser.printError(SQLParser.java:210)
	at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:307)
	at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:112)
	at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:107)
	at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:54)
	at com.alibaba.druid.filter.stat.StatFilter.mergeSql(StatFilter.java:145)
	at com.alibaba.druid.filter.stat.StatFilter.createSqlStat(StatFilter.java:629)
	at com.alibaba.druid.filter.stat.StatFilter.statementPrepareAfter(StatFilter.java:305)
	at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:124)
	at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448)
	at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:342)
	at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:318)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:162)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160)
	at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1885)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
	at org.hibernate.loader.Loader.doQuery(Loader.java:910)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
	at org.hibernate.loader.Loader.doList(Loader.java:2554)
	at org.hibernate.loader.Loader.doList(Loader.java:2540)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
	at org.hibernate.loader.Loader.list(Loader.java:2365)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:497)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387)
	at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:236)
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1300)
	at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
	at com.cx.wxs.base.dao.BaseDaoImpl.get(BaseDaoImpl.java:557)
	at com.cx.wxs.dao.impl.DDiaryDaoImpl.getCurrentDiarys(DDiaryDaoImpl.java:171)
	at com.cx.wxs.service.impl.DDiaryServiceImpl.getCurrentDiarys(DDiaryServiceImpl.java:92)
	at com.cx.wxs.action.diary.diaryAction.articlePreview(diaryAction.java:139)
	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.doInvoke(InvocableHandlerMethod.java:221)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:806)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:729)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at com.cx.wxs.filter.SessionFilter.doFilterInternal(SessionFilter.java:147)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:151)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:123)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:85)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
	at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2466)
	at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2455)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)

　　如上面的报错方式，hql 被整理之后的sql 如上，是sql server 的样式

而mysql 的样式 是 select * from [table]  where  1=1 limit m，n

在找了自己的配置文件之后，发现我的hibernate配置有问题。其中参数：hibernate.dialect：org.hibernate.dialect.SQLServerDialect

而之前我一直没有使用分页查询，所以一直没有发现这个问题。毕竟使用select * from 【table】是所有的关系型数据的基本查询语句，所以不会报错，但是分页就不同了。

所以正确的配置，请参考对照表：

 

RDBMS 方言
DB2org.hibernate.dialect.DB2Dialect
DB2 AS/400org.hibernate.dialect.DB2400Dialect
DB2 OS390org.hibernate.dialect.DB2390Dialect
PostgreSQLorg.hibernate.dialect.PostgreSQLDialect
MySQLorg.hibernate.dialect.MySQLDialect
MySQL with InnoDBorg.hibernate.dialect.MySQLInnoDBDialect
MySQL with MyISAMorg.hibernate.dialect.MySQLMyISAMDialect
Oracle (any version)org.hibernate.dialect.OracleDialect
Oracle 9i/10gorg.hibernate.dialect.Oracle9Dialect
Sybaseorg.hibernate.dialect.SybaseDialect
Sybase Anywhereorg.hibernate.dialect.SybaseAnywhereDialect
Microsoft SQL Serverorg.hibernate.dialect.SQLServerDialect
SAP DBorg.hibernate.dialect.SAPDBDialect
Informixorg.hibernate.dialect.InformixDialect
HypersonicSQLorg.hibernate.dialect.HSQLDialect
Ingresorg.hibernate.dialect.IngresDialect
Progressorg.hibernate.dialect.ProgressDialect
Mckoi SQLorg.hibernate.dialect.MckoiDialect
Interbaseorg.hibernate.dialect.InterbaseDialect
Pointbaseorg.hibernate.dialect.PointbaseDialect
FrontBaseorg.hibernate.dialect.FrontbaseDialect
Firebird

org.hibernate.dialect.FirebirdDialect

 

org.hibernate.dialect.SQLServerDialect

转载于:https://www.cnblogs.com/roychenyi/p/5384470.html

weixin_30915275
关注
mysql hibernate 分页_【整理】Hibernate分页原理简单分析
weixin_39835117的博客
02-07 152
【背景说明】面试时曾被问得不知所以然，故收集整理之。通常使用的Hibernate查询是三种：hql查询，QBC查询和QBE查询。1、QBE(Qurey By Example)检索方式QBE是最简单的，但是功能也是最弱的，QBE的功能不是特别强大，仅在某些场合下有用。一个典型的使用场合就是在查询窗口中让用户输入一系列的查询条件，然后返回匹配的对象。QBE只支持=和like比较运算符，无法不大区间值，...
Hibernate5 MSSql分页问题处理
ytangdigl的博客
06-08 2000
1、前言 hibernate的分页查询，代码如下/** * 分页查询(适用于Easyui分页) * 局限:排序仅支持单表 * @param hql HQL 如:'from MyTable o' * @param pageParams 分页参数 * @return 结果集 */ @Override @SuppressWarnings("unchecked") public List
hibernate 用原始sql语句分页报错
zeng312197693的博客
08-12 129
SQLQuery query = session.createSQLQuery(sql.toString()); query.setFirstResult((offset - 1) * length); query.setMaxResults(length); list = query.list();
MySQL不能用Select top 1 * from...这样的语句
weixin_45284208的博客
09-21 4516
select top 1 * from [tablename] order by ID desc 报错 SELECT top 1 * FROM version_manage where title ='mes' order by version desc > 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right.
利用Spring + Hibernate如何实现提取数据库top语句
yeminping的专栏
01-05 158
如何用Spring+Hibernate实现如下SQL语句: Hibernate: select top 10 word0_.Fguid as Fguid1_, word0_.FAdjunct as FAdjunct1_, word0_.FAuditDate as FAuditDate1_, word0_.FAuditDeptID as FAuditDe4_1_, word0_.FAudi...
HQL 基础
jcc3120的专栏
02-18 1351
1 .from1.1单表查询from eg.cat as cat.其中，cat只是一个别名，为了用其他子语句的时候书写简单1.2多表查询from eg.Cat,eg.Dogfrom eg.Cat as cat,eg.Dog as dog2 join相关(inner) joinleft (outer) joinright (outer) joinfull joinHQL同样对SQL中的这些特性支
Hibernate框架数据分页技术实例分析
09-02
在开发Web应用时，数据分页是一项至关重要的技术，它能有效地提高用户体验，尤其是在处理大量数据时。在Java领域，Hibernate作为一款强大的ORM框架，提供了便捷的数据分页功能。本篇将深入探讨Hibernate框架实现数据...
Java Web 简单的分页显示实例代码
09-02
在Java Web开发中，分页显示是常见的需求，它...此外，还可以考虑使用ORM框架（如Hibernate或MyBatis）来简化数据库操作，或者使用成熟的分页组件（如Spring Data JPA的Pageable接口）来提高代码的可维护性和扩展性。
对IBatis分页的改进,使ibatis支持hibernate式的物理分页.doc
最新发布
09-19
我们可以在这个方法中插入代码，对SQL进行拦截和改写，添加物理分页的相关语句。 例如，对于MySQL，我们可以将SQL语句改写为包含`LIMIT`和`OFFSET`部分。假设`skipResults`表示要跳过的记录数，`maxResults`是每页...
idea配置SQLServer错误解决记录
HeyShHeyou的博客
11-06 1342
idea配置SQLServer错误解决记录 1.错误信息：2019-03-26 13:32:41.205 ERROR 7828 --- [nio-8081-exec-1] c.alibaba.druid.filter.stat.StatFilter : merge sql error, dbType sqlserver, druid-1.1.10, sql : null 截图：(程序可以正常运行，但是会报这个错误) 解决：将pom.xml的<druid-version&gt...
解决: com.alibaba.druid.filter.FilterManager : load filter error, filter not found : sl4j
拾年一剑 的专栏
09-05 4533
文章目录问题描述问题分析问题解决 问题描述 SpringBoot 项目中使用了Druid连接池，启动项目时，log中打印出下面的错误信息，但是不影响程序的运行： com.alibaba.druid.filter.FilterManager : load filter error, filter not found : sl4j 问题分析 打开druid jar包中，druid支持的过滤器类型都在一个druid-filters.properties 文件，，内容如下： druid.filters.defa
com.alibaba.druid.filter.stat.StatFilter:148 - merge sql error, dbType mysql
nkshuangyuan2012的博客
11-24 1万+
出错的sql代码段： UPDATE `agent` SET `name`=#{name}, `areacode`=#{areacode}, `address`=#{address}, `linkman`=#{linkman}, `tel`=#{tel}, `staff_type`=${staffType}, `station_ids`=#{stationIds}, `username`=#{username}, `account_bl.
hiveSQL操作
oba_gaga的博客
08-12 746
根据本博客的hadoop搭建文章，再继续本章内容。操作简单 直接把下载好的sogou.500w.utf8文件拖拽到lionel账户下的home目录下 然后执行 在master节点上执行 [lionel@master ~]$ cd ~/hadoop-2.5.2 [lionel@master hadoop-2.5.2]$ sbin/start-all.sh ↓ [lionel@...
“select top 1”导致查询非常慢
WolfShadow的博客
10-24 1万+
这是我2015年4月17日的一篇网易博客，搬迁至此。 通过JUnit进行单元测试时发现JdbcTemplate执行一个sql查询语句时间居然超过4000ms，然后直接在sqlserver2014数据库中执行这个sql，运行时间依然约4s。于是把矛头对准刚才自己写的这个视图，猜想问题可能是因为关联表太多，可能是由于group by 导致，但经过各种修改，问题依旧。偶然想到去掉“top 1”再试试，...
使用HQL 语句实现select top N
热门推荐
龙胜LONGAIN
04-25 1万+
在hibernate编程中，如何使用HQL 语句实现select top n的目标。例如，在某个数据表中总共有1万行的数据，那么我要如何才能取出最新的前20行，这个如果在sql中很简单，就直接使用select top n语句即可，然而hql不支持select top n，所以必须修改。那么下面的例子是在hibernate+spring整合项目中的代码，其中getHibernateTempl...
HIbernate 和Spring中没有select top语句的问题
chengyue2007的专栏
09-21 300
在hibernate的hql语句中没有select top n from Order as o 语句，于是我们就用了，下面的来的进行top的操作。 Query q=session.createQuery("");;    q.setFirstResult(0);    q.setMaxResult(10);;   下面的语句将会更好：from Torder  o where o.order...
数据库分页实现：MySQL, SQLServer, Oracle
"本文主要介绍了在Mysql、Oracle和SQLServer2005这三种主流数据库中实现分页的方法，包括使用Hibernate框架进行数据库连接，以及利用DisplayTag组件在JSP页面上展示分页结果。" 在Web开发中，为了提高用户体验和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值