Module io.ebean.api
Package io.ebean

Interface QueryBuilder<SELF,T>

Type Parameters:
SELF - The type of the builder
T - The entity bean type
All Superinterfaces:
QueryBuilderProjection<SELF,T>
All Known Subinterfaces:
Query<T>, SpiFetchGroupQuery<T>
public interface QueryBuilder<SELF,T> extends QueryBuilderProjection<SELF,T>
Build and execute an ORM query.

  • Method Details

    • alias

      SELF alias(String alias)
      Set root table alias.

    • alsoIf

      SELF alsoIf(BooleanSupplier predicate, Consumer<SELF> apply)
      Apply changes to the query conditional on the supplied predicate.

      Typically, the changes are extra predicates etc.

      Parameters:
      predicate - The predicate which when true the changes are applied
      apply - The changes to apply to the query

    • asOf

      SELF asOf(Timestamp asOf)
      Perform an 'As of' query using history tables to return the object graph as of a time in the past.

      To perform this query the DB must have underlying history tables.

      Parameters:
      asOf - the date time in the past at which you want to view the data

    • asDto

      <D> DtoQuery<D> asDto(Class<D> dtoClass)
      Convert the query to a DTO bean query.

      We effectively use the underlying ORM query to build the SQL and then execute and map it into DTO beans.

    • asUpdate

      UpdateQuery<T> asUpdate()
      Convert the query to a UpdateQuery.

      Typically this is used with query beans to covert a query bean query into an UpdateQuery like the examples below. 

      

  int rowsUpdated = new QCustomer()
       .name.startsWith("Rob")
       .asUpdate()
       .set("active", false)
       .update();;

 
      

   int rowsUpdated = new QContact()
       .notes.note.startsWith("Make Inactive")
       .email.endsWith("@foo.com")
       .customer.id.equalTo(42)
       .asUpdate()
       .set("inactive", true)
       .setRaw("email = lower(email)")
       .update();

    • copy

      SELF copy()
      Return a copy of the query.

      This is so that you can use a Query as a "prototype" for creating other query instances. You could create a Query with various where expressions and use that as a "prototype" - using this copy() method to create a new instance that you can then add other expressions then execute.

    • usingTransaction

      SELF usingTransaction(Transaction transaction)
      Execute the query using the given transaction.

    • usingConnection

      SELF usingConnection(Connection connection)
      Execute the query using the given connection.

    • usingDatabase

      SELF usingDatabase(Database database)
      Execute the query using the given database.

    • usingMaster

      SELF usingMaster()
      Ensure that the master DataSource is used if there is a read only data source being used (that is using a read replica database potentially with replication lag).

      When the database is configured with a read-only DataSource via say DatabaseConfig.setReadOnlyDataSource(DataSource) then by default when a query is run without an active transaction, it uses the read-only data source. We we use usingMaster() to instead ensure that the query is executed against the master data source.

    • setBaseTable

      SELF setBaseTable(String baseTable)
      Set the base table to use for this query.

      Typically this is used when a table has partitioning and we wish to specify a specific partition/table to query against. 

      

   QOrder()
   .setBaseTable("order_2019_05")
   .status.equalTo(Status.NEW)
   .findList();

    • setPersistenceContextScope

      SELF setPersistenceContextScope(PersistenceContextScope scope)
      Specify the PersistenceContextScope to use for this query.

      When this is not set the 'default' configured on DatabaseConfig.setPersistenceContextScope(PersistenceContextScope) is used - this value defaults to PersistenceContextScope.TRANSACTION.

      Note that the same persistence Context is used for subsequent lazy loading and query join queries.

      Note that #findEach uses a 'per object graph' PersistenceContext so this scope is ignored for queries executed as #findIterate, #findEach, #findEachWhile.

      Parameters:
      scope - The scope to use for this query and subsequent lazy loading.

    • setAutoTune

      SELF setAutoTune(boolean autoTune)
      Explicitly specify whether to use AutoTune for this query.

      If you do not call this method on a query the "Implicit AutoTune mode" is used to determine if AutoTune should be used for a given query.

      AutoTune can add additional fetch paths to the query and specify which properties are included for each path. If you have explicitly defined some fetch paths AutoTune will not remove them.

    • setAllowLoadErrors

      SELF setAllowLoadErrors()
      Execute the query allowing properties with invalid JSON to be collected and not fail the query. 
      

   // fetch a bean with JSON content
   EBasicJsonList bean= DB.find(EBasicJsonList.class)
       .setId(42)
       .setAllowLoadErrors()  // collect errors into bean state if we have invalid JSON
       .findOne();


   // get the invalid JSON errors from the bean state
   Map<String, Exception> errors = server().getBeanState(bean).getLoadErrors();

   // If this map is not empty tell we have invalid JSON
   // and should try and fix the JSON content or inform the user

    • setLazyLoadBatchSize

      SELF setLazyLoadBatchSize(int lazyLoadBatchSize)
      Set the default lazy loading batch size to use.

      When lazy loading is invoked on beans loaded by this query then this sets the batch size used to load those beans.

      Parameters:
      lazyLoadBatchSize - the number of beans to lazy load in a single batch

    • setLabel

      SELF setLabel(String label)
      Set a label on the query.

      This label can be used to help identify query performance metrics but we can also use profile location enhancement on Finders so for some that would be a better option.

    • setHint

      SELF setHint(String hint)
      Set a SQL query hint.

      This results in an inline comment that immediately follows after the select keyword in the form: /*+ hint *\/

    • setIncludeSoftDeletes

      SELF setIncludeSoftDeletes()
      Execute the query including soft deleted rows.

      This means that Ebean will not add any predicates to the query for filtering out soft deleted rows. You can still add your own predicates for the deleted properties and effectively you have full control over the query to include or exclude soft deleted rows as needed for a given use case.

    • setDisableLazyLoading

      SELF setDisableLazyLoading(boolean disableLazyLoading)
      Set true if you want to disable lazy loading.

      That is, once the object graph is returned further lazy loading is disabled.

    • setDistinct

      SELF setDistinct(boolean distinct)
      Set whether this query uses DISTINCT.

    • setFirstRow

      SELF setFirstRow(int firstRow)
      Set the first row to return for this query.
      Parameters:
      firstRow - the first row to include in the query result.

    • setMaxRows

      SELF setMaxRows(int maxRows)
      Set the maximum number of rows to return in the query.
      Parameters:
      maxRows - the maximum number of rows to return in the query.

    • setRawSql

      SELF setRawSql(RawSql rawSql)
      Set RawSql to use for this query.

    • setCountDistinct

      SELF setCountDistinct(CountDistinctOrder orderBy)
      Extended version for setDistinct in conjunction with "findSingleAttributeList"; 
      

  List<CountedValue<Order.Status>> orderStatusCount =

     DB.find(Order.class)
      .select("status")
      .where()
      .gt("orderDate", LocalDate.now().minusMonths(3))

      // fetch as single attribute with a COUNT
      .setCountDistinct(CountDistinctOrder.COUNT_DESC_ATTR_ASC)
      .findSingleAttributeList();

     for (CountedValue<Order.Status> entry : orderStatusCount) {
       System.out.println(" count:" + entry.getCount()+" orderStatus:" + entry.getValue() );
     }

   // produces

   count:3 orderStatus:NEW
   count:1 orderStatus:SHIPPED
   count:1 orderStatus:COMPLETE

    • setMapKey

      SELF setMapKey(String mapKey)
      Set the property to use as keys for a map.

      If no property is set then the id property is used. 

      

 // Assuming sku is unique for products...

 Map<String,Product> productMap = DB.find(Product.class)
     .setMapKey("sku")  // sku map keys...
     .findMap();
      Parameters:
      mapKey - the property to use as keys for a map.

    • setReadOnly

      SELF setReadOnly(boolean readOnly)
      When set to true when you want the returned beans to be read only.

    • setTimeout

      SELF setTimeout(int secs)
      Set a timeout on this query.

      This will typically result in a call to setQueryTimeout() on a preparedStatement. If the timeout occurs an exception will be thrown - this will be a SQLException wrapped up in a PersistenceException.

      Parameters:
      secs - the query timeout limit in seconds. Zero means there is no limit.

    • setBufferFetchSizeHint

      SELF setBufferFetchSizeHint(int fetchSize)
      A hint which for JDBC translates to the Statement.fetchSize().

      Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet.

      Note that internally findEach and findEachWhile will set the fetch size if it has not already as these queries expect to process a lot of rows. If we didn't then Postgres and MySql for example would eagerly pull back all the row data and potentially consume a lot of memory in the process.

      As findEach and findEachWhile automatically set the fetch size we don't have to do so generally but we might still wish to for tuning a specific use case.

    • setBeanCacheMode

      SELF setBeanCacheMode(CacheMode beanCacheMode)
      Set the mode to use the bean cache when executing this query.

      By default, "find by id" and "find by natural key" will use the bean cache when bean caching is enabled. Setting this to false means that the query will not use the bean cache and instead hit the database.

      By default, findList() with natural keys will not use the bean cache. In that case we need to explicitly use the bean cache.

    • setUseQueryCache

      SELF setUseQueryCache(CacheMode cacheMode)
      Set the CacheMode to use the query for executing this query.

    • setUseCache

      default SELF setUseCache(boolean useCache)
      Set this to false to not use the bean cache.

      This method is now superseded by setBeanCacheMode(CacheMode) which provides more explicit options controlled bean cache use.

      This method is likely to be deprecated in the future with migration over to setUseBeanCache().

    • setUseQueryCache

      default SELF setUseQueryCache(boolean enabled)
      Calls setUseQueryCache(CacheMode) with ON or OFF.

    • orderBy

      SELF orderBy(String orderByClause)
      Set the order by clause replacing the existing order by clause if there is one.

      This follows SQL syntax using commas between each property with the optional asc and desc keywords representing ascending and descending order respectively.

    • setOrderBy

      SELF setOrderBy(OrderBy<T> orderBy)
      Set an OrderBy object to replace any existing OrderBy clause.

    • orderById

      SELF orderById(boolean orderById)
      Controls, if paginated queries should always append an 'order by id' statement at the end to guarantee a deterministic sort result. This may affect performance. If this is not enabled, and an orderBy is set on the query, it's up to the programmer that this query provides a deterministic result.

    • withLock

      SELF withLock(Query.LockType lockType)
      Execute the query with the given lock type and WAIT.

      Note that forUpdate() is the same as withLock(LockType.UPDATE).

      Provides us with the ability to explicitly use Postgres SHARE, KEY SHARE, NO KEY UPDATE and UPDATE row locks.

    • withLock

      SELF withLock(Query.LockType lockType, Query.LockWait lockWait)
      Execute the query with the given lock type and lock wait.

      Note that forUpdateNoWait() is the same as withLock(LockType.UPDATE, LockWait.NOWAIT).

      Provides us with the ability to explicitly use Postgres SHARE, KEY SHARE, NO KEY UPDATE and UPDATE row locks.

    • forUpdate

      SELF forUpdate()
      Execute using "for update" clause which results in the DB locking the record.

      The same as withLock(LockType.UPDATE, LockWait.WAIT).

    • forUpdateNoWait

      SELF forUpdateNoWait()
      Execute using "for update" clause with "no wait" option.

      This is typically a Postgres and Oracle only option at this stage.

      The same as withLock(LockType.UPDATE, LockWait.NOWAIT).

    • forUpdateSkipLocked

      SELF forUpdateSkipLocked()
      Execute using "for update" clause with "skip locked" option.

      This is typically a Postgres and Oracle only option at this stage.

      The same as withLock(LockType.UPDATE, LockWait.SKIPLOCKED).

    • validate

      Set<String> validate()
      Returns the set of properties or paths that are unknown (do not map to known properties or paths).

      Validate the query checking the where and orderBy expression paths to confirm if they represent valid properties or paths for the given bean type.

    • getGeneratedSql

      String getGeneratedSql()
      Return the sql that was generated for executing this query.

      This is only available after the query has been executed and provided only for informational purposes.

    • getBeanType

      Class<T> getBeanType()
      Return the type of beans being queried.

    • delete

      int delete()
      Execute as a delete query deleting the 'root level' beans that match the predicates in the query.

      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.

    • exists

      boolean exists()
      Execute the query returning true if a row is found.

      The query is executed using max rows of 1 and will only select the id property. This method is really just a convenient way to optimise a query to perform a 'does a row exist in the db' check.

      Example using a query bean:

      

   boolean userExists =
     new QContact()
       .email.equalTo("rob@foo.com")
       .exists();

      Example:

      

   boolean userExists = query()
     .where().eq("email", "rob@foo.com")
     .exists();
      Returns:
      True if the query finds a matching row in the database

    • findOne

      @Nullable T findOne()
      Execute the query returning either a single bean or null (if no matching bean is found).

      If more than 1 row is found for this query then a PersistenceException is thrown.

      This is useful when your predicates dictate that your query should only return 0 or 1 results. 

      

 // assuming the sku of products is unique...
 Product product =
     new QProduct()
         .sku.equalTo("aa113")
         .findOne();
 ...

      It is also useful with finding objects by their id when you want to specify further join information to optimise the query. 

      

 // Fetch order 42 and additionally fetch join its order details...
 Order order =
     new QOrder()
         .fetch("details") // eagerly load the order details
         .id.equalTo(42)
         .findOne();

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

    • findOneOrEmpty

      Optional<T> findOneOrEmpty()
      Execute the query returning an optional bean.

    • findList

      List<T> findList()
      Execute the query returning the list of objects.

      This query will execute against the EbeanServer that was used to create it. 

      

 List<Customer> customers =
     new QCustomer()
       .name.ilike("rob%")
       .findList();
      See Also:

    • findStream

      Stream<T> findStream()
      Execute the query returning the result as a Stream.

      Note that this can support very large queries iterating any number of results. To do so internally it can use multiple persistence contexts. 

      

  // use try with resources to ensure Stream is closed

  try (Stream<Customer> stream = query.findStream()) {
    stream
    .map(...)
    .collect(...);
  }

    • findSet

      Set<T> findSet()
      Execute the query returning the set of objects.

      This query will execute against the EbeanServer that was used to create it. 

      

 Set<Customer> customers =
     new QCustomer()
       .name.ilike("rob%")
       .findSet();
      See Also:

    • findIds

      <A> List<A> findIds()
      Execute the query returning the list of Id's.

      This query will execute against the EbeanServer that was used to create it.

      See Also:

    • findMap

      <K> Map<K,T> findMap()
      Execute the query returning a map of the objects.

      This query will execute against the EbeanServer that was used to create it.

      You can use setMapKey() or asMapKey() to specify the property to be used as keys on the map. If one is not specified then the id property is used. 

      

 Map<String, Product> map =
   new QProduct()
     .sku.asMapKey()
     .findMap();
      See Also:

    • findIterate

      QueryIterator<T> findIterate()
      Execute the query iterating over the results.

      Note that findIterate (and findEach and findEachWhile) uses a "per graph" persistence context scope and adjusts jdbc fetch buffer size for large queries. As such it is better to use findList for small queries.

      Remember that with QueryIterator you must call QueryIterator.close() when you have finished iterating the results (typically in a finally block).

      findEach() and findEachWhile() are preferred to findIterate() as they ensure the jdbc statement and resultSet are closed at the end of the iteration.

      This query will execute against the EbeanServer that was used to create it. 

      

  Query<Customer> query =
    new QCustomer()
     .status.equalTo(Customer.Status.NEW)
     .orderBy()
       id.asc()
     .query();

  try (QueryIterator<Customer> it = query.findIterate()) {
    while (it.hasNext()) {
      Customer customer = it.next();
      // do something with customer ...
    }
  }

    • findSingleAttributeList

      <A> List<A> findSingleAttributeList()
      Execute the query returning a list of values for a single property.

      Example

      

  List<String> names =
    new QCustomer()
      .setDistinct(true)
      .select(name)
      .findSingleAttributeList();
      Returns:
      the list of values for the selected property

    • findSingleAttribute

      @Nullable <A> A findSingleAttribute()
      Execute the query returning a single value or null for a single property.

      Example

      

  LocalDate maxDate =
    new QCustomer()
      .select("max(startDate)")
      .findSingleAttribute();
      Returns:
      a single value or null for the selected property

    • findSingleAttributeOrEmpty

      <A> Optional<A> findSingleAttributeOrEmpty()
      Execute the query returning a single optional attribute value.

      Example

      

  Optional<String> maybeName =
    new QCustomer()
      .select(name)
      .id.eq(42)
      .status.eq(NEW)
      .findSingleAttributeOrEmpty();
      Returns:
      an optional value for the selected property

    • findSingleAttributeSet

      <A> Set<A> findSingleAttributeSet()
      Execute the query returning a hashset of values for a single property.

    • findEach

      void findEach(Consumer<T> consumer)
      Execute the query processing the beans one at a time.

      This method is appropriate to process very large query results as the beans are consumed one at a time and do not need to be held in memory (unlike #findList #findSet etc)

      Note that internally Ebean can inform the JDBC driver that it is expecting larger resultSet and specifically for MySQL this hint is required to stop it's JDBC driver from buffering the entire resultSet. As such, for smaller resultSets findList() is generally preferable.

      Compared with #findEachWhile this will always process all the beans where as #findEachWhile provides a way to stop processing the query result early before all the beans have been read.

      This method is functionally equivalent to findIterate() but instead of using an iterator uses the Consumer interface which is better suited to use with closures. 

      

  new QCustomer()
     .status.equalTo(Status.NEW)
     .orderBy().id.asc()
     .findEach((Customer customer) -> {

       // do something with customer
       System.out.println("-- visit " + customer);
     });
      Parameters:
      consumer - the consumer used to process the queried beans.

    • findEach

      void findEach(int batch, Consumer<List<T>> consumer)
      Execute findEach streaming query batching the results for consuming.

      This query execution will stream the results and is suited to consuming large numbers of results from the database.

      Typically, we use this batch consumer when we want to do further processing on the beans and want to do that processing in batch form, for example - 100 at a time.

      Parameters:
      batch - The number of beans processed in the batch
      consumer - Process the batch of beans

    • findEachWhile

      void findEachWhile(Predicate<T> consumer)
      Execute the query using callbacks to a visitor to process the resulting beans one at a time.

      This method is functionally equivalent to findIterate() but instead of using an iterator uses the Predicate interface which is better suited to use with closures. 

      

  new QCustomer()
     .status.equalTo(Status.NEW)
     .orderBy().id.asc()
     .findEachWhile((Customer customer) -> {

       // do something with customer
       System.out.println("-- visit " + customer);

       // return true to continue processing or false to stop
       return (customer.getId() < 40);
     });
      Parameters:
      consumer - the consumer used to process the queried beans.

    • findVersions

      List<Version<T>> findVersions()
      Return versions of a @History entity bean.

      Generally this query is expected to be a find by id or unique predicates query. It will execute the query against the history returning the versions of the bean.

    • findVersionsBetween

      List<Version<T>> findVersionsBetween(Timestamp start, Timestamp end)
      Return versions of a @History entity bean between a start and end timestamp.

      Generally this query is expected to be a find by id or unique predicates query. It will execute the query against the history returning the versions of the bean.

    • findCount

      int findCount()
      Return the count of entities this query should return.

      This is the number of 'top level' or 'root level' entities.

    • findFutureCount

      FutureRowCount<T> findFutureCount()
      Execute find row count query in a background thread.

      This returns a Future object which can be used to cancel, check the execution status (isDone etc) and get the value (with or without a timeout).

      Returns:
      a Future object for the row count query

    • findFutureIds

      FutureIds<T> findFutureIds()
      Execute find Id's query in a background thread.

      This returns a Future object which can be used to cancel, check the execution status (isDone etc) and get the value (with or without a timeout).

      Returns:
      a Future object for the list of Id's

    • findFutureList

      FutureList<T> findFutureList()
      Execute find list query in a background thread.

      This query will execute in it's own PersistenceContext and using its own transaction. What that means is that it will not share any bean instances with other queries.

      Returns:
      a Future object for the list result of the query

    • findPagedList

      PagedList<T> findPagedList()
      Return a PagedList for this query using firstRow and maxRows.

      The benefit of using this over findList() is that it provides functionality to get the total row count etc.

      If maxRows is not set on the query prior to calling findPagedList() then a PersistenceException is thrown. 

      

  PagedList<Order> pagedList =
    new QOrder()
       .setFirstRow(50)
       .setMaxRows(20)
       .findPagedList();

       // fetch the total row count in the background
       pagedList.loadRowCount();

       List<Order> orders = pagedList.getList();
       int totalRowCount = pagedList.getTotalRowCount();
      Returns:
      The PagedList