MyBaits批量Insert List<Dao>到Oracle数据库

        网上的视频基本上全是讲的MyBatis批量新增数据到MySql数据库中,所以按照网上的写法新增批量数据到Oracle中时会报错。所以这个问题从开始到解决零零碎碎花了大半天时间。特此记录一下。

        Controller、Service、Model之类的详细代码就不写了,直接上Mapper吧。

错误的操作如下:

@Mapper
public interface UserTestMapper {

    @Insert("<script>" +
            "insert into usertest(name,age,birthday) values" +
            "<foreach collection = 'userTestList' item = 'userTest' separator = ','>" +
            "( #{userTest.name} ,#{userTest.age} ,#{userTest.birthday} )" +
            "</foreach>" +
            "</script>")
    int insertUserTest(@Param("userTestList") List<UserTest> userTestList);
}

该方式的运行结果如下:

### The error may exist in com/matao/mapper/UserTestMapper.java (best guess)
### The error may involve com.matao.mapper.UserTestMapper.insertUserTest-Inline
### The error occurred while setting parameters
### SQL: insert into usertest(name,age,birthday) values  ( ? ,? ,? ) , ( ? ,? ,? ) , ( ? ,? ,? )
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
] with root cause

正确的操作如下:

@Mapper
public interface UserTestMapper {

    @Insert("<script>" +
            "insert into usertest(name,age,birthday) " +
            "<foreach collection = 'userTestList' item = 'userTest' separator = ' union all'>" +
            "( select #{userTest.name} ,#{userTest.age} ,#{userTest.birthday} from dual )" +
            "</foreach>" +
            "</script>")
    int insertUserTest(@Param("userTestList") List<UserTest> userTestList);
}

该方式的运行结果如下

JDBC Connection [HikariProxyConnection@83575218 wrapping oracle.jdbc.driver.T4CConnection@6079ccf8] will not be managed by Spring
==>  Preparing: insert into usertest(name,age,birthday) ( select ? ,? ,? from dual) union all ( select ? ,? ,? from dual) union all ( select ? ,? ,? from dual)
==> Parameters: qq(String), 12(String), 2013-05-06 12:00:23(String), ww(String), 12(String), 2013-05-06 12:00:23(String), ww(String), 12(String), 2013-05-06 12:00:23(String)
<==    Updates: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4af92ce0]

当然也还有其它写法,懒得写了!!!

其实就是------语法不支持!!!!!!!!!!!!!!!!!!!!