NativeSQLQuery binding parameters as unicode

Hibernate ORM
1

I have written a query string for SQL Server I am executing as a NativeSQLQuery. I used indexed parameter binding and things are working perfectly until I bind a search parameter that is unicode. Then the match just fails. In my JDBC connection, I have the parameter set: sendStringParametersAsUnicode=false. If I remove this the query works fine. I set this because it was sending all parameters as unicode and causing full table scans since some columns were not unicode at all. The server then reported possible deadlocks. Once I set this the system started working fine.

As I bind my parameters to my query, is there any way I can give the query a hint to treat as unicode? I see where if I were using string literals I could do something like "mycolumn = N’somevalue’. Is there any way I can give it such a hint when binding my parameter?
query.setParameter(1, ‘somevalue’);

2

Can you try to use some kind of cast? IMO this is an issue of the JDBC driver so maybe you should ask the SQL Server JDBC driver folks.

3

JDBC prepared statements have a method called setNString()

Sets the designated parameter to the given String object.
The driver converts this to a SQL NCHAR or NVARCHAR or LONGNVARCHAR value (depending on the argument’s size relative to the driver’s limits on NVARCHAR values) when it sends it to the database.

I guess what I am asking is if Hibernate Query object has a similiar method where I can tell it to pass this to the JDBC driver as NVARCHAR?

query.setParameter(i++, paramValue);

4

You can bind the parameter with a specific type through org.hibernate.query.NativeQuery#setParameter(int, java.lang.Object, org.hibernate.type.Type). The passed type is the responsible for binding the parameter to the JDBC statement. You can use StringNVarcharType.INSTANCE for this purpose.

5

That was exactly what I was looking for. Couldn’t find the StringNVarcharType. I added it to my code and it solved the problem. Thanks!