Query RawSql

findNative preferred over RawSql

Note that findNative is preferred over RawSql. findNative uses automatic mapping of columns to properties and is nicer and simpler to use.

We really only need to use RawSql when we hit one of the limitations of findNative.

RawSql - unparsed

With unparsed RawSql we explicitly specify the SQL to use and explicitly map columns to properties. The SQL can contain positioned parameters with ? or named parameters like :foo.

The RawSql instance is immutable and safe for multi-threaded use and can be declared as a static final.

With unparsed RawSql we can NOT specify additional expressions to the WHERE and HAVING clauses as well as set the ORDER BY and LIMIT OFFSET clauses. We can only do that with parsed RawSql.

Example - using positioned parameters

static final RawSql rawSql =
  RawSqlBuilder
    .unparsed("select r.id, r.name from o_customer r where r.id >= ? and r.name like ?")
    .columnMapping("r.id", "id")
    .columnMapping("r.name", "name")
    .create();

...

List<Customer> list = DB.find(Customer.class)
  .setRawSql(rawSql)
  .setParameter(42)
  .setParameter("R%")
  .findList();

Example - using named parameters

static final RawSql rawSql =
  RawSqlBuilder
    .unparsed("select r.id, r.name from o_customer r where r.id >= :a and r.name like :b")
    .columnMapping("r.id", "id")
    .columnMapping("r.name", "name")
    .create();

...

List<Customer> list = DB.find(Customer.class)
  .setRawSql(rawSql)
  .setParameter("a", 42)
  .setParameter("b", "R%")
  .findList();

RawSql - parsed

With parsed RawSql we can dynamically add expressions to the WHERE and HAVING clauses as well as set the ORDER BY and LIMIT OFFSET clauses.

The RawSql instance is immutable and safe for multi-threaded use and can be declared as a static final.

// 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 orders o"
+ " join customer c on c.id = o.customer_id "
+ " join order_detail d on d.order_id = o.id "
+ " group by order_id, o.status ";

static final 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();

...

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

fetch

When using RawSql we can use fetchQuery to fetch other parts of the object graph. That is, we are using RawSql for the "root" or "origin" query and can use fetchQuery to fetch other related parts of the graph.

// 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 order_detail d"
+ " group by order_id ";

static final RawSql rawSql = RawSqlBuilder
  .parse(sql)
  .columnMapping("order_id", "order.id")
  .create();

...

Query<OrderAggregate> query = DB.find(OrderAggregate.class);
  query.setRawSql(rawSql)
    // get ebean to fetch parts of the order and customer
    // after the raw SQL query is executed
    .fetchQuery("order", "status,orderDate",new FetchConfig().query())
    .fetchQuery("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 jakarata.persistence.Entity;
import jakarata.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;
  }
}

tableAliasMapping()

tableAliasMapping() will automatically map columns from the query results to the associated object based on its path. It does this using the query alias. This is a convenience method so that you don't have to map each column individually.

So a test case looks like:

static final String rs =
  "select o.id, o.status, c.id, c.name, "+
  "       d.id, d.order_qty, p.id, p.name " +
  "from orders o join customer c on c.id = o.customer_id " +
  "join order_detail d on d.order_id = o.id " +
  "join product p on p.id = d.product_id " +
  "where o.id <= :maxOrderId and p.id = :productId "+
  "order by o.id, d.id asc";

static final RawSql rawSql = RawSqlBuilder.parse(rs)
    .tableAliasMapping("c", "customer")
    .tableAliasMapping("d", "details")
    .tableAliasMapping("p", "details.product")
    .create();

...

List<Order> ordersFromRaw = DB.find(Order.class)
    .setRawSql(rawSql)
    .setParameter("maxOrderId", 2)
    .setParameter("productId", 1)
    .findList();

Rather than using columnMapping like:

static final RawSql rawSql = RawSqlBuilder.parse(rs)
    .columnMapping("t0.id", "id")
    .columnMapping("t0.status", "status")
    .columnMapping("t1.id", "customer.id")
    .columnMapping("t1.name", "customer.name")
    .columnMapping("t2.id", "details.id")
    .columnMapping("t2.order_qty", "details.orderQty")
    .columnMapping("t3.id", "details.product.id")
    .columnMapping("t3.name", "details.product.name")
    .create();

Named queries

We 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 database.createNamedQuery().