mysql - 使用 NOT IN 和 UNION 语句构建 MyBatis 查询

Question

我是 MyBatis 的新手，我使用的是 3.2.3 版本。我需要在 MySql 中使用 UNION 和 NOT IN，这里是查询，它在 Sequal Pro 中运行良好。

SELECT DISTINCT * FROM room WHERE roomNo NOT IN (
    SELECT roomNo FROM reservation WHERE resvStatus='DONE' AND checkout > "2014-04-27" AND checkout <= "2014-04-29"
    UNION
    SELECT roomNo FROM reservation WHERE resvStatus='DONE' AND checkin >= "2014-04-27" AND checkin < "2014-04-29"
    UNION
    SELECT roomNo FROM reservation WHERE resvStatus='DONE' AND checkin < "2014-04-27" AND checkout > "2014-04-29"
)

如果我把这个查询放在 MyBatis XML 文件中，那么它会显示语法错误。

<select id="roomSearch" parameterType="map" resultType="Room">
SELECT DISTINCT * FROM room WHERE roomNo NOT IN (
    SELECT roomNo FROM reservation WHERE resvStatus='DONE' AND checkout > #{checkIn} AND checkout <= #{checkOut}
    UNION
    SELECT roomNo FROM reservation WHERE resvStatus='DONE' AND checkin >= #{checkIn} AND checkin < #{checkOut}
    UNION
    SELECT roomNo FROM reservation WHERE resvStatus='DONE' AND checkin < #{checkIn} AND checkout > #{checkOut}
)
</select>

我可以在 MyBatis 中这样使用 UNION 吗？我在网上搜索，但找不到确切的答案。也许我需要更改查询以使用 MyBatis 获取结果。请给我一些建议。谢谢!

Answer 1

有两种选择:

1. 将 xml 字符实体用于 xml 特殊符号，如 <

2. 使用字符数据 block 来防止xml解析器解析这样的查询

   <select id="searchRoom" parameterType="map" resultType="Room">
   <![CDATA[
   SELECT DISTINCT * FROM room WHERE roomNo NOT IN (
       SELECT roomNo FROM reservation WHERE resvStatus="DONE" AND checkout > #{checkIn} AND checkout <= #{checkOut}  
       UNION 
       SELECT roomNo FROM reservation WHERE resvStatus="DONE" AND checkin >= #{checkIn} AND checkin < #{checkOut} 
       UNION 
       SELECT roomNo FROM reservation WHERE resvStatus="DONE" AND checkin < #{checkIn} AND checkout > #{checkOut}
    ]]>
   </select>