求助大神：com.microsoft.sqlserver.jdbc.SQLServerException: 索引 1 超出范围

qq_41115311 2017-11-29 04:18:54
ssm写的是一个模糊查询
<select id="queryUser" resultType="user">
SELECT userid,username,roleid, usertype, userphoneno, status FROM(SELECT TOP ${endIndex} ROW_NUMBER() OVER(ORDER BY ID ASC)
AS ROWID,* FROM [LCBUS].[dbo].[T_USER]
<where>
<if test="username!=null and username!=''">
username like '%#{username}%'
</if>
</where>
) AS TEMP WHERE ROWID>${startIndex}
</select>
下面是控制台报错相关信息：
INFO - 调用 com.lcgj.service.impl.UserServiceImpl@4f9657ce 的 queryUser 方法。方法入参：[冉, 1, 15]
模糊查询的条件是：冉
Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5d872841]
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@3eecf5fe] will be managed by Spring
ooo Using Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@3eecf5fe]
==> Preparing: SELECT userid,username,roleid, usertype, userphoneno, status FROM(SELECT TOP 15 ROW_NUMBER() OVER(ORDER BY ID ASC) AS ROWID,* FROM [LCBUS].[dbo].[T_USER] WHERE username like '%?%' ) AS TEMP WHERE ROWID>0
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5d872841]
INFO - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
INFO - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5d872841]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5d872841]
十一月 29, 2017 4:10:05 下午 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet [springmvc] in context with path [/lcgj] threw exception [Request processing failed; nested exception is org.springframework.dao.TransientDataAccessResourceException:
### Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 索引 1 超出范围。
### The error may exist in file [C:\Users\112\Workspaces\MyEclipse Professional 2014\.metadata\.me_tcat7\webapps\lcgj\WEB-INF\classes\com\lcgj\mappers\User.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT userid,username,roleid, usertype, userphoneno, status FROM(SELECT TOP 15 ROW_NUMBER() OVER(ORDER BY ID ASC) AS ROWID,* FROM [LCBUS].[dbo].[T_USER] WHERE username like '%?%' ) AS TEMP WHERE ROWID>0
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 索引 1 超出范围。
; SQL []; 索引 1 超出范围。; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 索引 1 超出范围。] with root cause
com.microsoft.sqlserver.jdbc.SQLServerException: 索引 1 超出范围。
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:933)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:948)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setString(SQLServerPreparedStatement.java:1578)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setString(FilterChainImpl.java:2821)
at com.alibaba.druid.filter.FilterAdapter.preparedStatement_setString(FilterAdapter.java:1340)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setString(FilterChainImpl.java:2818)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.setString(PreparedStatementProxyImpl.java:547)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.setString(DruidPooledPreparedStatement.java:365)
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.PreparedStatementLogger.invoke(PreparedStatementLogger.java:63)
at com.sun.proxy.$Proxy21.setString(Unknown Source)
at org.apache.ibatis.type.StringTypeHandler.setNonNullParameter(StringTypeHandler.java:28)
at org.apache.ibatis.type.StringTypeHandler.setNonNullParameter(StringTypeHandler.java:23)
at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:46)
at org.apache.ibatis.type.UnknownTypeHandler.setNonNullParameter(UnknownTypeHandler.java:42)
at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:46)
at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:77)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:77)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:58)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:71)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:56)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)
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.$Proxy13.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:114)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)
at com.sun.proxy.$Proxy14.queryUser(Unknown Source)
at com.lcgj.service.impl.UserServiceImpl.queryUser(UserServiceImpl.java:37)
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.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:266)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy15.queryUser(Unknown Source)
at com.lcgj.handler.UserHandler.queryUser(UserHandler.java:36)
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.invokeHandleMethod(RequestMappingHandlerAdapter.java:776)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705)
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:966)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:868)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
a
...全文
986 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
qq_41115311 2017-11-30
  • 打赏
  • 举报
 回复
解决了，xml里面的语句写的不够规范，把where username like 后面的‘%#{}%’改成‘%${}%’就好了
原来我还在这儿 2017-11-29
  • 打赏
  • 举报
 回复
记得使用#{}控制台打印的sql就是？ copy控制台打印的sql，到数据库连接的可视化工具，执行一下sql，就知道哪里的问题了
qq_41115311 2017-11-29
  • 打赏
  • 举报
 回复
引用 1 楼 SongYou05 的回复:
很简单么，把打印出的sql放到数据库里，替换掉里面的参数执行一下，这种情况一般不是sql的问题就是参数的问题，数据库中执行就能看出来！
三个参数都进了方法，但是模糊的那个条件进不了sql语句打印出来的sql语句另外两个参数都进方法并且转换好了，模糊的那个还是‘%？%’：SELECT userid,username,roleid, usertype, userphoneno, status FROM(SELECT TOP 15 ROW_NUMBER() OVER(ORDER BY ID ASC) AS ROWID,* FROM [LCBUS].[dbo].[T_USER] WHERE username like '%?%' ) AS TEMP WHERE ROWID>0 把问号对应的参数写死是可以查询的
qq_41115311 2017-11-29
  • 打赏
  • 举报
 回复
我在xml文件里面把like后面的那个参数写死了可以查询
Edward_S_Y 2017-11-29
  • 打赏
  • 举报
 回复
很简单么，把打印出的sql放到数据库里，替换掉里面的参数执行一下，这种情况一般不是sql的问题就是参数的问题，数据库中执行就能看出来！
java索引 2 超出范围_java中动态执行SQL语句报错：索引2超出范围，求大神指导~...
java中动态执行SQL语句报错：索引2超出范围，求大神指导~报错：添加了1条记录到student表中com.microsoft.sqlserver.jdbc.SQLServerException: 索引 2 超出范围。at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerExceptio...
atitit 故障排除--- 当前命令发生了严重错误 应放弃任何可能产生的结果sql server 2008
atitit 故障排除--- 当前命令发生了严重错误 应放弃任何可能产生的结果sql server 2008
SpringBoot 系列教程(六十五)：application.yml文件属性配置大全
SpringBoot官方完整配置 SpringBoot2.1.6.RELEASE:https://docs.spring.io/spring-boot/docs/2.1.6.RELEASE/reference/html/common-application-properties.html 以往版本SpringBoot官方完整配置：https://docs.spring.io/spring...
spring boot 2.0(二)：application.properties配置文件(日志配置为例)
 spring boot 底层默认实现的是 SLF4J + Logback，使用起来非常方便，配置也非常简单，如下： 一 导入依赖： &lt;dependency&gt; &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt; &lt;artifactId&gt;spring-boot-starter-logging...
用友NC二次开发问题汇总【转】
分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.csdn.net/jiangjunshow也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 一、供应链采购订单问题
Web 开发

81,091

社区成员

341,718

社区内容

发帖
与我相关
我的任务
社区描述
Java Web 开发
社区管理员
  • Web 开发社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章