We discussed how to declare named JDOQL queries in Section 11.10, “Named Queries”. Named queries, however, aren't limited to JDOQL. By replacing the filter attribute or element with sql in the XML, you can define a named SQL query. And because SQL queries do not require a candidate class, you can declare them outside of a class element.
Example 12.6. Named SQL Queries
<?xml version="1.0"?>
<jdoquery>
<query name="avgPrice">
<sql>SELECT AVG(PRICE) FROM MAGAZINE</sql>
</query>
<package name="org.mag">
<class name="Magazine">
<query name="ttl" sql="SELECT * FROM MAGAZINE WHERE TITLE = ?">
<result unique="true"/>
</query>
</class>
</package>
</jdoquery>
The avgPrice query above returns the average price of all magazines. Because it is a SQL aggregate, it does not have a candidate class, and so we specify it at the root level.
The ttl query returns the magazine with a the title given on execution. For this query, we chose to use the sql attribute rather than the element form used by the first query. The code below executes both queries.
PersistenceManager pm = ...;
Query query = pm.newNamedQuery (null, "avgPrice");
double avgPrice = ((Number) query.execute ()).doubleValue ();
query = newNamedQuery (Magazine.class, "ttl");
Magazine jdj = (Magazine) query.execute ("JDJ");