异常:java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0)

本文介绍了一个关于SpringBoot结合Mybatis和Shiro进行权限控制时出现的异常情况,并分析了异常产生的原因及解决办法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一、产生环境

spring Boot + Mybatis + shiro 实现权限控制功能

二、异常情况

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) ~[mysql-connector-java-5.1.44.jar:5.1.44]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897) ~[mysql-connector-java-5.1.44.jar:5.1.44]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886) ~[mysql-connector-java-5.1.44.jar:5.1.44]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) ~[mysql-connector-java-5.1.44.jar:5.1.44]
	at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3327) ~[mysql-connector-java-5.1.44.jar:5.1.44]
	at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3312) ~[mysql-connector-java-5.1.44.jar:5.1.44]
	at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4027) ~[mysql-connector-java-5.1.44.jar:5.1.44]
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.setString(DruidPooledPreparedStatement.java:366) ~[druid-1.1.4.jar:1.1.4]
	at org.apache.shiro.realm.jdbc.JdbcRealm.getPasswordForUser(JdbcRealm.java:287) ~[shiro-core-1.4.0.jar:1.4.0]
	at org.apache.shiro.realm.jdbc.JdbcRealm.doGetAuthenticationInfo(JdbcRealm.java:227) ~[shiro-core-1.4.0.jar:1.4.0]
	at org.apache.shiro.realm.AuthenticatingRealm.getAuthenticationInfo(AuthenticatingRealm.java:571) ~[shiro-core-1.4.0.jar:1.4.0]
	at org.apache.shiro.authc.pam.ModularRealmAuthenticator.doSingleRealmAuthentication(ModularRealmAuthenticator.java:180) ~[shiro-core-1.4.0.jar:1.4.0]
	at org.apache.shiro.authc.pam.ModularRealmAuthenticator.doAuthenticate(ModularRealmAuthenticator.java:267) ~[shiro-core-1.4.0.jar:1.4.0]
	at org.apache.shiro.authc.AbstractAuthenticator.authenticate(AbstractAuthenticator.java:198) ~[shiro-core-1.4.0.jar:1.4.0]
	at org.apache.shiro.mgt.AuthenticatingSecurityManager.authenticate(AuthenticatingSecurityManager.java:106) ~[shiro-core-1.4.0.jar:1.4.0]
	at org.apache.shiro.mgt.DefaultSecurityManager.login(DefaultSecurityManager.java:274) ~[shiro-core-1.4.0.jar:1.4.0]
	at org.apache.shiro.subject.support.DelegatingSubject.login(DelegatingSubject.java:260) ~[shiro-core-1.4.0.jar:1.4.0]
	at com.sulong.service.impl.UserServiceImpl.login(UserServiceImpl.java:148) ~[classes/:na]
	at com.sulong.controller.UserController.login(UserController.java:47) ~[classes/:na]
	at com.sulong.controller.UserController$$FastClassBySpringCGLIB$$755e4336.invoke(<generated>) ~[classes/:na]
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at com.sulong.aop.ValidateAspect.around(ValidateAspect.java:47) ~[classes/:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_144]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_144]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_144]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_144]
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:629) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:618) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673) ~[spring-aop-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at com.sulong.controller.UserController$$EnhancerBySpringCGLIB$$2b5b582d.login(<generated>) ~[classes/:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_144]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_144]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_144]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_144]
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:661) ~[tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) ~[tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat-embed-websocket-8.5.20.jar:8.5.20]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:108) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.11.RELEASE.jar:4.3.11.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) ~[tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478) [tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80) [tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) [tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799) [tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) [tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1457) [tomcat-embed-core-8.5.20.jar:8.5.20]
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-8.5.20.jar:8.5.20]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_144]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_144]
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-8.5.20.jar:8.5.20]
	at java.lang.Thread.run(Thread.java:748) [na:1.8.0_144]

三、产生原因

由于参数多次添加到SQL语句中

四、解决办法

把1位置的参数用?代替,因为后面有填充参数

    
        //使用自定义sql
        String sql = "select password from sl_user where account = ?";
        jdbcRealm.setAuthenticationQuery(sql);
        String roleSql = "select role from sl_user where account = ?";
        jdbcRealm.setUserRolesQuery(roleSql);


<think>好的,用户遇到了MyBatis的“Parameter index out of range (1 > number of parameters, which is 0)”错误,需要我帮忙解决。先回顾一下用户提供的引用内容和之前的对话。 用户之前给出的引用[1]和[2]提到了类似的问题,主要是参数索引超出范围,可能涉及参数数量不匹配或参数未正确绑定。之前的回答中,我已经解释了参数名称不匹配、@Param注解的使用、参数数量问题以及动态SQL的注意事项。现在用户再次提问,需要更具体的解决方案,特别是针对参数数量为0的情况。 首先,错误信息中的“number of parameters, which is 0”意味着MyBatis在执行SQL时没有接收到任何参数,但SQL语句中却引用了参数(比如#{param})。这种情况可能有几个原因: 1. **方法参数未正确传递**:可能Mapper接口方法被调用时没有传入任何参数,或者在动态代理过程中参数丢失。 2. **XML中的参数占位符错误**:比如错误地使用了${}而不是#{},或者在SQL语句中错误地添加了引号导致参数被当作字符串处理。 3. **集合/数组类型处理不当**:例如在使用foreach遍历集合时,如果传入的集合为空或未正确指定类型,可能导致参数未被识别。 4. **参数类型未指定JdbcType**:当参数为null时,MyBatis可能无法推断JdbcType,需要显式指定。 引用[2]中的例子显示,用户尝试插入数据时,虽然实体类的infos字段有值,但生成的SQL语句中的占位符被包裹在引号中,导致MyBatis认为是一个字符串而非参数列表,因此实际参数数量为0。这种情况下,需要检查XML映射文件中的SQL语句是否正确使用了#{},并且没有错误地添加额外的引号。 接下来需要针对这些可能的原因逐一分析,给出解决方案。例如,对于参数未传递的情况,需要检查Mapper接口方法的调用是否正确传参;对于XML中的占位符问题,需要修正SQL语句,确保参数正确绑定;对于集合类型,需要正确使用foreach标签并指定参数类型。此外,显式指定JdbcType也是一个常见的解决方法,特别是在参数可能为null的情况下。 最后,用户可能还需要了解如何预防此类错误,比如在编写XML时使用正确的语法,以及在测试时如何验证参数是否正确绑定。需要确保回答结构清晰,逐步引导用户检查各个可能的问题点,并提供具体的代码示例,如修正后的Mapper接口和XML配置,以及参数传递的正确方式。</think>--- ### MyBatis中“Parameter index out of range (1 > number of parameters, which is 0)”解决方案 此错误表明**SQL语句包含占位符(如`#{param}`),但实际未接收到任何参数**,以下是具体原因及解决方法: --- #### 1. **参数未正确传递**(核心原因) - **问题表现** XML中引用了参数(如`#{param}`),但调用Mapper方法时未传入参数,或方法参数被错误处理[^2]。 - **解决方案** - **检查Mapper接口与调用代码** 确保方法调用时参数传递正确: ```java // 正确示例:传入参数 day60sMapper.insertOne(entity); // entity必须为非空对象 ``` - **验证Mapper接口定义** 接口方法需明确接收参数: ```java // 错误示例:方法无参数 void insertOne(); // 正确示例:添加参数 void insertOne(@Param("entity") Day60sEntity entity); ``` --- #### 2. **XML中参数占位符错误** - **问题分析** 常见于集合类型参数处理错误,例如: ```xml <!-- 错误示例:占位符被单引号包裹,导致参数被识别为字符串 --> <insert id="insertOne"> INSERT INTO day60s (`infos`) VALUES ('[#{item}, #{item}, ...]') </insert> ``` - **解决方案** 使用`<foreach>`标签正确处理集合参数,**避免手动拼接引号**: ```xml <insert id="insertOne"> INSERT INTO day60s (`infos`) VALUES ( <foreach collection="entity.infos" item="item" separator="," open="[" close="]"> #{item,jdbcType=VARCHAR} </foreach> ) </insert> ``` --- #### 3. **参数类型不匹配** - **问题表现** 若参数为`null`且未指定`jdbcType`,MyBatis无法推断类型[^1]。 - **解决方案** 显式指定`jdbcType`(尤其是基本类型): ```xml <!-- 示例:处理可能为null的参数 --> <insert id="insertOne"> INSERT INTO day60s (`remark`) VALUES (#{entity.remark,jdbcType=VARCHAR}) </insert> ``` --- #### 4. **动态SQL逻辑错误** - **问题分析** 使用`<if>`标签时,若所有条件均不满足,SQL语句可能不含任何参数占位符,但MyBatis仍尝试绑定参数。 - **解决方案** 添加默认参数或验证动态条件: ```xml <insert id="insertOne"> INSERT INTO day60s <trim prefix="(" suffix=")" suffixOverrides=","> <if test="entity.infos != null"> `infos`, </if> <!-- 必须至少包含一个非空字段 --> <if test="true"> `deleted` </if> </trim> VALUES (...) </insert> ``` --- ### 示例修正(引用[^2]问题) **原错误SQL**: ```xml <!-- 错误:'[?,?,...]'被识别为字符串而非参数列表 --> INSERT INTO day60s (`infos`) VALUES ('[?,?,?,?,?,?,?,?]') ``` **修正后XML**: ```xml <insert id="insertOne"> INSERT INTO day60s (`infos`) VALUES ( <foreach collection="entity.infos" item="item" separator="," open="[" close="]"> #{item,jdbcType=VARCHAR} </foreach> ) </insert> ``` **调用代码**: ```java Day60sEntity entity = new Day60sEntity(); entity.setInfos(Arrays.asList(1,2,3,4,5,6,"abcd","喝咖啡哈萨克")); day60sMapper.insertOne(entity); // 确保参数非空 ``` --- ### 关键验证步骤 1. **检查SQL日志** 观察生成的SQL是否包含`?`占位符(而非硬编码值)。 2. **启用MyBatis调试日志** 在`log4j.properties`中添加: ```properties log4j.logger.org.apache.ibatis=DEBUG log4j.logger.java.sql=DEBUG ``` 3. **单元测试验证** 使用JUnit测试参数传递逻辑。 --- ### 相关问题 1. MyBatis如何自动映射集合类型参数? 2. 动态SQL中`<foreach>`标签的常见用法有哪些? 3. 如何通过日志分析MyBatis参数绑定过程?
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值