Using MySQL "SET" column with Ebean ORM

Question

I'm trying to recreate one of our web apps using Play 2.0 with Ebean and I've hit a road block. I can't figure out how to map MySQL's SET type to a field in the model. I've gotten ENUM columns working using the @Enumerated(EnumType.STRING) annotation but I can't seem to find any info on SET columns.

The table mimics a crontab:

CREATE TABLE IF NOT EXISTS `schedule` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `task_id` mediumint(8) unsigned NOT NULL default '0',
  `month` set('January','February','March','April','May','June','July','August','September','October','November','December') default NULL,
  `mday` set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','-1','-2','-3','-4','-5','-6','-7','-8','-9','-10') default NULL,
  `wday` set('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') default NULL,
  `hour` set('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23') default NULL,
  `minute` set('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59') default NULL,
  `updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `event` (`task_id`)
)

I've now created a UserType and related annotations as suggested by MvG:

@Entity
public class Schedule extends Model {

    public enum Month { JANUARY, FEBRUARY, MARCH, APRIL, MAY, JUNE, JULY, AUGUST, SEPTEMBER, OCTOBER, NOVEMBER, DECEMBER };
    public enum Weekday { SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY };

    @Id
    public Long id;

    @ManyToOne(cascade = CascadeType.MERGE)
    public Task task;
    @Version
    public Timestamp updated;

    @Type(type="models.EnumSetUserType",parameters=@Parameter(name="enumType",value="models.Schedule$Month"))
    @Column(name="month", columnDefinition="SET('JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST','SEPTEMBER','OCTOBER','NOVEMBER','DECEMBER')")
    @MonthEnum
    public EnumSet<Month> months;

    @Type(type="models.IntegerSetUserType")
    @IntegerSet(min=-30,max=30)
    @Column(name="mday",columnDefinition="SET('1','2','3','4','5','6','7','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','-1','-2','-3','-4','-5','-6','-7','-8','-9')")
    public Set<Integer> mdays;

    @Type(type="models.EnumSetUserType", parameters = @Parameter(name="enumType", value="models.Schedule$Weekday"))
    @Column(name="wday", columnDefinition="SET('MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY')")
    @WeekdayEnum
    public EnumSet<Weekday> weekdays;

    @Type(type="models.IntegerSetUserType")
    @IntegerSet(min=0,max=23)
    @Column(name="hour",columnDefinition="SET('0','1','2','3','4','5','6','7','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23')")
    public Set<Integer> hours;

    @Type(type="models.IntegerSetUserType")
    @IntegerSet(min=0,max=59)
    @Column(name="minute",columnDefinition="SET('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59')")
    public Set<Integer> minutes;

    public static Finder<Long,Schedule> find = new Finder<Long,Schedule>(Long.class, Schedule.class);

}

EnumSetUserType:

public class EnumSetUserType<E extends Enum<E>> implements UserType, ParameterizedType, Serializable {
    private Class<? extends EnumSet> clazz = null;
    private Class<E> enum_type = null;

    @Override
    public void setParameterValues(Properties parameters) {
        String enum_class_name = parameters.getProperty("enumType");
        try {
            enum_type = ReflectHelper.classForName(parameters.getProperty("enumType"), this.getClass()).asSubclass(Enum.class);
            //enum_type = (Class<E>) Class.forName(enum_class_name);
            //enum_type = (Class<E>) Play.application().classloader().loadClass(enum_class_name);
        }
        catch (ClassNotFoundException e) {
            throw new HibernateException("enum class " + enum_class_name + " not found", e);
        }
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] column_names, SessionImplementor session, Object owner) throws HibernateException, SQLException {
        String value_str = rs.getString(column_names[0]);
        System.out.println("getting " + column_names[0] + " using " + getClass());
        if (rs.wasNull())
            return null;

        List<E> enum_values = new ArrayList<E>();
        for (String value : value_str.split(","))
            enum_values.add(Enum.valueOf(enum_type, value));

        return EnumSet.copyOf(enum_values);
    }

    @Override
    public void nullSafeSet(PreparedStatement statement, Object object, int index, SessionImplementor session) throws HibernateException, SQLException {
        System.out.println("Setting " + index + " to " + object + " using " + getClass());
        if (object == null) {
            statement.setNull(index, Types.VARCHAR);
            return;
        }

        Set<E> values = (Set<E>) object;
        StringBuilder sb = new StringBuilder();
        for (E value : values)
            sb.append(value.name()).append(",");

        System.out.println("Setting " + index + " to " + sb.length() + " using " + getClass());
        statement.setString(index, sb.substring(0, sb.length() - 1));
    }

    @Override
    public int[] sqlTypes() {
        return new int[] { Types.VARCHAR };
    }

    @Override
    public Class returnedClass() {
        return clazz;
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        return value;
    }

    @Override
    public boolean isMutable() {
        return false;
    }

    @Override
    public Object replace(Object original, Object target, Object owner) throws HibernateException {
        return original;
    }

    @Override
    public Object assemble(Serializable cached, Object owner) throws HibernateException {
        return cached;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (Serializable) value;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        if (x == y)
            return true;

        if (x == null || y == null)
            return false;

        return x.equals(y);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        return x.hashCode();
    }

}

It no longer reports any errors, but even when I manually populate and save a Schedule in a controller action, the Set fields aren't saved in the database. How can I get Play+Ebean to work with this table?

Answer 1

If you map an ENUM column to a Java Enumeration, you should probably map a SET column to a Java EnumSet. There is at least one question on SF about mapping EnumSet, but the solution there appears to be a separate table, not the mysql SET type.

It also appears that there is no support in hybernate for mysql SET types. Therefore, you'll have to write your own UserType. I'm not sure about the difference, but it seems you could easily make this an EnhancedUserType, which would probably make your implementation more versatile. If your project allows using LGPL-licensed source code, you might use the implementation of EnumType as a template for your own EnumSetType. Adapting that should be easy, particularly as you could throw away all the “save as ordinal” parts of the code.

Once you have your own UserType for EnumSet, you could annotate the corresponding fields with that @Type. Or in the hibernation configuration, if there is such a thing in your setup. There might be even ways to register the type somewhere, to automatically map all EnumSet instances using that type, but I know too little about this whole hibernation stuff to decide whether this is desirable or even possible. I haven't even figured out yet how the @Enumerated annotation maps to the EnumType implementation.

With the right keywords (UserType EnumSet split), one can find some implementations on the web. So you wouldn't even have to write your own code, but could simply include one of these solutions. Some come with a short description about how to use them.