Hibernate, UTF-8 and SQL Server 2005

May 23rd, 2007 by Ioan Cocan in Java, General

I found out today that MS Sql server seems to handle Unicode in a very special way. Instead of having some support a database or table level, each Unicode column have to be created as “national”. That is be either nchar, nvarchar or ntext.

Ms SQL Server 2005 seems to go one step further by announcing future deprecation for ntext, text and image types.

From Sql Server 2005 notes:

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.”

When working with Hibernate it seems there is no dialect to handle Unicode integration properly. You have to get down and write a custom dialect that maps to the new data types.

/**
 * Unicode support in SQL Server
 *
 * @author icocan
 */
public class UnicodeSQLServerDialect extends SQLServerDialect {

    public UnicodeSQLServerDialect() {
        super();

        // Use Unicode Characters
        registerColumnType(Types.VARCHAR, 255, "nvarchar($l)");
        registerColumnType(Types.CHAR, "nchar(1)");
        registerColumnType(Types.CLOB, "nvarchar(max)");

        // Microsoft SQL Server 2000 supports bigint and bit
        registerColumnType(Types.BIGINT, "bigint");
        registerColumnType(Types.BIT, "bit");
    }
}
DZoneGoogle ReaderYahoo MessengerRedditEmailDelicious

You might also like

Migrate Lucene annotations 3.2.1GA to Hibernate Search 3.0.0.Beta1: object removal Changes in Hibernate Search are quite frequent, as it is in continued development. Today I found myself...
Hibernate schema maintenance Hibernate provides a nice way to keep your DB schema in sync with the model. At least 2 choices are available: -...
Hibernate Annotations 3.2.1 – Lucene sort Keeping database and Lucene index in sync is easily done using the new Lucene-Hibernate integration. Just...
File not found: transaction …, path …. SVN mysteries behind an Apache Reverse Proxy We have an SVN server somewhere on our intranet, but since outside people want to access it, I put it...
.

7 Responses

  1. Anders Wallgren Says:

    When using the hibernate schema validation, I found that I had to use ntext, not nvarchar(max), for CLOB, the reason being that the driver returns ntext as the column type when nvarchar(max) was used to define it.

  2. Robert Hughes Says:

    Can you explain more of your example/post an actual working file.

    I have the same problem I think.

  3. Petra Says:

    Thanks a lot, it solved my problem.

  4. terry Says:

    I want to use varchar(max) in hibernate,how can I define in *hbm*xml??

  5. sapna thakor Says:

    Q1. When using nvarchar(max), what do you specify as the length of the column.

    Could someone please provide a mapping file example – this would be very helpful. We have several areas in our application, when the application value needs to resides in the database as nvarchar(max) to allow foreign language characters.

    Please help.

    Regards,
    Sapna

  6. Andreas Kunft Says:

    to make hibernate work with nvarchar(max) add following method to your class:

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

    and damn it works :)

  7. Hibernate, UTF-8 and SQL Server 2005 « Sealyu's Blog Says:

    […] /** * Unicode support in SQL Server * * @author icocan */ public class UnicodeSQLServerDialect extends SQLServerDialect { public UnicodeSQLServerDialect() { super(); // Use Unicode Characters registerColumnType(Types.VARCHAR, 255, "nvarchar($l)"); registerColumnType(Types.CHAR, "nchar(1)"); registerColumnType(Types.CLOB, "nvarchar(max)"); // Microsoft SQL Server 2000 supports bigint and bit registerColumnType(Types.BIGINT, "bigint"); registerColumnType(Types.BIT, "bit"); } } read more … […]

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.