JPA2：不区分大小写，就像匹配任何地方一样

问题描述

我一直在JPA 1.0（Hibernate驱动程序）中使用Hibernate Restrictions。定义了 Restrictions.ilike（column，keyword，MatchMode.ANYWHERE），它测试关键字是否与列匹配，并且不区分大小写。

I have been using Hibernate Restrictions in JPA 1.0 ( Hibernate driver ). There is defined Restrictions.ilike("column","keyword", MatchMode.ANYWHERE) which tests if the keyword matching the column anywhere and it is case-insensitive.

现在，我使用JPA 2.0和EclipseLink作为驱动程序，所以我必须使用Restrictions内置JPA 2.0。我发现 CriteriaBuilder 和方法就像一样，我也发现了如何让它在任何地方匹配（虽然它是令人敬畏的手册），但我仍然没有想出如何做不区分大小写。

Now, I am using JPA 2.0 with EclipseLink as driver so I have to use "Restrictions" build-in JPA 2.0. I found CriteriaBuilder and method like, I have also found out how to make it matching anywhere ( although it is aweful and manual ), but still I haven't figured out how to do it case-insensitive.

我目前有一个令人难以置信的解决方案：

There is my current aweful solution:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<User> query = builder.createQuery(User.class);
EntityType<User> type = em.getMetamodel().entity(User.class);
Root<User> root = query.from(User.class);

// Where   
// important passage of code for question  
query.where(builder.or(builder.like(root.get(type.getDeclaredSingularAttribute("username", String.class)), "%" + keyword + "%"),
        builder.like(root.get(type.getDeclaredSingularAttribute("firstname", String.class)), "%" + keyword + "%"),
        builder.like(root.get(type.getDeclaredSingularAttribute("lastname", String.class)), "%" + keyword + "%")
        ));

// Order By
query.orderBy(builder.asc(root.get("lastname")),
            builder.asc(root.get("firstname")));

// Execute
return em.createQuery(query).
            setMaxResults(PAGE_SIZE + 1).
            setFirstResult((page - 1) * PAGE_SIZE).
            getResultList();

问题：

Hibernate驱动程序中是否有任何功能？

Is there any function like in Hibernate driver?

我是否正确使用JPA 2.0标准？与Hibernate Restrictions相比，这是一个尴尬和不舒服的解决方案。

Am I using the JPA 2.0 criteria correctly? This is awkward and uncomfortable solution in compare to Hibernate Restrictions.

或者有人可以帮助我如何将我的解决方案更改为不区分大小写吗？

Or can anybody help me how to change my solution to be case-insensitive, please?

非常感谢。

推荐答案

起初看起来有点尴尬，但它是类型安全的。从字符串构建查询不是，因此您在运行时而不是在编译时发现错误。您可以通过使用缩进或单独执行每个步骤来使查询更具可读性，而不是在一行中编写整个WHERE子句。

It may seem a little awkward at first, but it is type-safe. Building queries from strings isn't, so you notice errors at runtime instead of at compile time. You can make the queries more readable by using indentations or taking each step separately, instead of writing an entire WHERE clause in a single line.

使查询不区分大小写，将您的关键字和比较字段转换为小写：

To make your query case-insensitive, convert both your keyword and the compared field to lower case:

query.where(
    builder.or(
        builder.like(
            builder.lower(
                root.get(
                    type.getDeclaredSingularAttribute("username", String.class)
                )
            ), "%" + keyword.toLowerCase() + "%"
        ), 
        builder.like(
            builder.lower(
                root.get(
                    type.getDeclaredSingularAttribute("firstname", String.class)
                )
            ), "%" + keyword.toLowerCase() + "%"
        ), 
        builder.like(
            builder.lower(
                root.get(
                    type.getDeclaredSingularAttribute("lastname", String.class)
                )
            ), "%" + keyword.toLowerCase() + "%"
        )
    )
);

这篇关于JPA2：不区分大小写，就像匹配任何地方一样的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！