Oracle数据库LocalDateTime在Mybatis-plus报错：无效列类型；及【SQLException: 不支持的字符集】解决

左手藏剑

已于 2023-12-27 18:15:54 修改

阅读量7.9k

点赞数 8

分类专栏： mybatis-plus使用文章标签： oracle 数据库 database

于 2021-12-07 11:54:48 首次发布

本文链接：https://blog.csdn.net/qq_42533633/article/details/121765133

版权

mybatis-plus使用专栏收录该内容

1 篇文章 0 订阅

订阅专栏

oracle版本：11.2.0.4.0

pom依赖：

         <!--oracle驱动-->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.4</version>
        </dependency>
         <!--连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>
        <!--持久层-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>

实体类：

@Data
@TableName("TEST")
@ApiModel(value="Test对象", description="测试date")
public class Test implements Serializable {
    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "id")
    @TableId
    private Long id;

    @ApiModelProperty(value = "创建日期")
    @TableField(fill = FieldFill.INSERT)
    private LocalDateTime createTime;

    @ApiModelProperty(value = "开始时间")
    private LocalTime startTime;

    @ApiModelProperty(value = "名称")
    private String name;
}

主要报错信息：

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='createTime', mode=IN, javaType=class java.time.LocalDateTime, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #2 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: 无效的列类型
	
Caused by: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='createTime', mode=IN, javaType=class java.time.LocalDateTime, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #2 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: 无效的列类型
	at com.baomidou.mybatisplus.core.MybatisDefaultParameterHandler.setParameters(MybatisDefaultParameterHandler.java:233)
	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.$Proxy319.parameterize(Unknown Source)
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:97)
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:53)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.update(MybatisCachingExecutor.java:83)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
	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)
	... 100 common frames omitted
Caused by: org.apache.ibatis.type.TypeException: Error setting non null for parameter #2 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: 无效的列类型
	at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:71)
	at com.baomidou.mybatisplus.core.MybatisDefaultParameterHandler.setParameters(MybatisDefaultParameterHandler.java:231)
	... 119 common frames omitted
Caused by: java.sql.SQLException: 无效的列类型
	at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8921)
	at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8396)
	at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9176)
	at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9153)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:234)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setObject(FilterChainImpl.java:3453)
	at com.alibaba.druid.filter.FilterAdapter.preparedStatement_setObject(FilterAdapter.java:1321)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setObject(FilterChainImpl.java:3450)
	at com.alibaba.druid.filter.FilterAdapter.preparedStatement_setObject(FilterAdapter.java:1321)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setObject(FilterChainImpl.java:3450)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.setObject(PreparedStatementProxyImpl.java:460)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.setObject(DruidPooledPreparedStatement.java:480)
	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.$Proxy321.setObject(Unknown Source)
	at org.apache.ibatis.type.LocalDateTimeTypeHandler.setNonNullParameter(LocalDateTimeTypeHandler.java:33)
	at org.apache.ibatis.type.LocalDateTimeTypeHandler.setNonNullParameter(LocalDateTimeTypeHandler.java:28)
	at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:69)
	... 120 common frames omitted

网上也看了其它同学的帖子：Springboot Mybatis-plus Druid Oracle LocalDateTime转换异常_柏伦-CSDN博客地址其中主流解决方案是：

换连接池
LocalDateTime改为Date。
Druid改为1.1.21 同时将MybatisPlus版本为3.0.1

但是照做后，并没有解决问题。猜测数据类型在哪一层不匹配，就做了多方尝试，指定mp映射数据类型，不行；换oracle驱动版本到ojdbc7，也不行；又大胆试了一下ojdbc8，竟然成功了。

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>21.3.0.0</version>
</dependency>

由此，我推测oracle后续的驱动版本不再是固定对应oracle版本，而是可以向后兼容的了。

比较有意思的是：oracle中没有time类型，所以新增LocalTime类型时增进去的是1970-01-01的时间。

新增时参数：

{
	"name": "无可奈何花落去",
	"startTime": "12:12:12"
}

数据库里：

还有一点要注意：需要加入字符集支持，否则查询时要报错

Caused by: java.sql.SQLException: 不支持的字符集 (在类路径中添加 orai18n.jar): ZHS16GBK
	at oracle.sql.CharacterSetUnknown.failCharsetUnknown(CharacterSetFactoryThin.java:233)
	at oracle.sql.CharacterSetUnknown.convert(CharacterSetFactoryThin.java:194)
	at oracle.jdbc.driver.PhysicalConnection.throughDbCharset(PhysicalConnection.java:10365)
	at oracle.jdbc.driver.PhysicalConnection.enquoteIdentifier(PhysicalConnection.java:10442)
	at oracle.jdbc.driver.OracleStatement.enquoteIdentifier(OracleStatement.java:6452)

字符集支持pom:

        <dependency>
            <groupId>com.oracle.database.nls</groupId>
            <artifactId>orai18n</artifactId>
            <version>21.3.0.0</version>
        </dependency>

成功查询结果：