How to implement NamedQuery based finder in Spring Roo?

Question

I can't find information about how to use standard JPQL based queries with Spring Roo project. I'm not sure if dynamic finders will be sufficient for complex queries. Is there any way to use @NamedQueries in Spring Roo project without breaking the concept of absence of DAO layer ?

Answer 1

Assume you are using Hibernate. Following is the native name query in entity.

@NamedNativeQueries({
    @NamedNativeQuery(
            name="findBestSellingPurchases",
            query="select p.id,p.date_of_purchase,sum(p.quantity_purchased) as quantity_purchased,p.version,p.client,p.item,p.staff_member from purchases p group by p.item order by sum(p.quantity_purchased) desc",
            resultClass=Purchases.class
    )
})

This is how you call it from an Purchases entity define in Purchases.java.

    @SuppressWarnings("unchecked")
    public static List<Purchases> findBestSellingPurchases() {
        return entityManager().createNamedQuery("findBestSellingPurchases").getResultList();
    }

All the best

P.S this is ERD diagram in case you might need.