我们可以为JPA NamedQueries分配自定义查询提示

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

我们需要将查询号附加到应用程序执行的每个查询中。

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

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

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

OpenJPA支持查询提示，但仅针对特定实现的特定提示。

...
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.

但是根据JPA规范和openjpa“忽略了特定数据库无法处理的无效提示或提示。否则，无效提示将导致抛出ArgumentException。”因此，将“QUERYNO”指定为提示似乎没有任何影响。

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(); ...

...查询q = em.createQuery（“从杂志m中选择m ......”）; q.setHint（“com.me.CustomQueryNoHint”，new Integer（2234））;列表r = q.getResultList（）; ...

3 个解决方案

#1

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

将“QUERYNO”指定为提示似乎没有任何影响。

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. :-)

正确。根据您引用的文档，“QUERYNO”是无效提示，因此会被忽略。该文档有点令人困惑，但我相信如果可以解释为支持您观察到的行为。 :-)

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.

这是一项更高的任务。我不认为OpenJPA旨在允许编写自定义查询提示。

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?

我想到了更多关于你想要将特定字符串附加到每个SQL的实际问题，我认为在OpenJPA中做起来并不容易。也许您可以为JDBC驱动程序编写一个包装器，并将字符串附加到每个SQL？

#2

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?

而不是使用JPQL并尝试强制特定于供应商的东西进入查询，您是否考虑过编写映射到实体返回类型的本机查询？

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.

这将是更多的工作，因为您的本机查询需要选择与实体类中的映射列匹配的列值，但它应该工作。使用本机SQL时，查询引擎不得解析和解释特定于供应商的SQL，因此最终的DB2特定子句应在运行时传递给基础数据库。

#3

Not complete answer but just a pointer...

不完整的答案，但只是一个指针......

QueryCounter

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.

OpenJPA有类似的概念1.8.7。特定于数据库的提示。看看这些特定的提示是否可以解决您的目的。

UPDATED ANSWER

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

@Eddie，看看这可以帮到你...........

CustomLogFactory

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

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