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+"%'
from Table T where T.columnName like N'%"+var+"%'
Exception in thread "AWT-EventQueue-0"
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: N near line 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:
createQuery as follows:
session.createQuery("from Table T " + "where T.column like :fltr ") .setParameter( "fltr", "%" + content + "%", StringNVarcharType.INSTANCE ) .list();
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:
StringNVarcharTypetype found in chart of 2.3.1. Hibernate-provided BasicTypes: Table 1. Standard BasicTypes