Looking for software experts?
Need an expert advice on software development? Need consulting work done in time and at high standards? Tremend has the right solution for you.

We can provide expertise in:
  •    » high traffic and complex content website infrastructures using Java, PHP or .NET. More here ...
  •    » mobile applications for iPhone, Android or J2ME. More here ...

For an enquiry, send an email to contact [at] tremend [dot] ro.

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

Related posts

  • Apache Derby versus Hypersonic SQL
    There are several limitations and problems of Apache Derby as noted in the previous post (http://blog.tremend.ro/2006/10/03/about-the-maturity-of-apache-derby/), but with Derby it seems that you're still...
  • How to order by a custom SQL formula/expression when using hibernate Criteria API
    In our current project we are using Spring + Hibernate Annotations. Today I needed to use something like: select distinct t.id from MyClass t where ..... order by (a + b) desc where a and...
  • iBatis O/R mapping
    After some years of Hibernate or Spring+Hibernate it was time to try something new. Not for the sake of trying something new, but more out of the need. The problem Think of a legacy database, very...
  • Hibernate schema maintenance
    Hibernate provides a nice way to keep your DB schema in sync with the model. At least 2 choices are available: - have the hibernate.hbm2ddl.auto set to auto during development - use various utilities:...
  • About the maturity of Apache Derby
    For those of you that didn't hear about it yet, Apache Derby is a Java relational database engine that can both run as a network server or embedded in a Java application. You might have encountered Derby...
.

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.