java.sql.SQLException: Parameter index out of range (X ＞ number of parameters, which is X).

于 2024-07-16 13:42:24 发布
阅读量314 收藏 9
点赞数 3
分类专栏： Java Mysql 数据库 文章标签： java 开发语言 spring boot maven mybatis sql mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_37991944/article/details/140273315
版权
Java 同时被 3 个专栏收录
23 篇文章 0 订阅
订阅专栏
数据库
6 篇文章 0 订阅
订阅专栏
Mysql
4 篇文章 0 订阅
订阅专栏

问题

开发中在使用MyBatis-Plus过程中，调用xml进行分页查询时报错：java.sql.SQLException: Parameter index out of range (X ＞ number of parameters, which is X).

环境

MySQL驱动版本：

<dependency>
	<artifactId>mysql-connector-java</artifactId>
	<groupId>mysql</groupId>
	<version>8.0.23</version>
</dependency>

MyBatis-Plus版本：

<dependency>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <groupId>com.baomidou</groupId>
    <version>3.4.1</version>
</dependency>

相关代码

查询对象如下

//用户查询对象
public class UserQuery {
   /**
    * 主键id.
    */
    private Long id;
    /**
     * 真实姓名.
     */
    private String realName;
    /**
     * 性别key-value集合.
     */
    private List<KeyValue> sexList;
    
	//省略setter&getter方法
}

//key-value对象
public class KeyValue {
    /**
     * 编码.
     */
    private String key;
    /**
     * 名称.
     */
    private String value;
    
	//省略setter&getter方法
}

Mapper.java

//用户查询Mapper
public interface UserQueryMapper extends BaseMapper<UserEntity> {
    <T> IPage<UserVO> selectUserList(Page<T> page, @Param("query") UserQuery query);
}

xml文件如下

<select id="selectUserList" resultType="com.xxxx.vo.UserVO">
	SELECT
	a.username,
	b.sex_name
	FROM user_table a
	LEFT JOIN (
	    SELECT * FROM (
	        <foreach collection="query.sexList" item="sex" separator=" UNION ALL ">
	            select #{sex.key} AS sex_code, #{sex.value} AS sex_name
	        </foreach>
	    ) AS sex_table
	) AS b ON a.sex_code= b.sex_code
	WHERE a.delete_flag = 0
	<if test="query.id != null and query.id != ''">
	    AND a.id = #{query.id}
	</if>
	<if test="query.realName != null and query.realName != ''">
	    AND a.real_name = #{query.realName}
	</if>
</select>

报错信息

完整的报错信息如下：

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='__frch_paymentPart_0.paymentPart', mode=IN, javaType=class java.lang.String, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #3 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
	at com.sun.proxy.$Proxy180.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:122)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:87)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
	at com.sun.proxy.$Proxy220.selectDebitRecordList(Unknown Source)
	at com.xxxx.impl.TestServiceImpl.selectDebitRecordList(CustomerCapitalPerformanceServiceImpl.java:67)
	at com.xxxx.impl.TestServiceImpl.actionMatchDebitAndRefundFund(CustomerCapitalPerformanceServiceImpl.java:170)
	at com.xxxx.impl.TestServiceImpl$$FastClassBySpringCGLIB$$5d538077.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
	at com.xxxx.impl.TestServiceImpl$$EnhancerBySpringCGLIB$$4337aa95.actionMatchDebitAndRefundFund(<generated>)
	at com.xxxx.impl.DemoServiceImpl.initData(DemoServiceImpl.java:1027)
	at com.xxxx.impl.DemoServiceImpl$$FastClassBySpringCGLIB$$836bef7f.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
	at com.xxxx.impl.DemoServiceImpl$$EnhancerBySpringCGLIB$$519a9cad.initData(<generated>)
	at com.xxxx.controller.DemoController.initData(DemoController.java:118)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:782)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:123)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
	at com.xxxx.controller.DemoController$$EnhancerBySpringCGLIB$$4461c669.initData(<generated>)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1060)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:962)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:652)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at com.xxxx.common.core.handler.RequestFilter.doFilter(RequestFilter.java:81)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:282)
	at org.springframework.cloud.context.scope.GenericScope$LockedScopedProxyFactoryBean.invoke(GenericScope.java:485)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
	at com.xxxx.common.core.handler.RequestFilter$$EnhancerBySpringCGLIB$$76316b14.doFilter(<generated>)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:327)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:115)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:81)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:126)
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:81)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
	at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:105)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
	at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:149)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
	at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
	at org.springframework.security.oauth2.provider.authentication.OAuth2AuthenticationProcessingFilter.doFilter(OAuth2AuthenticationProcessingFilter.java:176)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:103)
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:89)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
	at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90)
	at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:110)
	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:80)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
	at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:55)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
	at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:211)
	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:183)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:888)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1597)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:750)
Caused by: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='__frch_paymentPart_0.paymentPart', mode=IN, javaType=class java.lang.String, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #3 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
	at com.baomidou.mybatisplus.core.MybatisParameterHandler.setParameters(MybatisParameterHandler.java:215)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:94)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:64)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
	at com.sun.proxy.$Proxy343.parameterize(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
	at com.sun.proxy.$Proxy343.parameterize(Unknown Source)
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:99)
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:68)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:165)
	at com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor.willDoQuery(PaginationInnerInterceptor.java:134)
	at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:59)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
	at com.sun.proxy.$Proxy342.query(Unknown Source)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
	... 150 common frames omitted
Caused by: org.apache.ibatis.type.TypeException: Error setting non null for parameter #3 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
	at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:75)
	at com.baomidou.mybatisplus.core.MybatisParameterHandler.setParameters(MybatisParameterHandler.java:213)
	... 180 common frames omitted
Caused by: java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.ClientPreparedStatement.checkBounds(ClientPreparedStatement.java:1372)
	at com.mysql.cj.jdbc.ClientPreparedStatement.getCoreParameterIndex(ClientPreparedStatement.java:1385)
	at com.mysql.cj.jdbc.ClientPreparedStatement.setString(ClientPreparedStatement.java:1752)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setString(FilterChainImpl.java:3353)
	at com.alibaba.druid.filter.FilterAdapter.preparedStatement_setString(FilterAdapter.java:1363)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setString(FilterChainImpl.java:3350)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.setString(PreparedStatementProxyImpl.java:611)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.setString(DruidPooledPreparedStatement.java:369)
	at org.apache.ibatis.type.StringTypeHandler.setNonNullParameter(StringTypeHandler.java:31)
	at org.apache.ibatis.type.StringTypeHandler.setNonNullParameter(StringTypeHandler.java:26)
	at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:73)
	... 181 common frames omitted

从下往上看具体是哪一行代码抛出的异常，最终发现是以下代码产生的报错：

at com.mysql.cj.jdbc.ClientPreparedStatement.checkBounds(ClientPreparedStatement.java:1372)

因此我们通过报错信息可以找到指定的报错的代码行，如下

 protected void checkBounds(int paramIndex, int parameterIndexOffset) throws SQLException {
        synchronized (checkClosed().getConnectionMutex()) {
            if ((paramIndex < 1)) {
                throw SQLError.createSQLException(Messages.getString("PreparedStatement.49") + paramIndex + Messages.getString("PreparedStatement.50"),
                        MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT, this.exceptionInterceptor);
            } else if (paramIndex > ((PreparedQuery<?>) this.query).getParameterCount()) {
                //下面就是报错的代码行
1372                throw SQLError.createSQLException(
                        Messages.getString("PreparedStatement.51") + paramIndex + Messages.getString("PreparedStatement.52")
                                + ((PreparedQuery<?>) this.query).getParameterCount() + Messages.getString("PreparedStatement.53"),
                        MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT, this.exceptionInterceptor);
            } else if (parameterIndexOffset == -1 && paramIndex == 1) {
                throw SQLError.createSQLException(Messages.getString("PreparedStatement.63"), MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT,
                        this.exceptionInterceptor);
            }
        }
    }

因为paramIndex > ((PreparedQuery<?>) this.query).getParameterCount()所以执行了报错，我们继续排查，看一下((PreparedQuery<?>) this.query)到底是啥？
在这里插入图片描述

它实际上是对于分页查询时获取总数据量的查询，然而我们可以发现这里只看到了两个占位符?

SELECT COUNT(*) FROM user_table a WHERE a.delete_flag = 0 AND a.id= ? AND a.real_name = ?

当前xml的SQL查询中包含了临时表关联，在MyBatis-Plus构建查询总数的SQL中将不会包含临时表的关联（除非WHERE条件中存在临时表的字段条件），那么查询SQL里就只有2个占位符，那么在遍历原占位符时已经超过了该查询的占位符数量，因此产生了报错。

解决方案

目前存在3种可行的解决方案
1、在xml中的WHERE条件中加上临时表的条件，这样在执行查询总数的SQL时，则会加上关联临时表的语句

<select id="selectUserList" resultType="com.xxxx.vo.UserVO">
	SELECT
	a.username,
	b.sex_name
	FROM user_table a
	LEFT JOIN (
	    SELECT * FROM (
	        <foreach collection="query.sexList" item="sex" separator=" UNION ALL ">
	            select #{sex.key} AS sex_code, 
        		<!--新增代码 Start-->
	            0 AS delete_flag,
        		<!--新增代码 End-->
	            #{sex.value} AS sex_name
	        </foreach>
	    ) AS sex_table
	) AS b ON a.sex_code= b.sex_code
	WHERE a.delete_flag = 0
    <!--新增代码 Start-->
	AND b.delete_flag = 0
    <!--新增代码 End-->
	<if test="query.id != null and query.id != ''">
	    AND a.id = #{query.id}
	</if>
	<if test="query.realName != null and query.realName != ''">
	    AND a.real_name = #{query.realName}
	</if>
</select>

2、将临时表数据保存至数据库中，关联临时表查询改用关联表查询

<select id="selectUserList" resultType="com.xxxx.vo.UserVO">
	SELECT
	a.username,
	b.sex_name
	FROM user_table a
	LEFT JOIN sex_table AS b ON a.sex_code= b.sex_code
	WHERE a.delete_flag = 0
	<if test="query.id != null and query.id != ''">
	    AND a.id = #{query.id}
	</if>
	<if test="query.realName != null and query.realName != ''">
	    AND a.real_name = #{query.realName}
	</if>
</select>

3、屏蔽MyBatis-Plus中分页查询自带的总数据量查询，单独写SQL查询总数据量

程序员阿瓜
关注
专栏目录
Caused by: java.sql.SQLException: Parameter index out of range (1 ＞ number of parameters, which is 0
知识改变命运，ヾ(◍°∇°◍)ﾉﾞ【求关注】
08-12 517
在编写like模糊匹配时报错
关于 mysql报错java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2)
未出笼的菜包的博客
03-03 914
报错信息 Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested excep...
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
diyingshou8608的博客
06-13 391
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0). at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1056) at com.mysql.jdbc...
java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).
gaoxiu012的专栏
01-13 4306
java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1)，出现这种现像，主要是因为call Position_count_H_Basic_Info(?)调用这个存储过程，少了一个参数call Position_count_H_Basic_Info(?，?)
java.sql.SQLException: Parameter index out of range (5 ＞ number of parameters, which is 3).
Too_Soup_Soup的博客
12-16 5147
java.sql.SQLException: Parameter index out of range (5 > number of parameters, which is 3).
java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
diyingshou8608的博客
06-14 2519
java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).java.sql.SQLException: Parameter index out of range (3 > number of parameters, wh...
java.sql.SQLException: Parameter index out of range (3 ＞ number of parameters, which is 2).
weixin_48699240的博客
11-04 520
这里主要的问题还是往MySQL中插入数据的时候,使用占位符的时候出了问题 修改为如下代码就可以了,之前需要2个参数,但是实际只有2个 val sqlText = s"insert into ${mySQLTableName3}(ename,cityStr,city) values(?,?,?)" ...
java.sql.SQLException:Parameter index out of range (1＞number of paramters,which is 0)异常解决
简介
03-12 2159
java.sql.SQLException:Parameter index out of range (1>number of paramters,which is 0)异常解决 今天在编程做MySQL groupby DATE_FORMAT分组统计 写入SpringBoot项目，运行时发现出现了***java.sql.SQLException***异常，以为是查询结果接收错误，修改了半天，还是一样的异常结果。最后发现是DATE_FORMAT()中，预编译处理#{}外不需要添加’ '。问题终于解
java.sql.SQLException: Parameter index out of range (12 > numbers of parameters, which is 11).
小景的博客
04-26 1007
今天使用MyBatis的过程中遇到一个奇怪的错误，错误信息提示“参数个数不匹配”，不过我对了几遍Service、Mapper、Mapper.xml中的参数都没有发现问题。 后面注意到，在错误提示的第一行中还有一句“Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #12 with J...
在做SSM整合时，关于抛出 java.sql.SQLException: Parameter index out of range (3 > number of parameter 的问题
炫风丶小子
04-20 1866
第一次写博客，写的不好望谅解！！！！！！！！！！！！！！！！！ 最近在做毕业设计，用SSM框架编写一个Web项目，在做到根据名称模糊分页查询时，抛出了java.sql.SQLException: Parameter index out of range (3 &gt; number of parameter异常，各种尝试    这个异常依然没有解决   java.sql.SQLExcept...
java.sql.SQLException: Parameter index out of range (5 ＞ number of parameters, which is 4).
wwwwwww31311的博客
02-15 7939
问题描述： 启动项目调接口的时候控制台报错 Cause: java.sql.SQLException: Parameter index out of range (5 > number of parameters, which is 4). 原因分析： 可以看到控制台日志输出了下面这一句话 ### The error occurred while setting parameters 很好理解，注入参数的时候发生错误，那肯定就是自己写的sql有问题了，而且是参数绑定类型的错误 .
java.sql.SQLException: Parameter index out of range (0 < 1 ).
weixin_30716141的博客
04-07 514
向SQL中传入数据是从1开始的！！！ 从ResultSet中取数据也是从1开始的！ 转载于:https://www.cnblogs.com/xww115/p/10665853.html
[异常]java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0)
菜鸟腾飞
10-12 2443
Caused by: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property....
异常：java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0)
nelson的博客
07-10 5898
一、产生环境spring Boot + Mybatis + shiro 实现权限控制功能二、异常情况java.sql.SQLException: Parameter index out of range (1 &gt; number of parameters, which is 0). at com.mysql.jdbc.SQLError.createSQLException(SQLError...
​​​​​​​报错:java.sql.SQLException: Parameter index out of range (4 ＞ number of parameters, which is 3)
cherry的博客
11-01 788
题目：在一个二维数组中，每一行按照从左到右递增的顺序排序，每一列都按照从上到下递增的顺序排序。完成一个函数，输入这样的一个二维数组和一个整数，判断每组整数中是否含有该整数。 思路： ...
java.sql.SQLException: Parameter index out of range(mybatis的模糊查询)
wJulio的博客
07-30 563
  select * from record &lt;where&gt; &lt;if test="spuName!= null and spuName!= ''"&gt; MERCHANT_NO like CONCAT('%','${spuName}','%' ) &lt;/if&gt;
Java.sql.SQLException: Parameter index out of range (1 ＞ number of parameter)
c_c18的博客
11-23 2438
在上面的代码中，我们试图向数据库中的users表插入一条新的记录，传入了name和age作为参数。然而，有时候我们可能会遇到一个名为"java.sql.SQLException: Parameter index out of range (1 > number of parameter)"的异常。这个异常的意思是参数索引超出范围，通常是由于预编译的SQL语句中的参数索引与提供的参数数量不匹配所导致的。出现这个异常的原因是由于我们错误地设置了参数的索引，不匹配预编译的SQL语句中的参数索引。
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is
最新发布
03-27
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 这个异常通常在使用JDBC进行数据库操作时出现。它表示参数索引超出了参数的数量范围。这个错误通常是由于以下几种情况...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值