openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Marc Logemann ...@logemann.org>
Subject Re: same jpa query generates different SQLs
Date Thu, 03 Mar 2011 10:21:56 GMT
Hi,

created an issue for it. Thanks for your ideas to make the cache disablement query based....

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




Am 02.03.2011 um 19:10 schrieb Jeremy Bauer:

> 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
View raw message