[jira] [Commented] (OPENJPA-1955) Parameter Types mapping error when using SQLCache

["Helen Xu (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OPENJPA-1955?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13433275#comment-13433275 ] 

Helen Xu commented on OPENJPA-1955:
-----------------------------------

When it processed the parameter of the cached query, it lost the metadata of the parameter. That's why it used timestamp instead of date as the query parameter.

There are several work around for this problem:

1. Covert the parameter type to java.sql.Date when setting the parameter like this:

         query.setParameter("termin", new java.sql.Date(termin.getTime()), TemporalType.DATE);

2.  Exclude all of the QuerySQLCache processing like Jeremy suggested.

3.  Exclude just one Query from the Cache by configuring openjpa.jdbc.QuerySQLCache
<property name="openjpa.jdbc.QuerySQLCache" value="true(excludes='select c from Company c;select d from Department d')"/>

4. Move up to a version of the database that supports the automatic conversion from timestamp to data.
                
> Parameter Types mapping error when using SQLCache
> -------------------------------------------------
>
>                 Key: OPENJPA-1955
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-1955
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: query
>    Affects Versions: 2.1.0
>         Environment: doesnt matter
>            Reporter: Marc Logemann
>         Attachments: OPENJPA-1955-Test.patch, testSchema.ddl
>
>
> 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]
> As Jeremy suggested, using this:
> <property name="openjpa.jdbc.QuerySQLCache" value="false"/>
> solves the problem but this is only a workaround.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira