java访问sqlserver2008分页问题

Question

最近想学java，在用jdbc连接sqlserver2008做分页的时候遇到如下问题，还请高手指教：
sql = "select * from (select ROW_NUMBER()over(order by orderby) as num,* from t_book where cid='5F79D0D246AD4216AC04E9C5FAB3199E') as t where num between 1 and 11";
List<Book> beanList = qr.query(sql, new BeanListHandler<Book>(Book.class));
这样就好使，但是换成参数化就报错sql = "select * from (select ROW_NUMBER()over(order by orderby) as num,* from t_book where cid=?) as t where num between ? and ?";
                params.add(“5F79D0D246AD4216AC04E9C5FAB3199E”);//当前页首行记录的下标
params.add((pc-1) * ps);//当前页首行记录的下标
params.add(ps);//一共查询几行，就是每页记录数
List<Book> beanList = qr.query(sql, new BeanListHandler<Book>(Book.class),params.toArray());
总是报sql错误
还请高手回答，难道参数化不支持嵌套？

Answer 1

[http-8080-1] DEBUG com.mchange.v2.c3p0.impl.NewPooledConnection - com.mchange.v2.c3p0.impl.NewPooledConnection@1e26d42 handling a throwable.
com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: 关键字 'between' 附近有语法错误。
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)[http-8080-1] DEBUG com.mchange.v2.sql.SqlUtils - Attempted to convert SQLException to SQLException. Leaving it alone. [SQLState: null; errorCode: 0]
com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: 关键字 'between' 附近有语法错误。
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerParameterMetaData.<init>(SQLServerParameterMetaData.java:426)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getParameterMetaData(SQLServerPreparedStatement.java:1532)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.getParameterMetaData(NewProxyPreparedStatement.java:573)
at org.apache.commons.dbutils.AbstractQueryRunner.fillStatement(AbstractQueryRunner.java:172)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:344)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:210)
at cn.itcast.jdbc.TxQueryRunner.query(TxQueryRunner.java:23)
at cn.itcast.goods.book.dao.BookDao.findByCriteria(BookDao.java:212)
at cn.itcast.goods.book.dao.BookDao.findByCategory(BookDao.java:103)
at cn.itcast.goods.book.service.BookService.findByCategory(BookService.java:70)
at cn.itcast.goods.book.web.servlet.BookServlet.findByCategory(BookServlet.java:96)
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:597)
at cn.itcast.servlet.BaseServlet.service(BaseServlet.java:44)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at cn.itcast.filter.EncodingFilter.doFilter(EncodingFilter.java:30)
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:230)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:581)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)
[http-8080-1] DEBUG com.mchange.v2.c3p0.impl.DefaultConnectionTester - Testing a Connection in response to an Exception:
com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: 关键字 'between' 附近有语法错误。
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerParameterMetaData.<init>(SQLServerParameterMetaData.java:426)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getParameterMetaData(SQLServerPreparedStatement.java:1532)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.getParameterMetaData(NewProxyPreparedStatement.java:573)
at org.apache.commons.dbutils.AbstractQueryRunner.fillStatement(AbstractQueryRunner.java:172)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:344)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:210)
at cn.itcast.jdbc.TxQueryRunner.query(TxQueryRunner.java:23)
at cn.itcast.goods.book.dao.BookDao.findByCriteria(BookDao.java:212)
at cn.itcast.goods.book.dao.BookDao.findByCategory(BookDao.java:103)
at cn.itcast.goods.book.service.BookService.findByCategory(BookService.java:70)
at cn.itcast.goods.book.web.servlet.BookServlet.findByCategory(BookServlet.java:96)
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:597)
at cn.itcast.servlet.BaseServlet.service(BaseServlet.java:44)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at cn.itcast.filter.EncodingFilter.doFilter(EncodingFilter.java:30)
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:230)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:581)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)
2016-6-2 19:15:58 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet BookServlet threw exception
java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
at cn.itcast.servlet.BaseServlet.service(BaseServlet.java:60)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at cn.itcast.filter.EncodingFilter.doFilter(EncodingFilter.java:30)
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:230)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:581)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.lang.reflect.InvocationTargetException
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:597)
at cn.itcast.servlet.BaseServlet.service(BaseServlet.java:44)
... 16 more
Caused by: java.lang.RuntimeException: java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: 关键字 'between' 附近有语法错误。 Query: select * from (select ROW_NUMBER()over(order by orderby) as num,* from t_book  where 1=1 and cid = ?) as t where num between ? and ? Parameters: [D45D96DA359A4FEAB3AB4DCF2157FC06, 0, 12]
at cn.itcast.goods.book.service.BookService.findByCategory(BookService.java:72)
at cn.itcast.goods.book.web.servlet.BookServlet.findByCategory(BookServlet.java:96)
... 21 more
Caused by: java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: 关键字 'between' 附近有语法错误。 Query: select * from (select ROW_NUMBER()over(order by orderby) as num,* from t_book  where 1=1 and cid = ?) as t where num between ? and ? Parameters: [D45D96DA359A4FEAB3AB4DCF2157FC06, 0, 12]
at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:320)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:349)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:210)
at cn.itcast.jdbc.TxQueryRunner.query(TxQueryRunner.java:23)
at cn.itcast.goods.book.dao.BookDao.findByCriteria(BookDao.java:212)
at cn.itcast.goods.book.dao.BookDao.findByCategory(BookDao.java:103)
at cn.itcast.goods.book.service.BookService.findByCategory(BookService.java:70)
... 22 more

Answer 2

Caused by: java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: 关键字 'between' 附近有语法错误。 Query: select * from (select ROW_NUMBER()over(order by orderby) as num,* from t_book  where 1=1 and cid = ?) 
看起来像sql语句的问题，你先把这条sql放到sql客户端上去执行，看能不能出结果。

Answer 3

把sql语句放到SQL Server Management Studio中一点问题也没有，而且我把？换成是具体的值就没问题了，很奇怪的现象

Answer 4

等号有问题？试试like 或者用 : 

Answer 5

应该是between后不能带 ？ and ？ 吧，这样的正则表达式 无法识别吧。