You can specify the exact SQL to use and have that mapped into Objects. You may want to do this so use aggregate functions like sum() max() etc or in cases where you just need exact control over the SQL.

You can programmatically use raw SQL like the following examples or put the Raw SQL and column mappings into ebean-orm.xml file and reference them as 'named queries' - see ebeanServer.createNamedQuery().

If you let Ebean 'parse' the raw SQL then Ebean can add expressions to the WHERE and HAVING clauses as well as set the ORDER BY and LIMIT OFFSET clauses.

        // Use raw SQL with an aggregate function

        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
                // let ebean parse the SQL so that it can
                // add expressions to the WHERE and HAVING 
                // clauses
                .parse(sql)
                // map resultSet 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")
                .create();

        
        Query<OrderAggregate> query = Ebean.find(OrderAggregate.class);
            query.setRawSql(rawSql)        
            // add expressions to the WHERE and HAVING clauses
            .where().gt("order.id", 0)
            .having().gt("totalAmount", 20);
        
        List<OrderAggregate> list = query.findList();

This example uses FetchConfig to fetch other parts of the object graph. After the raw SQL query is executed Ebean uses 'query joins' to fetch some order and customer properties.

        // You can also use FetchConfig to get Ebean to
        // fetch additional parts of the object graph
        // after the Raw SQL query is executed.

        String sql 
            = " select order_id, 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")
                .create();

        
        Query<OrderAggregate> query = Ebean.find(OrderAggregate.class);
        query.setRawSql(rawSql)       
            // get ebean to fetch parts of the order and customer 
            // after the raw SQL query is executed
            .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);

This is the OrderAggregate bean used in the examples above.

package com.avaje.tests.model.basic;

import javax.persistence.Entity;
import javax.persistence.OneToOne;

import com.avaje.ebean.annotation.Sql;

/**
 * An example of an Aggregate object.
 * 
 * Note the @Sql indicates to Ebean that this bean is not based on a table but
 * instead uses RawSql.
 * 
 */
@Entity
@Sql
public class OrderAggregate {

    @OneToOne
    Order order;

    Double totalAmount;

    Double totalItems;

    public String toString() {
        return order.getId() + " totalAmount:" + totalAmount + " totalItems:" + totalItems;
    }

    public Order getOrder() {
        return order;
    }

    public void setOrder(Order order) {
        this.order = order;
    }

    public Double getTotalAmount() {
        return totalAmount;
    }

    public void setTotalAmount(Double totalAmount) {
        this.totalAmount = totalAmount;
    }

    public Double getTotalItems() {
        return totalItems;
    }

    public void setTotalItems(Double totalItems) {
        this.totalItems = totalItems;
    }
}