在hibernate中映射到varchar和nvarchar [英] Mapping to varchar and nvarchar in hibernate

问题描述

如果数据库中有2列，例如

  code varchar（3）
 name nvarchar（50 ）
  

如何通过代码告诉hibernate传递varchar？

在hibernate映射中，字符串映射到nvarchar，并产生如下查询：

 选择代码，名称从表中代码= N'AAA'（而不是代码='AAA'）
  

因为它会导致索引扫描而不是索引查找操作（扫描所有索引节点而不是直接去请求一个）

由于代码在数百万行中以及在几个索引和外键中，将其从varchar更改为nvarchar将导致性能下降（更多IO操作，因为nvarchar使用的空间比varchar多两倍）。

有什么办法吗？告诉hibernate根据数据库类型做映射，而不是Java类型？

谢谢

解决方案

可能你已经解决了这个问题，但我遇到了类似的问题。

我使用jTDS JDBC驱动程序，扫描问题：

$ p $ ; sendStringParametersAsUnicode = false; prepareSQL = 0


添加到jTDS连接字符串的末尾。

可能它不会解决您的问题，因为这样做，jTDS将只使用VARCHAR（不再是NVARCHAR）。

另外，我必须禁用准备好的SQL，因为Hibernate使用'like'而不是'=' '生成查询时，通过使用'like'和变量（SELECT ... WHERE column LIKE @var）结合使用会导致索引扫描（MSSQL 2000）。

If there are 2 columns in database, eg.

code varchar(3)
name nvarchar(50)

How to tell hibernate to pass varchar for searching by code?

In the hibernate mappings string is mapped to nvarchar and it produces queries like:

Select code, name From table where code=N'AAA'  (instead of code='AAA')

This is very bad as it causes index scan instead of index seek operation (scanning all index nodes instead of directly going to requested one)

As code is used in millions of rows as well as in several indexes and foreign keys, changing it from varchar to nvarchar will cause performance degradation (more IO operations as nvarchar uses twice more space than varchar).

Is there any way to tell hibernate to do mapping according to database type, not to Java type?

Thanks

解决方案

Probably you already solved this, but I had a similar problem.

I'm using jTDS JDBC driver and I solved the index scan problem by adding:

;sendStringParametersAsUnicode=false;prepareSQL=0

to the end of the jTDS connection string.

Probably it would not had solved your problem because by doing this, jTDS will only use VARCHAR (no NVARCHAR anymore).

Also, I had to disable the prepared SQL, because Hibernate is using 'like' instead of '=' when generating the queries and by using 'like' combined with a variable (SELECT ... WHERE column LIKE @var) causes an index scan (MSSQL 2000).

这篇关于在hibernate中映射到varchar和nvarchar的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！