CODEWITHSUNDEEP
javahibernatesql-server-2014
joninx
joninx

Reputation: 1780

Wrong column type encountered with User-Defined Data Type

I'm using hibernate to fetch some data from SQL Server 2014 database.

There is a Table which has columns of user-defined data types:

dbo.Nodos
    Id (PK, int, not null)
    Tipo (PK, FK, TipoNodo(tinyint), not null)
    Nombre (varchar(40), null)
    PosX (Coordenada(real), not null)
    PosY (Coordenada(real), not null)
    Label (varchar(8), not null)

Those data types are Coordenada and TipoNodo:

User-Defined Data Types
    dbo.Coordenada (real, not null)
    dbo.TipoNodo (tinyint, not null)

I'm mapping Nodos table as StopDTO class in Java:

@Entity
@Table(name = "Nodos")
public class StopDTO {

    /*
     * Atributos
     */
    @Id
    @Column(name = "Id", insertable = false, updatable = false)
    private Integer id;
    @Column(name = "Tipo", insertable = false, updatable = false)
    private Integer tipo;
    @Column(name = "Nombre", insertable = false, updatable = false)
    private String nombre;
    @Column(name = "PosX", insertable = false, updatable = false)
    private Float posx;
    @Column(name = "PosY", insertable = false, updatable = false)
    private Float posy;
    @Column(name = "Label", insertable = false, updatable = false)
    private String label;
    ...

The problem comes when I query StopDTOs (using a NamedNativeQuery), I'm getting the following message:

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: wrong column type encountered in column [PosX] in table [Nodos]; found [coordenada (Types#REAL)], but expecting [float (Types#FLOAT)] at org.hibernate.tool.schema.internal.SchemaValidatorImpl.validateColumnType(SchemaValidatorImpl.java:165) at org.hibernate.tool.schema.internal.SchemaValidatorImpl.validateTable(SchemaValidatorImpl.java:150) at org.hibernate.tool.schema.internal.SchemaValidatorImpl.performValidation(SchemaValidatorImpl.java:95) at org.hibernate.tool.schema.internal.SchemaValidatorImpl.doValidation(SchemaValidatorImpl.java:62) ...

Is this problem solved by implementing a couple of UserTypes for Coordenada and TipoNodo? What can I do if it's not?

Thank you!

Upvotes: 0

Views: 1051

Answers (2)

joninx
joninx

Reputation: 1780

Solved!

Indeed, it was what expected: develop a pair of UserType. I'll post the code in case somebody needs:

TipoNodoUserType

package com.mycompany.usertype;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Objects;

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.type.descriptor.java.ShortTypeDescriptor;
import org.hibernate.usertype.UserType;

public class TipoNodoUserType implements UserType {

    public static final TipoNodoUserType INSTANCE = new TipoNodoUserType();

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

    @SuppressWarnings("rawtypes")
    @Override
    public Class returnedClass() {
        return Short.class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        return Objects.equals(x, y);
    }

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

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner)
            throws HibernateException, SQLException {
        String columnName = names[0];
        Short columnValue = (Short) rs.getObject(columnName);
        return columnValue == null ? null : columnValue;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session)
            throws HibernateException, SQLException {
        if(value == null){
            st.setNull( index, Types.TINYINT );
        }else{
            String stringValue = ShortTypeDescriptor.INSTANCE.toString((Short)value);
            st.setString(index, stringValue);
        }
    }

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

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

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

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

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

}

CoordenadaUserType

package com.mycompany.usertype;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Objects;

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.type.descriptor.java.FloatTypeDescriptor;
import org.hibernate.usertype.UserType;

public class CoordenadaUserType implements UserType {

    public static final CoordenadaUserType INSTANCE = new CoordenadaUserType();

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

    @SuppressWarnings("rawtypes")
    @Override
    public Class returnedClass() {
        return Float.class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        return Objects.equals(x, y);
    }

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

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner)
            throws HibernateException, SQLException {
        String columnName = names[0];
        Float columnValue = (Float) rs.getObject(columnName);
        return columnValue == null ? null : columnValue;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session)
            throws HibernateException, SQLException {
        if(value == null){
            st.setNull( index, Types.REAL );
        }else{
            String stringValue = FloatTypeDescriptor.INSTANCE.toString((Float)value);
            st.setString(index, stringValue);
        }
    }

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

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

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

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

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

}

StopDTO

    @Entity
@Table(name = "Nodos")
public class StopDTO implements java.io.Serializable{

    /*
     * Atributos
     */
    private static final long serialVersionUID = 8171715812406080593L;

    @Id
    @Column(name = "Id", insertable = false, updatable = false)
    private Integer id;
    @Id
    @Column(name = "Tipo", insertable = false, updatable = false)
    @Type(type = "com.mycompany.usertype.TipoNodoUserType")
    private Short tipo;
    @Column(name = "Nombre", insertable = false, updatable = false)
    private String nombre;
    @Column(name = "PosX", insertable = false, updatable = false)
    @Type(type = "com.mycompany.usertype.CoordenadaUserType")
    private Float posx;
    @Column(name = "PosY", insertable = false, updatable = false)
    @Type(type = "com.mycompany.usertype.CoordenadaUserType")
    private Float posy;
    @Column(name = "Label", insertable = false, updatable = false)
    private String label;
    ...
    // Don't forget to code an empty constructor, setters/getters for every attribute and implementing hashCode and equals methods (as we're implementing Serializable interface).

Upvotes: 0

CiderMan87
CiderMan87

Reputation: 97

Please check the dialect configured in Hibernate, it should be SQLServer2008Dialect.

Upvotes: 1

Related Questions