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

You can use Custom TypeHandler for converting you result directly into ENUM so that you don't need to put all values in your database as UPPER CASE ENUM Names.

This is how your Status Enum Custom Handler will look like

public class StatusTypeHandler implements TypeHandler<Status> {

public Status getResult(ResultSet rs, String param) throws SQLException {
    return Status.getEnum(rs.getInt(param));
}

public Status getResult(CallableStatement cs, int col) throws SQLException {
    return Status.getEnum(cs.getInt(col));
}

public void setParameter(PreparedStatement ps, int paramInt, Status paramType, JdbcType jdbctype)
        throws SQLException {
    ps.setInt(paramInt, paramType.getId());
}
}

Define your TypeHandler to handle Status by default in your mybatis-config.xml by adding this code.

    <typeHandlers> 
            <typeHandler javaType='Status' handler='StatusTypeHandler' /> 
    </typeHandlers>

Now let us consider an example where you have following two functions in your Dao,

Status getStatusById(int code);
Status getStatusByName(String name);

Your mapper will look like

<select id="getStatusById" resultType="Status" parameterType="int">       
    SELECT  ls.id
    FROM status AS ls
    WHERE ls.id = #{id}
</select>

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

Now as the resultType for both the mapper is Status, myBatis will use the CustomTypeHandler for this type i.e. StatusTypeHandler instead of EnumTypeHandler that it uses by default for Handling Enums, so there would be no need to maintain proper Enum names in your database.

Answer 2

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.