How do I use a list of enums as parameters in MyBatis Spring Boot?

Question

How do I use a List of enums as a parameter to a MyBatis query? I've created a type handler for it already and specified the mapped type as described in this other question. It is returning 0 count when it should be thousands.

@Mapper
public interface BadgeMapper {
    @Select("select count(*) from badges where appType in (#{appTypes})")
    int countByType(@Param("appTypes") List<AppType> appTypes);

package com.example.mapper;
@MappedTypes({AppType.class})
public class AppTypeTypeHandler implements TypeHandler<AppType> {

    @Override
    public void setParameter(PreparedStatement ps, int i, AppType parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, parameter.toString()); // use toString not name()
    }

public static enum AppType {
    ANDROID("A", "Android"), THEME("T", "Theme"), ...
    private String val;
    private String desc;
    AppType(String v, String d) { val = v; desc = d; }
    public String toString() {
        return val;
    }

application.properties

mybatis.type-handlers-package=com.example.mapper

The debug logs appear to show the correct values ('A', 'T', 'ST'), yet it prints 0 for the count.

            System.out.println(badgeMapper.countByType(appTypes));

Console

c.s.s.mapper.BadgeMapper.countByType     : ==>  Preparing: select count(*) from badges where appType in (?)
c.s.s.mapper.BadgeMapper.countByType     : ==> Parameters: [A, T, ST](ArrayList)                           
0

MySQL

mysql> select count(*) from badges where appType in ('A', 'T', 'ST');
+----------+
| count(*) |
+----------+
|     2365 |

Reference documentation for MyBatis XML: http://www.mybatis.org/mybatis-3/configuration.html#typeHandlers

Somewhat related, but for results assignments, not parameters: https://stackoverflow.com/a/45781629/148844 — Chloe, Feb 28 '19 at 23:13
I tried `where appType in (#{appTypes, typeHandler=com.example.mapper.AppTypeTypeHandler})` but got `java.lang.ClassCastException: java.util.ArrayList cannot be cast to com.example.model.Badge$AppType`. — Chloe, Feb 28 '19 at 23:30

score 1 · Accepted Answer · answered Mar 01 '19 at 13:07

The problem is that you type handler is not invoked at all.

First of all, the whole list is treated as a whole and processed as one argument to the JDBC prepared statement. It means that individual elements are not processed via the type handler you specified.

There is no portable way to set the list to IN prepared statement parameter in JDBC and therefore in mybatis (there are ways to do that if you are using postgres).

If you are using postgresql you can create a type handler that will accept the list of enums and set it using the method described in the question above.

In generic case you need to generate the query dynamically to handle each value separately:

@Select("<script>select count(*) from enu " +
  " where appType in ( " +
  "<foreach item='appType' collection='appTypes' separator=','>" +
  "   #{appType,typeHandler=AppTypeTypeHandler}" +
  "</foreach>)</script>")
int countByType(@Param("appTypes") List<AppType> appTypes);

Alternatively you can use @SelectProvider and build query using java code.

How do I use a list of enums as parameters in MyBatis Spring Boot?

1 Answers1