Integrating Spring + Hibernate + Sql server with unicode support

Question

How could I integrate Spring with Hibernate using sql server 2005 and have Unicode support. I tried many different ways but I just couldn't get it to work.

Column in the table is nvarchar, character set in Spring is UTF-8. I can read Unicode text (which I added myself using the sql server management tool) just fine but writing doesn't work, it get's gibberished in the DB.

jdbc url is

jdbc:sqlserver://localhost:1433;useUnicode=true;characterEncoding=UTF-8;databaseName=test;

with these properties in the hibernate configuration file

<property name="hibernate.connection.useUnicode">true</property>
<property name="hibernate.connection.charSet">UTF8</property>

I also have a filter which changes the encoding for all pages

response.setContentType("text/html; charset=UTF-8");
request.setCharacterEncoding("UTF8");

chain.doFilter(request, response);

//do it again, since JSPs will set it to the default
response.setContentType("text/html; charset=UTF-8");
request.setCharacterEncoding("UTF8");

is there some good soul who succeeded in doing so, and can help?

Many thanks!

Answer 1

Check on this, but I don't believe SQL Server supports UTF8. I think the closest approximation you can get is their UCS-2 encoding. See this MSDN article.

Answer 2

It seems that you need to change the response and the request encoding in the filter to UTF-8 and all is good!

Answer 3

Try the suggestion here: http://blog.tremend.ro/2007/05/23/hibernate-utf-8-and-sql-server-2005/

/**
 * Unicode support in SQL Server
 */
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");
    }
}