Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@openjpa.apache.org
Received-SPF: pass (athena.apache.org: local policy)
From: Marc Logemann <li@logemann.org>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Subject: same jpa query generates different SQLs
Date: Wed, 2 Mar 2011 16:43:28 +0100
Message-Id: <ED90CC3E-C6E6-4271-AC0D-178A260A56E4@logemann.org>
To: users@openjpa.apache.org
Mime-Version: 1.0 (Apple Message framework v1082)

Hi,

i am totally astonished:

I am using this query:

    public List<Order> findByDate(Date date) {

        TypedQuery<Order> query =3D getEntityManager().
                createQuery("select o FROM Order o where o.createdYmd =3D =
?1 order by o.id", Order.class);

        query.setParameter(1, date, TemporalType.DATE);
        return query.getResultList();
    }

This query is called by a service class which is scheduled ever 30 =
seconds. See the caller:

        // get yesterdays Date
        Date today =3D new Date();
        Calendar calendar =3D Calendar.getInstance();
        calendar.setTime(today);
        calendar.add(Calendar.DATE, -1);
        Date yesterday =3D calendar.getTime();

        List<Order> list =3D orderDao.findByDate(yesterday);


Now, on the second run, the query parameter is of type timestamp with =
full time specified, giving me 0 records of course. First query returns =
records because there TemporalType seems to work. See log.

FIRST RUN ->

[DEBUG myScheduler-3 16:36:30] | <t 941878577, conn 650647154> executing =
prepstmnt 1488869003 SELECT t0.oid, t0.`_version`, t1.oid, =
t1.`_version`, t1.cleared, t1.id, t1.lastused, t2.oid, t2.`_version`, =
t2.created, t2.createdymd, t2.custnr, t2.greenoption, t2.ordernr, =
t2.invaddress_oid, t2.iscardowner, t2.cost, t2.currency, t2.pricing, =
t2.paymenttype, t2.printed, t2.printedby, t2.totalprice, t1.boxtype, =
t0.created, t0.createdymd, t0.custnr, t3.oid, t3.jpatype, t3.`_version`, =
t3.addresstype, t3.city, t3.company, t3.country, t3.department, =
t3.email, t3.firstname, t3.gender, t3.lastname, t3.middlename, t3.phone, =
t3.zip, t3.postofficebox, t3.street, t3.housenr, t3.title, =
t3.deliverymode, t0.greenoption, t0.ordernr, t4.oid, t4.jpatype, =
t4.`_version`, t4.addresstype, t4.city, t4.company, t4.country, =
t4.department, t4.email, t4.firstname, t4.gender, t4.lastname, =
t4.middlename, t4.phone, t4.zip, t4.postofficebox, t4.street, =
t4.housenr, t4.title, t4.deliverymode, t4.order_oid, t0.iscardowner, =
t0.cost, t0.currency, t0.pricing, t0.paymenttype, t0.printed, =
t0.printedby, t0.totalprice FROM orders t0 LEFT OUTER JOIN boxes t1 ON =
t0.box_oid =3D t1.oid LEFT OUTER JOIN address t3 ON t0.oid =3D =
t3.order_oid LEFT OUTER JOIN address t4 ON t0.invaddress_oid =3D t4.oid =
LEFT OUTER JOIN orders t2 ON t1.oid =3D t2.box_oid WHERE (t0.createdymd =
=3D ?) AND (t3.jpatype IS NULL OR t3.jpatype IN (?)) ORDER BY t0.ordernr =
ASC [params=3D(Date) 2011-03-01, (int) 2]

SECOND RUN ->

[DEBUG myScheduler-2 16:37:00] | <t 1835085919, conn 1503676955> =
executing prepstmnt 154018541 SELECT t0.oid, t0.`_version`, t1.oid, =
t1.`_version`, t1.cleared, t1.id, t1.lastused, t2.oid, t2.`_version`, =
t2.created, t2.createdymd, t2.custnr, t2.greenoption, t2.ordernr, =
t2.invaddress_oid, t2.iscardowner, t2.cost, t2.currency, t2.pricing, =
t2.paymenttype, t2.printed, t2.printedby, t2.totalprice, t1.boxtype, =
t0.created, t0.createdymd, t0.custnr, t3.oid, t3.jpatype, t3.`_version`, =
t3.addresstype, t3.city, t3.company, t3.country, t3.department, =
t3.email, t3.firstname, t3.gender, t3.lastname, t3.middlename, t3.phone, =
t3.zip, t3.postofficebox, t3.street, t3.housenr, t3.title, =
t3.deliverymode, t0.greenoption, t0.ordernr, t4.oid, t4.jpatype, =
t4.`_version`, t4.addresstype, t4.city, t4.company, t4.country, =
t4.department, t4.email, t4.firstname, t4.gender, t4.lastname, =
t4.middlename, t4.phone, t4.zip, t4.postofficebox, t4.street, =
t4.housenr, t4.title, t4.deliverymode, t4.order_oid, t0.iscardowner, =
t0.cost, t0.currency, t0.pricing, t0.paymenttype, t0.printed, =
t0.printedby, t0.totalprice FROM orders t0 LEFT OUTER JOIN boxes t1 ON =
t0.box_oid =3D t1.oid LEFT OUTER JOIN address t3 ON t0.oid =3D =
t3.order_oid LEFT OUTER JOIN address t4 ON t0.invaddress_oid =3D t4.oid =
LEFT OUTER JOIN orders t2 ON t1.oid =3D t2.box_oid WHERE (t0.createdymd =
=3D ?) AND (t3.jpatype IS NULL OR t3.jpatype IN (?)) ORDER BY t0.ordernr =
ASC [params=3D(Timestamp) 2011-03-01 16:37:00.001, (int) 2]

I have completely no clue what to do now ;-) Thanks for input.


---
regards
Marc Logemann
http://www.logemann.org
http://www.logentis.de