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.
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 (ie the N -prefix on string literals).
So, it seems like you have two options:
HQL
Continue using createQuery as follows:
session.createQuery("from Table T " +
"where T.column like :fltr ")
.setParameter( "fltr", "%" + content + "%", StringNVarcharType.INSTANCE )
.list();
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:
StringNVarcharType type found in chart of 2.3.1. Hibernate-provided BasicTypes: Table 1. Standard BasicTypes 将var声明为NVARCHAR，并删除引号。
FROM Table T WHERE T.columnName LIKE N'%' + var + N'%'
使用以下HQL：
String hql = "FROM Table T WHERE T.columnName LIKE CONCAT('%', :columnValue, '%')";
session.createQuery(hql)
.setString("columnValue", "Sample Value")
.list();