Module storm.java
Package st.orm.template

Class QueryBuilder<T extends Data,R,ID>

java.lang.Object
st.orm.template.QueryBuilder<T,R,ID>
Type Parameters:
T - the type of the table being queried.
R - the type of the result.
ID - the type of the primary key.
Direct Known Subclasses:
QueryBuilderImpl
public abstract class QueryBuilder<T extends Data,R,ID> extends Object
QueryBuilder relies on preview features of the Java platform:
Programs can only use QueryBuilder when preview features are enabled.
Preview features may be removed in a future release, or upgraded to permanent features of the Java platform.
A fluent builder for constructing type-safe SELECT and DELETE queries using the entity graph and metamodel.

The QueryBuilder provides a composable, chainable API for building SQL queries without writing raw SQL. It supports joins, WHERE clauses with type-safe metamodel paths, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET, row locking (FOR SHARE/FOR UPDATE), and result retrieval as streams, lists, or single results.

Instances are obtained from an EntityRepository or ProjectionRepository via their select(), selectCount(), or delete() methods, or from a QueryTemplate via selectFrom() and deleteFrom().

Example: Select with type-safe WHERE clause


 List<User> users = userRepository
         .select()
         .where(User_.address.city.name, EQUALS, "Sunnyvale")
         .orderBy(User_.email)
         .limit(10)
         .getResultList();

Example: Delete with WHERE clause


 int deleted = userRepository
         .delete()
         .where(User_.email, IS_NULL)
         .executeUpdate();

Example: Join and subquery


 List<User> users = userRepository
         .select()
         .innerJoin(Order.class).on(User.class)
         .where(predicate -> predicate
             .where(User_.email, LIKE, "%@example.com")
             .and(predicate.where(Order_.total, GREATER_THAN, 100)))
         .getResultList();

Immutability

QueryBuilder is immutable: every builder method (such as where(), orderBy(), limit(), etc.) returns a new instance with the modification applied, leaving the original unchanged. If you call a builder method and ignore the return value, the change is silently lost.


 // WRONG - the where clause is lost because the return value is discarded:
 var builder = userRepository.select();
 builder.where(User_.email, LIKE, "%@example.com");  // returns a new builder, but it's ignored
 builder.getResultList();                     // executes without the WHERE clause

 // CORRECT - chain the calls or capture the returned builder:
 var results = userRepository.select()
         .where(User_.email, LIKE, "%@example.com")
         .getResultList();
See Also:

  • Constructor Details

    • QueryBuilder

      public QueryBuilder()

  • Method Details

    • typedId

      public abstract <X> QueryBuilder<T,R,X> typedId(Class<X> pkType)
      Returns a query builder whose primary key type is pkType, so the operations that take an id can be used.

      A builder that did not come from a typed entity lookup carries no primary key type. selectFrom(...) names the table but not its key, so the id is a wildcard and WhereBuilder.whereId(Object) has nothing to match against. Stating the key type resolves it:

      
 List<City> cities = orm.selectFrom(City.class)
         .typedId(Integer.class)
         .where(predicate -> predicate.whereId(List.of(1, 3, 5)))
         .getResultList();

      The type is checked against the model, so a type that is not the table's key fails here rather than when the query runs. This types the key, while narrow(Class) types the root; the two are independent, and neither is undone by a join.

      Type Parameters:
      X - the type of the primary key.
      Parameters:
      pkType - the primary key type.
      Returns:
      the typed query builder.
      Throws:
      PersistenceException - if the pk type is not valid.
      Since:
      1.14

    • narrow

      public abstract <X extends Data> QueryBuilder<X,R,ID> narrow(Class<X> rootType)
      Returns a query builder rooted at the specified type, narrowing a builder whose root was relaxed by a join.

      A join relaxes the root so that clauses may name any entity in the query. This narrows it again, which re-enables the operations that are defined relative to the root, such as fetch(Navigable[]) and getResultGroupedBy(TypedMetamodel).

      Type Parameters:
      X - the root table type.
      Parameters:
      rootType - the type this query is rooted at.
      Returns:
      the query builder, rooted at rootType.
      Throws:
      PersistenceException - if rootType is not the type this query selects from.
      Since:
      1.14

    • widen

      public abstract QueryBuilder<Data,R,ID> widen()
      Widens the query as a join does, without joining: from here on, every clause accepts paths from any entity in the query. Use it to reference an entity of the query's graph in short form on a query that joins nothing; resolution happens when the query is built, and a table the query does not contain, or contains more than once, fails with an error naming the candidates.

      Widening is always safe, so unlike narrow(Class) there is nothing to verify.

      Returns:
      the query builder, accepting paths from any entity in the query.
      Since:
      1.14

    • unsafe

      public abstract QueryBuilder<T,R,ID> unsafe()
      Returns a query builder that allows UPDATE and DELETE queries without a WHERE clause.

      By default, Storm rejects UPDATE and DELETE queries that lack a WHERE clause, throwing a PersistenceException. Call this method to disable that check when you intentionally want to affect all rows in the table.

      Since:
      1.2

    • distinct

      public abstract QueryBuilder<T,R,ID> distinct()
      Marks the current query as a distinct query.
      Returns:
      the query builder.

    • fetch

      @SafeVarargs public final QueryBuilder<T,R,ID> fetch(Navigable<T,? extends Data>... path)
      Resolves the references at the specified paths as part of this query.

      A Ref foreign key is selected as its foreign key column and resolved on demand, which costs a query per reference. A path named here is selected as the referenced table's columns instead, joined into the same statement, so the reference comes back already loaded: Ref.fetch() returns the record without querying and Ref.isLoaded() reports true.

      
 List<User> users = orm.entity(User.class)
     .select()
     .fetch(User_.city, User_.city.country)
     .getResultList();

 City city = users.getFirst().city().fetch();   // already loaded, no query

      The record type is unchanged: the field stays a Ref, so the same record can come from a query that resolves the reference and from one that does not. Reference identity and equality are unaffected, and Ref.unload() returns to a reference that carries the key alone.

      The plan is prefix-closed: naming User_.city.country resolves User_.city as well, since the city record is what holds the country reference. A reference is always a to-one foreign key, so resolving one widens the row without multiplying it, and a cycle stays bounded by the depth the path names.

      A nullable reference is joined with an outer join, so a row whose foreign key is null yields a null reference, matching a nullable entity foreign key. A path that crosses no reference is rejected: the target is already part of the entity graph and there is nothing to resolve.

      Parameters:
      path - the paths of the references to resolve.
      Returns:
      the query builder.
      Throws:
      PersistenceException - if no path is provided, if a path crosses no reference, or if this query does not select a record that can hold one.
      Since:
      1.13

    • fetch

      public abstract QueryBuilder<T,R,ID> fetch(List<? extends Navigable<T,? extends Data>> paths)
      Resolves the references at the specified paths as part of this query.

      A path a generated metamodel cannot express, a cycle deeper than the two hops it constructs in particular, is named with Metamodel.of(Class, String).

      Parameters:
      paths - the paths of the references to resolve.
      Returns:
      the query builder.
      Throws:
      PersistenceException - if no path is provided, if a path crosses no reference, or if this query does not select a record that can hold one.
      Since:
      1.13
      See Also:

    • crossJoin

      public abstract QueryBuilder<Data,R,ID> crossJoin(Class<? extends Data> relation)
      Adds a cross join to the query.
      Parameters:
      relation - the relation to join.
      Returns:
      the query builder.

    • innerJoin

      public abstract TypedJoinBuilder<T,R,ID> innerJoin(Class<? extends Data> relation)
      Adds an inner join to the query.
      Parameters:
      relation - the relation to join.
      Returns:
      the query builder.

    • leftJoin

      public abstract TypedJoinBuilder<T,R,ID> leftJoin(Class<? extends Data> relation)
      Adds a left join to the query.
      Parameters:
      relation - the relation to join.
      Returns:
      the query builder.

    • rightJoin

      public abstract TypedJoinBuilder<T,R,ID> rightJoin(Class<? extends Data> relation)
      Adds a right join to the query.
      Parameters:
      relation - the relation to join.
      Returns:
      the query builder.

    • join

      public abstract TypedJoinBuilder<T,R,ID> join(JoinType type, Class<? extends Data> relation, String alias)
      Adds a join of the specified type to the query.
      Parameters:
      type - the type of the join (e.g., INNER, LEFT, RIGHT).
      relation - the relation to join.
      alias - the alias to use for the joined relation.
      Returns:
      the query builder.

    • crossJoin

      public abstract QueryBuilder<Data,R,ID> crossJoin(StringTemplatePREVIEW template)
      Adds a cross join to the query.
      Parameters:
      template - the condition to join.
      Returns:
      the query builder.

    • innerJoin

      public abstract JoinBuilder<T,R,ID> innerJoin(StringTemplatePREVIEW template, String alias)
      Adds an inner join to the query.
      Parameters:
      template - the condition to join.
      alias - the alias to use for the joined relation.
      Returns:
      the query builder.

    • leftJoin

      public abstract JoinBuilder<T,R,ID> leftJoin(StringTemplatePREVIEW template, String alias)
      Adds a left join to the query.
      Parameters:
      template - the template to join.
      alias - the alias to use for the joined relation.
      Returns:
      the query builder.

    • rightJoin

      public abstract JoinBuilder<T,R,ID> rightJoin(StringTemplatePREVIEW template, String alias)
      Adds a right join to the query.
      Parameters:
      template - the template to join.
      alias - the alias to use for the joined relation.
      Returns:
      the query builder.

    • join

      public abstract JoinBuilder<T,R,ID> join(JoinType type, StringTemplatePREVIEW template, String alias)
      Adds a join of the specified type to the query using a template.
      Parameters:
      type - the join type.
      template - the template to join.
      alias - the alias to use for the joined relation.
      Returns:
      the query builder.

    • join

      public abstract JoinBuilder<T,R,ID> join(JoinType type, QueryBuilder<?,?,?> subquery, String alias)
      Adds a join of the specified type to the query using a subquery.
      Parameters:
      type - the join type.
      subquery - the subquery to join.
      alias - the alias to use for the joined relation.
      Returns:
      the query builder.

    • where

      public final QueryBuilder<T,R,ID> where(ID id)
      Adds a WHERE clause that matches the specified primary key of the table.
      Parameters:
      id - the id to match.
      Returns:
      the query builder.

    • where

      public final QueryBuilder<T,R,ID> where(Ref<T> ref)
      Adds a WHERE clause that matches the specified primary key of the table, expressed by a ref.
      Parameters:
      ref - the ref to match.
      Returns:
      the query builder.
      Since:
      1.3

    • where

      public final QueryBuilder<T,R,ID> where(T record)
      Adds a WHERE clause that matches the specified record.
      Parameters:
      record - the record to match.
      Returns:
      the query builder.

    • whereId

      public final QueryBuilder<T,R,ID> whereId(Iterable<? extends ID> it)
      Adds a WHERE clause that matches the specified primary keys of the table.
      Parameters:
      it - ids to match.
      Returns:
      the query builder.
      Since:
      1.2

    • whereRef

      public final QueryBuilder<T,R,ID> whereRef(Iterable<? extends Ref<T>> it)
      Adds a WHERE clause that matches the specified primary keys of the table, expressed by a ref.
      Parameters:
      it - refs to match.
      Returns:
      the query builder.
      Since:
      1.3

    • where

      public final <V extends Data> QueryBuilder<T,R,ID> where(Navigable<? extends T,V> path, V record)
      Adds a WHERE clause that matches the specified record. The record can represent any of the related tables in the table graph.
      Parameters:
      path - the path to the object in the table graph.
      record - the records to match.
      Returns:
      the predicate builder.

    • where

      public final <V extends Data> QueryBuilder<T,R,ID> where(Navigable<? extends T,V> path, Ref<V> ref)
      Adds a WHERE clause that matches the specified ref. The ref can represent any of the related tables in the table graph.
      Parameters:
      path - the path to the object in the table graph.
      ref - the ref to match.
      Returns:
      the predicate builder.
      Since:
      1.3

    • where

      public final <V extends Data> QueryBuilder<T,R,ID> where(Navigable<? extends T,V> path, Iterable<V> it)
      Adds a WHERE clause that matches the specified records. The records can represent any of the related tables in the table graph.
      Parameters:
      path - the path to the object in the table graph.
      it - the records to match.
      Returns:
      the predicate builder.

    • whereRef

      public final <V extends Data> QueryBuilder<T,R,ID> whereRef(Navigable<? extends T,V> path, Iterable<? extends Ref<V>> it)
      Adds a WHERE clause that matches the specified records. The records can represent any of the related tables in the table graph.
      Parameters:
      path - the path to the object in the table graph.
      it - the records to match.
      Returns:
      the predicate builder.
      Since:
      1.3

    • where

      public final QueryBuilder<T,R,ID> where(Iterable<? extends T> it)
      Adds a WHERE clause that matches the specified records.
      Parameters:
      it - the records to match.
      Returns:
      the query builder.

    • where

      public final <V> QueryBuilder<T,R,ID> where(Navigable<? extends T,V> path, Operator operator, Iterable<? extends V> it)
      Adds a WHERE clause that matches the specified objects at the specified path in the table graph.
      Type Parameters:
      V - the type of the object that the metamodel represents.
      Parameters:
      path - the path to the object in the table graph.
      operator - the operator to use for the comparison.
      it - the objects to match, which can be primary keys, records representing the table, or fields in the table graph.
      Returns:
      the query builder.
      Since:
      1.2

    • where

      @SafeVarargs public final <V> QueryBuilder<T,R,ID> where(Navigable<? extends T,V> path, Operator operator, V... o)
      Adds a WHERE clause that matches the specified objects at the specified path in the table graph.
      Type Parameters:
      V - the type of the object that the metamodel represents.
      Parameters:
      path - the path to the object in the table graph.
      operator - the operator to use for the comparison.
      o - the object(s) to match, which can be primary keys, records representing the table, or fields in the table graph.
      Returns:
      the query builder.
      Since:
      1.2

    • where

      public final QueryBuilder<T,R,ID> where(StringTemplatePREVIEW template)
      Adds a WHERE clause to the query for the specified expression.
      Parameters:
      template - the expression.
      Returns:
      the query builder.

    • where

      public abstract QueryBuilder<T,R,ID> where(Function<WhereBuilder<T,R,ID>,PredicateBuilder<T,?,?>> predicate)
      Adds a WHERE clause to the query using a WhereBuilder.
      Parameters:
      predicate - the predicate to add.
      Returns:
      the query builder.

    • whereExists

      public final QueryBuilder<T,R,ID> whereExists(QueryBuilder<?,?,?> subquery)
      Adds a WHERE clause that keeps the rows for which the specified subquery returns at least one row.

      Use where(Function) with WhereBuilder.exists(st.orm.template.QueryBuilder<?, ?, ?>) to combine the condition with others in a single clause; consecutive where calls are AND-combined.

      Parameters:
      subquery - the subquery to test for existence.
      Returns:
      the query builder.
      Since:
      1.13

    • whereNotExists

      public final QueryBuilder<T,R,ID> whereNotExists(QueryBuilder<?,?,?> subquery)
      Adds a WHERE clause that keeps the rows for which the specified subquery returns no rows.
      Parameters:
      subquery - the subquery to test for absence.
      Returns:
      the query builder.
      Since:
      1.13

    • groupBy

      @SafeVarargs public final QueryBuilder<T,R,ID> groupBy(Navigable<? extends T,?>... path)
      Adds a GROUP BY clause to the query for field at the specified path in the table graph. The metamodel can refer to manually added joins.

      A path resolves to the same columns a predicate on that path would use: a foreign key expands to its foreign key column(s) on the referencing table, without joining the referenced table, and an inline record expands to its component columns. A single-column path contributes exactly one column.

      Parameters:
      path - the path to group by.
      Returns:
      the query builder.
      Since:
      1.2

    • groupBy

      public abstract QueryBuilder<T,R,ID> groupBy(StringTemplatePREVIEW template)
      Adds a GROUP BY clause to the query using a string template. Multiple calls to this method append additional columns to the GROUP BY clause.
      Parameters:
      template - the template to group by.
      Returns:
      the query builder.
      Since:
      1.2

    • having

      @SafeVarargs public final <V> QueryBuilder<T,R,ID> having(Navigable<? extends T,V> path, Operator operator, V... o)
      Adds a HAVING clause to the query using the specified expression.
      Parameters:
      path - the path to the object in the table graph.
      operator - the operator to use for the comparison.
      o - the object(s) to match, which can be primary keys, records representing the table, or fields in the table graph.
      Returns:
      the query builder.
      Since:
      1.2

    • having

      public abstract QueryBuilder<T,R,ID> having(StringTemplatePREVIEW template)
      Adds a HAVING clause to the query using the specified expression. Multiple calls to this method are combined using AND.
      Parameters:
      template - the expression to add.
      Returns:
      the query builder.
      Since:
      1.2

    • havingExists

      public abstract QueryBuilder<T,R,ID> havingExists(QueryBuilder<?,?,?> subquery)
      Adds a HAVING clause that keeps the groups for which the specified subquery returns at least one row.
      Parameters:
      subquery - the subquery to test for existence.
      Returns:
      the query builder.
      Since:
      1.13

    • havingNotExists

      public abstract QueryBuilder<T,R,ID> havingNotExists(QueryBuilder<?,?,?> subquery)
      Adds a HAVING clause that keeps the groups for which the specified subquery returns no rows.
      Parameters:
      subquery - the subquery to test for absence.
      Returns:
      the query builder.
      Since:
      1.13

    • orderBy

      @SafeVarargs public final QueryBuilder<T,R,ID> orderBy(Navigable<? extends T,?>... path)
      Adds an ORDER BY clause to the query for the field at the specified path in the table graph.
      Parameters:
      path - the path to order by.
      Returns:
      the query builder.
      Since:
      1.2

    • orderByDescending

      public final QueryBuilder<T,R,ID> orderByDescending(Navigable<? extends T,?> path)
      Adds an ORDER BY clause to the query for the field at the specified path in the table graph. The results are sorted in descending order.
      Parameters:
      path - the path to order by.
      Returns:
      the query builder.
      Since:
      1.2

    • orderByDescending

      @SafeVarargs public final QueryBuilder<T,R,ID> orderByDescending(Navigable<? extends T,?>... path)
      Adds an ORDER BY clause to the query for the fields at the specified paths in the table graph. The results are sorted in descending order for each column.
      Parameters:
      path - the paths to order by.
      Returns:
      the query builder.
      Since:
      1.9

    • orderByDescending

      public final QueryBuilder<T,R,ID> orderByDescending(StringTemplatePREVIEW template)
      Adds an ORDER BY clause to the query using a string template. The results are sorted in descending order. Multiple calls to this method append additional columns to the ORDER BY clause.
      Parameters:
      template - the template to order by.
      Returns:
      the query builder.
      Since:
      1.9

    • orderBy

      public abstract QueryBuilder<T,R,ID> orderBy(StringTemplatePREVIEW template)
      Adds an ORDER BY clause to the query using a string template. Multiple calls to this method append additional columns to the ORDER BY clause.
      Parameters:
      template - the template to order by.
      Returns:
      the query builder.
      Since:
      1.2

    • hasOrderBy

      protected abstract boolean hasOrderBy()
      Returns true if any ORDER BY columns have been added to this query builder.
      Returns:
      true if ORDER BY columns are present, false otherwise.
      Since:
      1.9

    • limit

      public abstract QueryBuilder<T,R,ID> limit(int limit)
      Adds a LIMIT clause to the query.
      Parameters:
      limit - the maximum number of records to return.
      Returns:
      the query builder.
      Since:
      1.2

    • offset

      public abstract QueryBuilder<T,R,ID> offset(int offset)
      Adds an OFFSET clause to the query.
      Parameters:
      offset - the offset.
      Returns:
      the query builder.
      Since:
      1.2

    • forShare

      public abstract QueryBuilder<T,R,ID> forShare()
      Locks the selected rows for reading.
      Returns:
      the query builder.
      Throws:
      PersistenceException - if the database does not support the specified lock mode, or if the lock mode is not supported for the current query.
      Since:
      1.2

    • forUpdate

      public abstract QueryBuilder<T,R,ID> forUpdate()
      Locks the selected rows for reading.
      Returns:
      the query builder.
      Throws:
      PersistenceException - if the database does not support the specified lock mode, or if the lock mode is not supported for the current query.
      Since:
      1.2

    • forLock

      public abstract QueryBuilder<T,R,ID> forLock(StringTemplatePREVIEW template)
      Locks the selected rows using a custom lock mode.

      Note: This method results in non-portable code, as the lock mode is specific to the underlying database.

      Returns:
      the query builder.
      Throws:
      PersistenceException - if the lock mode is not supported for the current query.
      Since:
      1.2

    • hint

      public abstract QueryBuilder<T,R,ID> hint(String hint)
      Adds an optimizer hint to the statement this builder builds. The hint is the database's own hint text, passed through as is, such as NO_MERGE(recent) on MariaDB or RECOMPILE on SQL Server; its content and its effect depend on the database.

      The dialect decides where the hint goes. MySQL, MariaDB and Oracle read hints from a /*+ ... */ comment right after the statement's leading keyword; SQL Server reads them from an OPTION (...) clause at the end of the statement. Several calls add several hints to the same comment or clause. A database without hint syntax, such as PostgreSQL, H2 or SQLite, leaves the hint out, as a hint never changes what a statement returns.

      SQL Server takes its query hints once per statement, so on SQL Server a builder with a hint cannot serve as a subquery; put the hint on the outer query instead.

      Parameters:
      hint - the hint text, as the database reads it.
      Returns:
      the query builder.
      Throws:
      PersistenceException - if the hint is blank, or contains */ or ;, which would end the comment that carries it or the statement.
      Since:
      1.15

    • build

      public abstract Query build()
      Builds the query based on the current state of the query builder.
      Returns:
      the constructed query.

    • prepare

      public final PreparedQuery prepare()
      Prepares the query for execution.

      Unlike regular queries, which are constructed lazily, prepared queries are constructed eagerly. Prepared queries allow the use of bind variables and enable reading generated keys after row insertion.

      Note: The prepared query must be closed after usage to prevent resource leaks. As the prepared query is AutoCloseable, it is recommended to use it within a try-with-resources block.

      Returns:
      the prepared query.
      Throws:
      PersistenceException - if the query preparation fails.

    • page

      public final Page<R> page(int pageNumber, int pageSize)
      Executes the query and returns a Page of results using offset-based pagination.

      This method executes the query for the requested page and, when the total cannot be derived from the fetched page, a count query (without offset or limit). A page that is not full determines the total directly, so the count query only runs for a full page, or for an empty page beyond the first. The caller is responsible for adding ORDER BY clauses to ensure deterministic ordering across pages.

      Page numbers are zero-based: pass 0 for the first page.

      Parameters:
      pageNumber - the zero-based page index (must not be negative).
      pageSize - the maximum number of results per page (must be positive).
      Returns:
      a page containing the results and pagination metadata.
      Throws:
      IllegalArgumentException - if pageNumber is negative or pageSize is not positive.
      Since:
      1.10

    • page

      public final Page<R> page(Pageable pageable)
      Executes the query and returns a Page of results using offset-based pagination.

      This method executes the query for the requested page and, when the total cannot be derived from the fetched page, a count query (without offset or limit). A page that is not full determines the total directly, so the count query only runs for a full page, or for an empty page beyond the first. Sort orders can be specified either through the pageable or through explicit orderBy calls on the query builder, but not both. If both are present, a PersistenceException is thrown.

      Use Pageable.ofSize(int) for the first page, then navigate with Page.next() or Page.previous().

      Parameters:
      pageable - the pagination request specifying page number and page size.
      Returns:
      a page containing the results and pagination metadata.
      Throws:
      PersistenceException - if the pageable has sort orders and the query builder has explicit orderBy calls.
      Since:
      1.10

    • page

      public final Page<R> page(Pageable pageable, long totalCount)
      Executes the query and returns a Page of results using offset-based pagination with a pre-computed total count.

      This method applies the sort orders from the pageable, then fetches the content for the requested page using the provided total count instead of executing a separate count query. This is useful when the total count is already known (for example, cached from a previous request or obtained from an external source), avoiding a redundant COUNT query.

      Sort orders can be specified either through the pageable or through explicit orderBy calls on the query builder, but not both. If both are present, a PersistenceException is thrown.

      Parameters:
      pageable - the pagination request specifying page number and page size.
      totalCount - the pre-computed total number of matching results.
      Returns:
      a page containing the results and pagination metadata.
      Throws:
      PersistenceException - if the pageable has sort orders and the query builder has explicit orderBy calls.
      Since:
      1.10

    • slice

      public final Slice<R> slice(int pageNumber, int pageSize)
      Executes the query and returns a Slice of results using offset-based pagination without a count.

      The slice is read the way a page is, with OFFSET and LIMIT from the request, but one row beyond the page size is fetched instead of running a count query: Slice.hasNext() says whether that row existed, and Slice.hasPrevious() follows from the page number. Use it for a "load more" that needs no total, and for a query without a unique key, where scroll(Scrollable) is not possible.

      Page numbers are zero-based: pass 0 for the first slice.

      Parameters:
      pageNumber - the zero-based page index.
      pageSize - the maximum number of results per slice.
      Returns:
      the slice.
      Since:
      1.14

    • slice

      public final Slice<R> slice(Pageable pageable)
      Executes the query and returns a Slice of results using offset-based pagination without a count.

      The slice is read the way a page is, with OFFSET and LIMIT from the request, but one row beyond the page size is fetched instead of running a count query: Slice.hasNext() says whether that row existed, and Slice.hasPrevious() follows from the page number. Use it for a "load more" that needs no total, and for a query without a unique key, where scroll(Scrollable) is not possible.

      Use Pageable.ofSize(int) for the first slice, then navigate with Pageable.next() or Pageable.previous().

      Parameters:
      pageable - the request specifying page number, page size and sort orders.
      Returns:
      the slice.
      Throws:
      PersistenceException - if the request carries sort orders and the query an explicit ORDER BY.
      Since:
      1.14

    • scroll

      public abstract Window<R> scroll(Scrollable<T> scrollable)
      Executes a scroll request and returns a Window: the results in the request's sort order, the flags that say whether rows exist after and before the window, and the tokens that continue from it.

      The request owns the ordering, so the query must not carry an ORDER BY of its own. The sort fields and the key are read from each row alongside the result, so the tokens are there for every result type: the entity, a projection, a ref or a custom select type. A window reached through Window.previous() comes back in the same sort order as every other window.

      Parameters:
      scrollable - the scroll request: ordering, size and position.
      Returns:
      a window containing the results and navigation tokens.
      Throws:
      PersistenceException - if the query carries an explicit ORDER BY, the key is compound or nullable, or a sort field is nullable.
      Since:
      1.11

    • windows

      public abstract Stream<Window<R>> windows(int size)
      Executes the query in windows of size rows ordered by the primary key, each window one closed statement.

      Where getResultStream() holds one open statement on the connection for as long as the stream is consumed, a window is fetched by a statement that has returned and closed before the window is handed to the caller. Between windows the connection is free, so the loop over a window may query, fetch references and write, inside a transaction or with a transaction per window. The stream carries no database resource and needs no closing. Each window is its own statement: it runs the query's WHERE clause again from the cursor position, and under READ COMMITTED it sees rows committed since the previous window.

      Windows are keyset windows over the primary key, so the query must not carry an ORDER BY of its own, and the result type must be the entity type the key belongs to: selectRef() and custom select types are refused. A compound primary key is refused too; pass windows(Scrollable) a single-column unique key instead. The rows come in ascending key order; pass Scrollable.of(key, size).descending() to windows(Scrollable) for descending order.

      
 users.select().where(User_.city, EQUALS, city).windows(1000).forEach(window ->
     users.update(window.content().stream()
         .map(user -> new User(user.id(), user.email().toLowerCase(), user.birthDate(), user.street(), user.postalCode(), user.city()))
         .toList()));
      Parameters:
      size - the maximum number of rows per window (must be positive).
      Returns:
      a stream of windows; each window's Window.next() resumes the iteration after that window.
      Throws:
      IllegalArgumentException - if size is not positive.
      PersistenceException - if the query has no single-column primary key, carries an explicit ORDER BY, or does not select the entity type.
      Since:
      1.14

    • windows

      public abstract Stream<Window<R>> windows(Scrollable<T> scrollable)
      Executes the query in windows described by the given scroll request, each window one closed statement.

      This is the form of windows(int) that chooses the key, the sort fields, the directions and the starting position: Scrollable.of(key, size) iterates from the start, a Window.next() token or Scrollable.from(String) resumes after an earlier window, and .descending() iterates in descending key order. The same key rules as scroll(Scrollable) apply.

      Parameters:
      scrollable - the scroll request describing key, sort, size, direction and starting position.
      Returns:
      a stream of windows; each window's Window.next() resumes the iteration after that window.
      Throws:
      PersistenceException - if the query carries an explicit ORDER BY, the key is nullable, or the result type does not carry the key.
      Since:
      1.14

    • getResultStream

      public abstract Stream<R> getResultStream()
      Executes the query and returns a stream of results.

      The resulting stream is lazily loaded, meaning that the records are only retrieved from the database as they are consumed by the stream. This approach is efficient and minimizes the memory footprint, especially when dealing with large volumes of records.

      The stream is one open statement on the connection it reads from, and that connection is consume-only until the stream is read to its end or closed: inside a transaction every statement shares the transaction's connection, so a query, a Ref.fetch() or a write issued while rows remain unread is refused with a PersistenceException, on every database. A loop that needs the connection while it iterates uses windows(int), which runs one closed statement per window. Outside a transaction the stream holds a connection of its own for as long as it is open.

      Note: Calling this method does trigger the execution of the underlying query, so it should only be invoked when the query is intended to run. Since the stream holds resources open while in use, it must be closed after usage to prevent resource leaks. As the stream is AutoCloseable, it is recommended to use it within a try-with-resources block.

      Returns:
      a stream of results.
      Throws:
      PersistenceException - if the query operation fails due to underlying database issues, such as connectivity.

    • getResultCount

      public long getResultCount()
      Returns the number of results of this query.

      Select queries execute a dedicated count query derived from this builder: the select clause is replaced by COUNT(*), or the query is counted as a derived table when its shape requires it (DISTINCT, GROUP BY, HAVING, limit, offset or a custom select clause). Queries that lock rows fetch and count the results instead, so the requested locks are acquired.

      Returns:
      the total number of results of this query as a long value.
      Throws:
      PersistenceException - if the query operation fails due to underlying database issues, such as connectivity.

    • getResultList

      public abstract List<R> getResultList()
      Executes the query and returns a list of results.
      Returns:
      the list of results.
      Throws:
      PersistenceException - if the query fails.

    • getResultGroupedBy

      public abstract <V extends Data> SequencedMap<V,List<R>> getResultGroupedBy(TypedMetamodel<T,V,V> path)
      Executes the query and returns the results grouped by the record reached via path, typically the parent entity of a foreign key. The SQL is not affected by the grouping; the same select is executed and the results are grouped during hydration.

      The returned map and its lists are unmodifiable and insertion-ordered: groups appear in the order their first result is encountered, and results appear in encounter order within each group. Use orderBy() to control both. Duplicate entities within a result set are guaranteed to share the same instance as long as earlier occurrences remain strongly reachable, and the grouping retains every result and group key while the result set is consumed; each result's reference to its group key is therefore the map key itself.

      This method requires an entity query: the result type must be the table type T so that the path can be resolved against the results. The path must also resolve to a non-null record for every result; paths over nullable foreign keys must be narrowed with a where() clause first.

      The signature requires a path whose component type equals its field type, which is how the generated metamodels type eagerly fetched fields. Paths over Ref fields are typed TypedMetamodel<T, V, Ref<V>> and therefore do not compile; use getResultGroupedByRef(Metamodel) for those.

      Type Parameters:
      V - the type of the record to group by.
      Parameters:
      path - the metamodel path from the table type to the record to group by, for example Pet_.owner.
      Returns:
      the results grouped by the record reached via path, in encounter order.
      Throws:
      PersistenceException - if the query fails, if the result type is not the table type, or if the path resolves to null for a result.
      Since:
      1.13

    • getResultGroupedByRef

      public abstract <V extends Data> SequencedMap<Ref<V>,List<R>> getResultGroupedByRef(Metamodel<T,V> path)
      Executes the query and returns the results grouped by a lightweight ref to the record reached via path, typically the parent entity of a foreign key. The SQL is not affected by the grouping; the same select is executed and the results are grouped during hydration.

      This is the ref-based variant of getResultGroupedBy(TypedMetamodel): the map keys are Ref instances, which are compared by primary key, keeping map lookups constant-cost regardless of the size of the group record.

      The behavior of the keys follows how the foreign key is declared on the record:

      • Entity field (for example @FK Owner owner): the referenced record is fetched eagerly, as part of the query's auto-joined graph, and is materialized with each result. The keys are loaded refs wrapping that record: Ref.getOrNull() returns it directly, without touching the database.
      • Ref field (for example @FK Ref<Pet> pet): the referenced record is fetched lazily; the query reads only the foreign key column, without joining or fetching the referenced table. The keys are the unloaded refs produced by the query, carrying just the primary key. When the records are needed, fetch them afterwards in a single query with findAllByRef(map.keySet()).

      The returned map and its lists are unmodifiable and insertion-ordered: groups appear in the order their first result is encountered, and results appear in encounter order within each group. Use orderBy() to control both.

      This method requires an entity query: the result type must be the table type T so that the path can be resolved against the results. The path must also resolve to a non-null value for every result; paths over nullable foreign keys must be narrowed with a where() clause first.

      Type Parameters:
      V - the type of the record to group by.
      Parameters:
      path - the metamodel path from the table type to the record to group by, for example Pet_.owner.
      Returns:
      the results grouped by a ref to the record reached via path, in encounter order.
      Throws:
      PersistenceException - if the query fails, if the result type is not the table type, if the path does not reference an entity or ref, or if the path resolves to null for a result.
      Since:
      1.13

    • getSingleResult

      public abstract R getSingleResult()
      Executes the query and returns a single result.
      Returns:
      the single result.
      Throws:
      NoResultException - if there is no result.
      NonUniqueResultException - if more than one result.
      PersistenceException - if the single row's value is null, or the query fails.

    • getOptionalResult

      public abstract Optional<R> getOptionalResult()
      Executes the query and returns an optional result.
      Returns:
      the optional result.
      Throws:
      NonUniqueResultException - if more than one result.
      PersistenceException - if the single row's value is null, or the query fails.

    • executeUpdate

      public final int executeUpdate()
      Execute a DELETE statement.
      Returns:
      the number of rows impacted as result of the statement.
      Throws:
      PersistenceException - if the statement fails.