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