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