Mapping to varchar and nvarchar in hibernate

sql hibernate varchar nvarchar
Niikola picture Niikola · Aug 20, 2009 · Viewed 24.8k times · Source

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

Answer

Gilberto Olimpio picture Gilberto Olimpio · Jan 11, 2010

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).

Related questions

could not extract ResultSet in hibernate

I have a problem with Hibernate. I try to parse to List but It throws an exception: HTTP Status 500 - could not extract ResultSet. When I debug, It fault at line query.list()... My sample code here @Entity @Table(name = "…

Read More
Hibernate show real SQL

if I set <property name="show_sql">true</property> in my hibernate.cfg.xml configuration file in the console I can see the SQL. But it's not real SQL... Can I see the SQL code that …

Read More
How to print a query string with parameter values when using Hibernate

Is it possible in Hibernate to print generated SQL queries with real values instead of question marks? How would you suggest to print queries with real values if it is not possible with Hibernate API?

Read More