openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeremy Bauer <techhu...@gmail.com>
Subject Re: same jpa query generates different SQLs
Date Wed, 02 Mar 2011 18:10:58 GMT
Marc,

I'm glad that worked for you.  You could actually just exclude that query
for the time being, that way you still get caching benefits for your other
statements.  To exclude the query you can either set the query hint via
query.setHint("openjpa.hint.IgnorePreparedQuery", true)  on the query or
exclude it via persistence property:

<property name="openjpa.jdbc.QuerySQLCache"
value="true(excludes='select o FROM Order o where o.createdYmd = ?1
order by o.id')"/>

You can get more specifics on cache exclusion in this section of the OpenJPA
manual[1].

That should get you by for the time being, but please open a JIRA for this
problem so that it gets fixed.

[1]
http://openjpa.apache.org/builds/latest/docs/manual/ref_guide_cache_querysql.html

-Jeremy

On Wed, Mar 2, 2011 at 10:51 AM, Marc Logemann <li@logemann.org> wrote:

> Hi Jeremy,
>
> thanks for your instant feedback. I directly tried your suggestion and now
> it works. So there is a bug in the cache implementation with regard to Type
> Mappings.
> And its not a DB2 thingy because i am using MySQL ;-)
>
> if you need more infos in the error case, i am willing to help if you
> want.... I would like to see this solved because disabling the cache means
> decreasing the performance somehow right?
>
> ---
> regards
> Marc Logemann
> http://www.logemann.org
> http://www.logentis.de
>
>
>
>
> Am 02.03.2011 um 17:31 schrieb Jeremy Bauer:
>
> > Hi Marc,
> >
> > I'm wondering if this isn't caused by an inconsistent parameter type
> mapping
> > as a result of using the QuerySQLCache. I saw a similar issue a few weeks
> > ago, but it was oddly specific to DB2 on zOS. Please try:
> >
> > <property name="openjpa.jdbc.QuerySQLCache" value="false"/>
> >
> > -Jeremy
> >
> > On Wed, Mar 2, 2011 at 9:43 AM, Marc Logemann <li@logemann.org> wrote:
> >
> >> Hi,
> >>
> >> i am totally astonished:
> >>
> >> I am using this query:
> >>
> >>   public List<Order> findByDate(Date date) {
> >>
> >>       TypedQuery<Order> query = getEntityManager().
> >>               createQuery("select o FROM Order o where o.createdYmd = ?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 = new Date();
> >>       Calendar calendar = Calendar.getInstance();
> >>       calendar.setTime(today);
> >>       calendar.add(Calendar.DATE, -1);
> >>       Date yesterday = calendar.getTime();
> >>
> >>       List<Order> list = 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 =
> t1.oid
> >> LEFT OUTER JOIN address t3 ON t0.oid = t3.order_oid LEFT OUTER JOIN
> address
> >> t4 ON t0.invaddress_oid = t4.oid LEFT OUTER JOIN orders t2 ON t1.oid =
> >> t2.box_oid WHERE (t0.createdymd = ?) AND (t3.jpatype IS NULL OR
> t3.jpatype
> >> IN (?)) ORDER BY t0.ordernr ASC [params=(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 =
> t1.oid
> >> LEFT OUTER JOIN address t3 ON t0.oid = t3.order_oid LEFT OUTER JOIN
> address
> >> t4 ON t0.invaddress_oid = t4.oid LEFT OUTER JOIN orders t2 ON t1.oid =
> >> t2.box_oid WHERE (t0.createdymd = ?) AND (t3.jpatype IS NULL OR
> t3.jpatype
> >> IN (?)) ORDER BY t0.ordernr ASC [params=(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
> >>
> >>
> >>
> >>
> >>
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message