Using enum parameters in myBatis dynamic SQL

Question

How to do dynamic SQL in myBatis 3.1.1 based on an enum constant parameter?

[After MyBatis 3.4.1](https://github.com/mybatis/mybatis-3/issues/549), if your mapper method has only one param which is enum type and you don't annotate it with `@Param`, for example, that enum type is `Gender`, you can use `test="name == 'MALE'"`. — Jason Law, Apr 18 '20 at 04:00

score 18 · Accepted Answer · answered Oct 17 '12 at 11:58

18

How to do dynamic SQL based on enum constants

public enum Test {
    A, B;
}

Mapper.java:
    int test(@Param("t") Test t);

Mapper.xml:
    <select id="test" resultType="int">
        select
        <choose>
            <when test='t.name().equals("A")'>65</when>
            <when test='t.name().equals("B")'>66</when>
            <otherwise>0</otherwise>
        </choose>
    </select>

Notes

The test expression must refer to strings using double quotes, not single quotes.
You can't compare constants, only strings.

answered Oct 17 '12 at 11:58

Tomer

610
1
7
12

This however throws NullPointerException in case t is null – Jurass Nov 08 '21 at 15:01
@Jurass you can easily avoid null exception by inverting order of comparison ie: 65 – Pierpaolo Piccoli Sep 21 '22 at 09:03

score 10 · Answer 2 · answered Mar 18 '14 at 12:21

MyBatis allows == instead of equals for strings in if (or when) statements. So the following would also work (quotes don't matter):

public enum Test {
    A, B;
}

Mapper.java:

int test(@Param("t") Test t);

Mapper.xml:

<select id="test" resultType="int">
    select
    <choose>
        <when test="t.name() == 'A'">65</when>
        <when test="t.name() == 'B'">66</when>
        <otherwise>0</otherwise>
    </choose>
</select>

Vlad Dinulescu · Answer 3 · 2017-07-27T14:08:48.603

Besides Tomer's answer, which works just fine, you can also compare enum values by using the OGNL notation.

(Comparing enum values directly has compile-time advantages, ie, if you change the enum members, the query will fail fast.)

If the Test enum lives in full.package.name as a public class, Test.java, then, in mapper.xml, you'd have:

<when test='t == @full.package.name.Test@A'>

If the Test enum is inside another class, like this:

package full.package.name;

public class ExampleClass {
    public enum Test {
        A, B
    }
}

Then, in mapper.xml you'd have:

<when test='t == @full.package.name.ExampleClass$Test@A'>

The $ sign is undocumented in the OGNL specification, I've found it in the MyBatis issues page

PS: Older versions of MyBatis (for instance, 3.2.3) show ugly errors if the OGNL string is not correct (like, NullPointerException). Newer versions show a more understandable error.

score -1 · Answer 4 · answered Aug 09 '14 at 02:55

public enum Test {
    A, B;
}

Mapper.java:
    int test(@Param("t") Test t);

Mapper.xml:
    <select id="test" resultType="int">
        select
        <choose>
            **<when test='t.name() == &quot;A&quot;'>65</when>
            <when test='t.name() == &quot;A&quot;'>66</when>**
            <otherwise>0</otherwise>
        </choose>
    </select>   

is another solution.

Using enum parameters in myBatis dynamic SQL

4 Answers4