We are required to append query numbers to each and every query our application executes.

EX: SELECT * FROM ... WHERE ... QUERYNO 123456;

OpenJPA supports query hints, but only for specific hints on specific implementations.

...
Query q = em.createQuery("select m from Magazine m where ... ");
q.setHint("openjpa.hint.OptimizeResultCount", new Integer(2));
q.setHint("openjpa.FetchPlan.ReadLockMode","WRITE");
List r = q.getResultList();
...

But according to the JPA spec, and openjpa "Invalid hints or hints开发者_如何学编程 which can not be processed by a particular database are ignored. Otherwise, invalid hints will result in an ArgumentException being thrown." SO specifycing "QUERYNO" as a hint seems to have no impact.

How do I create a custom Query Hint to specify at runtime?

... Query q = em.createQuery("select m from Magazine m where ... "); q.setHint("com.me.CustomQueryNoHint", new Integer(2234)); List r = q.getResultList(); ...

specifying "QUERYNO" as a hint seems to have no impact.

Correct. Per the doc that you quoted, "QUERYNO" is an invalid hint so it is ignored. The doc is somewhat confusing, but I believe if can be interpreted to support the behavior that you observed. :-)

How do I create a custom Query Hint to specify at runtime?

That is a much taller task. I don't believe that OpenJPA was designed to allow writing custom query hints.

I thought some more about your actual problem of wanting to append a specific string to EVERY SQL and I don't think it would be very easy to do in OpenJPA. Perhaps you could write a wrapper for your JDBC driver and have that append your string to every SQL?

Rather than use JPQL and try to force vendor-specific stuff into the query, have you considered writing a native query that is mapped to an entity return type?

em.createNativeQuery(YOUR_DB2_NATIVE_SQL_QUERY_STRING, Magazine.class)

It will be more work for you since your native query needs to select column values that match up with the mapped columns in the entity class, but it should work. When you use native SQL the query engine must not parse and interpret vendor-specific SQL, so your DB2-specific clause at the end should be passed through to the underlying database at runtime.

Not complete answer but just a pointer...

QueryCounter

public class QueryCounter {
    private static long COUNTER = 0;

    private static long next() {
        return ++COUNTER;
    }

    private static String getHintValue() {
        return "/*Query No. " + next() + " */";
    }

    public static void setQueryCount(Query query) {
        /* EclipseLink */
        //query.setHint(QueryHints.HINT, getHintValue());
            query.setHint("eclipselink.sql.hint", getHintValue());

        /* OpenJPA + Oracle */
        //query.setHint("openjpa.hint.OracleSelectHint", getQueryHint());

        /* OpenJPA + MySQL */
        //query.setHint("openjpa.hint.MySQLSelectHin", getQueryHint());
    }
}

Usage

Organization sun = new Organization("Sun");
        em.persist(sun);
        tx.commit();
        Assert.assertNotNull(sun.getEntityId());

        Query query = em.createQuery("SELECT org.entityId FROM Organization org WHERE org.entityId = " + sun.getEntityId());
        QueryCounter.setQueryCount(query);
        query.getResultList();

        /*ServerSession does NOT log ReadObjectQuery??*/
        query = em.createQuery("SELECT org FROM Organization org WHERE org.entityId = " + sun.getEntityId());
        QueryCounter.setQueryCount(query);
        query.getResultList();

        query = em.createQuery("SELECT org.entityId FROM Organization org WHERE org.entityId = " + sun.getEntityId());
        QueryCounter.setQueryCount(query);
        query.getResultList();

Console

[EL Finest]: 2010-11-20 19:06:16.45--UnitOfWork(717879615)--Thread(Thread[main,5,main])--Execute query ReportQuery(referenceClass=Organization sql="SELECT entity_id FROM organization_tt WHERE (entity_id = ?)")
[EL Fine]: 2010-11-20 19:06:16.475--ServerSession(699542937)--Connection(1949550475)--Thread(Thread[main,5,main])--SELECT /*Query No. 1 */ entity_id FROM organization_tt WHERE (entity_id = ?)
    bind => [1]
[EL Finest]: 2010-11-20 19:06:23.372--UnitOfWork(717879615)--Thread(Thread[main,5,main])--Execute query ReadObjectQuery(referenceClass=Organization sql="SELECT entity_id, name FROM organization_tt WHERE (entity_id = ?)")
[EL Finest]: 2010-11-20 19:06:35.916--UnitOfWork(717879615)--Thread(Thread[main,5,main])--Execute query ReportQuery(referenceClass=Organization sql="SELECT entity_id FROM organization_tt WHERE (entity_id = ?)")
[EL Fine]: 2010-11-20 19:06:35.92--ServerSession(699542937)--Connection(1949550475)--Thread(Thread[main,5,main])--SELECT /*Query No. 3 */ entity_id FROM organization_tt WHERE (entity_id = ?)
    bind => [1]

OpenJPA has similar concept 1.8.7. Database-Specific Hints. See if these specific hints can solve your purpose.

UPDATED ANSWER

@Eddie, see if this can help you...........

CustomLogFactory

    public class MyLogFactory extends org.apache.openjpa.lib.log.LogFactoryImpl {

    /* copied from LogFactoryImpl.NEWLINE */
    private static final String NEWLINE = J2DoPrivHelper.getLineSeparator();

    private boolean sqlLogger;

    @Override
    public Log getLog(String channel) {
        if("openjpa.jdbc.SQL".equals(channel)) { // OR equalsIgnoreCase(channel) ??? 
            sqlLogger = true;
        }
        return super.getLog(channel);
    }

    @Override
    protected LogImpl newLogImpl() {
        if(sqlLogger) {
            sqlLogger = false; /* once an SQL Logger is created, we dont't need any more instances */
            return new LogImpl() {
                private long sqlCounter = 0;

                @Override
                protected String formatMessage(short level, String message, Throwable t) {
                    if(isSQLString(message)) {
                        StringBuffer formattedMessage = new StringBuffer(super.formatMessage(level, message, t));
                        StringBuffer queryNo = new StringBuffer();
                        queryNo.append("  [Query # ").append(++sqlCounter).append("]").append(NEWLINE);
                        formattedMessage.delete(formattedMessage.length() - NEWLINE.length(), formattedMessage.length());
                        formattedMessage.append(queryNo);
                        return formattedMessage.toString();
                    }
                    return super.formatMessage(level, message, t);
                }

                /* just a sample implementation; checks whether message contains the word "executing"
                 * more concrete implementation should check the message for SELECT, UPDATE, INSERT INTO, ALTER.... clauses */
                private boolean isSQLString(String message) {
                    if(message.contains("executing")) {
                        return true;
                    }
                    return false;
                }

            };
        }
        return super.newLogImpl();
    }

}

peristence.xml

<property name="openjpa.Log" value="org.opensource.logger.MyLogFactory(DefaultLevel=WARN, Runtime=INFO, Tool=INFO, SQL=TRACE)"/>

Test

EntityManager em = Persistence.createEntityManagerFactory("default").createEntityManager();
EntityTransaction tx = em.getTransaction();
tx.begin();
Person person = new Person();
person.setName("Bond-OO7");
person.setAge(22);
em.persist(person);
tx.commit();
em.close();

Console

............
2084  default  TRACE  [main] openjpa.jdbc.SQL - <t 346613126, conn 1551158018> executing prepstmnt 556472773 SELECT SEQUENCE_OWNER AS SEQUENCE_SCHEMA, SEQUENCE_NAME FROM ALL_SEQUENCES  [Query # 1]
2136  default  TRACE  [main] openjpa.jdbc.SQL - <t 346613126, conn 1551158018> [52 ms] spent
2305  default  TRACE  [main] openjpa.jdbc.SQL - <t 346613126, conn 2026561073> executing prepstmnt 6637010 INSERT INTO Person (id, age, name) VALUES (?, ?, ?) [params=?, ?, ?]  [Query # 2]
2306  default  TRACE  [main] openjpa.jdbc.SQL - <t 346613126, conn 2026561073> [1 ms] spent
............

Reference