Implementing dynamic paged queries - POJOs in Action: Developing Enterprise Applications with Lightweight Frameworks - page 447

A SQL query that returns DTO s is also known as a SQL projection query and, if
you do not need to load the actual objects, provides more flexibility. The columns
of the result set are mapped to the JavaBean-style properties of the result class
instead of the fields of a persistent class and can come from multiple tables and
denormalized columns. You can, for example, use a SQL projection query to load
fields from multiple related objects.
Listing 11.4 shows an example of a SQL projection query that retrieves orders
and their restaurants using a SQL SELECT statement that has a FIRST_ROWS opti-
mizer hint. It returns a collection of OrderSummaryDTO objects that contain the
order's ID , delivery time, email, and phone number, and the restaurant's name.

Listing 11.4 An example of a JDO SQL projection query

public class JDOOrderRepositoryImpl extends
JdoDaoSupport implements OrderRepository {
…
public List findOrdersUsingSQL() {
return (List) getJdoTemplate().executeFind(
new SQLCallback());
}

private final class SQLCallback implements
JdoCallback {

public Object doInJdo(PersistenceManager pm)
throws JDOException {
String sqlQuery = "SELECT /*+ FIRST_ROWS(20) */ "
+ " o.order_id as orderId, "
+ " o.delivery_time as deliveryTime, "
+ " o.payment_email as email, "
+ " o.payment_phone as phoneNumber, "
+ " r.name as restaurantName "
+ " from FTGO_ORDER o, FTGO_RESTAURANT r "
+ " where o.restaurant_id = r.restaurant _id";
Query query = pm.newQuery(
"javax.jdo.query.SQL", sqlQuery);
((KodoQuery) query)
.setResultClass(OrderSummaryDTO.class );
bb List result = (List) query.execute();
return result;
}

Creates SQL query

Specifies return type

Executes
query

}
…
}