6

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.

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.

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-2015 is the String being searched for;

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

Community
  • 1
  • 1
SourceVisor
  • 1,868
  • 1
  • 27
  • 43
  • A date field isn't text! The database provides you with method to search for date ranges. When you want everything from october 2015 you need to search the date range from 1st october till 31th october. A text search with like '%10-2015' is not possible. – Rene M. Oct 15 '15 at 15:59
  • Actually, it wasn't me. It's @Franck who suggested this: Use SQL function on the DB side - TO_CHAR – Ish Oct 15 '15 at 16:21
  • @Ish... Oh, myBad! the reference has now been corrected. Thanks – SourceVisor Oct 15 '15 at 17:10

3 Answers3

9

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

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

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 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!

Community
  • 1
  • 1
SourceVisor
  • 1,868
  • 1
  • 27
  • 43
  • Getting error " Internal Exception: org.postgresql.util.PSQLException: function date_format(time without time zone, character varying) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 82 Error Code: 0 Call: SELECT COUNT(t0.ID) FROM icom_leader.t_run t0, icom_leader.t_tripfile t1 WHERE ((DATE_FORMAT(t1.BEGINTIME, ?) = ?) AND (t1.ID = t0.fk_tripfile_id)) bind => ['HH24:MI', 11:18]" – Milesh Nov 10 '16 at 11:22
  • Apparently you are using postgres database. Hence you need to find the function name in postgres that is equivalent to MySQL's `DATE_FORMAT` function. Unfortunately this is one of the backdrops of writing these type of expressions because it makes you lose JPA Criteria Query's database agnostic advantage – SourceVisor Nov 10 '16 at 12:49
  • Ohhh. You were right Postgresql as of now supports 'to_char' functions. – Milesh Nov 11 '16 at 04:46
4

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.

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.

Aleksei Egorov
  • 801
  • 9
  • 16
  • I have used TO_CHAR function and it works well when I execute the query in console but "the query returns nothing when used through CriteriaBuilder". Any idea what is stopping it ? for your reference the expression is * cb.function("to_char", String.class, entity.get(propertyName), cb.literal("'HH24:MI'")) * – Milesh Nov 11 '16 at 04:50
1

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

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

See a sample here.

Attribute Converters are only available in JPA 2.1 version.

Ish
  • 3,992
  • 1
  • 17
  • 23
  • 1
    The problem here is that the search String wouldn't necessarily be a Date string and may not be format-able to a Date object.... For example: if there was an html table with date being displayed in one of the columns, user may decide to type in a search TextField only part of the date e.g '10-2015' and expect to get all rows with that text. i hope you get me now. – SourceVisor Oct 15 '15 at 17:20
  • Then put a condition check when you already comparing you search string to the dateJoined column, then apply the code I've given. Otherwise, for other columns, do the normal String to String comparison in Criteria. – Ish Oct 15 '15 at 17:53