MyBatis传入Employee列表多条件查询报ORA-00933错误求助

错误原因 #

Oracle默认不允许在单个SQL执行请求中运行多条用分号分隔的独立SELECT语句，你的MyBatis配置通过<foreach>生成了这种多语句格式的SQL，从而触发ORA-00933: SQL command not properly ended错误。

解决方案 #

方案1：使用UNION ALL合并查询 #

将<foreach>的分隔符改为UNION ALL，移除末尾的分号，生成单条合法SQL：

<resultMap type="EmployeeDetails" id="EmployeeDetailMap">
    <result property="employeeId" column="EMPLOYEE_ID"/>
    <result property="employeeName" column="EMPLOYEE_NAME"/>        
</resultMap>

<select id="getEmployeeDetails" resultMap="EmployeeDetailMap">
    <foreach collection="employeeList" item="employee" index="index" separator="UNION ALL">
        SELECT EMPLOYEE_ID, EMPLOYEE_NAME
        FROM EMPLOYEE 
        WHERE TRUNC(EFFECTIVE_DT)=TRUNC(SYSDATE) 
          AND EMPLOYEE_ID = #{employee.id} 
          AND EMPLOYEE_NAME = #{employee.Name}
    </foreach>
</select>

注意：原Employee类属性为id和Name，需修正参数绑定为#{employee.id}和#{employee.Name}，避免参数绑定失败。

方案2：使用Oracle多列IN条件（推荐） #

利用Oracle支持的多列匹配语法，将多个(id, name)对作为IN条件，SQL更简洁且执行效率更高：

<resultMap type="EmployeeDetails" id="EmployeeDetailMap">
    <result property="employeeId" column="EMPLOYEE_ID"/>
    <result property="employeeName" column="EMPLOYEE_NAME"/>        
</resultMap>

<select id="getEmployeeDetails" resultMap="EmployeeDetailMap">
    SELECT EMPLOYEE_ID, EMPLOYEE_NAME
    FROM EMPLOYEE 
    WHERE TRUNC(EFFECTIVE_DT)=TRUNC(SYSDATE) 
      AND (EMPLOYEE_ID, EMPLOYEE_NAME) IN (
          <foreach collection="employeeList" item="employee" separator=",">
              (#{employee.id}, #{employee.Name})
          </foreach>
      )
</select>

该方案仅执行一次查询，相比UNION ALL减少数据库交互，性能更优。

内容的提问来源于stack exchange，提问作者Pat