java.sql.Date equals comparison not working with CriteriaAPI

Hello,

Using ODB 2.4.5 and the Criteria API, doing a simple equals comparison with java.sql.Date value doesn't match any results in the database. Our code looks like this:

CriteriaBuilder builder = ...;
Root<MyEntity> root = ...;
java.sql.Date myDate = ...;
Predicate predicate = builder.equal(root.get("eintritt"), builder.literal(myDate));

Doing exactly the same on the same DB in the DB Explorer via a query string and the same data yields in correctly two results. Calling the toString method of the created predicate yields in something like:

$1.eintritt=2013-11-01

whereas as seen in my manual query string i do something like:

$1.eintritt = {d '2013-11-01'}

Any ideas why this might be the case? Note: We can currently not update the ODB version as this is a production system.

Note also that doing greaterEqual or lessEqual with Criteria API will yield in correct results except if the given date is exactly the same like the value of eintritt field in the DB.

java.sql.Date is used everywhere also in the Entity property field to ensure pure date comparison and saving without time parts so this is made sure.

thanks

Any feedback on this? Sorry to rush but this really is a big show-stopper in a production system right now..

thanks

We will check your report and if this problem still exists in the last version it will be fixed in a new build. However, since you will not be able to use a new build, try as a workaround to replace the date literal with a date parameter.

After further investigation of this issue, it seems that criteria queries work well with literal dates, including in version 2.4.5.

The following test case demonstrates it:

import java.util.*;

import javax.persistence.*;
import javax.persistence.criteria.*;

public final class T1469
{
    static java.sql.Date myDate;

    public static void main(String[] args)
    {
        Calendar cal = Calendar.getInstance();
        cal.set(Calendar.YEAR, 2014);
        cal.set(Calendar.MONTH, Calendar.JULY);
        cal.set(Calendar.DATE, 11);
        cal.set(Calendar.HOUR_OF_DAY, 0);
        cal.set(Calendar.MINUTE, 0);
        cal.set(Calendar.SECOND, 0);
        cal.set(Calendar.MILLISECOND, 0);
        myDate = new java.sql.Date(cal.getTime().getTime());       

        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory(
                "objectdb:$objectdb/db/test.tmp;drop");
        EntityManager em = emf.createEntityManager();

        em.getTransaction().begin();
        em.persist(new MyDateEntity());
        em.getTransaction().commit();
       
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<MyDateEntity> q = cb.createQuery(MyDateEntity.class);
        Root<MyDateEntity> root = q.from(MyDateEntity.class);
        q.select(root);
        Predicate predicate = cb.equal(root.get("date"), cb.literal(myDate));
        q.where(predicate);
        TypedQuery<MyDateEntity> query = em.createQuery(q);
        List<MyDateEntity> resultList = query.getResultList();

        System.out.println("Predicate: " + predicate);
        System.out.println("Query: " + query.toString());
        System.out.println("Result Size: " + resultList.size());
       
        em.close();
        emf.close();
    }

    @Entity
    public static class MyDateEntity
    {
        java.sql.Date date = myDate;
    }
}

The output (of the last ObjectDB build, but it works also with build 2.4.5) is:

Predicate: $1.date=2014-07-11

Query: SELECT $1 FROM MyDateEntity $1 WHERE $1.date=:$$cmlp1
Result Size: 1

The Predicate's toString is irrelevant. As can seen above in the query's toString (not shown in version 2.4.5), the date literal is replaced with a virtual parameter (which is initialized with the date literal value).

Note that the query will return 0 results if the date literal is not a pure date, i.e. if the following lines in the above test case are removed:

        cal.set(Calendar.HOUR_OF_DAY, 0);
        cal.set(Calendar.MINUTE, 0);
        cal.set(Calendar.SECOND, 0);
        cal.set(Calendar.MILLISECOND, 0);