4;Sql indicates to that this bean // is based on RawSql rather than a table @Entity @Sql public class OrderAggregate { @OneToOne Order order; Double totalAmount; Double totalItems; // getters and setters ...
Example 1:
String sql = " select order_id, o.status, c.id, c.name, sum(d.order_qty*d.unit_price) as totalAmount" + " from o_order o" + " join o_customer c on c.id = o.kcustomer_id " + " join o_order_detail d on d.order_id = o.id " + " group by order_id, o.status "; RawSql rawSql = RawSqlBuilder.parse(sql) // map the sql result columns to bean properties .columnMapping("order_id", "order.id").columnMapping("o.status", "order.status") .columnMapping("c.id", "order.customer.id") .columnMapping("c.name", "order.customer.name") // we don't need to map this one due to the sql column alias // .columnMapping("sum(d.order_qty*d.unit_price)", "totalAmount") .create(); Query<OrderAggregate> query = Ebean.find(OrderAggregate.class); query.setRawSql(rawSql).where().gt("order.id", 0).having().gt("totalAmount", 20); List<OrderAggregate> list = query.findList(); Example 2:
The following example uses a FetchConfig().query() so that after the initial RawSql query is executed Ebean executes a secondary query to fetch the associated order status, orderDate along with the customer name.
String sql = " select order_id, 'ignoreMe', sum(d.order_qty*d.unit_price) as totalAmount " + " from o_order_detail d" + " group by order_id "; RawSql rawSql = RawSqlBuilder.parse(sql).columnMapping("order_id", "order.id") .columnMappingIgnore("'ignoreMe'").create(); Query<OrderAggregate> query = Ebean.find(OrderAggregate.class); query.setRawSql(rawSql).fetch("order", "status,orderDate", new FetchConfig().query()) .fetch("order.customer", "name").where() .gt("order.id", 0).having().gt("totalAmount", 20).order().desc("totalAmount").setMaxRows(10); Note that lazy loading also works with object graphs built with RawSql.