关于mybatis-plus 设置jdbc-type-for-null:‘null‘的问题-优快云博客

本文链接：https://blog.youkuaiyun.com/ldlly0505/article/details/130332042

本文探讨了在使用mybatis-plus批量插入Oracle数据库时遇到的jdbc-type-for-null配置问题，通过三种不同的写法进行示例，推荐第三种使用mybatis-plus自动生成的batchSave方法，以避免字段类型不一致导致的错误，实现省时省力的批量插入操作。

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

自定义批量插入oracle数据库目前我采用的有三种写法，今天主要就是要谈一谈union all这种写法的问题。话不多说，直接上代码

第一种写法

配置文件：

mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true
    auto-mapping-behavior: full
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    jdbc-type-for-null: 'null'
  mapper-locations: classpath*:mapper/**/*Mapper.xml

import com.lindl.business.entity.DbTest;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface DbTestMapper {
    int batchInsert(@Param("list") List<DbTest> list);
}

mapper.xml

    <insert id="batchInsert">


        insert into DB_TEST(ID, CREATE_TIME)
        <foreach collection="list" index="index" item="item" separator="union all">
            (select #{item.id}, #{item.createTime} from dual)
        </foreach>
    </insert>

controller


    @Resource
    DbTestMapper dbTestMapper;

    @GetMapping("/test")
    public String test() {
        List<DbTest> list = new ArrayList<>();
         list.add(DbTest.build("13", null));
        list.add(DbTest.build("14", new Date()));
        list.add(DbTest.build("15", null));
        list.add(DbTest.build("16", new Date()));
        list.add(DbTest.build("17", null));
        list.add(DbTest.build("18", new Date()));
        dbTestMapper.batchInsert(list);
        return "ok";
    }

报错信息：

un Apr 23 22:22:40 CST 2023
There was an unexpected error (type=Internal Server Error, status=500).
### Error updating database. Cause: java.sql.SQLSyntaxErrorException: ORA-01790: 表达式必须具有与对应表达式相同的数据类型 ### The error may exist in file [D:\code\spring-cloud-demo\nacos-server2\target\classes\mapper\DbTestMapper.xml] ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: insert into DB_TEST(ID, CREATE_TIME) (select ?, ? from dual) union all (select ?, ? from dual) union all (select ?, ? from dual) union all (select ?, ? from dual) union all (select ?, ? from dual) union all (select ?, ? from dual) ### Cause: java.sql.SQLSyntaxErrorException: ORA-01790: 表达式必须具有与对应表达式相同的数据类型 ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-01790: 表达式必须具有与对应表达式相同的数据类型

当我们代码做了一定的处理后：

  @Resource
    DbTestMapper dbTestMapper;

    @GetMapping("/test")
    public String test() {
        List<DbTest> list = new ArrayList<>();
         list.add(DbTest.build("13", null));
        list.add(DbTest.build("14", new Date()));
        list.add(DbTest.build("15", null));
        list.add(DbTest.build("16", new Date()));
        list.add(DbTest.build("17", null));
        list.add(DbTest.build("18", new Date()));
        List<DbTest> noZeroList = list.stream().filter(e -> !Objects.isNull(e.getCreateTime())).collect(Collectors.toList());
        List<DbTest> zeroList = list.stream().filter(e -> Objects.isNull(e.getCreateTime())).collect(Collectors.toList());
        dbTestMapper.batchInsert(noZeroList);
        dbTestMapper.batchInsert(zeroList);
        return "ok";
    }

这样就不会报错，看后台日志：

SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@649a9bdf] was not registered for synchronization because synchronization is not active
2023-04-23 22:25:17.598  INFO 6756 --- [nio-8089-exec-1] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
JDBC Connection [oracle.jdbc.driver.T4CConnection@c2d543a] will not be managed by Spring
==>  Preparing: insert into DB_TEST(ID, CREATE_TIME) (select ?, ? from dual) union all (select ?, ? from dual) union all (select ?, ? from dual) 
==> Parameters: 14(String), 2023-04-23 22:25:17.488(Timestamp), 16(String), 2023-04-23 22:25:17.488(Timestamp), 18(String), 2023-04-23 22:25:17.488(Timestamp)
<==    Updates: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@649a9bdf]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7b2b4236] was not registered for synchronization because synchronization is not active
JDBC Connection [oracle.jdbc.driver.T4CConnection@c2d543a] will not be managed by Spring
==>  Preparing: insert into DB_TEST(ID, CREATE_TIME) (select ?, ? from dual) union all (select ?, ? from dual) union all (select ?, ? from dual) 
==> Parameters: 13(String), null, 15(String), null, 17(String), null
<==    Updates: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7b2b4236]

这种情况就是：

union all 这种写法，它需要union的每一条插入sql的每个字段的类型都要保持一致，上面那种情况就是存在日期有的为空：由于我们配置了 jdbc-type-for-null: 'null'这个配置，这种为空的数据类型会被设置为NULL类型，而不为空的createTime的数据类型为DATE，这样就会导致报上面那个错了：ORA-01790: 表达式必须具有与对应表达式相同的数据类型.为什么要加入这个配置呢？原因在于不加的话mybatis-plus对于空的数据它会默认给它设置为JdbcType.OTHER这种类型，这样会报另一个错：

Sun Apr 23 22:33:23 CST 2023
There was an unexpected error (type=Internal Server Error, status=500).
nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='__frch_item_0.createTime', 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 #2 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型: 1111

这种如果表里只有一个字段可能为空，还好处理（像我上面那样根据是否为空去分开插入），如果有多个的情况，不建议采用这种写法去取批量插入。可以采用下面那种写法

第二种批量插入写法

mapper.xml

<insert id="batchInsert">
        INSERT ALL
        <foreach collection="list" item="item" index="index">
            into DB_TEST(ID, CREATE_TIME)
            values
            (#{item.id}, #{item.createTime})
        </foreach>
        SELECT 1 FROM DUAL

    </insert>

controller

 @GetMapping("/test")
    public String test() {
        List<DbTest> list = new ArrayList<>();
        list.add(DbTest.build("19", null));
        list.add(DbTest.build("20", new Date()));
        list.add(DbTest.build("21", null));
        list.add(DbTest.build("22", new Date()));
        list.add(DbTest.build("23", null));
        list.add(DbTest.build("24", new Date()));
        dbTestMapper.batchInsert(list);
        return "ok";
    }

后台日志：

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3561c885] was not registered for synchronization because synchronization is not active
2023-04-23 22:37:56.014  INFO 5228 --- [nio-8089-exec-1] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
JDBC Connection [oracle.jdbc.driver.T4CConnection@24cbb558] will not be managed by Spring
==>  Preparing: INSERT ALL into DB_TEST(ID, CREATE_TIME) values (?, ?) into DB_TEST(ID, CREATE_TIME) values (?, ?) into DB_TEST(ID, CREATE_TIME) values (?, ?) into DB_TEST(ID, CREATE_TIME) values (?, ?) into DB_TEST(ID, CREATE_TIME) values (?, ?) into DB_TEST(ID, CREATE_TIME) values (?, ?) SELECT 1 FROM DUAL 
==> Parameters: 19(String), null, 20(String), 2023-04-23 22:37:55.907(Timestamp), 21(String), null, 22(String), 2023-04-23 22:37:55.907(Timestamp), 23(String), null, 24(String), 2023-04-23 22:37:55.907(Timestamp)
<==    Updates: 6

这种亲测没啥问题