Calendar in Criteria Querys

Hi,
i am having troubles creating criteria querys for Calendar objects. As far as I can see CriteriaBuilder has no methods for checking if one Calendar is bevor or after another. Javas Calendar Class got such methods, but i dont know how to use those in my query.

JPA supports 3 temporal types:

• java.sql.Date - representing date with no time.
• java.sql.Time - representing time with no date.
• java.sql.Timestamp - representing both data and time.

If possible, you should use these types instead of java.util.Calendar and java.util.Date, which eventually should be handled as one of the above value types. Particularly Calendar is not commonly used with ObjectDB and may have some limitations.

Anyway, the following example demonstrates criteria queries with Calendar. The usual comparison methods are used rather than before and after:

import java.util.Calendar;
import java.util.List;
import java.sql.Timestamp;

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


public final class T615 {

    public static void main(String[] args) {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory(
                "objectdb:test.tmp;drop");
        EntityManager em = emf.createEntityManager();
       
        em.getTransaction().begin();
        em.persist(new Event());
        em.getTransaction().commit();
       
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery query1 = cb.createQuery();
        Root e = query1.from(Event.class);
        query1.select(e);
        query1.where(cb.lessThanOrEqualTo(e.get("c1"), e.get("c2")));
        List results = em.createQuery(query1).getResultList();
        System.out.println(results.size());

        CriteriaQuery query = cb.createQuery();
        e = query.from(Event.class);
        query.select(e);
        ParameterExpression<Timestamp> p = cb.parameter(Timestamp.class);
        query.where(cb.lessThanOrEqualTo(e.get("c1"), p));
        results = em.createQuery(query)
            .setParameter(p, Calendar.getInstance()).getResultList();
        System.out.println(results.size());

        em.close();
        emf.close();
    }

    @Entity
    public static final class Event {

        @Id
        private long id;
       
        @Temporal(TemporalType.TIMESTAMP)
        Calendar c1;
       
        @Temporal(TemporalType.TIMESTAMP)
        Calendar c2;
       
        Event() {
            c1 = Calendar.getInstance();
            try {Thread.sleep(200); } catch (InterruptedException e) {}
            c2 = Calendar.getInstance();
        }
    }
}

Notice that Calendar values are stored in this example as Timestamp and consequently the query parameter is defined as Timestamp.

If I am running your example I get the folling error:

Exception in thread "main" [ObjectDB 2.3.4] Unexpected exception (Error 990)
  Generated by Java HotSpot(TM) Client VM 1.7.0 (on Windows 7 6.1).
Please report this error on http://www.objectdb.com/database/issue/new
com.objectdb.o.InternalException: java.lang.NullPointerException: null
java.lang.NullPointerException
at com.objectdb.o.EXR$e.c(EXR.java:190)
at com.objectdb.o.VAR.aq(VAR.java:792)
at com.objectdb.o.VAR.ap(VAR.java:735)
at com.objectdb.o.BCN.o(BCN.java:305)
at com.objectdb.o.BCN.UU(BCN.java:264)
at com.objectdb.o.PBI.C(PBI.java:146)
at com.objectdb.o.PBI.q(PBI.java:115)
at com.objectdb.o.OBI.Vi(OBI.java:236)
at com.objectdb.o.BQI.Vr(BQI.java:144)
at com.objectdb.o.PRG.ag(PRG.java:734)
at com.objectdb.o.PRG.ae(PRG.java:663)
at com.objectdb.o.PRG.ad(PRG.java:539)
at com.objectdb.o.QRM.U4(QRM.java:259)
at com.objectdb.o.MST.U4(MST.java:947)
at com.objectdb.o.WRA.U4(WRA.java:290)
at com.objectdb.o.WSM.U4(WSM.java:113)
at com.objectdb.o.QRR.g(QRR.java:232)
at com.objectdb.o.QRR.b(QRR.java:151)
at com.objectdb.jpa.JpaQuery.getResultList(JpaQuery.java:666)
at TestCase.main(TestCase.java:35)

You are right. The parameter value had to be specified as a Timestamp:

results = em.createQuery(query)
    .setParameter(p, Calendar.getInstance().getTime()).getResultList();

Automatic conversion from Calendar to Timestamp was added in build 2.3.4_06.