Cannot query SQL Server table containing Arabic from my Java app using HQL

Question

I'm working on a Java Swing application where I query a table in a SQL Server database. This table contains some data that is in Arabic, Chinese etc... But the problem is that I am not getting any results while using this query: (var can be Arabic or any other language):

from Table T where T.columnName like '%"+var+"%'

I did some searching and then tried the following:

from Table T where T.columnName like N'%"+var+"%'

I am getting this error message on NetBeans:

Exception in thread "AWT-EventQueue-0"

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: N near line 1

Can someone help me with this problem? I'm confused knowing that this same last query worked perfectly in SQL Server Management Studio.

Answer 1

The problem is that HQL != T-SQL, and you are mixing the two. HQL is pseudo-SQL and so does not understand the T-SQL-specific dialect handling of Unicode strings (i.e. the N-prefix on string literals).

So, it seems like you have two options:

1. HQL

   Continue using createQuery as follows:

   session.createQuery("from Table T " +
                       "where T.column like :fltr ")
          .setParameter( "fltr", "%" + content + "%", StringNVarcharType.INSTANCE )
          .list();
   

2. T-SQL

   Switch to using createSQLQuery as follows:

   session.createSQLQuery("select * from Table T where T.column like N'%" +
                          content + "%' ").list();
   

   There might be more to do here, but I have no way to test it.

More info and examples can be found at: Hibernate ORM 5.2.4.Final User Guide:

• HQL code above based on Example 334
• StringNVarcharType type found in chart of 2.3.1. Hibernate-provided BasicTypes: Table 1. Standard BasicTypes
• T-SQL code above based on Example 402 and Example 423