让Hibernate和SQL Server与VARCHAR和NVARCHAR配合使用 [英] Getting Hibernate and SQL Server to play nice with VARCHAR and NVARCHAR

查看:328
本文介绍了让Hibernate和SQL Server与VARCHAR和NVARCHAR配合使用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我现在正在大型数据库的某些表中启用UTF-8字符。这些表已经是MS-SQL类型NVARCHAR。另外,我还有几个使用VARCHAR的字段。

Hibernate与JDBC驱动程序的交互存在一个众所周知的问题(请参阅映射到hibernate中的varchar和nvarchar )。简而言之,Hibernate / JDBC生成的SQL将所有字符串作为Unicode传递,而不管基础SQL类型如何。将数据库中的非Unicode(varchar)字段与Unicode输入字符串进行比较时,该列的标记与编码不匹配,因此将执行全表扫描。在JDBC驱动程序(包括JTDS和MS版本)中,有一个参数将Unicode字符串作为ASCII传递,但这是一个全部或全部命题,不允许将国际字符输入到数据库中。



我在这个问题上看到的大多数帖子都提出了两种解决方案之一 - 1)将数据库中的所有内容更改为NVARCHAR或2)将sendStringParametersAsUnicode = false ,
我的问题是这样的 - 有什么已知的解决方案让VARCHAR和NVARCHAR很好地结合在一起?对于我的环境来说,由于下游依赖性和其他外部问题,将所有内容都更改为NVARCHAR是一个巨大的问题。

>

我决定尝试这种做法,可以在不触摸数据库的情况下使用 。为此,我为NVARCHAR字段创建了一个自定义类型。这需要JDBC 4驱动程序(使用Microsoft的)和Hibernate 3.6.0。 sendStringParametersAsUnicode为false。



以下是我的方法,我仍在验证其正确性 - 任何有比我更多经验的人的评论

添加一个新的Dialect以支持新的数据类型

  public class SQLAddNVarCharDialect extends SQLServerDialect {

public SQLAddNVarCharDialect(){
super();

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

添加新的类型。注意 nullSafeSet 中的 setNString
$ b

  public class NStringUserType implements UserType {

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

返回deepCopy(arg0);

$ b @Override
public Object deepCopy(Object arg0)抛出HibernateException {
if(arg0 == null)return null;
return arg0.toString();

$ b @Override
public Serializable反汇编(Object arg0)抛出HibernateException {
return(Serializable)deepCopy(arg0);

$ b @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;


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

$ b @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};


$ b @Override
public Object nullSafeGet(ResultSet resultSet,String [] names,Object owner)
抛出HibernateException,SQLException {
String result = resultSet.getString(names [0]);
返回结果== null || result.trim()。length()== 0
? null:result;


$ b

更新所有NVARCHAR字段的映射

 < property name =firstNametype =NStringUserType> 
< column name =firstNamelength =40not-null =false/>
< / property>

原始SQL之前(with sendUnicode .. = true):

  exec sp_prepexec @ p1输出,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 ...其中AccountId = @ P35 
$ b $

之后:

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

似乎对于'SELECT ..'类似地工作。



I'm currently in the process of enabling UTF-8 characters in some tables of a large database. These tables are already of MS-SQL type NVARCHAR. Additionally, I have several fields using VARCHAR as well.

There is a well known issue with Hibernate's interactions with the JDBC driver (see e.g., Mapping to varchar and nvarchar in hibernate) . In short, Hibernate/JDBC generates SQL that passes all strings as Unicode, regardless of the underlying SQL type. When a non-unicode (varchar) field in the database is compared to a Unicode input string, the indicies for that column do not match the encoding so a full table scan is performed. In the JDBC driver (both JTDS and MS versions) there is a parameter to pass Unicode strings as ASCII, but this is an all or nothing proposition that disallows international characters from being input into the the database.

Most posts I've seen on this issue have come up with one of two solutions - 1) change everything in the database to NVARCHAR or 2) set the sendStringParametersAsUnicode=false, My question then is this - is there any known solution for having VARCHAR and NVARCHAR play nicely together? It is a huge issue for my environment to change everything to NVARCHAR because of downstream dependencies and other external issues.

解决方案

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和SQL Server与VARCHAR和NVARCHAR配合使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆