如何在mybatis中使用单个查询将值插入多个表中

Question

我试图使用 mybatis 通过单个查询插入 4 个表，但不幸的是，这样做时出错，请帮助我。

映射器.xml

<insert id="insert" parameterType="com.technoshinelabs.eduskill.bean.Course" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
    START TRANSACTION;
    INSERT INTO course (course_name, training_provider, type_of_course, training_duration, number_of_license, type_of_users, about_course,
    who_can_attend, certificate, training_type, course_image,created_date,updated_date)
    VALUES (#{courseName}, #{trainingProvider}, #{typeOfCourse}, #{trainingDuation}, #{numberOfLicense}, #{typeOfUsers},
    #{aboutCourse}, #{whoCanAttend}, #{certificate}, #{trainingType}, #{courseImagePath}, now(), now());
    INSERT INTO assignment_material (course_id, file_name)
    VALUES (#{courseId},
    <foreach item="Course" collection="assignmentMaterialPath">#{Course}</foreach>);
    INSERT INTO trainer_image (course_id, file_name)
    VALUES (#{courseId},
    <foreach item="Course" collection="trainerImagePath">#{Course}</foreach>);
    INSERT INTO trainer_provider_logo (course_id, file_name)
    VALUES (#{courseId},
    <foreach item="Course" collection="trainingProviderPath">#{Course}</foreach>);
    COMMIT;
</insert>

如果我尝试执行此操作，则会出现错误：

There was an unexpected error 
(type=Internal Server Error, status=500).
### Error updating database. 
Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO course (course_name, training_provider, type_of_course, training_dur' at line 2 
### The error may involve com.technoshinelabs.eduskill.mapper.CourseMapper.insert-Inline 
### The error occurred while setting parameters 
### SQL: 
START TRANSACTION; 
INSERT INTO course (course_name, training_provider, type_of_course, training_duration, number_of_license, type_of_users, about_course, who_can_attend, certificate, training_type, course_image,created_date,updated_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now()); 
INSERT INTO assignment_material (course_id, file_name) VALUES (?, ? ? ? ); INSERT INTO trainer_image (course_id, file_name) VALUES (?, ? ? ? ); 
INSERT INTO trainer_provider_logo (course_id, file_name) VALUES (?, ? ? ? );
COMMIT; 
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO course (course_name, training_provider, type_of_course, training_dur' at line 2 ; bad SQL grammar []; 
nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO course (course_name, training_provider, type_of_course, training_dur' at line 2

Answer 1

删除START TRANSACTION;并COMMIT;它们不得是查询的一部分。

4 个插入将在单个语句中执行。但事务必须由应用程序或容器管理。

交易实际上是在会话打开时开始的。