Using enum parameters in myBatis dynamic SQL

Question

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

Conventioneer

After MyBatis 3.4.1, 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'". – Backstroke

Answer 1

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.

Conventioneer

This however throws NullPointerException in case t is null – Koslo

@Koslo you can easily avoid null exception by inverting order of comparison ie: <when test='"A".equals(t.name())'>65</when> – Lacrimator

Answer 2

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>

Allative

Answer 3

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.

Displeasure

Answer 4

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.

Topside