java HQL 是否具有 Restrictions.ilike 的等效项（用于不区分大小写的匹配）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7977765/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does HQL have an equivalent for Restrictions.ilike (for case-insensitive matching)?
提问by gotch4
I wrote a project for Hibernate+MySQL. Now I'm porting it to Derby (for a number of reasons).
我为 Hibernate+MySQL 写了一个项目。现在我将它移植到 Derby（出于多种原因）。
Now I discovered that Derby is case sensitive when using LIKE in queries. This could be solved using Restrictions.ilike(...)in Criteria queries... but I've many complexHQL queries that use that. Is there a way to have a functionality similar to ilikein HQL?
现在我发现在查询中使用 LIKE 时，Derby 区分大小写。这可以Restrictions.ilike(...)在 Criteria 查询中使用来解决……但是我有很多使用它的复杂HQL 查询。有没有办法拥有类似于ilikeHQL的功能？
回答by Xavi López
There is no ilikeequivalent functionality in HQL. As Konstantin has already pointed out in his suggestion, your best choice is to tune the database connectionand set collationto TERRITORY_BASED:SECONDARY, as explained in this JIRA: DERBY-1748: Global case insensitive setting.
ilikeHQL 中没有等效的功能。正如 Konstantin 在他的建议中已经指出的那样，您最好的选择是调整数据库连接并将排序规则设置为TERRITORY_BASED:SECONDARY，如 JIRA: DERBY-1748: Global case insensitive setting 中所述。
Take into account that all equalities (=) and likes will be case insensitive. This might go a bit too far, and be not suitable for your particular situation.
考虑到所有等式 ( =) 和likes 不区分大小写。这可能有点过分，并且不适合您的特定情况。
Another way of addressing this would be creating function-based indexes (if Derby supports them, of course) and tune your HQL to combine likeand lowerlike this.
解决这个问题的另一种方法是创建基于函数的索引（当然，如果 Derby 支持它们）并调整您的 HQL 以组合like和lower喜欢这个。
Query q = session.createQuery("... WHERE lower(entity.field) like ?)");
q.setString(0, '%' + variable.toLowerCase() + '%');
If Derby doesn't support FBI's (I think it doesn't), you could also create trigger-filled columns with the lower values and index them.
如果 Derby 不支持 FBI（我认为它不支持），您还可以创建具有较低值的触发器填充列并为它们编制索引。
UPDATEIt seems to be possible to define derived/autogenerated columns, as explained in this other JIRA: JIRA-481: implement SQL generated columns.
更新似乎可以定义派生/自动生成的列，如其他 JIRA：JIRA-481：实现 SQL 生成的列中所述。
回答by Konstantin Pribluda
I'm affraid, there is no way to achieve this via HQL, as it is just translated to native SQL. I think it could be possible by tuning schema:
我很害怕，没有办法通过 HQL 实现这一点，因为它只是被转换为本地 SQL。我认为可以通过调整架构来实现：
http://old.nabble.com/case-insensitive-searching-td17756019.html
http://old.nabble.com/case-insensitive-searching-td17756019.html
回答by luisZavaleta
a easy workaround would be to convert both words to to upper case (or lower case) something like this:
一个简单的解决方法是将两个单词都转换为大写（或小写），如下所示：
from DomesticCat cat where upper(cat.name) like 'FRI%'
来自DomesticCat cat where upper(cat.name) like 'FRI%'
references: http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html
参考资料：http: //docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html
