SpringBoot 中 使用Mybatis时 如果后端数据库为 Oracle注意事项
报错信息如下：
Could not set parameters for mapping: ParameterMapping{property='age', mode=IN, javaType=class java.lang.Integer, jdbcType=null
1 org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='age', mode=IN, javaType=class java.lang.Integer, 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 2 at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77) 3 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) 4 at com.sun.proxy.$Proxy72.insert(Unknown Source) 5 at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278) 6 at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58) 7 at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) 8 at com.sun.proxy.$Proxy95.insert(Unknown Source) 9 at com.leecx.service.impl.UserServiceImpl.saveUser(UserServiceImpl.java:23) 10 at com.leecx.controller.MyBatisCRUDController.saveUser(MyBatisCRUDController.java:44) 11 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 12 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 13 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 14 at java.lang.reflect.Method.invoke(Method.java:498) 15 at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) 16 at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) 17 at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97) 18 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) 19 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) 20 at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) 21 at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967) 22 at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) 23 at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) 24 at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) 25 at javax.servlet.http.HttpServlet.service(HttpServlet.java:635) 26 at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) 27 at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) 28 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) 29 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) 30 at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 31 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) 32 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) 33 at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:123) 34 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) 35 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) 36 at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) 37 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) 38 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) 39 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) 40 at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:108) 41 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) 42 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) 43 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) 44 at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81) 45 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) 46 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) 47 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) 48 at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) 49 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) 50 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) 51 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) 52 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199) 53 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) 54 at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478) 55 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) 56 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) 57 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) 58 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) 59 at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803) 60 at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) 61 at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) 62 at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459) 63 at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) 64 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) 65 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) 66 at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) 67 at java.lang.Thread.run(Thread.java:748) 68 Caused by: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='age', mode=IN, javaType=class java.lang.Integer, 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 69 at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:89) 70 at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:93) 71 at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:64) 72 at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:86) 73 at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49) 74 at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) 75 at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) 76 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 77 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 78 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 79 at java.lang.reflect.Method.invoke(Method.java:498) 80 at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) 81 at com.sun.proxy.$Proxy92.update(Unknown Source) 82 at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) 83 at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185) 84 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 85 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 86 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 87 at java.lang.reflect.Method.invoke(Method.java:498) 88 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) 89 ... 64 more 90 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 91 at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:47) 92 at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:87) 93 ... 83 more 94 Caused by: java.sql.SQLException: 无效的列类型: 1111 95 at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3900) 96 at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4406) 97 at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4388) 98 at oracle.jdbc.driver.OraclePreparedStatementWrapper.setNull(OraclePreparedStatementWrapper.java:1281) 99 at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setNull(FilterChainImpl.java:2807) 100 at com.alibaba.druid.filter.FilterAdapter.preparedStatement_setNull(FilterAdapter.java:1286) 101 at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setNull(FilterChainImpl.java:2804) 102 at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.setNull(PreparedStatementProxyImpl.java:402) 103 at com.alibaba.druid.pool.DruidPooledPreparedStatement.setNull(DruidPooledPreparedStatement.java:266) 104 at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:45) 105 ... 84 more
因为Mybaties对ORACLE数据的支持 中 默认存储数据库表的 字段不能保存为空
如果允许保存为空 则需要指定 参数 jdbcTypeForNull
1、配置文件只要在mybatis-config.xml 中添加 这条 代码即可
<settings>
<setting name="jdbcTypeForNull" value="NULL" />
</settings>
2、SpringBoot如果没有配置文件的 则需要在 SpringBoot启动主类上 加上如下代码
1 /*************对于是ORACLE 数据库的 mybatis必须增加如下的配置*********Start */ 2 @Bean 3 public ConfigurationCustomizer configurationCustomizer() { 4 return new MybatisPlusCustomizers(); 5 } 6 7 class MybatisPlusCustomizers implements ConfigurationCustomizer { 8 @Override 9 public void customize(org.apache.ibatis.session.Configuration configuration) { 10 configuration.setJdbcTypeForNull(JdbcType.NULL); 11 } 12 } 13 /*************对于是ORACLE 数据库的 mybatis必须增加如下的配置*********End */
如 Application.java
1 package com.leecx; 2 3 import org.apache.ibatis.type.JdbcType; 4 import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer; 5 import org.springframework.boot.SpringApplication; 6 import org.springframework.boot.autoconfigure.SpringBootApplication; 7 import org.springframework.context.annotation.Bean; 8 import org.springframework.context.annotation.ComponentScan; 9 10 import tk.mybatis.spring.annotation.MapperScan; 11 12 @SpringBootApplication 13 //扫描 mybatis mapper 包路径 14 @MapperScan(basePackages = "com.leecx.mapper") 15 //扫描 所有需要的包, 包含一些自用的工具类包 所在的路径 16 @ComponentScan(basePackages= {"com.leecx", "org.n3r.idworker"}) 17 public class Application { 18 19 /*************对于是ORACLE 数据库的 mybatis必须增加如下的配置*********Start */ 20 @Bean 21 public ConfigurationCustomizer configurationCustomizer() { 22 return new MybatisPlusCustomizers(); 23 } 24 25 class MybatisPlusCustomizers implements ConfigurationCustomizer { 26 @Override 27 public void customize(org.apache.ibatis.session.Configuration configuration) { 28 configuration.setJdbcTypeForNull(JdbcType.NULL); 29 } 30 } 31 /*************对于是ORACLE 数据库的 mybatis必须增加如下的配置*********End */ 32 33 public static void main(String[] args) { 34 SpringApplication.run(Application.class, args); 35 } 36 37 38 }
