CODEWITHSUNDEEP
mysqlcasemybatisibatis
Mingoo
Mingoo

Reputation: 477

Build MyBatis query with NOT IN and UNION statement

I'm new to MyBatis, and I'm using version 3.2.3. I need to use UNION and NOT IN in MySql, and here's the query and it works fine with 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"
)

If I put this query in MyBatis XML file, then it shows grammar error.

<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>

Can I use UNION like this in MyBatis? I searched the web, but couldn't find the exact answer. Maybe I need to change my query to get the result using MyBatis. Please give me some advise. Thanks!

Upvotes: 1

Views: 5907

Answers (2)

Mingoo
Mingoo

Reputation: 477

I found the answer while I searched the Internet several hours. Basically, you cannot use <. Instead, you should use & l t ; without space between characters just like HTML code. Below is the working code I just tested.

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

I thought that UNION wouldn't work, but I was wrong. You could use UNION.

Also, I've tried to use <![CDATA[ ......... ]]> BUT it didn't work. My Tomcat server says there's an error in XML file. I'm not sure it will work for other cases.

So, IMO, using < will be the most simple way to do this.

Upvotes: 0

Roman-Stop RU aggression in UA
Roman-Stop RU aggression in UA

Reputation: 15861

There are two options:

  1. Use xml character entities for xml special symbols like &lt;

  2. Use character data blocks to prevent xml parser from parsing of query like this

    <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>

Upvotes: 2

Related Questions