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:

  • 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

  • 将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();
    
    链接地址: http://www.djcxy.com/p/93998.html

    上一篇: @HostListener和Renderer.listen之间哪个更好?

    下一篇: 使用HQL无法从我的Java应用程序查询包含阿拉伯语的SQL Server表