com.avaje.ebean.RawSql Maven / Gradle / Ivy
The newest version!
package com.avaje.ebean;
import java.io.Serializable;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import com.avaje.ebean.util.CamelCaseHelper;
/**
* Used to build object graphs based on a raw SQL statement (rather than
* generated by Ebean).
*
* If you don't want to build object graphs you can use {@link SqlQuery} instead
* which returns {@link SqlRow} objects rather than entity beans.
*
*
* Unparsed RawSql:
*
*
* When RawSql is created via RawSqlBuilder.unparsed(sql) then Ebean can not
* modify the SQL at all. It can't add any extra expressions into the SQL.
*
*
* Parsed RawSql:
*
*
* When RawSql is created via RawSqlBuilder.parse(sql) then Ebean will parse the
* SQL and find places in the SQL where it can add extra where expressions, add
* extra having expressions or replace the order by clause. If you want to
* explicitly tell Ebean where these insertion points are you can place special
* strings into your SQL (${where} or ${andWhere} and ${having} or
* ${andHaving}).
*
*
* If the SQL already includes a WHERE clause put in ${andWhere} in the location
* you want Ebean to add any extra where expressions. If the SQL doesn't have a
* WHERE clause put ${where} in instead. Similarly you can put in ${having} or
* ${andHaving} where you want Ebean put add extra having expressions.
*
*
* Aggregates:
*
*
* Often RawSql will be used with Aggregate functions (sum, avg, max etc). The
* follow example shows an example based on Total Order Amount -
* sum(d.order_qty*d.unit_price).
*
*
* We can use a OrderAggregate bean that has a @Sql to indicate it is based
* on RawSql and not based on a real DB Table or DB View. It has some properties
* to hold the values for the aggregate functions (sum etc) and a @OneToOne
* to Order.
*
*
*
*
*
* Example OrderAggregate
*
*
*
* ...
* // @Sql indicates to that this bean
* // is based on RawSql rather than a table
*
* @Entity
* @Sql
* public class OrderAggregate {
*
* @OneToOne
* Order order;
*
* Double totalAmount;
*
* Double totalItems;
*
* // getters and setters
* ...
*
*
* Example 1:
*
*
*
* String sql = " select order_id, o.status, c.id, c.name, sum(d.order_qty*d.unit_price) as totalAmount"
* + " from o_order o"
* + " join o_customer c on c.id = o.kcustomer_id "
* + " join o_order_detail d on d.order_id = o.id " + " group by order_id, o.status ";
*
* RawSql rawSql = RawSqlBuilder.parse(sql)
* // map the sql result 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")
* // we don't need to map this one due to the sql column alias
* // .columnMapping("sum(d.order_qty*d.unit_price)", "totalAmount")
* .create();
*
* Query<OrderAggregate> query = Ebean.find(OrderAggregate.class);
* query.setRawSql(rawSql).where().gt("order.id", 0).having().gt("totalAmount", 20);
*
* List<OrderAggregate> list = query.findList();
*
*
*
* Example 2:
*
*
*
* The following example uses a FetchConfig().query() so that after the initial
* RawSql query is executed Ebean executes a secondary query to fetch the
* associated order status, orderDate along with the customer name.
*
*
*
* String sql = " select order_id, 'ignoreMe', sum(d.order_qty*d.unit_price) as totalAmount "
* + " from o_order_detail d"
* + " group by order_id ";
*
* RawSql rawSql = RawSqlBuilder.parse(sql).columnMapping("order_id", "order.id")
* .columnMappingIgnore("'ignoreMe'").create();
*
* Query<OrderAggregate> query = Ebean.find(OrderAggregate.class);
* query.setRawSql(rawSql).fetch("order", "status,orderDate", new FetchConfig().query())
* .fetch("order.customer", "name").where()
* .gt("order.id", 0).having().gt("totalAmount", 20).order().desc("totalAmount").setMaxRows(10);
*
*
*
*
* Note that lazy loading also works with object graphs built with RawSql.
*
*
* @author rbygrave
*
*/
public final class RawSql implements Serializable {
private static final long serialVersionUID = 1L;
private final Sql sql;
private final ColumnMapping columnMapping;
protected RawSql(Sql sql, ColumnMapping columnMapping) {
this.sql = sql;
this.columnMapping = columnMapping;
}
/**
* Return the Sql either unparsed or in parsed (broken up) form.
*/
public Sql getSql() {
return sql;
}
/**
* Return the column mapping for the SQL columns to bean properties.
*/
public ColumnMapping getColumnMapping() {
return columnMapping;
}
/**
* Return the hash for this query.
*/
public int queryHash() {
return 31 * sql.queryHash() + columnMapping.queryHash();
}
/**
* Represents the sql part of the query. For parsed RawSql the sql is broken
* up so that Ebean can insert extra WHERE and HAVING expressions into the
* SQL.
*/
public static final class Sql implements Serializable {
private static final long serialVersionUID = 1L;
private final boolean parsed;
private final String unparsedSql;
private final String preFrom;
private final String preWhere;
private final boolean andWhereExpr;
private final String preHaving;
private final boolean andHavingExpr;
private final String orderBy;
private final boolean distinct;
private final int queryHashCode;
/**
* Construct for unparsed SQL.
*/
protected Sql(String unparsedSql) {
this.queryHashCode = unparsedSql.hashCode();
this.parsed = false;
this.unparsedSql = unparsedSql;
this.preFrom = null;
this.preHaving = null;
this.preWhere = null;
this.andHavingExpr = false;
this.andWhereExpr = false;
this.orderBy = null;
this.distinct = false;
}
/**
* Construct for parsed SQL.
*/
protected Sql(int queryHashCode, String preFrom, String preWhere, boolean andWhereExpr,
String preHaving, boolean andHavingExpr,
String orderBy, boolean distinct) {
this.queryHashCode = queryHashCode;
this.parsed = true;
this.unparsedSql = null;
this.preFrom = preFrom;
this.preHaving = preHaving;
this.preWhere = preWhere;
this.andHavingExpr = andHavingExpr;
this.andWhereExpr = andWhereExpr;
this.orderBy = orderBy;
this.distinct = distinct;
}
/**
* Return a hash for this query.
*/
public int queryHash() {
return queryHashCode;
}
public String toString() {
if (!parsed) {
return "unparsed[" + unparsedSql + "]";
}
return "select[" + preFrom + "] preWhere[" + preWhere + "] preHaving[" + preHaving
+ "] orderBy[" + orderBy + "]";
}
public boolean isDistinct() {
return distinct;
}
/**
* Return true if the SQL is left completely unmodified.
*
* This means Ebean can't add WHERE or HAVING expressions into the query -
* it will be left completely unmodified.
*
*/
public boolean isParsed() {
return parsed;
}
/**
* Return the SQL when it is unparsed.
*/
public String getUnparsedSql() {
return unparsedSql;
}
/**
* Return the SQL prior to FROM clause.
*/
public String getPreFrom() {
return preFrom;
}
/**
* Return the SQL prior to WHERE clause.
*/
public String getPreWhere() {
return preWhere;
}
/**
* Return true if there is already a WHERE clause and any extra where
* expressions start with AND.
*/
public boolean isAndWhereExpr() {
return andWhereExpr;
}
/**
* Return the SQL prior to HAVING clause.
*/
public String getPreHaving() {
return preHaving;
}
/**
* Return true if there is already a HAVING clause and any extra having
* expressions start with AND.
*/
public boolean isAndHavingExpr() {
return andHavingExpr;
}
/**
* Return the SQL ORDER BY clause.
*/
public String getOrderBy() {
return orderBy;
}
}
/**
* Defines the column mapping for raw sql DB columns to bean properties.
*/
public static final class ColumnMapping implements Serializable {
private static final long serialVersionUID = 1L;
private final LinkedHashMap dbColumnMap;
private final Map propertyMap;
private final Map propertyColumnMap;
private final boolean parsed;
private final boolean immutable;
private final int queryHashCode;
/**
* Construct from parsed sql where the columns have been identified.
*/
protected ColumnMapping(List columns) {
this.queryHashCode = 0;
this.immutable = false;
this.parsed = true;
this.propertyMap = null;
this.propertyColumnMap = null;
this.dbColumnMap = new LinkedHashMap();
for (int i = 0; i < columns.size(); i++) {
Column c = columns.get(i);
dbColumnMap.put(c.getDbColumn(), c);
}
}
/**
* Construct for unparsed sql.
*/
protected ColumnMapping() {
this.queryHashCode = 0;
this.immutable = false;
this.parsed = false;
this.propertyMap = null;
this.propertyColumnMap = null;
this.dbColumnMap = new LinkedHashMap();
}
/**
* Construct an immutable ColumnMapping based on collected information.
*/
protected ColumnMapping(boolean parsed, LinkedHashMap dbColumnMap) {
this.immutable = true;
this.parsed = parsed;
this.dbColumnMap = dbColumnMap;
int hc = ColumnMapping.class.getName().hashCode();
HashMap pcMap = new HashMap();
HashMap pMap = new HashMap();
for (Column c : dbColumnMap.values()) {
pMap.put(c.getPropertyName(), c.getDbColumn());
pcMap.put(c.getPropertyName(), c);
hc = 31 * hc + c.getPropertyName() == null ? 0 : c.getPropertyName().hashCode();
hc = 31 * hc + c.getDbColumn() == null ? 0 : c.getDbColumn().hashCode();
}
this.propertyMap = Collections.unmodifiableMap(pMap);
this.propertyColumnMap = Collections.unmodifiableMap(pcMap);
this.queryHashCode = hc;
}
/**
* Creates an immutable copy of this ColumnMapping.
*
* @throws IllegalStateException
* when a propertyName has not been defined for a column.
*/
protected ColumnMapping createImmutableCopy() {
for (Column c : dbColumnMap.values()) {
c.checkMapping();
}
return new ColumnMapping(parsed, dbColumnMap);
}
protected void columnMapping(String dbColumn, String propertyName) {
if (immutable) {
throw new IllegalStateException("Should never happen");
}
if (!parsed) {
int pos = dbColumnMap.size();
dbColumnMap.put(dbColumn, new Column(pos, dbColumn, null, propertyName));
} else {
Column column = dbColumnMap.get(dbColumn);
if (column == null) {
String msg = "DB Column [" + dbColumn + "] not found in mapping. Expecting one of ["
+ dbColumnMap.keySet() + "]";
throw new IllegalArgumentException(msg);
}
column.setPropertyName(propertyName);
}
}
/**
* Return the query hash for this column mapping.
*/
public int queryHash() {
if (queryHashCode == 0) {
throw new RuntimeException("Bug: queryHashCode == 0");
}
return queryHashCode;
}
/**
* Returns true if the Columns where supplied by parsing the sql select
* clause.
*
* In the case where the columns where parsed then we can do extra checks on
* the column mapping such as, is the column a valid one in the sql and
* whether all the columns in the sql have been mapped.
*
*/
public boolean isParsed() {
return parsed;
}
/**
* Return the number of columns in this column mapping.
*/
public int size() {
return dbColumnMap.size();
}
/**
* Return the column mapping.
*/
protected Map mapping() {
return dbColumnMap;
}
/**
* Return the mapping by DB column.
*/
public Map getMapping() {
return propertyMap;
}
/**
* Return the index position by bean property name.
*/
public int getIndexPosition(String property) {
Column c = propertyColumnMap.get(property);
return c == null ? -1 : c.getIndexPos();
}
/**
* Return an iterator of the Columns.
*/
public Iterator getColumns() {
return dbColumnMap.values().iterator();
}
/**
* A Column of the RawSql that is mapped to a bean property (or ignored).
*/
public static class Column implements Serializable {
private static final long serialVersionUID = 1L;
private final int indexPos;
private final String dbColumn;
private final String dbAlias;
private String propertyName;
/**
* Construct a Column.
*/
public Column(int indexPos, String dbColumn, String dbAlias) {
this(indexPos, dbColumn, dbAlias, derivePropertyName(dbAlias, dbColumn));
}
private Column(int indexPos, String dbColumn, String dbAlias, String propertyName) {
this.indexPos = indexPos;
this.dbColumn = dbColumn;
this.dbAlias = dbAlias;
if (propertyName == null && dbAlias != null) {
this.propertyName = dbAlias;
} else {
this.propertyName = propertyName;
}
}
private static String derivePropertyName(String dbAlias, String dbColumn) {
if (dbAlias != null) {
return dbAlias;
}
int dotPos = dbColumn.indexOf('.');
if (dotPos > -1) {
dbColumn = dbColumn.substring(dotPos + 1);
}
return CamelCaseHelper.toCamelFromUnderscore(dbColumn);
}
private void checkMapping() {
if (propertyName == null) {
String msg = "No propertyName defined (Column mapping) for dbColumn [" + dbColumn + "]";
throw new IllegalStateException(msg);
}
}
public String toString() {
return dbColumn + "->" + propertyName;
}
/**
* Return the index position of this column.
*/
public int getIndexPos() {
return indexPos;
}
/**
* Return the DB column name including table alias (if it has one).
*/
public String getDbColumn() {
return dbColumn;
}
/**
* Return the DB column alias (if it has one).
*/
public String getDbAlias() {
return dbAlias;
}
/**
* Return the bean property this column is mapped to.
*/
public String getPropertyName() {
return propertyName;
}
private void setPropertyName(String propertyName) {
this.propertyName = propertyName;
}
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy