findNative 优于 RawSql

请注意，findNative 优于 RawSql。findNative 使用列到属性的自动映射，使用起来更简单、更方便。

我们仅在遇到 findNative 的 限制 时才真正需要使用 RawSql。

RawSql - 未解析

使用 unparsed RawSql，我们可以明确指定要使用的 SQL，并明确将列映射到属性。SQL 可以包含带 ? 的定位参数或带 :foo 的命名参数。

RawSql 实例是不可变的，可以安全地用于多线程，并且可以声明为static final。

使用 unparsed RawSql，我们不能为 WHERE 和 HAVING 子句指定其他表达式，也不能设置 ORDER BY 和 LIMIT OFFSET 子句。我们只能使用 parsed RawSql 来执行这些操作。

示例 - 使用定位参数
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();
示例 - 使用命名参数
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 RawSql，我们可以动态地为 WHERE 和 HAVING 子句添加表达式，并设置 ORDER BY 和 LIMIT OFFSET 子句。

RawSql 实例是不可变的，可以安全地用于多线程，并且可以声明为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

使用 RawSql 时，我们可以使用 fetchQuery 来获取对象图的其他部分。也就是说，我们对“根”或“原始”查询使用 RawSql，并且可以使用 fetchQuery 来获取图的其他相关部分。 

// 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);

这是上面示例中使用的 OrderAggregate Bean。 

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;
  }
}

tableAliasMapping()

tableAliasMapping() 会根据查询结果中的路径自动将列映射到关联的对象。它使用查询别名来执行此操作。这是一个便捷方法，因此您不必单独映射每列。

因此，测试用例如下所示 

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

而不是像下面这样使用 columnMapping 

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

命名查询

我们可以像以下示例一样以编程方式使用原始 SQL，或者将原始 SQL 和列映射放入 ebean-orm.xml 文件中，并将其作为“命名查询”引用 - 请参阅 database.createNamedQuery()。