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