com.avaje.ebean.annotation
Annotation Type SqlSelect

Deprecated.

@Target(value=TYPE)
@Retention(value=RUNTIME)
@Deprecated
public @interface SqlSelect

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}"),
 

Optional Element Summary

String	columnMapping Deprecated. (Optional) Explicitly specify column to property mapping.
boolean	debug Deprecated. Set this to true to have debug output when Ebean parses the sql-select.
String	extend Deprecated. Specify the name of a sql-select query that this one 'extends'.
String	having Deprecated. Specify a having clause typically containing named parameters.
String	name Deprecated. The name of the query.
String	query Deprecated. The sql select statement.
String	tableAlias Deprecated. The tableAlias used when adding where expressions to the query.
String	where Deprecated. Specify a where clause typically containing named parameters.

name

public abstract String name

Deprecated.

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.

Default:

"default"

tableAlias

public abstract String tableAlias

Deprecated.

The tableAlias used when adding where expressions to the query.

Default:

""

query

public abstract String query

Deprecated.

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.

Default:

""

extend

public abstract String extend

Deprecated.

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.

Default:

""

where

public abstract String where

Deprecated.

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 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();
 

Default:

""

having

public abstract String having

Deprecated.

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 Query.setParameter(String, Object).

Default:

""

columnMapping

public abstract String columnMapping

Deprecated.

(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.

Default:

""

debug

public abstract boolean debug

Deprecated.

Set this to true to have debug output when Ebean parses the sql-select.

Default:

false