Java Persistence/Ebean/Aggregation - Group By/SQL

From Wikibooks, open books for an open world
Jump to navigation Jump to search

Using raw SQL with Ebean.createSqlQuery(sql) and SqlRow

// using raw sql
String sql = "select order_id, "
           +"        sum(order_qty*unit_price) as total_amount "
           +" from o_order_detail "
           +" where order_qty > :minQty "
           +" group by order_id";

List<SqlRow> sqlRows = 
    Ebean.createSqlQuery(sql)
        .setParameter("minQty",1)
        .findList();

for (SqlRow sqlRow : sqlRows) {
    Integer id = sqlRow.getInteger("order_id");
    Double amount = sqlRow.getDouble("total_amount");

    System.out.println("orderId:" + id + " amt:" + amount);
}