问题代码
<insert id="insertBatch" keyProperty="id" useGeneratedKeys="true">
insert into QC_OP_DR_RECIPE_ITEM (ID,RECIPE_ID, SN, RECIPE_SN, GROUP_SN, CHARGE_ITEM_ID, ITEM_ID, ITEM_NAME, ITEM_PARENT_ID, CHARA, IS_DISPLAY, SPEC, USAGE, DOSE, DOSE_UNIT, FREQUENCY, DAYS, QTY, UNIT, PRICE, AMT, REMARK, EXEC_DEPT_ID, EXEC_DEPT_NAME, FOOT_NOTE, INSPECT_RESULT, CREATED_TIME, ITEM_TYPE, ENTRY_TYPE, DOSAGE, DOSAGE_UNIT, IS_MANUAL_PRICE, EXEC_NURSE_ID, EXEC_NURSE_NAME, EXEC_TIME, EXEC_STATUS, SKIN_TEST, INVENTORY_ID, INVENTORY_SPLIT_FLAG, CREATOR_CORP_ID, IS_STRIKING, DOCTOR_NAME, HIS_CHECK_PART_CODE)
<foreach collection="entities" item="entity" separator=",">
(
#{entity.id,jdbcType=VARCHAR},#{entity.recipeId,jdbcType=VARCHAR}, #{entity.sn,jdbcType=NUMERIC}, #{entity.recipeSn,jdbcType=NUMERIC}, #{entity.groupSn,jdbcType=NUMERIC}, #{entity.chargeItemId,jdbcType=VARCHAR}, #{entity.itemId,jdbcType=VARCHAR}, #{entity.itemName,jdbcType=VARCHAR}, #{entity.itemParentId,jdbcType=VARCHAR}, #{entity.chara,jdbcType=VARCHAR}, #{entity.isDisplay,jdbcType=CHAR}, #{entity.spec,jdbcType=VARCHAR}, #{entity.usage,jdbcType=VARCHAR}, #{entity.dose,jdbcType=NUMERIC}, #{entity.doseUnit,jdbcType=VARCHAR}, #{entity.frequency,jdbcType=VARCHAR},#{entity.days,jdbcType=NUMERIC}, #{entity.qty,jdbcType=NUMERIC}, #{entity.unit,jdbcType=VARCHAR}, #{entity.price,jdbcType=NUMERIC}, #{entity.amt,jdbcType=NUMERIC}, #{entity.remark,jdbcType=VARCHAR}, #{entity.execDeptId,jdbcType=VARCHAR}, #{entity.execDeptName,jdbcType=VARCHAR}, #{entity.footNote,jdbcType=VARCHAR}, #{entity.inspectResult,jdbcType=VARCHAR}, #{entity.createdTime,jdbcType=TIMESTAMP}, #{entity.itemType,jdbcType=VARCHAR},#{entity.entryType,jdbcType=NUMERIC}, #{entity.dosage,jdbcType=NUMERIC}, #{entity.dosageUnit,jdbcType=VARCHAR}, #{entity.isManualPrice,jdbcType=CHAR}, #{entity.execNurseId,jdbcType=VARCHAR}, #{entity.execNurseName,jdbcType=VARCHAR}, #{entity.execTime,jdbcType=VARCHAR}, #{entity.execStatus,jdbcType=CHAR}, #{entity.skinTest,jdbcType=VARCHAR}, #{entity.inventoryId,jdbcType=VARCHAR}, #{entity.inventorySplitFlag,jdbcType=CHAR}, #{entity.creatorCorpId,jdbcType=VARCHAR}, #{entity.isStriking,jdbcType=CHAR}, #{entity.doctorName,jdbcType=VARCHAR}, #{entity.hisCheckPartCode,jdbcType=VARCHAR}
)
</foreach>
</insert>
执行报错
### SQL: insert into QC_OP_DR_RECIPE_ITEM(ID,RECIPE_ID, SN, RECIPE_SN, GROUP_SN, CHARGE_ITEM_ID, ITEM_ID, ITEM_NAME, ITEM_PARENT_ID, CHARA, IS_DISPLAY, SPEC, USAGE, DOSE, DOSE_UNIT, FREQUENCY, DAYS, QTY, UNIT, PRICE, AMT, REMARK, EXEC_DEPT_ID, EXEC_DEPT_NAME, FOOT_NOTE, INSPECT_RESULT, CREATED_TIME, ITEM_TYPE, ENTRY_TYPE, DOSAGE, DOSAGE_UNIT, IS_MANUAL_PRICE, EXEC_NURSE_ID, EXEC_NURSE_NAME, EXEC_TIME, EXEC_STATUS, SKIN_TEST, INVENTORY_ID, INVENTORY_SPLIT_FLAG, CREATOR_CORP_ID, IS_STRIKING, DOCTOR_NAME, HIS_CHECK_PART_CODE) values (?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?,?, ?, ?, ?) , (?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?,?, ?, ?, ?)
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
正确代码
<insert id="insertBatch" parameterType="java.util.List">
insert into QC_OP_DR_RECIPE_ITEM ( ID, RECIPE_ID, SN, RECIPE_SN, GROUP_SN, CHARGE_ITEM_ID, ITEM_ID, ITEM_NAME, ITEM_PARENT_ID, CHARA, IS_DISPLAY, SPEC, USAGE, DOSE, DOSE_UNIT, FREQUENCY, DAYS, QTY, UNIT, PRICE, AMT, REMARK, EXEC_DEPT_ID, EXEC_DEPT_NAME, FOOT_NOTE, INSPECT_RESULT, CREATED_TIME, ITEM_TYPE, ENTRY_TYPE, DOSAGE, DOSAGE_UNIT, IS_MANUAL_PRICE, EXEC_NURSE_ID, EXEC_NURSE_NAME, EXEC_TIME, EXEC_STATUS, SKIN_TEST, INVENTORY_ID, INVENTORY_SPLIT_FLAG, CREATOR_CORP_ID, IS_STRIKING, DOCTOR_NAME, HIS_CHECK_PART_CODE)
values
<foreach collection="entities" item="entity" separator="UNION ALL">
select
#{entity.id,jdbcType=VARCHAR},#{entity.recipeId,jdbcType=VARCHAR}, #{entity.sn,jdbcType=NUMERIC}, #{entity.recipeSn,jdbcType=NUMERIC}, #{entity.groupSn,jdbcType=NUMERIC}, #{entity.chargeItemId,jdbcType=VARCHAR}, #{entity.itemId,jdbcType=VARCHAR}, #{entity.itemName,jdbcType=VARCHAR}, #{entity.itemParentId,jdbcType=VARCHAR}, #{entity.chara,jdbcType=VARCHAR}, #{entity.isDisplay,jdbcType=CHAR}, #{entity.spec,jdbcType=VARCHAR}, #{entity.usage,jdbcType=VARCHAR}, #{entity.dose,jdbcType=NUMERIC}, #{entity.doseUnit,jdbcType=VARCHAR}, #{entity.frequency,jdbcType=VARCHAR},#{entity.days,jdbcType=NUMERIC}, #{entity.qty,jdbcType=NUMERIC}, #{entity.unit,jdbcType=VARCHAR}, #{entity.price,jdbcType=NUMERIC}, #{entity.amt,jdbcType=NUMERIC}, #{entity.remark,jdbcType=VARCHAR}, #{entity.execDeptId,jdbcType=VARCHAR}, #{entity.execDeptName,jdbcType=VARCHAR}, #{entity.footNote,jdbcType=VARCHAR}, #{entity.inspectResult,jdbcType=VARCHAR}, #{entity.createdTime,jdbcType=TIMESTAMP}, #{entity.itemType,jdbcType=VARCHAR},#{entity.entryType,jdbcType=NUMERIC}, #{entity.dosage,jdbcType=NUMERIC}, #{entity.dosageUnit,jdbcType=VARCHAR}, #{entity.isManualPrice,jdbcType=CHAR}, #{entity.execNurseId,jdbcType=VARCHAR}, #{entity.execNurseName,jdbcType=VARCHAR}, #{entity.execTime,jdbcType=VARCHAR}, #{entity.execStatus,jdbcType=CHAR}, #{entity.skinTest,jdbcType=VARCHAR}, #{entity.inventoryId,jdbcType=VARCHAR}, #{entity.inventorySplitFlag,jdbcType=CHAR}, #{entity.creatorCorpId,jdbcType=VARCHAR}, #{entity.isStriking,jdbcType=CHAR}, #{entity.doctorName,jdbcType=VARCHAR}, #{entity.hisCheckPartCode,jdbcType=VARCHAR}
from dual
</foreach>
</insert>
1、 将VALUES去掉
2、 foreach标签内的()去掉,使用select … from dual
3、 separator =","改为separator=“UNION ALL”