如何使用JPA Criteria API在字符串的日期字段中搜索 [英] How to search date field for a String using JPA Criteria API

问题描述

这是一个衍生出我其他问题的问题 @Franck )指出我

This is a question that spins off my other Question here . I thought it would be best put as a different question after someone(@Franck) pointed me to this link and this one too.

我很困惑如何使用JPA Criteria API在数据库日期"列(在我的情况下为MySQL DATETIME)中搜索字符串.

I'm stumped on how to search for a string in a database Date column (in my case MySQL DATETIME) using the JPA Criteria API.

这就是我所做的；

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Client> cq = cb.createQuery(Client.class);
Root<Client> entity = cq.from(Client.class);
cq.select(entity);

List<Predicate> predicates = new ArrayList<Predicate>();
predicates.add(cb.like(cb.lower(entity.get("dateJoined").as(String.class)), "%"+search.toLowerCase()+"%")); 

cq.where(predicates.toArray(new Predicate[]{}));
TypedQuery<Client> query = em.createQuery(cq); //<--- Error gets thrown here
return query.getResultList();

但是它失败，但出现以下异常；

But it fails with the following exception;

java.lang.IllegalArgumentException: Parameter value [%10-2015%] did not
match expected type [java.lang.Character]

其中10-2015是要搜索的字符串；

where 10-2015 is the String being searched for;

我坚持如何实现这一目标.我需要一些帮助.

I'm stuck on how to go by achieving this. I need some help.

推荐答案

好吧，在尝试了各种策略之后，这就是我最终成功的方法.

Ok, after lots of experimenting with various strategies, here's what I did that finally worked.

我在此处，突然想起了JPA Tuple 接口，它是一个可以返回多个结果类型的对象.因此，执行我的like比较，并且由于Date不能简单地转换为String，因此以下步骤；

I saw this post here and suddenly remembered the JPA Tuple Interface which is an Object that can return multiple result Type(s). So to perform my like comparison, and since Date cannot be simply cast to a String here are the steps;

1. 我将该列作为Tuple
2. 检查元组对象是否可以从日期分配
3. 如果是，则获取Date-Format表达式并将其传递给like表达式.

1. I get the column as a Tuple
2. do a check on The Tuple Object to see if it's assignable from Date
3. if it is, then get the Date-Format expression and pass it to the like expression.

从本质上讲，这是我最初遇到的东西，但显然失败了；

So essentially, here's what I initially had which was apparently failing;

predicates.add(cb.like(cb.lower(entity.get("dateJoined").as(String.class)), "%"+search.toLowerCase()+"%")); 

现在，这就是我拥有的漂亮的作品；

Now, this is what I have that works beautifully;

Path<Tuple> tuple = entity.<Tuple>get("dateJoined");
if(tuple.getJavaType().isAssignableFrom(Date.class)){
    Expression<String> dateStringExpr = cb.function("DATE_FORMAT", String.class, entity.get("dateJoined"), cb.literal("'%d/%m/%Y %r'"));
    predicates.add(cb.like(cb.lower(dateStringExpr), "%"+search.toLowerCase()+"%"));
}

值得一经的注意事项-

1. 我知道，无论从哪里开始搜索，我的所有日​​期都以07/10/2015 10:25:09 PM格式显示，因此我知道如何将要比较的日期格式设置为"'%d/%m/%Y %r'"的形式.
2. 这只是适用于Dates的一个步骤.大多数其他类型，例如int，long，char ...等都可以直接转换为String，并且随着我探索更多的数据类型，对于任何其他不能直接转换为String的Type，我肯定也会做同样的事情.

1. I am aware that from wherever the search would be initiated, all my Dates are presented in this form 07/10/2015 10:25:09 PM hence my ability to know how to format the Date for the comparison in my like expression as "'%d/%m/%Y %r'".
2. This is just one step that works for Dates. Most other Types e.g int, long, char ...etc... can all be directly Cast to String and as I explore more Types of data, I'll definitely do the same for any other Type that cannot be directly Cast to String.

尽管这对我来说非常有效，但是在我将其标记为正确答案之前，我将对其进行一些更广泛的测试，并在此过程中让任何对我的策略有保留的人都可以公开评论

Though this works perfectly for me, but before I mark this as the right answer, I'm going to subject it to some more extensive tests and in the process keep it open for comments by anyone that has any reservations about my strategy.

最后，这对以任何方式提供帮助的那个人……欢呼！

And finally, to that one person that this helped out in any way... Cheers!

这篇关于如何使用JPA Criteria API在字符串的日期字段中搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！