MyBatis enum usage

Question

I know this has been asked before, but I wasn't able to implement a solution based on the information I found so far. so perhaps someone can explain it to me.

I have a table "status". It has two columns:id and name. id is a PK.

Instead of using a POJO Status, I would like to use an enum. I created such an enum as follows:

public enum Status {
    NEW(1), READY(2), CLOSED(3);

    private int id;

    public void setId(int id) {
        this.id = id;
    }

    public int getId() {
        return this.id;
    }

    Status(int id) {
        this.id = id;
    }
}

here is my mapper

     <select id="getStatusByName" resultType="Status" parameterType="String">       
        SELECT  ls.id, ls.name
        FROM status AS ls
        WHERE ls.name = #{name}
    </select>

but for some reason, when I try to retrieve an enum, something breaks, but no exception is thrown.

Answer 1

I have worked on this question from a couple of angles and here are my findings. Caveat: I did all these investigations using MyBatis-3.1.1, so things might have behaved differently in earlier versions.

First, MyBatis has a built-in EnumTypeHandler. By default, any time you specify a Java enum as a resultType or parameterType, this is what will handle that type. For queries, when trying to convert a database record into a Java enum, the EnumTypeHandler only takes one argument and tries to look up the Java enum value that corresponds to that value.

An example will better illustrate. Suppose your query above returns 2 and "Ready" when I pass in "Ready" as the argument. In that case, I get the error message No enum constant com.foo.Status.2. If I reverse the order of your SELECT statement to be

SELECT ls.name, ls.id

then the error message is No enum constant com.foo.Status.Ready. I assume you can infer what MyBatis is doing. Note that the EnumTypeHandler is ignoring the second value returned from the query.

Changing your query to

SELECT UPPER(ls.name)

causes it to work: the Status.READY enum is returned.

So next I tried to define my own TypeHandler for the Status enum. Unfortunately, as with the default EnumTypeHandler, I could only get one of the values (id or name) in order to reference the right Enum, not both. So if the database id does not match the value you hardcoded above, then you will have a mismatch. If you ensure that the database id always matches the id you specify in the enum, then all you need from the database is the name (converted to upper case).

Then I thought I'd get clever and implement a MyBatis ObjectFactory, grab both the int id and String name and ensure those are matched up in the Java enum I pass back, but that did not work as MyBatis does not call the ObjectFactory for a Java enum type (at least I couldn't get it to work).

So my conclusion is that Java enums in MyBatis are easy as long as you just need to match up the name from the database to the enum constant name - either use the built-in EnumTypeHandler or define your own if doing UPPER(name) in the SQL isn't enough to match the Java enum names. In many cases, this is sufficient, as the enumerated value may just be a check constraint on a column and it has only the single value, not an id as well. If you need to also match up an int id as well as a name, then make the IDs match manually when setting up the Java enum and/or database entries.

Finally, if you'd like to see a working example of this, see koan 23 of my MyBatis koans here: https://github.com/midpeter444/mybatis-koans. If you just want to see my solution, look in the completed-koans/koan23 directory. I also have an example there of inserting a record into the database via a Java enum.