Use inequality comparison operators in MyBatis SQL

at first

MyBatis defines SQL statements in XML files and uses them, but cannot be used because they are XML. I didn't notice it and threw an error why! When I hit this SQL on the console, it could be executed normally! I was worried for an hour.

Execution environment

Example of error

<select id="hoge" resultType="hoge">
    SELECT
        *
    FROM
        HOGE
    WHERE
        HOGE.HOGE_DATE < SYSDATE
</select>

The WHERE clause that uses the inequality sign gets angry.

error contents

Cause: org.xml.sax.SAXParseException; lineNumber: XX; columnNumber: XX;The content of the element must consist of well-formed character data or markup.

Improvement measure ①

The method of enclosing the part using the inequality sign with <![CDATA [...]]>.

<select id="hoge" resultType="hoge">
    SELECT
        *
    FROM
        HOGE
    WHERE
        HOGE_DATE <![CDATA[ < ]]> SYSDATE
</select>

Improvement measure ②

It was okay to enclose the entire SELECT clause as in the example below. This one is more readable. (I feel.)

<select id="hoge" resultType="hoge">
<![CDATA[ 
    SELECT
        *
    FROM
        HOGE
    WHERE
        HOGE_DATE < SYSDATE
]]>
</select>

Improvement measure ③

This is the method of using the entity reference provided in the comment. After a little research, it seems that there are the following 5 types.

letter Entity reference
< &lt;
> &gt;
& &amp;
'(Single quote) &apos;
"(Double quotation) &quot;

Described using an entity reference, it looks like this:

<select id="hoge" resultType="hoge">
    SELECT
        *
    FROM
        HOGE
    WHERE
        HOGE_DATE &lt; SYSDATE
</select>

At the end

I'm touching MyBatis for the first time, but it's convenient because I can use it just by copying and calling the SQL that has been confirmed to work. The official MyBatis document has been translated into Japanese, and the amount of information is abundant, so it is easy to use. Dynamic SQL such as if, choose, and foreach will be summarized at a later date.

References

MyBatis – MyBatis 3 | Mapper XML File Character and entity references [XML standard]

Recommended Posts

Use inequality comparison operators in MyBatis SQL
[JAVA] [Spring] [MyBatis] Use IN () with SQL Builder
[JAVA] [Spring] [MyBatis] Use GROUP BY in SQL Builder
MyBatis string comparison
Use java.time in Jackson
Comparison operators and conditionals
Use Interceptor in Spring
Use OpenCV in Java
Use MouseListener in Processing
Use images in Rails
Use PostgreSQL in Scala
Use PreparedStatement in Java
How to use Java enums (Enum) in MyBatis Mapper XML