Custom Boolean User Type with Hibernate JPA

Wim Van HaarenJune 5th, 2015Last Updated: June 4th, 2015

1 682 3 minutes read

The ANSI SQL 1999 standard introduced a BOOLEAN data type (although unfortunately only as an optional feature). But to date it still isn’t implemented by most major database systems. As a consequence boolean columns are implemented in various ways. E.g., CHAR columns containing ‘Y’ or ‘N’, or using BIT columns. Subsequently, there is no way for JPA to provide a standardized way of mapping an entity’s boolean fields onto database columns.

Hibernate offers a custom YesNoType for boolean implementations using CHAR(1) columns containing ‘Y’ or ’N’ characters. But for other practices you basically have to provide your own solution. Fortunately, Hibernate offers the possibility of creating your own custom UserType’s. In this blog entry I will give an example of one such custom Boolean UserType.

Recently, I’ve been confronted with a Dutch legacy database schema in which ‘Y’ (for yes) and ‘N’ (for no) are represented by ‘J’ (“ja”) and ‘N’ (“nee”), respectively. This ruled out using Hibernate’s YesNoType. Adding to the complexity was the fact that some of these columns were using CHAR(1) and others using CHAR(2) with a padded space – don’t ask why!

So I ended up writing a custom UserType that allowed me to basically convert the following…

The starting point

@Entity
@Table(name = "FOO_BAR")
public class FooBar implements Serializable {
    @Column(name = "FOO_ INDICATOR")
    private String fooIndicator;
 
    @Column(name = "BAR_ INDICATOR", length = 2)
    private String barIndicator;
 
    // …
}

into…

The desired situation

@Entity
@Table(name = "FOO_BAR")
@TypeDefs({
    @TypeDef(name = JaNeeType.NAME, typeClass = JaNeeType.class)
})
public class FooBar implements Serializable {
    @Column(name = "FOO_INDICATOR)
    @Type(type = JaNeeType.NAME)
    private Boolean fooIndicator;
 
    @Column(name = "BAR_INDICATOR", length = 2)
    @Type(type = JaNeeType.NAME, parameters = { @Parameter(name = "length", value = "2") })
    @Type(type = JaNeeType.NAME)
    private Boolean barIndicator;
 
    // …
}

Coding the custom type proved to be fairly straight forward. I just had to implement the interface org.hibernate.usertype.UserType. Dealing with the varying column lengths involved adding the ‘length’ parameter required implementing a second interface – org.hibernate.usertype.ParameterizedType.

Given below is what I did end up with.

JaNeeType

package it.jdev.examples.persistence.hibernate;
 
import java.io.Serializable;
import java.lang.invoke.MethodHandles;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Properties;
 
import org.apache.commons.lang3.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.ParameterizedType;
import org.hibernate.usertype.UserType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
/**
 * A type that maps between {@link java.sql.Types#VARCHAR CHAR(1) or CHAR(2)} and {@link Boolean} (using "J " and "N ").
 * <p>
 * Optionally, a parameter "length" can be set that will result in right-padding with spaces up to the
 * specified length.
 */
public class JaNeeType implements UserType, ParameterizedType {
 
    public static final String NAME = "ja_nee";
 
    private static final Logger LOGGER = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());
 
    private int length = 1;
 
    @Override
    public int[] sqlTypes() {
        return new int[] { Types.VARCHAR };
    }
 
    @SuppressWarnings("rawtypes")
    @Override
    public Class returnedClass() {
        return Boolean.class;
    }
 
    @Override
    public boolean equals(final Object x, final Object y) throws HibernateException {
        if (x == null || y == null) {
            return false;
        } else {
            return x.equals(y);
        }
    }
 
    @Override
    public int hashCode(final Object x) throws HibernateException {
        assert (x != null);
        return x.hashCode();
    }
 
    @Override
    public Object nullSafeGet(final ResultSet rs, final String[] names, final SessionImplementor session, final Object owner) throws HibernateException, SQLException {
        final String s = rs.getString(names[0]);
        if (StringUtils.isBlank(s)) {
            return false;
        }
        if ("J".equalsIgnoreCase(s.trim())) {
            return Boolean.TRUE;
        }
        return Boolean.FALSE;
    }
 
    @Override
    public void nullSafeSet(final PreparedStatement st, final Object value, final int index, final SessionImplementor session) throws HibernateException, SQLException {
        String s = Boolean.TRUE.equals(value) ? "J" : "N";
        if (this.length > 1) {
            s = StringUtils.rightPad(s, this.length);
        }
        st.setString(index, s);
    }
 
    @Override
    public Object deepCopy(final Object value) throws HibernateException {
        return value;
    }
 
    @Override
    public boolean isMutable() {
        return true;
    }
 
    @Override
    public Serializable disassemble(final Object value) throws HibernateException {
        return (Serializable) value;
    }
 
    @Override
    public Object assemble(final Serializable cached, final Object owner) throws HibernateException {
        return cached;
    }
 
    @Override
    public Object replace(final Object original, final Object target, final Object owner) throws HibernateException {
        return original;
    }
 
    @Override
    public void setParameterValues(final Properties parameters) {
        if (parameters != null && !parameters.isEmpty()) {
            final String lengthString = parameters.getProperty("length");
            try {
                if (StringUtils.isNotBlank(lengthString)) {
                    this.length = Integer.parseInt(lengthString);
                }
            } catch (final NumberFormatException e) {
                LOGGER.error("Error parsing int " + lengthString, e);
            }
        }
    }
 
}

Reference:

Custom Boolean User Type with Hibernate JPA from our JCG partner Wim van Haaren at the JDev blog.