Mapping to Varchar and Nvarchar in Hibernate

Mapping to varchar and nvarchar in hibernate

<type-mapping>
        <sql-type jdbc-type="NVARCHAR" hibernate-type="string" />
 </type-mapping>

Add the above code in the hibernate reveng file.

How can Hibernate map the SQL data-type nvarchar(max)?

Found the answer at Tremend Tech Blog. You have to write your own SQLServerDialect class, it looks something like this:

public class SQLServerNativeDialect extends SQLServerDialect {
     public SQLServerNativeDialect() {
         super();
         registerColumnType(Types.VARCHAR, "nvarchar($l)");
         registerColumnType(Types.CLOB, "nvarchar(max)");
     }

    public String getTypeName(int code, int length, int precision, int scale) throws HibernateException {
        if(code != 2005) {
            return super.getTypeName(code, length, precision, scale);
        } else {
            return "ntext";
        }
    }
}

This class maps Hibernate's types to SQL types, so the class will map the nvarchar(max) SQL Data Type to Hibernate's CLOB data type.

The getTypeName method is used to return "ntext" when Hibernate asks about the data type with code 2005 (which looks like it's the nvarchar(max) data type).

Finally, you need to change your hibernate persistence dialect to this new SQLServerDialect class, which allows hibernate to translate data types into SQL data types.

Spring Boot & Hibernate: NVARCHAR column type without @Type annotation

Actually, it turns out that the @Column JPA annotation allows you to specify the column type (without having to use the @Type or @Nationalized annotations):

@Column(name = "first_name", columnDefinition = "nvarchar", nullable = false)
private String firstName; 

Getting Hibernate and SQL Server to play nice with VARCHAR and NVARCHAR

I decided to try this as a hack that might work without touching the database. To do this I created a custom type for NVARCHAR fields. This requires JDBC 4 drivers (using the ones from Microsoft) and Hibernate 3.6.0. The sendStringParametersAsUnicode is false.

Here's the approach, I'm still verifying its correctness - any comments from folks with more experience than I are welcome

Add a new Dialect to support the new datatype

public class SQLAddNVarCharDialect extends SQLServerDialect {

    public SQLAddNVarCharDialect(){
        super();

        registerColumnType( Types.NVARCHAR, 8000, "nvarchar($1)" );     
        registerColumnType( Types.NVARCHAR,  "nvarchar(255)" );     
    }
}

Add the new Type. Notice the setNString in nullSafeSet

public class NStringUserType implements UserType  {

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

        return deepCopy(arg0);
    }

    @Override
    public Object deepCopy(Object arg0) throws HibernateException {
        if(arg0==null) return null;
        return arg0.toString();
    }

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

    @Override
    public boolean equals(Object arg0, Object arg1) throws HibernateException {
        if(arg0 == null )
            return arg1 == null;
        return arg0.equals(arg1);
    }

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

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

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index)
            throws HibernateException, SQLException {
        if(value == null)
            st.setNull(index,Types.NVARCHAR);
        else
            st.setNString(index, value.toString());
    }

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

    @Override
    public Class returnedClass() {
        return String.class;
    }

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

    @Override
    public Object nullSafeGet(ResultSet resultSet, String[] names, Object owner)
            throws HibernateException, SQLException {
        String result = resultSet.getString(names[0]);
        return result == null || result.trim().length() == 0 
            ? null : result;
    }

}

Update mappings for all NVARCHAR fields

    <property name="firstName" type="NStringUserType">
        <column name="firstName" length="40" not-null="false" />
    </property>    

Raw SQL before (with sendUnicode..=true):

 exec sp_prepexec @p1 output,N'@P0 nvarchar(4000),@P1 datetime,@P2 varchar(8000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000)... ,N'update Account set ... where AccountId=@P35    

And after:

 exec sp_prepexec @p1 output,N'@P0 varchar(8000),@P1  .... @P6 nvarchar(4000),@P7 ... ,N'update Account set ... Validated=@P4, prefix=@P5, firstName=@P6 ... where AccountId=@P35    

Seems to work similarly for 'SELECT.."

Hibernate with Sql Server fail for nvarchar field with No Dialect mapping...

I was able to resolve that issue by subclassing the SQLServerDialect:

package packagename;

import java.sql.Types;

public class SqlServerDialectWithNvarchar extends org.hibernate.dialect.SQLServerDialect {

    public SqlServerDialectWithNvarchar() {
        registerHibernateType(Types.NVARCHAR, 4000, "string");
    }

}

and referencing it in my persistence.xml:

        <property name="hibernate.dialect" value="packagename.SqlServerDialectWithNvarchar" />

PS: It seems to be fixed with hibernate 5.1 according to this ticket: https://hibernate.atlassian.net/browse/HHH-10183

How to avoid implicit type conversion from VARCHAR to VARCHAR2 using Hibernate?

The easiest way is to extend the default StringType and override the sqlType property and supply the new Hibernate Type to your entity attribute via the @Type annotation.

Most likely the VARCHAR2 mapping comes via the Oracle Hibernate Dialect, so you should not probably override the default Dialect mapping as there might be columns rightfully using VARCHAR2.

So, the custom Hibernate Type gives you control and allows you to use it only for the VARCHAR columns.

---

Related Topics