213 words

Order in WHERE Clause affects behaviour on DATE/DATETIME columns

2017-07-26 07:33

Quick example: 

public class MyClass {
    @Column(name = "myText")
    String myText;
    @Column(name = "myDate")
    Date myDate;

Now, when querying the db with:

Query q = JPA.em().createQuery("SELECT m FROM MyClass m WHERE m.myDate LIKE :query OR m.myText LIKE :query");
q.setParameter("query", "%myQuery%");

I would get an IllegalArgumentException: Parameter value [%myQuery%] did not match expected type [java.util.Date (n/a)] because I haven't declared the query parameter as TemporalType.TIMESTAMP.

But when I write following query:

Query q = JPA.em().createQuery("SELECT m FROM MyClass m WHERE m.myText LIKE :query OR m.myDate LIKE :query");
q.setParameter("query", "%myQuery%");

I don't get any error! The order of the WHERE clauses obviously makes a difference. The date column seems to be converted to a String so it can be compared with the query parameter.

While I want to compare the date column against the user query as a String (this is good behaviour for me), I don't think this is the expected behaviour? 

I am developing an web application using the Play Framwork and JPA and use following libraries:

"mysql" % "mysql-connector-java" % "5.1.34"
"org.hibernate" % "hibernate-entitymanager" % "4.3.8.Final"

enigma969's picture
Joined on 2017-07-26
User Post #1
2017-07-26 09:33

The error message that you get is from Hibernate rather than from ObjectDB.

This is the ObjectDB forum. Please use the Hibernate forum / StackOverflow for questions about Hibernate.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support's picture
Joined on 2010-05-03
User Post #2,784

Post Reply

Please read carefully the posting instructions - before posting to the ObjectDB website.

  • You may have to disable pop up blocking in order to use the toolbar (e.g. in Chrome).
  • Use ctrl + right click to open the browser context menu in the editing area (e.g. for using a browser spell checker).
  • To insert formatted lines (e.g. Java code, stack trace) - select a style in the toolbar and then insert the text in the new created block.
  • Avoid overflow of published source code examples by breaking long lines.
  • You may mark in paragraph code words (e.g. class names) with the code style (can be applied by ctrl + D).
  • Long stack traces (> 50 lines) and complex source examples (> 100 lines) should be posted as attachments.
Maximum file size: 32 MB