java 如何使用 JPA Criteria API 搜索字符串的日期字段

How to search date field for a String using JPA Criteria API

提问by techknowcrat

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 linkand this one too.

这是一个从我这里的另一个问题衍生出来的问题。我认为最好在有人（@Franck）指出这个链接和这个链接之后提出一个不同的问题。

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.

我对如何使用 JPA Criteria API 在数据库日期列（在我的例子中是 MySQL DATETIME）中搜索字符串感到困惑。

Here's what I've done;

这是我所做的；

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]

where 10-2015is the String being searched for;

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

10-2015正在搜索的字符串在哪里；

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

我被困在如何实现这一点上。我需要一些帮助。

回答by techknowcrat

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

好的，在对各种策略进行了大量试验之后，这就是我所做的最终奏效的事情。

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

我在这里看到这篇文章，突然想起了 JPATuple接口，它是一个可以返回多个结果类型的对象。因此，要执行我的like比较，并且由于 Date 不能简单地转换为 String，这里是步骤；

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 likeexpression.

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

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()+"%"));
}

NOTE-WORTHY CONSIDERATIONS-

值得注意的注意事项-

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 PMhence my ability to know how to format the Date for the comparison in my likeexpression 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.

1. 我知道，无论从哪里开始搜索，我的所有日​​期都以这种形式显示，07/10/2015 10:25:09 PM因此我能够知道如何将like表达式中的比较日期格式化为"'%d/%m/%Y %r'".
2. 这只是适用于日期的一步。大多数其他类型，例如 int、long、char ...等...都可以直接转换为字符串，随着我探索更多类型的数据，我肯定会对不能直接转换为字符串的任何其他类型执行相同的操作.

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!

最后，对于这以任何方式帮助的那个人......干杯！

回答by Aleksei Egorov

This works in my case H2 (I use it for unit-tests), and I hope will work as well in Postgresql and Oracle, since TO_CHAR function seems to be cross-DB supported.

这适用于我的情况 H2（我将它用于单元测试），我希望在 Postgresql 和 Oracle 中也能工作，因为 TO_CHAR 函数似乎是跨数据库支持的。

Path<Date> path = ua.get(MyEntity_.timestamp);
Expression<String> dateStringExpr = cb.function("TO_CHAR", String.class, path, cb.literal("DD.MM.YYYY HH24:MI:SS"));
predicates.add(cb.like(dateStringExpr, "%" + value + "%"));

PS. MyEntity_stands for metamodel generated for real MyEntity. You may read about Metamodels in Oracle docuemntation for Criteria API.

附注。MyEntity_代表为 real 生成的元模型MyEntity。您可以在 Oracle 文档中为Criteria API阅读有关元模型的信息。

回答by Ish

I would suggest you convert you search string to Date object, and do the comparison

我建议您将搜索字符串转换为 Date 对象，然后进行比较

SimpleDateFormat dateFormat = new SimpleDateFormat(...desired date format here...);
Date dateSearchParam = dateFormat.format(search);
predicates.add(cb.eq(entity.get("dateJoined"), dateSearchParam);

Or if you want, you can change the type of your dateJoined attribute in your Entity to String, while your MySQL DB type remains DATETIME. You can utilize JPA @Convert to convert DATETIME to java.lang.String when Entity is retrieved from DB (and vice-versa when Entity is being persisted to DB).

或者，如果需要，您可以将实体中的 dateJoined 属性的类型更改为字符串，而您的 MySQL 数据库类型仍为 DATETIME。当从 DB 检索 Entity 时，您可以利用 JPA @Convert 将 DATETIME 转换为 java.lang.String （反之亦然，当 Entity 被持久化到 DB 时）。

See a sample here.

在此处查看示例。

Attribute Converters are only available in JPA 2.1 version.

属性转换器仅在 JPA 2.1 版本中可用。