Nutz
  1. 主页/
  2. 问答
NutzCN Logo
问答 NutzDao用在MSSQL 分页查询的问题
发布于 3288天前 作者 Toby244 3018 次浏览 复制 上一个帖子 下一个帖子
标签: dao sqlserver 
    Pager pager=new Pager();
    pager.setPageNumber(pagenum);
    pager.setPageSize(pagesize);

    Condition cnd = Cnd.where("OrderID", "like", "%"+where+"%").or("OrderUID", "like", "%"+where+"%").or("ClientID", "like", "%"+where+"%").or("ClientName", "like", "%"+where+"%").desc("OrderDate");
  dao.query(Ifw_Order.class, cnd, pager);

  报错
  Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: “LIMIT”附近有语法错误。
  是不是MSSQL不支持这么用啊？
  新手，求大神指导
10 回复

mssql版本及启动时的日志信息, 全贴上

2015 11 25 15:12:53,618 WARN org.nutz.dao.jdbc.Jdbcs:159 - Can not support database 'Microsoft SQL Server 12.00.2269', fallback to MySql 5
org.nutz.dao.DaoException: org.nutz.dao.DaoException: !Nutz SQL Error: 'SELECT * FROM Ifw_Order WHERE OrderID LIKE ? OR OrderUID LIKE ? OR ClientID LIKE ? OR ClientName LIKE ? ORDER BY OrderDate DESC LIMIT 0, 8
| 1 | 2 | 3 | 4 |
|--------|--------|--------|--------|
| %7111% | %7111% | %7111% | %7111% |
For example:> "SELECT * FROM Ifw_Order WHERE OrderID LIKE '%7111%' OR OrderUID LIKE '%7111%' OR ClientID LIKE '%7111%' OR ClientName LIKE '%7111%' ORDER BY OrderDate DESC LIMIT 0, 8 "'
PreparedStatement:
'SELECT * FROM Ifw_Order WHERE OrderID LIKE ? OR OrderUID LIKE ? OR ClientID LIKE ? OR ClientName LIKE ? ORDER BY OrderDate DESC LIMIT 0, 8 '
at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:76)
at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:204)
at org.nutz.dao.impl.NutDao.query(NutDao.java:419)
at com.gisuni.client.dao.Ifw_OrderDao.queryIfw_OrderByWhere(Ifw_OrderDao.java:158)
at com.gisuni.client.service.Ifw_OrderService.queryIfw_OrderByWhere(Ifw_OrderService.java:52)
at com.gisuni.client.controller.Ifw_OrderController.queryIfw_OrderByWhere(Ifw_OrderController.java:64)
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.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:22)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:33)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:40)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:99)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:40)
at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:64)
at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
at org.nutz.mvc.NutFilter.doFilter(NutFilter.java:94)
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:505)
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:956)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2522)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2511)
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)
Caused by: org.nutz.dao.DaoException: !Nutz SQL Error: 'SELECT * FROM Ifw_Order WHERE OrderID LIKE ? OR OrderUID LIKE ? OR ClientID LIKE ? OR ClientName LIKE ? ORDER BY OrderDate DESC LIMIT 0, 8
| 1 | 2 | 3 | 4 |
|--------|--------|--------|--------|
| %7111% | %7111% | %7111% | %7111% |
For example:> "SELECT * FROM Ifw_Order WHERE OrderID LIKE '%7111%' OR OrderUID LIKE '%7111%' OR ClientID LIKE '%7111%' OR ClientName LIKE '%7111%' ORDER BY OrderDate DESC LIMIT 0, 8 "'
PreparedStatement:
'SELECT * FROM Ifw_Order WHERE OrderID LIKE ? OR OrderUID LIKE ? OR ClientID LIKE ? OR ClientName LIKE ? ORDER BY OrderDate DESC LIMIT 0, 8 '
at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:90)
at org.nutz.dao.impl.DaoSupport$DaoExec.invoke(DaoSupport.java:249)
at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:64)
... 42 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: “LIMIT”附近有语法错误。
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:223)
at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:43)
... 44 more
2015 11 25 15:24:53,786 ERROR com.gisuni.client.service.Ifw_OrderService:62 - org.nutz.dao.DaoException: !Nutz SQL Error: 'SELECT * FROM Ifw_Order WHERE OrderID LIKE ? OR OrderUID LIKE ? OR ClientID LIKE ? OR ClientName LIKE ? ORDER BY OrderDate DESC LIMIT 0, 8
| 1 | 2 | 3 | 4 |
|--------|--------|--------|--------|
| %7111% | %7111% | %7111% | %7111% |
For example:> "SELECT * FROM Ifw_Order WHERE OrderID LIKE '%7111%' OR OrderUID LIKE '%7111%' OR ClientID LIKE '%7111%' OR ClientName LIKE '%7111%' ORDER BY OrderDate DESC LIMIT 0, 8 "'
PreparedStatement:
'SELECT * FROM Ifw_Order WHERE OrderID LIKE ? OR OrderUID LIKE ? OR ClientID LIKE ? OR ClientName LIKE ? ORDER BY OrderDate DESC LIMIT 0, 8 '

WARN org.nutz.dao.jdbc.Jdbcs:159 - Can not support database 'Microsoft SQL Server 12.00.2269', fallback to MySql 5

这是SqlServer啥版本?

sqlserver2012

dataSource : {
type : "org.nutz.dao.impl.SimpleDataSource",
fields : {
driverClassName : "com.microsoft.sqlserver.jdbc.SQLServerDriver",
jdbcUrl : "jdbc:sqlserver://192.168.120.188:1433;databaseName=BspSap",
username : "sa",
password : "esri_1234"
}
},

你用的不是最新版的nutz??

这是nutz匹配数据库的配置文件

https://github.com/nutzam/nutz/blob/master/src/org/nutz/dao/jdbc/nutz_jdbc_experts.js

最新版有匹配12这个版本的

QQ截图20151125162448_png

我这应该也支持2012，是不是要在哪里设置一下experts？？？

sqlserver2012那个实现类不行的, 因为必须带order by才能分页

我希望你debug一下 org.nutz.dao.jdbc.JdbcExpertConfigFile.matchExpert(String dbName) 这个方法,看看为啥没匹配上

如果只是低版本, 拷贝一份最新的nutz_jdbc_experts.js放在src下,然后改里面的experts

experts : {
   ".+" : "org.nutz.dao.impl.jdbc.sqlserver2005.Sqlserver2005JdbcExpert"
}
添加回复
请先登陆
回到顶部