SpringBoot Mybatis Oracle相关报错解决与原理源码解析：java.sql.SQLException 无效的列类型 1111_Oxye

网络投稿 02-07 3314

问题

SpringBoot+Mybatis+Oracle这套环境出现报错

Caused by: org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='modifyTime', mode=IN, javaType=class java.util.Date, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #5 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型: 1111 at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:96) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441) at com.sun.proxy.$Proxy133.insert(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272) at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:59) 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.$Proxy138.insert(Unknown Source) at com.baomidou.mybatisplus.extension.service.IService.save(IService.java:63) at com.baomidou.mybatisplus.extension.service.IService$$FastClassBySpringCGLIB$$f8525d18.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.retry.annotation.AnnotationAwareRetryOperationsInterceptor.invoke(AnnotationAwareRetryOperationsInterceptor.java:156) 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.ais.cdc.service.impl.FileDirectoryServiceImpl$$EnhancerBySpringCGLIB$$9db0eb62.save(<generated>) at com.ais.cdc.config.init.DbDataInitializer.initParentDir(DbDataInitializer.java:28) at com.ais.cdc.config.init.DbDataInitializer.run(DbDataInitializer.java:21) at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:783) ... 10 common frames omitted Caused by: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='modifyTime', mode=IN, javaType=class java.util.Date, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #5 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型: 1111 at com.baomidou.mybatisplus.core.MybatisParameterHandler.setParameters(MybatisParameterHandler.java:236) 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:64) at com.sun.proxy.$Proxy228.parameterize(Unknown Source) at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:88) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) 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.Invocation.proceed(Invocation.java:49) at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:106) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62) at com.sun.proxy.$Proxy227.update(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:64) at com.sun.proxy.$Proxy227.update(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:64) at com.sun.proxy.$Proxy227.update(Unknown Source) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:194) at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:181) 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:427) ... 30 common frames omitted Caused by: org.apache.ibatis.type.TypeException: Error setting null for parameter #5 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型: 1111 at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:67) at com.baomidou.mybatisplus.core.MybatisParameterHandler.setParameters(MybatisParameterHandler.java:234) ... 69 common frames omitted Caused by: java.sql.SQLException: 无效的列类型: 1111 at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:4369) at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:5836) at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:5818) at oracle.jdbc.driver.OraclePreparedStatementWrapper.setNull(OraclePreparedStatementWrapper.java:1292) at com.alibaba.druid.pool.DruidPooledPreparedStatement.setNull(DruidPooledPreparedStatement.java:270) 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.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:67) at com.sun.proxy.$Proxy230.setNull(Unknown Source) at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:65) ... 70 common frames omitted 剖析报错

错误分一下层次

第一层转发错误

MyBatisSystemException

报错位置

org.mybatis.spring.MyBatisExceptionTranslator

public DataAccessException translateExceptionIfPossible(RuntimeException e) { if (e instanceof PersistenceException) { if (((RuntimeException)e).getCause() instanceof PersistenceException) { e = (PersistenceException)((RuntimeException)e).getCause(); } if (((RuntimeException)e).getCause() instanceof SQLException) { this.initExceptionTranslator(); String task = ((RuntimeException)e).getMessage() + "\n"; SQLException se = (SQLException)((RuntimeException)e).getCause(); DataAccessException dae = this.exceptionTranslator.translate(task, (String)null, se); return (DataAccessException)(dae != null ? dae : new UncategorizedSQLException(task, (String)null, se)); } else if (((RuntimeException)e).getCause() instanceof TransactionException) { throw (TransactionException)((RuntimeException)e).getCause(); } else { return new MyBatisSystemException((Throwable)e); } //*** }

只是负责转发报错

第二层获取默认jdbcType

TypeException

Caused by: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='modifyTime', mode=IN, javaType=class java.util.Date, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #5 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型: 1111 at com.baomidou.mybatisplus.core.MybatisParameterHandler.setParameters(MybatisParameterHandler.java:236)

报错位置

com.baomidou.mybatisplus.core.MybatisParameterHandler

public void setParameters(PreparedStatement ps) { //*** TypeHandler typeHandler = parameterMapping.getTypeHandler(); //这一步获取的jdbcType是null JdbcType jdbcType = parameterMapping.getJdbcType(); if (value == null && jdbcType == null) { //从配置获取jdbcType，获取到的是1111，指什么，看下一节 jdbcType = configuration.getJdbcTypeForNull(); } try { //把1111传给了TypeHandler对象的setParameter方法 typeHandler.setParameter(ps, i + 1, value, jdbcType); } catch (SQLException | TypeException var10) { throw new TypeException("Could not set parameters for mapping: " + parameterMapping + ". Cause: " + var10, var10); } //*** }

进configuration.getJdbcTypeForNull();看一下

org.apache.ibatis.session.Configuration

protected JdbcType jdbcTypeForNull = JdbcType.OTHER; public JdbcType getJdbcTypeForNull() { return this.jdbcTypeForNull; } public void setJdbcTypeForNull(JdbcType jdbcTypeForNull) { this.jdbcTypeForNull = jdbcTypeForNull; }

如果没人来set一下，那默认值是JdbcType.OTHER

org.apache.ibatis.type.JdbcType

public enum JdbcType { //*** OTHER(Types.OTHER), //*** }

java.sql.Types 用来标识数据库类型对应的值

package java.sql; /** * <P>The class that defines the constants that are used to identify generic * SQL types, called JDBC types. * <p> * This class is never instantiated. */ public class Types { /** * <P>The constant in the Java programming language, sometimes referred * to as a type code, that identifies the generic SQL type * <code>BIT</code>. */ public final static int BIT = -7; //*** /** * The constant in the Java programming language that indicates * that the SQL type is database-specific and * gets mapped to a Java object that can be accessed via * the methods <code>getObject</code> and <code>setObject</code>. */ public final static int OTHER = 1111; //*** }

这里获取了默认的jdbcType，值是1111

第三层空参数的处理

TypeException

Caused by: org.apache.ibatis.type.TypeException: Error setting null for parameter #5 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型: 1111 at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:67)

报错位置

org.apache.ibatis.type.BaseTypeHandler

public void setParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException { //参数为空，进来了 if (parameter == null) { //jdbcType是默认值1111兜底，不会进 if (jdbcType == null) { throw new TypeException("JDBC requires that the JdbcType must be specified for all nullable parameters."); } try { //进了这 ps.setNull(i, jdbcType.TYPE_CODE); } catch (SQLException var7) { //报了这个错 throw new TypeException("Error setting null for parameter #" + i + " with JdbcType " + jdbcType + " . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: " + var7, var7); } } else { try { this.setNonNullParameter(ps, i, parameter, jdbcType); } catch (Exception var6) { throw new TypeException("Error setting non null for parameter #" + i + " with JdbcType " + jdbcType + " . Try setting a different JdbcType for this parameter or a different configuration property. Cause: " + var6, var6); } } }

在参数为空、jdbcType为1111时，调用了PreparedStatement对象的setNull方法

第四层查找JDK与Oracle数据类型的对应关系

java.sql.SQLException

Caused by: java.sql.SQLException: 无效的列类型: 1111 at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:4369)

报错位置

oracle.jdbc.driver.OracleStatement

void setNullCritical(int var1, int var2) throws SQLException { int var3 = var1 - 1; if (var3 >= 0 && var1 <= this.numberOfBindPositions) { Object var4 = null; int var5 = this.getInternalType(var2); //*** }

这没捕获异常，进去

int getInternalType(int var1) throws SQLException { boolean var2 = false; short var3; switch (var1) { case -104: var3 = 183; break; case -103: var3 = 182; break; case -102: var3 = 231; break; case -101: case 2013: case 2014: var3 = 181; break; case -100: case 93: var3 = 180; break; case -16: case -1: var3 = 8; break; case -15: case -9: case 12: var3 = 1; break; case -14: var3 = 998; break; case -13: var3 = 114; break; case -10: case 2012: var3 = 102; break; case -8: var3 = 104; break; case -7: case -6: case -5: case 2: case 3: case 4: case 5: case 6: case 7: case 8: var3 = 6; break; case -4: var3 = 24; break; case -3: case -2: var3 = 23; break; case 0: var3 = 995; break; case 1: var3 = 96; break; case 70: var3 = 1; break; case 91: case 92: var3 = 12; break; case 100: var3 = 100; break; case 101: var3 = 101; break; case 252: var3 = 252; break; case 999: var3 = 999; break; case 2002: case 2003: case 2007: case 2008: case 2009: var3 = 109; break; case 2004: var3 = 113; break; case 2005: case 2011: var3 = 112; break; case 2006: var3 = 111; break; default: throw (SQLException)((SQLException)DatabaseError.createSqlException(this.getConnectionDuringExceptionHandling(), 4, Integer.toString(var1)).fillInStackTrace()); } return var3; }

这里1111匹配不到对应的数字，把1111，就是入参var1打了出来

这数字对应关系，应该就是java.sql.Types（参数类型值）和对应数据库（就是Oracle）的数据类型的对应关系，找不到就报错

解决设置 mybatis.configuration.jdbc-type-for-null='null'

mybatis-plus是这个

mybatis-plus.configuration.jdbc-type-for-null=null 原理

原理是这个类或读取配置中的jdbcTypeForNull，配置文件json中mybatis.configuration.jdbc-type-for-null对应的就是org.apache.ibatis.session.Configuration的jdbcTypeForNull

org\mybatis\spring\boot\mybatis-spring-boot-autoconfigure\2.2.0\mybatis-spring-boot-autoconfigure-2.2.0.jar!\META-INF\spring-configuration-metadata.json

{ "name": "mybatis.configuration.jdbc-type-for-null", "type": "org.apache.ibatis.type.JdbcType", "sourceType": "org.apache.ibatis.session.Configuration" },

null会定位到org.apache.ibatis.type.JdbcType的NULL(0),

org.apache.ibatis.builder.xml.XMLConfigBuilder

configuration.setJdbcTypeForNull(JdbcType.valueOf(props.getProperty("jdbcTypeForNull", "OTHER")));

org.apache.ibatis.type.JdbcType

NULL(0),