让 Hibernate 和 SQL Server 与 VARCHAR 和 NVARCHAR 一起玩 [英] Getting Hibernate and SQL Server to play nice with VARCHAR and NVARCHAR
问题描述
我目前正在大型数据库的某些表中启用 UTF-8 字符.这些表已经是 MS-SQL 类型的 NVARCHAR.此外,我还有几个使用 VARCHAR 的字段.
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.
Hibernate 与 JDBC 驱动程序的交互存在一个众所周知的问题(参见例如 在休眠中映射到 varchar 和 nvarchar) .简而言之,Hibernate/JDBC 生成的 SQL 将所有字符串作为 Unicode 传递,而不管底层 SQL 类型如何.当数据库中的非 unicode (varchar) 字段与 Unicode 输入字符串进行比较时,该列的索引与编码不匹配,因此执行全表扫描.在 JDBC 驱动程序(JTDS 和 MS 版本)中有一个参数可以将 Unicode 字符串作为 ASCII 传递,但这是一个全有或全无的提议,它不允许将国际字符输入到数据库中.
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.
我在这个问题上看到的大多数帖子都提出了两种解决方案之一 - 1) 将数据库中的所有内容更改为 NVARCHAR 或 2) 设置 sendStringParametersAsUnicode=false,那么我的问题是 - 是否有任何已知的解决方案可以让 VARCHAR 和 NVARCHAR 一起很好地发挥作用?由于下游依赖性和其他外部问题,我的环境将所有内容更改为 NVARCHAR 是一个巨大问题.
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.
推荐答案
我决定尝试将其作为一种可能无需接触数据库即可工作的技巧.为此,我为 NVARCHAR 字段创建了一个自定义类型.这需要 JDBC 4 驱动程序(使用 Microsoft 的驱动程序)和 Hibernate 3.6.0.sendStringParametersAsUnicode 为假.
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)" );
}
}
添加新类型.注意 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;
}
}
更新所有 NVARCHAR 字段的映射
Update mappings for all NVARCHAR fields
<property name="firstName" type="NStringUserType">
<column name="firstName" length="40" not-null="false" />
</property>
之前的原始 SQL(使用 sendUnicode..=true):
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
之后:
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
似乎对SELECT.."的工作方式类似
Seems to work similarly for 'SELECT.."
这篇关于让 Hibernate 和 SQL Server 与 VARCHAR 和 NVARCHAR 一起玩的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!