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

Question

I have a column in my SQL-2005 database that used to be a varchar(max), but it has been changed to an nvarchar(max).

Now I need to update my hibernate mapping file to reflect the change, and this is what it used to be:

<element type="text" column="Value"/>

When I try to run the application, the following error appears:

org.hibernate.HibernateException: Wrong column type in [Table] for column Value. Found: ntext, expected: text

What should I put in the 'type' attribute to correctly map the column as an nvarchar(max)?

I've tried setting the type to ntext, but hibernate didn't know what that was. I tried setting the type to string, but it treated string as a text type.

score 21 · Answer 1 · answered Jun 27 '12 at 12:31

21

What worked for me is to put the actual column definition in a @Column annotation:

    @Column(name="requestXml", columnDefinition = "ntext")
private String request;

answered Jun 27 '12 at 12:31

Vaal

634
7
9

3

This worked for me too. I'm using nvarchar(max) in DB and @Column(name="colName", columnDefinition = "nvarchar") fixed it. – petrsyn Sep 17 '14 at 13:20

score 11 · Answer 2 · answered Apr 26 '17 at 16:03

11

It can be fixed by @Nationalized annotation from hibernate, that marks a character data type like a nationalized variant (NVARCHAR, NCHAR, NCLOB, etc).

answered Apr 26 '17 at 16:03

Artem

1,157
1
14
24

ampersandre · Accepted Answer · 2009-09-09T21:38:11.100

8

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.

edited Sep 09 '09 at 21:38

answered Jul 08 '09 at 20:20

ampersandre

3,156
3
29
37

3

why did you register column type COLB aswell? and what does this $1 means in nvarchar($1)? Thanks – vishnu viswanath Oct 07 '13 at 05:13
1

This was a long time ago and I can't quite remember, but registerColumnType() maps SQL types to Hibernate types. $l (L, not #1) is a matching pattern that represents the length of the nvarchar column. If the nvarchar column has a defined length, it will be mapped to the VARCHAR type. If the nvarchar column has max length, it will be mapped to the CLOB type. – ampersandre Nov 19 '13 at 19:23
1

Please note that for sql server 2014 I had to *remove* the overridden method `getTypeName` in the code above !! – vikingsteve Apr 13 '16 at 11:08

score 0 · Answer 4 · answered Aug 20 '09 at 20:36

0

I think it is registerColumnType(Types.VARCHAR, "nvarchar($l)"); // l like _l_ength, not 1.

answered Aug 20 '09 at 20:36

it is $1. for example registerColumnType(Types.NVARCHAR, 4000, "nvarchar($1)"); – Eyal Oct 28 '15 at 08:20

score 0 · Answer 5 · answered Oct 07 '22 at 20:42

Using @Nationalized with length = Integer.MAX_VALUE should resolve the issue of NVARCHAR(MAX).

The entity class should somewhat like this

    @Nationalized
    @Column(length = Integer.MAX_VALUE)
    private String nvarcharMax;

For more details checkout the documentation here

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

5 Answers5

Linked