com.avaje.ebean.annotation.SqlSelect Maven / Gradle / Ivy
package com.avaje.ebean.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import com.avaje.ebean.Query;
/**
* Specify an explicit sql select statement to use for querying an entity bean.
*
* The reason for using explicit sql is that you want better control over the
* exact sql or sql that Ebean does not generate for you (such as group by,
* union, intersection, window functions, recursive queries).
*
*
* An example of two sql select queries deployed on the ReportTopic entity bean.
* The first one has no name specified so it becomes the default query. The
* second query extends the first adding a where clause with a named parameter.
*
*
*
* ...
* @Entity
* @Sql(select = {
* @SqlSelect(query =
* "select t.id, t.title, count(p.id) as score "+
* "from f_topic t "+
* "join f_topic_post p on p.topic_id = t.id "+
* "group by t.id, t.title"),
* @SqlSelect(
* name = "with.title",
* extend = "default",
* debug = true,
* where = "title like :likeTitle")
* })
* public class ReportTopic
* @Id Integer id;
* String title;
* Double score;
* ...
*
*
*
* An example using the first "default" query.
*
*
*
*
* List<ReportTopic> list =
* Ebean.find(ReportTopic.class)
* .having().gt("score", 0)
* .findList();
*
*
*
*
* The resulting sql, note the having clause has been added.
*
*
*
* select t.id, t.title, count(p.id) as score
* from f_topic t join f_topic_post p on p.topic_id = t.id
* group by t.id, t.title
* having count(p.id) > ?
*
*
*
* An example using the second query. Note the named parameter "likeTitle" must
* be set.
*
*
*
* List<ReportTopic> list =
* Ebean.find(ReportTopic.class, "with.title")
* .set("likeTitle", "a%")
* .findList();
*
*
*
* Ebean tries to parse the sql in the query to determine 4 things
*
Location for inserting WHERE expressions (if required)
* Location for inserting HAVING expressions (if required)
* Mapping of columns to bean properties
* The order by clause
*
*
* If Ebean is unable to parse out this information (perhaps because the sql
* contains multiple select from keywords etc) then you need to manually specify
* it.
*
*
* Insert ${where} or ${andWhere} into the location where Ebean can insert any
* expressions added to the where clause. Use ${andWhere} if the sql already has
* the WHERE keyword and Ebean will instead start with a AND keyword.
*
*
* Insert ${having} or ${andHaving} into the location where Ebean can insert any
* expressions added to the having clause. Use ${andHaving} if the sql already
* has a HAVING keyword and Ebean will instead start with a AND keyword.
*
*
* Use the columnMapping property if Ebean is unable to determine the columns
* and map them to bean properties.
*
*
* Example with ${andWhere} & ${having}.
*
*
*
* @SqlSelect(
* name = "explicit.where",
* query =
* "select t.id, t.title, count(p.id) as score "+
* "from f_topic t, f_topic_post p "+
* "where p.topic_id = t.id ${andWhere} "+
* "group by t.id, t.title ${having}"),
*
*/
@Target({ ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
@Deprecated
public @interface SqlSelect {
/**
* The name of the query. If left blank this is assumed to be the default
* query for this bean type.
*
* This will default to "default" and in that case becomes the default query
* used for the bean.
*
*/
String name() default "default";
/**
* The tableAlias used when adding where expressions to the query.
*/
String tableAlias() default "";
/**
* The sql select statement.
*
* If this query extends another then this string is appended to the
* parent query string. Often when using extend you will leave the
* query part blank and just specify a where and/or having clauses.
*
*
* This sql CAN NOT contain named parameters. You have to put these
* in the separate where and/or having sections.
*
*
* Ebean automatically tries to determine the location in the sql string for
* putting in additional where or having clauses. If Ebean is unable to
* successfully determine this then you have to explicitly specify these
* locations by including
* ${where} or ${andWhere} and ${having} or ${andHaving} in the sql.
*
*
* ${where} location of where clause (and will add WHERE ... )
* Use this when there is no where clause in the sql. If expressions are added
* to the where clause Ebean will put them in at this location starting with
* the WHERE keyword.
*
*
* ${andWhere}
* Use this instead of ${where} if there IS an existing where clause in the
* sql. Ebean will add the expressions starting with the AND keyword.
*
* ${having} location of having clause (and will add HAVING... )
*
*
* ${andHaving}
* Use this instead of ${having} when there IS an existing HAVING clause.
* Ebean will add the expressions starting with the AND keyword.
*
*
* You can include one of ${where} OR ${andWhere} but not both.
*
*
* You can include one of ${having} OR ${andHaving} but not both.
*
*/
String query() default "";
/**
* Specify the name of a sql-select query that this one 'extends'.
*
* When a query is extended the sql query contents are appended together. The
* where and having clauses are NOT appended but overridden.
*
*/
String extend() default "";
/**
* Specify a where clause typically containing named parameters.
*
* If a where clause is specified with named parameters then they will need to
* be set on the query via {@link Query#setParameter(String, Object)}.
*
*
* In the example below the query specifies a where clause that includes a
* named parameter "likeTitle".
*
*
*
* ...
* @Entity
* @Sql(select = {
* ...
* @SqlSelect(
* name = "with.title",
* extend = "default",
* debug = true,
* where = "title like :likeTitle")
* })
* public class ReportTopic
* ...
*
*
*
* Example use of the above named query.
*
*
*
*
* Query<ReportTopic> query0 = Ebean.createQuery(ReportTopic.class, "with.title");
*
* query0.set("likeTitle", "Bana%");
*
* List<ReportTopic> list0 = query0.findList();
*
*
*/
String where() default "";
/**
* Specify a having clause typically containing named parameters.
*
* If a having clause is specified with named parameters then they will need
* to be set on the query via {@link Query#setParameter(String, Object)}.
*
*/
String having() default "";
/**
* (Optional) Explicitly specify column to property mapping.
*
* This is required when Ebean is unable to parse the sql. This could occur if
* the sql contains multiple select keywords etc.
*
*
* Specify the columns and property names they map to in the format.
*
*
*
* column1 propertyName1, column2 propertyName2, ...
*
*
*
* Optionally put a AS keyword between the column and property.
*
*
*
* // the AS keyword is optional
* column1 AS propertyName1, column2 propertyName2, ...
*
*
*
* column should contain the table alias if there is one
*
*
* propertyName should match the property name.
*
*
*
* Example mapping 5 columns to properties.
*
*
*
* columnMapping="t.id, t.bug_body description, t.bug_title as title, count(p.id) as scoreValue",
*
*
*
* Without this set Ebean will parse the sql looking for the select clause and
* try to map the columns to property names. It is expected that Ebean will
* not be able to successfully parse some sql and for those cases you should
* specify the column to property mapping explicitly.
*
*
*/
String columnMapping() default "";
/**
* Set this to true to have debug output when Ebean parses the sql-select.
*/
boolean debug() default false;
}