org.hibernate.MappingException: No Dialect mapping for JDBC type: 2000

Question

I am trying to map a column of my table on JSONUserType.

I have the model:

@Entity
@TypeDefs({ @TypeDef(name = "JSONUserType", typeClass = JSONUserType.class) })
@Table( name = "my_table")
public class MyClass {
....
.....
@Column(name = "permissions")
@Type(type = "JSONUserType", parameters = { @Parameter(name = "classType", 
value = "java.util.HashMap") })
private Map<String, Boolean> permissions;
......
......
Getter and setter
.....
.....
}

I have JSONUserType implemented:

package it......model;

import java.io.IOException;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Objects;
import java.util.Properties;

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.internal.util.ReflectHelper;
import org.hibernate.usertype.ParameterizedType;
import org.hibernate.usertype.UserType;

import com.fasterxml.jackson.annotation.JsonInclude.Include;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;

/**
 * Hibernate {@link UserType} implementation to handle JSON objects
 *
 * @see https://docs.jboss.org/hibernate/orm/4.1/javadocs/org/hibernate/usertype/ UserType.html
 */
public class JSONUserType implements UserType, ParameterizedType, Serializable {

    private static final long serialVersionUID = 1L;

    private static final ObjectMapper MAPPER = new ObjectMapper()
    // do NOT serialize null properties
    .setSerializationInclusion(Include.NON_NULL)
    // serialize dates using ISO format instead of epoch time
    .setDateFormat(new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'"));

    private static final String CLASS_TYPE = "classType";
    private static final String COLLECTION_CLASS_TYPE = "collectionClassType";

    private static final int[] SQL_TYPES = new int[] { Types.JAVA_OBJECT };

    private Class<?> classType;
    private Class<?> collectionClassType;
    private int sqlType = Types.LONGVARCHAR; // before any guessing

    @Override
    public void setParameterValues(Properties params) {

        // Retrieve the class name from params
        String classTypeName = params.getProperty(CLASS_TYPE);
        try {
            // Retrieve the classType by reflection
            classType = ReflectHelper.classForName(classTypeName, this.getClass());
            // If class is a collection, we may have to retrieve the classType of its elements
            if (Collection.class.isAssignableFrom(classType)) {
                // Retrieve the elements class name from params
                String collectionClassTypeName = params.getProperty(COLLECTION_CLASS_TYPE);
                if (collectionClassTypeName != null) {
                    try {
                        // Retrieve the elements classType by reflection
                        collectionClassType = ReflectHelper.classForName(collectionClassTypeName, this.getClass());
                    }
                    catch (ClassNotFoundException e) {
                        throw new HibernateException("collectionClassType not found : " + collectionClassTypeName, e);
                    }
                }
            }
        }
        catch (ClassNotFoundException e) {
            throw new HibernateException("classType not found : " + classTypeName, e);
        }

        sqlType = Types.OTHER;

    }

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

        return deepCopy(cached);
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {

        Object copy = null;
        if (value != null) {

            try {
                return MAPPER.readValue(MAPPER.writeValueAsString(value), classType);
            }
            catch (IOException e) {
                throw new HibernateException("unable to deep copy object", e);
            }
        }
        return copy;
    }

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

        try {
            return MAPPER.writeValueAsString(value);
        }
        catch (JsonProcessingException e) {
            throw new HibernateException("unable to disassemble object", e);
        }
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {

        if (x == null && y == null) return true;
        if (x == null) return false;
        if (y == null) return false;

        try {
            return MAPPER.writeValueAsString(x).equals(MAPPER.writeValueAsString(y));
        }
        catch (JsonProcessingException e) {
        }
        return false;
    }

    @Override
    public int hashCode(Object x) throws HibernateException {

        return Objects.hash(x);
    }

    @Override
    public boolean isMutable() {

        return true;
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws HibernateException, SQLException {

        if (rs.getString(names[0]) == null) return null;

        Object obj = null;
        try {
            // If we have defined a class type for the collection elements, cast JSON string to Collection<collectionClassType>
            if (collectionClassType != null) {
                obj = MAPPER.readValue(rs.getString(names[0]), MAPPER.getTypeFactory().constructCollectionType((Class<? extends Collection>) classType, collectionClassType));
            }
            // Else simply cast JSON string to classType
            else {
                obj = MAPPER.readValue(rs.getString(names[0]), classType);
            }
        }
        catch (IOException e) {
            throw new HibernateException("unable to read object from result set", e);
        }
        return obj;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws HibernateException, SQLException {

        if (value == null) {
            st.setNull(index, sqlType);
        } else {
            try {
                st.setObject(index, MAPPER.writeValueAsString(value), sqlType);
            }
            catch (JsonProcessingException e) {
                throw new HibernateException("unable to set object to result set", e);
            }
        }
    }

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

        return deepCopy(original);
    }

    @Override
    public Class<?> returnedClass() {

        return classType;
    }

    @Override
    public int[] sqlTypes() {

        return SQL_TYPES;
    }
}

Then i have:

<tx:annotation-driven transaction-manager="transactionManager" />

<bean id="transactionManager" 
class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="persistenceUnitName" value="persistenceUnit" />
    <property name="dataSource" ref="dataSource" />
    <property name="packagesToScan" value="it.oandsi" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
    </property>
    <property name="jpaProperties">
        <props>
            <prop key="hibernate.dialect">${hibernate.dialect}</prop>
            <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
            <prop key="hibernate.format_sql">${hibernate.format_sql}</prop>
            <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
        </props>
    </property> 
</bean>

where hibernate.dialect is org.hibernate.dialect.MySQL5InnoDBDialect

When I launch tomcat I got the error "org.hibernate.MappingException: No Dialect mapping for JDBC type: 2000"

Here the stacktrace:

Caused by: org.hibernate.MappingException: No Dialect mapping for JDBC type: 2000
at org.hibernate.dialect.TypeNames.get(TypeNames.java:70)
at org.hibernate.dialect.TypeNames.get(TypeNames.java:101)
at org.hibernate.dialect.Dialect.getTypeName(Dialect.java:346)
at org.hibernate.mapping.Column.getSqlType(Column.java:231)
at org.hibernate.mapping.Table.sqlAlterStrings(Table.java:473)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.migrateTable(AbstractSchemaMigrator.java:295)
at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:75)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:203)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:110)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:183)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:309)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:452)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:889)

Please, can you help me?

Answer 1

You need to extend the MySQL5InnoDBDialect dialect and provide the mapping. It's as simple (provided that you have the usertype and others already configured) as adding the following constructor to the custom class.

public MyCustomDialect() {
    super();
    this.registerColumnType(Types.JAVA_OBJECT, "json"); // JAVA_OBJECT = 2000
}