TheLila TheLila - 17 days ago 7
Java Question

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

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

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: