OpenJPA DB2 and the QUERYNO Clause

[ashish22 <as...@libertymutual.com>]

Hello,

We have a requirments from our  DBAs to add the QUERYNO clause in our
queries. We are using OpenJPA 1.2.1 and the underlying database  is DB2.

The current queries that go from the application server to the database via
the JPA are like

Select X from Y where A = B;

The DBAs want it to be

Select X from Y where A = B  QUERYNO 2223456;

We played around with the setHint but we are not able to add the QUERYNO
clause as required.

Any help would be appriciated


Thanks
Ashish








-- 
View this message in context: http://openjpa.208410.n2.nabble.com/OpenJPA-DB2-and-the-QUERYNO-Clause-tp5875426p5875426.html
Sent from the OpenJPA Developers mailing list archive at Nabble.com.

Re: OpenJPA DB2 and the QUERYNO Clause

[Miłosz Tylenda <mt...@o2.pl>]

Ashish,

This can be done by extending DB2Dictionary and implementing a new hint, say "openjpa.queryno". The hint name might need to start with "openjpa.", otherwise it could get ignored. The example is for OpenJPA 2.2.0-SNAPSHOT but you should be able to apply it to 1.2.1 also, possible with some modification. I don't know the exact rules of where the QUERYNO clause should be placed in the query, if it is not always at the end, the overridden method will be more complex but rather doable.

public class MyDB2Dictionary extends DB2Dictionary {

    @Override
    protected String getForUpdateClause(JDBCFetchConfiguration fetch, boolean isForUpdate, Select sel) {
        String queryClause = super.getForUpdateClause(fetch, isForUpdate, sel);
        if (fetch == null) {
            return queryClause;
        }
        Object queryno = fetch.getHint("openjpa.queryno");
        if (queryno == null) {
            return queryClause;
        }
        return queryClause + " QUERYNO " + queryno;
    }
}

You use the hint like this:

        Query q = em.createQuery("select m from Message m");
        q.setHint("openjpa.queryno", 2223456);
        q.getResultList();

Hope this helps.

Greetings,
Milosz

> 
> Hello,
> 
> We have a requirments from our  DBAs to add the QUERYNO clause in our
> queries. We are using OpenJPA 1.2.1 and the underlying database  is DB2.
> 
> The current queries that go from the application server to the database via
> the JPA are like
> 
> Select X from Y where A = B;
> 
> The DBAs want it to be
> 
> Select X from Y where A = B  QUERYNO 2223456;
> 
> We played around with the setHint but we are not able to add the QUERYNO
> clause as required.
> 
> Any help would be appriciated
> 
> 
> Thanks
> Ashish
> 
> 
> 
> 
> 
> 
> 
> 
>