001package io.ebean;
002
003/**
004 * Used to build object graphs based on a raw SQL statement (rather than
005 * generated by Ebean).
006 * <p>
007 * If you don't want to build object graphs you can use {@link SqlQuery} instead
008 * which returns {@link SqlRow} objects rather than entity beans.
009 * <p>
010 * <b>Unparsed RawSql:</b>
011 * <p>
012 * When RawSql is created via {@link RawSqlBuilder#unparsed(String)} then Ebean can not
013 * modify the SQL at all. It can't add any extra expressions into the SQL.
014 * <p>
015 * <b>Parsed RawSql:</b>
016 * <p>
017 * When RawSql is created via {@link RawSqlBuilder#parse(String)} then Ebean will parse the
018 * SQL and find places in the SQL where it can add extra where expressions, add
019 * extra having expressions or replace the order by clause. If you want to
020 * explicitly tell Ebean where these insertion points are you can place special
021 * strings into your SQL ({@code ${where}} or {@code ${andWhere}} and {@code ${having}} or
022 * {@code ${andHaving})}.
023 * <p>
024 * If the SQL already includes a WHERE clause put in {@code ${andWhere}} in the location
025 * you want Ebean to add any extra where expressions. If the SQL doesn't have a
026 * WHERE clause put {@code ${where}} in instead. Similarly you can put in {@code ${having}} or
027 * {@code ${andHaving}} where you want Ebean put add extra having expressions.
028 * <p>
029 * <b>Aggregates:</b>
030 * <p>
031 * Often RawSql will be used with Aggregate functions (sum, avg, max etc). The
032 * follow example shows an example based on Total Order Amount -
033 * sum(d.order_qty*d.unit_price).
034 * </p>
035 * <p>
036 * We can use a OrderAggregate bean that has a &#064;Sql to indicate it is based
037 * on RawSql and not based on a real DB Table or DB View. It has some properties
038 * to hold the values for the aggregate functions (sum etc) and a &#064;OneToOne
039 * to Order.
040 * <p>
041 * <h3>Example OrderAggregate</h3>
042 * <pre>{@code
043 *  ...
044 *   // @Sql indicates to that this bean
045 *   // is based on RawSql rather than a table
046 *
047 *   @Entity
048 *   @Sql
049 *   public class OrderAggregate {
050 *
051 *    @OneToOne
052 *    Order order;
053 *
054 *    Double totalAmount;
055 *
056 *    Double totalItems;
057 *
058 *    // getters and setters
059 *    ...
060 *   }
061 * }</pre>
062 *
063 * <h3>Example 1:</h3>
064 *
065 * <pre>{@code
066 *
067 *   String sql = " select order_id, o.status, c.id, c.name, sum(l.order_qty*l.unit_price) as totalAmount"
068 *     + " from order o"
069 *     + " join customer c on c.id = o.customer_id "
070 *     + " join order_line l on l.order_id = o.id " + " group by order_id, o.status ";
071 *
072 *   RawSql rawSql = RawSqlBuilder.parse(sql)
073 *     // map the sql result columns to bean properties
074 *     .columnMapping("order_id", "order.id")
075 *     .columnMapping("o.status", "order.status")
076 *     .columnMapping("c.id", "order.customer.id")
077 *     .columnMapping("c.name", "order.customer.name")
078 *     // we don't need to map this one due to the sql column alias
079 *     // .columnMapping("sum(d.order_qty*d.unit_price)", "totalAmount")
080 *     .create();
081 *
082 *   List<OrderAggregate> list = DB.find(OrderAggregate.class)
083 *       .setRawSql(rawSql)
084 *       .where().gt("order.id", 0)
085 *       .having().gt("totalAmount", 20)
086 *       .findList();
087 *
088 *
089 * }</pre>
090 *
091 * <h3>Example 2:</h3>
092 * <p>
093 * The following example uses a FetchConfig().query() so that after the initial
094 * RawSql query is executed Ebean executes a secondary query to fetch the
095 * associated order status, orderDate along with the customer name.
096 *
097 * <pre>{@code
098 *
099 *  String sql = " select order_id, 'ignoreMe', sum(l.order_qty*l.unit_price) as totalAmount "
100 *     + " from order_line l"
101 *     + " group by order_id ";
102 *
103 *   RawSql rawSql = RawSqlBuilder.parse(sql)
104 *     .columnMapping("order_id", "order.id")
105 *     .columnMappingIgnore("'ignoreMe'")
106 *     .create();
107 *
108 *   List<OrderAggregate> orders = DB.find(OrderAggregate.class)
109 *     .setRawSql(rawSql)
110 *     .fetch("order", "status,orderDate", FetchConfig.ofQuery())
111 *     .fetch("order.customer", "name")
112 *     .where().gt("order.id", 0)
113 *     .having().gt("totalAmount", 20)
114 *     .order().desc("totalAmount")
115 *     .setMaxRows(10)
116 *     .findList();
117 *
118 * }</pre>
119 * <h3>Example 3: tableAliasMapping</h3>
120 * <p>
121 * Instead of mapping each column you can map each table alias to a path using tableAliasMapping().
122 * <pre>{@code
123 *
124 *   String rs = "select o.id, o.status, c.id, c.name, "+
125 *               " l.id, l.order_qty, p.id, p.name " +
126 *               "from orders o join o_customer c on c.id = o.customer_id " +
127 *               "join order_line l on l.order_id = o.id  " +
128 *               "join product p on p.id = l.product_id  " +
129 *               "where o.id <= :maxOrderId  and p.id = :productId "+
130 *               "order by o.id, l.id asc";
131 *
132 *  RawSql rawSql = RawSqlBuilder.parse(rs)
133 *       .tableAliasMapping("c", "customer")
134 *       .tableAliasMapping("l", "lines")
135 *       .tableAliasMapping("p", "lines.product")
136 *       .create();
137 *
138 *  List<Order> ordersFromRaw = DB.find(Order.class)
139 *       .setRawSql(rawSql)
140 *       .setParameter("maxOrderId", 2)
141 *       .setParameter("productId", 1)
142 *       .findList();
143 *
144 * }</pre>
145 * <p>
146 * Note that lazy loading also works with object graphs built with RawSql.
147 */
148public interface RawSql {
149
150}