SQL 使用 Hibernate 进行不区分大小写的搜索
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/120588/
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
Case-insensitive search using Hibernate
提问by Yuval
I'm using Hibernate for ORM of my Java app to an Oracle database (not that the database vendor matters, we may switch to another database one day), and I want to retrieve objects from the database according to user-provided strings. For example, when searching for people, if the user is looking for people who live in 'fran', I want to be able to give her people in San Francisco.
我正在将 Hibernate 用于我的 Java 应用程序到 Oracle 数据库的 ORM（不是数据库供应商很重要，我们可能有一天会切换到另一个数据库），我想根据用户提供的字符串从数据库中检索对象。例如，在搜索人员时，如果用户正在寻找居住在“fran”的人员，我希望能够为她提供旧金山的人员。
SQL is not my strong suit, and I prefer Hibernate's Criteriabuilding code to hard-coded strings as it is. Can anyone point me in the right direction about how to do this in code, and if impossible, how the hard-coded SQL should look like?
SQL 不是我的强项，我更喜欢 Hibernate 的Criteria构建代码而不是硬编码的字符串。任何人都可以指出我如何在代码中执行此操作的正确方向，如果不可能，硬编码的 SQL 应该是什么样子？
Thanks,
谢谢，
Yuval =8-)
尤瓦尔 =8-)
回答by Cowan
For the simple case you describe, look at Restrictions.ilike(), which does a case-insensitive search.
对于您描述的简单情况，请查看 Restrictions.ilike()，它执行不区分大小写的搜索。
Criteria crit = session.createCriteria(Person.class);
crit.add(Restrictions.ilike('town', '%fran%');
List results = crit.list();
回答by Yuval
Criteria crit = session.createCriteria(Person.class);
crit.add(Restrictions.ilike('town', 'fran', MatchMode.ANYWHERE);
List results = crit.list();
回答by SamS
If you use Spring's HibernateTemplate to interact with Hibernate, here is how you would do a case insensitive search on a user's email address:
如果您使用 Spring 的 HibernateTemplate 与 Hibernate 交互，那么您将如何对用户的电子邮件地址进行不区分大小写的搜索：
getHibernateTemplate().find("from User where upper(email)=?", emailAddr.toUpperCase());
回答by Richard
The usual approach to ignoring case is to convert both the database values and the input value to upper or lower case - the resultant sql would have something like
忽略大小写的常用方法是将数据库值和输入值都转换为大写或小写 - 生成的 sql 将具有类似
select f.name from f where TO_UPPER(f.name) like '%FRAN%'
In hibernate criteria restrictions.like(...).ignoreCase()
在休眠条件限制中。like(...).ignoreCase()
I'm more familiar with Nhibernate so the syntax might not be 100% accurate
我对 Nhibernate 比较熟悉，所以语法可能不是 100% 准确
for some more info see pro hibernate 3 extractand hibernate docs 15.2. Narrowing the result set
回答by Arthur Thomas
You also do not have to put in the '%' wildcards. You can pass MatchMode(docs for previous releases here) in to tell the search how to behave. START, ANYWHERE, EXACT, and ENDmatches are the options.
您也不必放入“%”通配符。您可以传入MatchMode（此处为先前版本的文档）以告诉搜索如何运行。 START、ANYWHERE、EXACT和END匹配项是选项。
回答by Casey
This can also be done using the criterion Example, in the org.hibernate.criterion package.
这也可以使用 org.hibernate.criterion 包中的标准 Example 来完成。
public List findLike(Object entity, MatchMode matchMode) {
Example example = Example.create(entity);
example.enableLike(matchMode);
example.ignoreCase();
return getSession().createCriteria(entity.getClass()).add(
example).list();
}
Just another way that I find useful to accomplish the above.
这是我发现对完成上述任务有用的另一种方式。
回答by gawi
Since Hibernate 5.2 session.createCriteriais deprecated. Below is solution using JPA 2 CriteriaBuilder. It uses likeand upper:
由于 Hibernate 5.2session.createCriteria已被弃用。以下是使用 JPA 2 CriteriaBuilder 的解决方案。它使用like和upper：
CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Person> criteria = builder.createQuery(Person.class);
Root<Person> root = criteria.from(Person.class);
Expression<String> upper = builder.upper(root.get("town"));
criteria.where(builder.like(upper, "%FRAN%"));
session.createQuery(criteria.select(root)).getResultList();
回答by Cade Roux
Most default database collations are not case-sensitive, but in the SQL Server world it can be set at the instance, the database, and the column level.
大多数默认数据库排序规则不区分大小写，但在 SQL Server 世界中，它可以在实例、数据库和列级别进行设置。
回答by Paul Whelan
You could look at using Compass a wrapper above lucene.
您可以考虑在 lucene 之上使用 Compass 包装器。
http://www.compass-project.org/
http://www.compass-project.org/
By adding a few annotations to your domain objects you get achieve this kind of thing.
通过向域对象添加一些注释，您可以实现这种事情。
Compass provides a simple API for working with Lucene. If you know how to use an ORM, then you will feel right at home with Compass with simple operations for save, and delete & query.
Compass 为使用 Lucene 提供了一个简单的 API。如果您知道如何使用 ORM，那么您会感到宾至如归，使用 Compass 进行简单的保存、删除和查询操作。
From the site itself. "Building on top of Lucene, Compass simplifies common usage patterns of Lucene such as google-style search, index updates as well as more advanced concepts such as caching and index sharding (sub indexes). Compass also uses built in optimizations for concurrent commits and merges."
从网站本身。“构建在 Lucene 之上，Compass 简化了 Lucene 的常见使用模式，例如 google 风格的搜索、索引更新以及更高级的概念，例如缓存和索引分片（子索引）。Compass 还使用内置优化来进行并发提交和合并。”
I have used this in the past and I find it great.
我过去使用过这个，我觉得它很棒。
