MySql的分页问题

window24 2010-08-12 05:38:25
我根据网上查到的自己写了个分页语句，如下

select * from (select r.*, rownum rownum_

          	from (select g.GOODS_ID,g.GOODS_TYPE,g.GOODS_BRAND,g.GOODS_FLAG,g.GOODS_NAME_CN,g.GOODS_NAME_EN,g.GOODS_COMMENT,g.GOODS_PRICE,g.CREATE_TIME

          	    from tbl_goods_menu g) as r

         <dynamic prepend="WHERE" >

           <isGreaterThan prepend="AND" property="page_end" compareValue="0" >

             rownum $end$ #page_end#

           </isGreaterThan>

          </dynamic>

         ) as a

 		<dynamic prepend="WHERE" >

             <isGreaterThan prepend="AND" property="page_start" compareValue="0" >

               rownum_ $start$ #page_start#

             </isGreaterThan>

        </dynamic>

        ORDER BY create_time desc



Tomcat报错是
StandardWrapperValve[action]: Servlet.service() for servlet action threw exception
org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the menu.showGoodsMenuList-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'rownum' in 'field list'
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the menu.showGoodsMenuList-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'rownum' in 'field list'
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:615)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:589)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
at org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:245)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:181)
at org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(SqlMapClientTemplate.java:204)
at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClientTemplate.java:243)
at com.yunjie.goodsmenu.dao.ibatis.SqlMapGoodsMenuDao.showGoodsMenuList(SqlMapGoodsMenuDao.java:40)
at com.yunjie.goodsmenu.services.Impl.GoodsMenuServiceImpl.selectAllList(GoodsMenuServiceImpl.java:55)
at com.yunjie.goodsmenu.action.GoodsMenuAction.search(GoodsMenuAction.java:44)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:269)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:170)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:228)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.common.filter.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:122)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:595)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'rownum' in 'field list'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:843)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:62)
at $Proxy1.execute(Unknown Source)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:186)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
... 50 more



请问是怎么回事???rownum rownum_ 我也没搞懂是什么意思
...全文
231 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
window24 2010-08-14
  • 打赏
  • 举报
 回复
额。 。郁闷 是我火星了...我还以为是MySql的新分页方法... 网上找的例子是oracle数据库的...
window24 2010-08-13
  • 打赏
  • 举报
 回复
額。。我已经贴出了iBATIS文件里的sql语句了 ，我再贴下。
select * from (select r.*, rownum rownum_

              from (select g.GOODS_ID,g.GOODS_TYPE,g.GOODS_BRAND,g.GOODS_FLAG,g.GOODS_NAME_CN,g.GOODS_NAME_EN,g.GOODS_COMMENT,g.GOODS_PRICE,g.CREATE_TIME

                  from tbl_goods_menu g) as r

         <dynamic prepend="WHERE" >

           <isGreaterThan prepend="AND" property="page_end" compareValue="0" >

             rownum $end$ #page_end#

           </isGreaterThan>

          </dynamic>

         ) as a

         <dynamic prepend="WHERE" >

             <isGreaterThan prepend="AND" property="page_start" compareValue="0" >

               rownum_ $start$ #page_start#

             </isGreaterThan>

        </dynamic>

        ORDER BY create_time desc


rownum rownum_ 在下面的<dynamic prepend="WHERE" >里面
yangxiao_jiang 2010-08-13
  • 打赏
  • 举报
 回复
select r.*, rownum rownum_
from (select g.GOODS_ID,g.GOODS_TYPE,g.GOODS_BRAND,g.GOODS_FLAG,g.GOODS_NAME_CN,g.GOODS_NAME_EN,g.GOODS_COMMENT,g.GOODS_PRICE,g.CREATE_TIME
from tbl_goods_menu g)

你的rownum rownum_在临时表select g.GOODS_ID,g.GOODS_TYPE,g.GOODS_BRAND,g.GOODS_FLAG,g.GOODS_NAME_CN,g.GOODS_NAME_EN,g.GOODS_COMMENT,g.GOODS_PRICE,g.CREATE_TIME
from tbl_goods_menu g)里面，没有，当然会报错了。
wminjay 2010-08-13
  • 打赏
  • 举报
 回复
你的报错中 很 明显的写着 Unknown column 'rownum' in 'field list'
window24 2010-08-13
  • 打赏
  • 举报
 回复
额 可不可以不要答非所问？

怎么现在csdn上不是不帮助回答就是这样的答非所问
kimsung 2010-08-13
  • 打赏
  • 举报
 回复
你可以试试


SELECT g.GOODS_ID,

       g.GOODS_TYPE,

       g.GOODS_BRAND,

       g.GOODS_FLAG,

       g.GOODS_NAME_CN,

       g.GOODS_NAME_EN,

       g.GOODS_COMMENT,

       g.GOODS_PRICE,

       g.CREATE_TIME

  FROM tbl_goods_menu

ORDER BY create_time DESC

 LIMIT #page_start#, #page_end#
kimsung 2010-08-13
  • 打赏
  • 举报
 回复
mysql里没有rownum.
select r.*, rownum rownum_

              from (select g.GOODS_ID,g.GOODS_TYPE,g.GOODS_BRAND,g.GOODS_FLAG,g.GOODS_NAME_CN,g.GOODS_NAME_EN,g.GOODS_COMMENT,g.GOODS_PRICE,g.CREATE_TIME

                  from tbl_goods_menu g) as r



这句就会报错. 因为你的子查询没有rownum.找不到rownum的column.
在mysql可以通过limit来做分页.
可以网上查下.

select *

  from tbl

 limit 0, 10


查询0到10的数据.
战斗生活 2010-08-12
  • 打赏
  • 举报
 回复
就是啊 分页何必搞得这么难看啊 。。。
mysql_fetch_rows
...count...

不就搞定了吗
不过分页函数很多啊
moths 2010-08-12
  • 打赏
  • 举报
 回复
mysql分页有这么麻烦吗？ 直接limit不就行吗？
springMvc+myBatis+mysql+分页（含架包）
springMvc+myBatis+mysql+分页，带架包，适合初学者！
MySql深分页问题详解
MySql深分页问题详解1. 问题描述2. 问题分析3. 环境准备3.1 创建两个表3.2 创建两个函数3.3 编写存储过程3.4 编写存储过程3.5 创建索引3.6 验证测试4. 解决方案4.1 使用索引覆盖+子查询优化4.2 起始位置重定义4.3 ...
JavaWeb分页技术实现
技术参数：Java+Servlet/Jsp实现分页数据库：MySQL课程中包含内存分页、数据库分页、按添加查询分页、以及分页封装及源代码，通过该课程，可以掌握分页的实现过程，建议由兴趣的同学可以学习一下！ 
【mySQL】mysql数据库分页查询讨论专题
MySQL：limit分页公式、总页数公式 1) limit分页公式 （1）limit分页公式：curPage是当前第几页；pageSize是一页多少条记录 limit (curPage-1)*pageSize,pageSize （2）用的地方：sql语句中 select * from student...
8种MySQL分页方法总结
这篇文章主要介绍了8种MySQL分页方法总结,小编现在才知道,MySQL分页竟然有8种实现方法,本文就一一讲解了这些方法,需要的朋友可以参考下MySQL的分页似乎一直是个问题，有什么优化方法吗？网上看到网上推荐了一些分页...
MySQL

56,679

社区成员

56,709

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章