Order in WHERE Clause affects behaviour on DATE/DATETIME columns


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"


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)

Post Reply

To post a reply and/or subscribe to update notifications - please login