网上的视频基本上全是讲的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]
当然也还有其它写法,懒得写了!!!
其实就是------语法不支持!!!!!!!!!!!!!!!!!!!!