Module io.ebean.api
Package io.ebean

Interface Query<T>

Type Parameters:
T - the type of Entity bean this query will fetch.
All Superinterfaces:
CancelableQuery, QueryBuilder<Query<T>,T>, QueryBuilderProjection<Query<T>,T>
All Known Subinterfaces:
SpiFetchGroupQuery<T>
@NonNullApi public interface Query<T> extends CancelableQuery, QueryBuilder<Query<T>,T>
Object relational query for finding a List, Set, Map or single entity bean.

Example: Create the query using the API. 



 List<Order> orderList = DB.find(Order.class)
     .where()
       .like("customer.name","rob%")
       .gt("orderDate",lastWeek)
     .order("customer.id, id desc")
     .setMaxRows(50)
     .findList();

Example: The same query using the query language 



 String oql =
   	+" where customer.name like :custName and orderDate > :minOrderDate "
   	+" order by customer.id, id desc "
   	+" limit 50 ";

 List<Order> orderList = DB.createQuery(Order.class, oql)
   .setParameter("custName", "Rob%")
   .setParameter("minOrderDate", lastWeek)
   .findList();
 ...

AutoTune

Ebean has built in support for "AutoTune". This is a mechanism where a query can be automatically tuned based on profiling information that is collected.

This is effectively the same as automatically using select() and fetch() to build a query that will fetch all the data required by the application and no more.

It is expected that AutoTune will be the default approach for many queries in a system. It is possibly not as useful where the result of a query is sent to a remote client or where there is some requirement for "Read Consistency" guarantees.

Query Language

Partial Objects

The find and fetch clauses support specifying a list of properties to fetch. This results in objects that are "partially populated". If you try to get a property that was not populated a "lazy loading" query will automatically fire and load the rest of the properties of the bean (This is very similar behaviour as a reference object being "lazy loaded").

Partial objects can be saved just like fully populated objects. If you do this you should remember to include the "Version" property in the initial fetch. If you do not include a version property then optimistic concurrency checking will occur but only include the fetched properties. Refer to "ALL Properties/Columns" mode of Optimistic Concurrency checking. 


 [ select [ ( * | {fetch properties} ) ] ]
 [ fetch {path} [ ( * | {fetch properties} ) ] ]
 [ where {predicates} ]
 [ order by {order by properties} ]
 [ limit {max rows} [ offset {first row} ] ]

SELECT [ ( * | {fetch properties} ) ]

With the select you can specify a list of properties to fetch.

FETCH {path} [ ( * | {fetch properties} ) ]

With the fetch you specify the associated property to fetch and populate. The path is a OneToOne, ManyToOne, OneToMany or ManyToMany property.

For fetch of a path we can optionally specify a list of properties to fetch. If you do not specify a list of properties ALL the properties for that bean type are fetched.

WHERE {list of predicates}

The list of predicates which are joined by AND OR NOT ( and ). They can include named (or positioned) bind parameters. These parameters will need to be bound by setParameter(String, Object).

ORDER BY {order by properties}

The list of properties to order the result. You can include ASC (ascending) and DESC (descending) in the order by clause.

LIMIT {max rows} [ OFFSET {first row} ]

The limit offset specifies the max rows and first row to fetch. The offset is optional.

Examples of Ebean's Query Language

Find orders fetching its id, shipDate and status properties. Note that the id property is always fetched even if it is not included in the list of fetch properties. 



 select (shipDate, status)

Find orders with a named bind variable (that will need to be bound via setParameter(String, Object)). 



 where customer.name like :custLike

Find orders and also fetch the customer with a named bind parameter. This will fetch and populate both the order and customer objects. 



 fetch customer
 where customer.id = :custId

Find orders and also fetch the customer, customer shippingAddress, order details and related product. Note that customer and product objects will be "Partial Objects" with only some of their properties populated. The customer objects will have their id, name and shipping address populated. The product objects (associated with each order detail) will have their id, sku and name populated. 



 fetch customer (name)
 fetch customer.shippingAddress
 fetch details
 fetch details.product (sku, name)

  • Method Details

    • getExpressionFactory

      ExpressionFactory getExpressionFactory()
      Return the ExpressionFactory used by this query.

    • isAutoTuned

      boolean isAutoTuned()
      Returns true if this query was tuned by autoTune.

    • isCountDistinct

      boolean isCountDistinct()
      Return true if this is countDistinct query.

    • delete

      @Deprecated(forRemoval=true, since="14.1.0") int delete(Transaction transaction)
      Deprecated, for removal: This API element is subject to removal in a future version.
      migrate to QueryBuilder.usingTransaction(Transaction) then delete().

      Execute as a delete query returning the number of rows deleted using the given transaction.

      Note that if the query includes joins then the generated delete statement may not be optimal depending on the database platform.

      Returns:
      the number of beans/rows that were deleted.

    • update

      @Deprecated(forRemoval=true, since="14.1.0") int update(Transaction transaction)
      Deprecated, for removal: This API element is subject to removal in a future version.
      migrate to QueryBuilder.usingTransaction(Transaction) then update().

      Execute the UpdateQuery returning the number of rows updated using the given transaction.

      Returns:
      the number of beans/rows updated.

    • update

      int update()
      Execute the UpdateQuery returning the number of rows updated.
      Returns:
      the number of beans/rows updated.

    • setParameter

      Query<T> setParameter(String name, Object value)
      Set a named bind parameter. Named parameters have a colon to prefix the name. 
      

 // a query with a named parameter
 String oql = "find order where status = :orderStatus";

 List<Order> list = DB.find(Order.class, oql)
   .setParameter("orderStatus", OrderStatus.NEW)
   .findList();
      Parameters:
      name - the parameter name
      value - the parameter value

    • setParameter

      Query<T> setParameter(int position, Object value)
      Set an ordered bind parameter according to its position. Note that the position starts at 1 to be consistent with JDBC PreparedStatement. You need to set a parameter value for each ? you have in the query. 
      

 // a query with a positioned parameter
 String oql = "where status = ? order by id desc";

 List<Order> list = DB.createQuery(Order.class, oql)
   .setParameter(1, OrderStatus.NEW)
   .findList();
      Parameters:
      position - the parameter bind position starting from 1 (not 0)
      value - the parameter bind value.

    • setParameter

      Query<T> setParameter(Object value)
      Bind the next positioned parameter. 
      

 // a query with a positioned parameters
 String oql = "where status = ? and name = ?";

 List<Order> list = DB.createQuery(Order.class, oql)
   .setParameter(OrderStatus.NEW)
   .setParameter("Rob")
   .findList();

    • setParameters

      Query<T> setParameters(Object... values)
      Bind all the positioned parameters.

      A convenience for multiple calls to setParameter(Object)

    • setId

      Query<T> setId(Object id)
      Set the Id value to query. This is used with findOne().

      You can use this to have further control over the query. For example adding fetch joins. 

      

 Order order = DB.find(Order.class)
     .setId(1)
     .fetch("details")
     .findOne();

 // the order details were eagerly fetched
 List<OrderDetail> details = order.getDetails();

    • getId

      Object getId()
      Return the Id value.

    • where

      Query<T> where(Expression expression)
      Add a single Expression to the where clause returning the query. 
      

 List<Order> newOrders = DB.find(Order.class)
 		.where().eq("status", Order.NEW)
 		.findList();
 ...

    • where

      ExpressionList<T> where()
      Add Expressions to the where clause with the ability to chain on the ExpressionList. You can use this for adding multiple expressions to the where clause. 
      

 List<Order> orders = DB.find(Order.class)
     .where()
       .eq("status", Order.NEW)
       .ilike("customer.name","rob%")
     .findList();
      Returns:
      The ExpressionList for adding expressions to.
      See Also:

    • filterMany

      ExpressionList<T> filterMany(String propertyName)
      This applies a filter on the 'many' property list rather than the root level objects.

      Typically, you will use this in a scenario where the cardinality is high on the 'many' property you wish to join to. Say you want to fetch customers and their associated orders... but instead of getting all the orders for each customer you only want to get the new orders they placed since last week. In this case you can use filterMany() to filter the orders. 

      

 List<Customer> list = DB.find(Customer.class)
     .fetch("orders")
     .where().ilike("name", "rob%")
     .filterMany("orders").eq("status", Order.Status.NEW).gt("orderDate", lastWeek)
     .findList();

      Please note you have to be careful that you add expressions to the correct expression list - as there is one for the 'root level' and one for each filterMany that you have.

      Parameters:
      propertyName - the name of the many property that you want to have a filter on.
      Returns:
      the expression list that you add filter expressions for the many to.

    • having

      ExpressionList<T> having()
      Add Expressions to the Having clause return the ExpressionList.

      Currently only beans based on raw sql will use the having clause.

      Note that this returns the ExpressionList (so you can add multiple expressions to the query in a fluent API way).

      Returns:
      The ExpressionList for adding more expressions to.
      See Also:

    • having

      Query<T> having(Expression addExpressionToHaving)
      Add an expression to the having clause returning the query.

      Currently only beans based on raw sql will use the having clause.

      This is similar to having() except it returns the query rather than the ExpressionList. This is useful when you want to further specify something on the query.

      Parameters:
      addExpressionToHaving - the expression to add to the having clause.
      Returns:
      the Query object

    • order

      @Deprecated(since="13.19", forRemoval=true) default Query<T> order(String orderByClause)
      Deprecated, for removal: This API element is subject to removal in a future version.
      migrate to orderBy().

    • order

      @Deprecated(since="13.19", forRemoval=true) default OrderBy<T> order()
      Deprecated, for removal: This API element is subject to removal in a future version.
      migrate to orderBy().

    • setOrder

      @Deprecated(since="13.19", forRemoval=true) default Query<T> setOrder(OrderBy<T> orderBy)
      Deprecated, for removal: This API element is subject to removal in a future version.
      migrate to QueryBuilder.setOrderBy(OrderBy).

    • orderBy

      OrderBy<T> orderBy()
      Return the OrderBy so that you can append an ascending or descending property to the order by clause.

      This will never return a null. If no order by clause exists then an 'empty' OrderBy object is returned.

      This is the same as order()

    • getFirstRow

      int getFirstRow()
      Return the first row value.

    • getMaxRows

      int getMaxRows()
      Return the max rows for this query.

    • isForUpdate

      boolean isForUpdate()
      Return true if this query has forUpdate set.

    • getForUpdateLockWait

      Query.LockWait getForUpdateLockWait()
      Return the "for update" wait mode to use.

    • getForUpdateLockType

      Query.LockType getForUpdateLockType()
      Return the lock type (strength) to use with "for update".

    • getQueryType

      QueryType getQueryType()
      Return the type of query being executed.

    • setProfileLocation

      Query<T> setProfileLocation(ProfileLocation profileLocation)
      Set the profile location of this query. This is used to relate query execution metrics back to a location like a specific line of code.