Module storm.java
Package st.orm.template

Interface Templates

public interface Templates
Templates relies on preview features of the Java platform:
Programs can only use Templates when preview features are enabled.
Preview features may be removed in a future release, or upgraded to permanent features of the Java platform.
The Templates interface provides a collection of static methods for constructing SQL query elements and creating ORM repository templates. It serves as a central point for building SQL queries or getting access to repositories that can interact with databases in a type-safe and manner, supporting both JPA and JDBC.

This interface includes methods for generating SQL clauses such as SELECT, FROM, WHERE, INSERT, UPDATE, and DELETE, as well as utility methods for working with parameters, tables, aliases, and more.

Additionally, the Templates interface provides methods to create ORMTemplate instances for use with different data sources like JDBC's DataSource or Connection, or JPA's EntityManager (see JpaTemplate).

Using Templates

Define the records to use them to construct the query using templates:


 record City(int id, String name, long population) {};

 record User(int id, String name, int age, LocalDate birthDate,
             String street, String postalCode, int cityId) {};

Create

Insert a user into the database. The template engine also supports insertion of multiple entries by passing an array (or list) of objects or primary keys. Alternatively, insertion can also be executed in batch mode using BindVars.


 User user = ...;
 ORM(dataSource).query(RAW."""
         INSERT INTO \{User.class}
         VALUES \{user}""");

Read

Select all users from the database that are linked to City with primary key 1. The value is passed to the underling JDBC or JPA system as variable. The results can also be retrieved as a stream of objects by using Query.getResultStream(Class).


 List<User> users = ORM(dataSource).query(RAW."""
         SELECT \{User.class}
         FROM \{User.class}
         WHERE \{User.class}.city_id = \{1}""")
     .getResultList(User.class);

Update

Update a user in the database. The template engine also supports updates for multiple entries by passing an array or list of objects. Alternatively, updates can be executed in batch mode by using BindVars.


 User user = ...;
 ORM(dataSource).query(RAW."""
         UPDATE \{User.class}
         SET \{user}
         WHERE \{user}""");

Delete

Delete user in the database. The template engine also supports updates for multiple entries by passing an array (or list) of objects or primary keys. Alternatively, deletion can be executed in batch mode by using BindVars.


 User user = ...;
 ORM(dataSource).query(RAW."""
         DELETE FROM \{User.class}
         WHERE \{user}""");

Howto start

The Templates interface provides static methods to create ORMTemplate instances based on your data source:

Using EntityManager (JPA)


 EntityManager entityManager = ...;
 ORMTemplate orm = Templates.ORM(entityManager);

Using DataSource (JDBC)


 DataSource dataSource = ...;
 ORMTemplate orm = Templates.ORM(dataSource);

Using Connection (JDBC)

Note: The caller is responsible for closing the connection after usage. 


 Connection connection = ...;
 ORMTemplate orm = Templates.ORM(connection);
See Also:

  • Method Summary

    Static Methods
    Modifier and Type
    Method
    Description
    static Element
    alias(Class<? extends Data> table)
    Generates an alias element for the specified table class.
    static Element
    alias(Class<? extends Data> table, ResolveScope scope)
    Generates an alias element for the specified table class.
    static Element
    bindVar(BindVars bindVars, Function<Data,?> extractor)
    Creates a new var element that can be used to specify individual bind variables in the query.
    static Element
    column(Metamodel<?,?> path)
    Generates a column element for a column specified by the given metamodel in a type safe manner.
    static Element
    column(Metamodel<?,?> path, ResolveScope scope)
    Generates a column element for a column specified by the given metamodel in a type safe manner.
    static Element
    delete(Class<? extends Data> table)
    Generates a DELETE element for the specified table class.
    static Element
    delete(Class<? extends Data> table, String alias)
    Generates a DELETE element for the specified table class with an alias.
    static Element
    from(Class<? extends Data> table, boolean autoJoin)
    Generates a FROM element for the specified table class without an alias and optional auto-joining of foreign keys.
    static Element
    from(Class<? extends Data> table, String alias, boolean autoJoin)
    Generates a FROM element for the specified table class, with an alias and optional auto-joining of foreign keys.
    static Element
    from(StringTemplatePREVIEW template, String alias)
    Generates a FROM element using a provided SQL string template with an alias.
    static Element
    insert(Class<? extends Data> table)
    Generates an INSERT element for the specified table class.
    static Element
    insert(Class<? extends Data> table, boolean ignoreAutoGenerate)
    Generates an INSERT element for the specified table class.
    static Element
    param(Object value)
    Generates a parameter element for the specified value, to be used in SQL queries.
    static Element
    param(String name, Object value)
    Generates a named parameter element for the specified value, to be used in SQL queries.
    static Element
    param(String name, Calendar value, TemporalType temporalType)
    Generates a named parameter element for the specified Calendar value with a temporal type.
    static Element
    param(String name, Date value, TemporalType temporalType)
    Generates a named parameter element for the specified Date value with a temporal type.
    static <P> Element
    param(String name, P value, Function<? super P,?> converter)
    Generates a named parameter element for the specified value with a converter function.
    static Element
    param(Calendar value, TemporalType temporalType)
    Generates a parameter element for the specified Calendar value with a temporal type.
    static Element
    param(Date value, TemporalType temporalType)
    Generates a parameter element for the specified Date value with a temporal type.
    static <P> Element
    param(P value, Function<? super P,?> converter)
    Generates a parameter element for the specified value with a converter function.
    static Element
    select(Class<? extends Data> table)
    Generates a SELECT element for the specified table class.
    static Element
    select(Class<? extends Data> table, SelectMode mode)
    Generates a SELECT element for the specified table class.
    static Element
    set(BindVars bindVars)
    Generates a SET clause using the specified BindVars.
    static Element
    set(BindVars bindVars, Collection<Metamodel<?,?>> fields)
    Generates a SET clause using the specified BindVars.
    static Element
    set(Data record)
    Generates a SET clause for the specified record.
    static Element
    set(Data record, Collection<Metamodel<?,?>> fields)
    Generates a SET clause for the specified record.
    static Element
    subquery(StringTemplatePREVIEW template, boolean correlate)
    Creates a new subquery element using a string template.
    static Element
    subquery(QueryBuilder<?,?,?> builder, boolean correlate)
    Creates a new subquery element using a query builder.
    static Element
    table(Class<? extends Data> table)
    Generates a Table element for the specified table class.
    static Element
    table(Class<? extends Data> table, String alias)
    Generates a Table element with an alias for the specified table class.
    static Element
    unsafe(String sql)
    Injects raw SQL into the query without any processing or sanitization.
    static Element
    update(Class<? extends Data> table)
    Generates an UPDATE element for the specified table class.
    static Element
    update(Class<? extends Data> table, String alias)
    Generates an UPDATE element for the specified table class with an alias.
    static Element
    values(Iterable<? extends Data> records)
    Generates a VALUES clause for the specified iterable of record instances.
    static Element
    values(Iterable<? extends Data> records, boolean ignoreAutoGenerate)
    Generates a VALUES clause for the specified iterable of record instances.
    static Element
    values(BindVars bindVars)
    Generates a VALUES clause using the specified BindVars for batch insertion.
    static Element
    values(BindVars bindVars, boolean ignoreAutoGenerate)
    Generates a VALUES clause using the specified BindVars for batch insertion.
    static Element
    values(Data... r)
    Generates a VALUES clause for the specified record instance(s).
    static Element
    values(Data r, boolean ignoreAutoGenerate)
    Generates a VALUES clause for the specified record instance(s).
    static Element
    where(Iterable<?> it)
    Generates a WHERE clause based on the provided iterable of values or records.
    static Element
    where(Object o)
    Generates a WHERE clause based on the provided value or record.
    static Element
    where(BindVars bindVars)
    Generates a WHERE clause using the specified BindVars for batch operations.
    static <V> Element
    where(Metamodel<?,V> path, Operator operator, Iterable<? extends V> it)
    Generates a WHERE clause based on the provided path, operator, and iterable of values or records.
    static <V> Element
    where(Metamodel<?,V> path, Operator operator, V... o)
    Generates a WHERE clause based on the provided path, operator, and values or records.

  • Method Details

    • select

      static Element select(Class<? extends Data> table)
      Generates a SELECT element for the specified table class.

      This method creates a SELECT clause for the provided table record, including all of its columns as well as columns from any foreign key relationships defined within the record. It is designed to be used within SQL string templates to dynamically construct queries based on the table's structure.

      Example usage in a string template: 

      
 SELECT \{select(MyTable.class)}
 FROM \{from(MyTable.class)}

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that a SELECT element is required based on its placement in the query: 

      
 SELECT \{MyTable.class}
 FROM \{MyTable.class}
      Parameters:
      table - the Class object representing the table record.
      Returns:
      an Element representing the SELECT clause for the specified table.

    • select

      static Element select(@Nonnull Class<? extends Data> table, @Nonnull SelectMode mode)
      Generates a SELECT element for the specified table class.

      This method creates a SELECT clause for the provided table record, including all of its columns as well as columns from any foreign key relationships defined within the record. It is designed to be used within SQL string templates to dynamically construct queries based on the table's structure.

      Example usage in a string template: 

      
 SELECT \{select(MyTable.class, NESTED)}
 FROM \{from(MyTable.class)}

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that a SELECT element is required based on its placement in the query: 

      
 SELECT \{MyTable.class}
 FROM \{MyTable.class}
      Parameters:
      table - the Class object representing the table record.
      mode - NESTED to include the full object hierarchy, FLAT to include only the main table's fields, PK to include only the primary key fields.
      Returns:
      an Element representing the SELECT clause for the specified table.

    • from

      static Element from(@Nonnull Class<? extends Data> table, boolean autoJoin)
      Generates a FROM element for the specified table class without an alias and optional auto-joining of foreign keys.

      This method creates a FROM clause for the provided table record. If autoJoin is set to true, it will automatically include JOIN clauses for all foreign keys defined in the record. This facilitates constructing complex queries that involve related tables without manually specifying each join.

      Example usage in a string template: 

      
 SELECT \{select(MyTable.class)}
 FROM \{from(MyTable.class, true)}

      For convenience, you can also use the shorthand notation. In this case, autoJoin defaults to false. The SQL template engine automatically detects that a FROM element is required based on its placement in the query: 

      
 SELECT \{MyTable.class}
 FROM \{MyTable.class}
      Parameters:
      table - the Class object representing the table record.
      autoJoin - if true, automatically join all foreign keys listed in the record.
      Returns:
      an Element representing the FROM clause for the specified table.

    • from

      static Element from(@Nonnull Class<? extends Data> table, @Nonnull String alias, boolean autoJoin)
      Generates a FROM element for the specified table class, with an alias and optional auto-joining of foreign keys.

      This method creates a FROM clause for the provided table record, applying the specified alias. If autoJoin is set to true, it will automatically include JOIN clauses for all foreign keys defined in the record. This facilitates constructing complex queries that involve related tables without manually specifying each join.

      Example usage in a string template: 

      
 SELECT \{select(Table.class)}
 FROM \{from(MyTable.class, "t", true)}
      Parameters:
      table - the Class object representing the table record.
      alias - the alias to use for the table in the query. The alias must not require escaping.
      autoJoin - if true, automatically join all foreign keys listed in the record.
      Returns:
      an Element representing the FROM clause for the specified table.

    • from

      static Element from(@Nonnull StringTemplatePREVIEW template, @Nonnull String alias)
      Generates a FROM element using a provided SQL string template with an alias.

      This method allows you to specify a custom StringTemplatePREVIEW to be used as the source in the FROM clause, applying the provided alias. This is useful when you need to include subqueries or complex table expressions in your SQL queries.

      Example usage in a string template: 

      
 SELECT \{select(Table.class)}
 FROM \{from(RAW."SELECT column_a, column_b FROM table", "t")}

      in this context, the alias is mandatory and auto-joining of foreign keys is not applicable.

      Parameters:
      template - the StringTemplatePREVIEW representing the custom SQL to be used in the FROM clause.
      alias - the alias to assign to the frame clause in the query. The alias must not require escaping.
      Returns:
      an Element representing the FROM clause with the specified template and alias.

    • insert

      static Element insert(@Nonnull Class<? extends Data> table)
      Generates an INSERT element for the specified table class.

      This method creates an INSERT clause for the provided table record. It is designed to be used within SQL string templates to dynamically construct INSERT queries based on the table's structure.

      Example usage in a string template: 

      
 INSERT INTO \{insert(MyTable.class)}
 VALUES \{values(entity)}

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that an INSERT element is required based on its placement in the query: 

      
 INSERT INTO \{MyTable.class}
 VALUES \{entity}

      Here, entity is an instance of the Table class containing the values to be inserted.

      Parameters:
      table - the Class object representing the table record.
      Returns:
      an Element representing the INSERT clause for the specified table.

    • insert

      static Element insert(@Nonnull Class<? extends Data> table, boolean ignoreAutoGenerate)
      Generates an INSERT element for the specified table class.

      This method creates an INSERT clause for the provided table record. It is designed to be used within SQL string templates to dynamically construct INSERT queries based on the table's structure.

      Example usage in a string template: 

      
 INSERT INTO \{insert(MyTable.class)}
 VALUES \{values(entity)}

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that an INSERT element is required based on its placement in the query: 

      
 INSERT INTO \{MyTable.class}
 VALUES \{entity}

      Here, entity is an instance of the Table class containing the values to be inserted.

      Parameters:
      table - the Class object representing the table record.
      ignoreAutoGenerate - true to ignore the auto-generate flag on the primary key and explicitly insert the provided primary key value. Use this flag only when intentionally providing the primary key value (e.g., migrations, data exports).
      Returns:
      an Element representing the INSERT clause for the specified table.

    • values

      static Element values(@Nonnull Data r, boolean ignoreAutoGenerate)
      Generates a VALUES clause for the specified record instance(s).

      This method creates a VALUES clause using the provided Data instance(s). It is intended to be used within SQL string templates to dynamically construct INSERT statements with the given values.

      Example usage in a string template: 

      
 INSERT INTO \{MyTable.class}
 VALUES \{values(entity1, entity2)}

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that a VALUES element is required based on its placement in the query: 

      
 INSERT INTO \{MyTable.class}
 VALUES \{new Data[] {entity1, entity2}}

      Here, entity1, entity2, etc., are instances of the Data class containing the values to be inserted.

      Parameters:
      r - one or more Data instances containing the values to be inserted.
      ignoreAutoGenerate - true to ignore the auto-generate flag on the primary key and explicitly insert the provided primary key value. Use this flag only when intentionally providing the primary key value (e.g., migrations, data exports).
      Returns:
      an Element representing the VALUES clause with the specified records.

    • values

      static Element values(@Nonnull Data... r)
      Generates a VALUES clause for the specified record instance(s).

      This method creates a VALUES clause using the provided Data instance(s). It is intended to be used within SQL string templates to dynamically construct INSERT statements with the given values.

      Example usage in a string template: 

      
 INSERT INTO \{MyTable.class}
 VALUES \{values(entity1, entity2)}

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that a VALUES element is required based on its placement in the query: 

      
 INSERT INTO \{MyTable.class}
 VALUES \{new Data[] {entity1, entity2}}

      Here, entity1, entity2, etc., are instances of the Data class containing the values to be inserted.

      Parameters:
      r - one or more Data instances containing the values to be inserted.
      Returns:
      an Element representing the VALUES clause with the specified records.

    • values

      static Element values(@Nonnull Iterable<? extends Data> records)
      Generates a VALUES clause for the specified iterable of record instances.

      This method creates a VALUES clause using the provided Iterable of Data instances. It is intended to be used within SQL string templates to dynamically construct INSERT statements with the given values.

      Example usage in a string template: 

      
 INSERT INTO \{MyTable.class}
 VALUES \{values(records)}

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that a VALUES element is required based on its placement in the query: 

      
 INSERT INTO \{MyTable.class}
 VALUES \{records}

      Here, records is an Iterable of Data instances containing the values to be inserted.

      Parameters:
      records - an Iterable of Data instances containing the values to be inserted.
      Returns:
      an Element representing the VALUES clause with the specified records.

    • values

      static Element values(@Nonnull Iterable<? extends Data> records, boolean ignoreAutoGenerate)
      Generates a VALUES clause for the specified iterable of record instances.

      This method creates a VALUES clause using the provided Iterable of Data instances. It is intended to be used within SQL string templates to dynamically construct INSERT statements with the given values.

      Example usage in a string template: 

      
 INSERT INTO \{MyTable.class}
 VALUES \{values(records)}

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that a VALUES element is required based on its placement in the query: 

      
 INSERT INTO \{MyTable.class}
 VALUES \{records}

      Here, records is an Iterable of Data instances containing the values to be inserted.

      Parameters:
      records - an Iterable of Data instances containing the values to be inserted.
      ignoreAutoGenerate - true to ignore the auto-generate flag on the primary key and explicitly insert the provided primary key value. Use this flag only when intentionally providing the primary key value (e.g., migrations, data exports).
      Returns:
      an Element representing the VALUES clause with the specified records.

    • values

      static Element values(@Nonnull BindVars bindVars)
      Generates a VALUES clause using the specified BindVars for batch insertion.

      This method creates a VALUES clause that utilizes a BindVars instance, allowing for batch insertion of records using bind variables. This is particularly useful when performing batch operations where the same query is executed multiple times with different variable values.

      Example usage in a batch insertion scenario: 

      
 var bindVars = orm.createBindVars();
 try (var query = orm.query(RAW."""
         INSERT INTO \{MyTable.class}
         VALUES \{values(bindVars)}""").prepare()) {
     records.forEach(query::addBatch);
     query.executeBatch();
 }

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that a VALUES element is required based on its placement in the query: 

      
 INSERT INTO \{MyTable.class}
 VALUES \{bindVars}

      In this example, bindVars is a BindVars instance created by the ORM. The records are iterated over, and each is added to the batch. The query is then executed as a batch operation.

      Parameters:
      bindVars - the BindVars instance used for batch insertion.
      Returns:
      an Element representing the VALUES clause utilizing the specified bind variables.

    • values

      static Element values(@Nonnull BindVars bindVars, boolean ignoreAutoGenerate)
      Generates a VALUES clause using the specified BindVars for batch insertion.

      This method creates a VALUES clause that utilizes a BindVars instance, allowing for batch insertion of records using bind variables. This is particularly useful when performing batch operations where the same query is executed multiple times with different variable values.

      Example usage in a batch insertion scenario: 

      
 var bindVars = orm.createBindVars();
 try (var query = orm.query(RAW."""
         INSERT INTO \{MyTable.class}
         VALUES \{values(bindVars)}""").prepare()) {
     records.forEach(query::addBatch);
     query.executeBatch();
 }

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that a VALUES element is required based on its placement in the query: 

      
 INSERT INTO \{MyTable.class}
 VALUES \{bindVars}

      In this example, bindVars is a BindVars instance created by the ORM. The records are iterated over, and each is added to the batch. The query is then executed as a batch operation.

      Parameters:
      bindVars - the BindVars instance used for batch insertion.
      ignoreAutoGenerate - true to ignore the auto-generate flag on the primary key and explicitly insert the provided primary key value. Use this flag only when intentionally providing the primary key value (e.g., migrations, data exports).
      Returns:
      an Element representing the VALUES clause utilizing the specified bind variables.

    • update

      static Element update(@Nonnull Class<? extends Data> table)
      Generates an UPDATE element for the specified table class.

      This method creates an UPDATE clause for the provided table record. It is designed to be used within SQL string templates to dynamically construct UPDATE queries based on the table's structure.

      Example usage in a string template: 

      
 UPDATE \{update(MyTable.class)}
 SET \{set(record)}
 WHERE \{where(record)}

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that an UPDATE element is required based on its placement in the query: 

      
 UPDATE \{MyTable.class}
 SET \{record}
 WHERE \{record}

      Here, record is an instance of the Data class containing the values to be updated.

      Parameters:
      table - the Class object representing the table record.
      Returns:
      an Element representing the UPDATE clause for the specified table.

    • update

      static Element update(@Nonnull Class<? extends Data> table, @Nonnull String alias)
      Generates an UPDATE element for the specified table class with an alias.

      This method creates an UPDATE clause for the provided table record, applying the specified alias. It is designed to be used within SQL string templates to dynamically construct UPDATE queries based on the table's structure.

      Example usage in a string template: 

      
 UPDATE \{update(MyTable.class, "t")}
 SET \{set(record)}
 WHERE \{where(record)}

      Here, record is an instance of the Data class containing the values to be updated.

      Parameters:
      table - the Class object representing the table record.
      alias - the alias to use for the table in the query. The alias must not require escaping.
      Returns:
      an Element representing the UPDATE clause for the specified table with alias.

    • set

      static Element set(@Nonnull Data record)
      Generates a SET clause for the specified record.

      This method creates a SET clause using the provided Data instance. It is intended to be used within SQL string templates to dynamically construct UPDATE statements with the given values.

      Example usage in a string template: 

      
 UPDATE \{MyTable.class}
 SET \{set(record)}
 WHERE \{where(record)}

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that a SET element is required based on its placement in the query: 

      
 UPDATE \{MyTable.class}
 SET \{record}
 WHERE \{record}

      Here, record is an instance of the Data class containing the values to be set.

      Parameters:
      record - the Data instance containing the values to be set.
      Returns:
      an Element representing the SET clause with the specified record.

    • set

      static Element set(@Nonnull Data record, @Nonnull Collection<Metamodel<?,?>> fields)
      Generates a SET clause for the specified record.

      This method creates a SET clause using the provided Data instance. It is intended to be used within SQL string templates to dynamically construct UPDATE statements with the given values.

      Example usage in a string template: 

      
 UPDATE \{MyTable.class}
 SET \{set(record)}
 WHERE \{where(record)}

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that a SET element is required based on its placement in the query: 

      
 UPDATE \{MyTable.class}
 SET \{record}
 WHERE \{record}

      Here, record is an instance of the Data class containing the values to be set.

      Parameters:
      record - the Data instance containing the values to be set.
      fields - the fields to update.
      Returns:
      an Element representing the SET clause with the specified record.
      Since:
      1.7

    • set

      static Element set(@Nonnull BindVars bindVars)
      Generates a SET clause using the specified BindVars.

      This method creates a SET clause that utilizes a BindVars instance, allowing for batch updates using bind variables. This is particularly useful when performing batch operations where the same update query is executed multiple times with different variable values.

      Example usage in a batch update scenario: 

      
 var bindVars = orm.createBindVars();
 try (var query = orm.query(RAW."""
         UPDATE \{MyTable.class}
         SET \{set(bindVars)}
         WHERE \{where(bindVars)}""").prepare()) {
     records.forEach(query::addBatch);
     query.executeBatch();
 }

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that an UPDATE element is required based on its placement in the query: 

      
 UPDATE \{MyTable.class}
 SET \{record}
 WHERE \{record}

      In this example, bindVars is a BindVars instance created by the ORM. The records are iterated over, and each is added to the batch. The query is then executed as a batch operation.

      Parameters:
      bindVars - the BindVars instance used for batch updates.
      Returns:
      an Element representing the SET clause utilizing the specified bind variables.

    • set

      static Element set(@Nonnull BindVars bindVars, @Nonnull Collection<Metamodel<?,?>> fields)
      Generates a SET clause using the specified BindVars.

      This method creates a SET clause that utilizes a BindVars instance, allowing for batch updates using bind variables. This is particularly useful when performing batch operations where the same update query is executed multiple times with different variable values.

      Example usage in a batch update scenario: 

      
 var bindVars = orm.createBindVars();
 try (var query = orm.query(RAW."""
         UPDATE \{MyTable.class}
         SET \{set(bindVars)}
         WHERE \{where(bindVars)}""").prepare()) {
     records.forEach(query::addBatch);
     query.executeBatch();
 }

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that an UPDATE element is required based on its placement in the query: 

      
 UPDATE \{MyTable.class}
 SET \{record}
 WHERE \{record}

      In this example, bindVars is a BindVars instance created by the ORM. The records are iterated over, and each is added to the batch. The query is then executed as a batch operation.

      Parameters:
      bindVars - the BindVars instance used for batch updates.
      fields - the fields to update.
      Returns:
      an Element representing the SET clause utilizing the specified bind variables.
      Since:
      1.7

    • where

      static Element where(@Nonnull Iterable<?> it)
      Generates a WHERE clause based on the provided iterable of values or records.

      This method creates a WHERE clause that matches the primary key(s) of the root table, a record instance of the root table, or foreign key(s) in the hierarchy against the provided records using the IN operator. It is useful when you want to select records where the primary key, a specific record, or related foreign keys match any of the values in the iterable.

      The objects in the iterable can be:

      • Primitive values matching the primary key of the root table.
      • Instances of Record matching the compound primary key of the root table.
      • Instances of Data representing records of related (foreign key) tables in the hierarchy of the root table.

      Example usage with primary key values: 

      
 SELECT \{MyTable.class}
 FROM \{MyTable.class}
 WHERE \{where(listOfIds)}

      Example usage with records: 

      
 List<Table> entities = List.of(entity1, entity2);
 SELECT \{MyTable.class}
 FROM \{MyTable.class}
 WHERE \{where(entities)}

      In this example, the query selects all entries in Table that are linked to any of the otherTables in the list.

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that a WHERE element is required based on its placement in the query: 

      
 List<Table> entities = List.of(entity1, entity2);
 SELECT \{MyTable.class}
 FROM \{MyTable.class}
 WHERE \{entities}

      As per the resolution rules:

      • If \{entities}, or any other primitive or object, is placed after WHERE the SQL template engine resolves it into a WHERE element.
      • If \{entities}, or any other primitive or object, is placed after keywords like VALUES, SET, the SQL template engine resolves it into the appropriate element (e.g., VALUES element, SET element).
      • If \{entities} is not in such a placement, it is resolved into a param element.
      Parameters:
      it - an Iterable of values or records to match against the primary key(s) or foreign keys.
      Returns:
      an Element representing the WHERE clause.

    • where

      static Element where(@Nonnull Object o)
      Generates a WHERE clause based on the provided value or record.

      This method creates a WHERE clause that matches the primary key(s) of the root table, a record instance of the root table, or foreign key(s) in the hierarchy against the provided records using the EQUALS operator. It is useful when you want to select records where the primary key, a specific record, or related foreign keys match the object.

      The object can be:

      • Primitive values matching the primary key of the root table.
      • Instances of Record matching the compound primary key of the root table.
      • Instances of Data representing records of related (foreign key) tables in the hierarchy of the root table.

      Example usage with a primary key value: 

      
 SELECT \{MyTable.class}
 FROM \{MyTable.class}
 WHERE \{where(id)}

      Example usage with a record: 

      
 Table entity = ...;
 SELECT \{MyTable.class}
 FROM \{MyTable.class}
 WHERE \{where(otherTable)}

      In this example, the query selects all entries in Table that are linked to the specified otherTable.

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that a WHERE element is required based on its placement in the query: 

      
 Table entity = ...;
 SELECT \{MyTable.class}
 FROM \{MyTable.class}
 WHERE \{entity}

      As per the resolution rules:

      • If \{entity}, or any other primitive or object, is placed after WHERE the SQL template engine resolves it into a WHERE element.
      • If \{entity}, or any other primitive or object, is placed after keywords like VALUES, SET, the SQL template engine resolves it into the appropriate element (e.g., VALUES element, SET element).
      • If \{entity} is not in such a placement, it is resolved into a param element.
      Parameters:
      o - the value or record to match against the primary key or foreign key.
      Returns:
      an Element representing the WHERE clause.

    • where

      static <V> Element where(@Nonnull Metamodel<?,V> path, @Nonnull Operator operator, @Nonnull Iterable<? extends V> it)
      Generates a WHERE clause based on the provided path, operator, and iterable of values or records.

      This method creates a WHERE clause for the specified column or path using the given operator and values or records. The path parameter specifies the column or property to apply the condition on, which can include nested properties using dot notation.

      The objects in the iterable must match the type of the record component found at the specified path. If the path points to a record, the objects may also match the primary key type of that record.

      Example usage with primary keys: 

      
 SELECT \{MyTable.class}
 FROM \{MyTable.class}
 WHERE \{where(MyTable_.otherTable.id, Operator.IN, listOfIds)}

      In this example, listOfIds contains the primary key values of the MyTable records, and the query selects all entries in Table linked to that MyTable.

      Example usage with records: 

      
 List<MyTable> entities = ...;
 SELECT \{MyTable.class}
 FROM \{MyTable.class}
 WHERE \{where(MyTable_.otherTable, Operator.IN, entities)}

      In this example, entities is a list of MyTable records. The query matches entries in Table linked to any of the records in the list via their foreign keys.

      Parameters:
      path - the path or column name to apply the condition on.
      operator - the Operator to use in the condition.
      it - an Iterable of values or records for the condition.
      Returns:
      an Element representing the WHERE clause.

    • where

      @SafeVarargs static <V> Element where(@Nonnull Metamodel<?,V> path, @Nonnull Operator operator, @Nonnull V... o)
      Generates a WHERE clause based on the provided path, operator, and values or records.

      This method creates a WHERE clause for the specified column or path using the given operator and values or records. The path parameter specifies the column or property to apply the condition on, which can include nested properties using dot notation.

      The objects in the array must match the type of the record component found at the specified path. If the path points to a record, the objects may also match the primary key type of that record.

      Example usage with primary keys: 

      
 SELECT \{MyTable.class}
 FROM \{MyTable.class}
 WHERE \{where(MyTable_.otherTable.id, Operator.BETWEEN, 1, 10)}

      In this example, the query selects all entries in Table where the associated MyTable records have primary keys between 1 and 10.

      Parameters:
      path - the path or column name to apply the condition on.
      operator - the Operator to use in the condition.
      o - the values or records for the condition.
      Returns:
      an Element representing the WHERE clause.

    • where

      static Element where(@Nonnull BindVars bindVars)
      Generates a WHERE clause using the specified BindVars for batch operations.

      This method is particularly useful when performing batch operations where the same query is executed multiple times with different parameter values. The BindVars instance allows for parameterized queries, enhancing performance and security by preventing SQL injection and enabling query plan reuse.

      Example usage in a batch operation: 

      
 var bindVars = orm.createBindVars();
 try (var query = orm.query(RAW."""
         UPDATE \{MyTable.class}
         SET \{bindVars}
         WHERE \{where(bindVars)}""").prepare()) {
     records.forEach(query::addBatch);
     query.executeBatch();
 }

      In this example, the bindVars instance is used to bind variables for the WHERE clause in a batch operation. Each record in records provides the parameter values for a single execution of the query.

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that a WHERE element is required based on its placement in the query: 

      
 UPDATE \{MyTable.class}
 SET \{bindVars}
 WHERE \{bindVars}
      Parameters:
      bindVars - the BindVars instance used for binding variables in the WHERE clause; must not be null.
      Returns:
      an Element representing the WHERE clause utilizing the specified bind variables.

    • delete

      static Element delete(@Nonnull Class<? extends Data> table)
      Generates a DELETE element for the specified table class.

      This method creates a DELETE clause for the provided table record. It is designed to be used within SQL string templates to dynamically construct DELETE queries based on the table's structure.

      Example usage in a string template: 

      
 DELETE \{delete(MyTable.class)} FROM \{from(MyTable.class)}
 WHERE \{where(record)}

      For convenience, you can also use the shorthand notation. The SQL template engine automatically detects that a DELETE element is required based on its placement in the query: 

      
 DELETE \{MyTable.class} FROM \{MyTable.class}
 WHERE \{record}

      Here, record is an instance of the Data class containing the criteria for deletion.

      Note: In most databases, specifying the table in the DELETE clause is not necessary, or even disallowed; the DELETE statement is usually constructed with only a FROM clause:

      
 DELETE FROM \{from(MyTable.class)}
 WHERE \{where(record)}
      Parameters:
      table - the Class object representing the table record.
      Returns:
      an Element representing the DELETE clause for the specified table.

    • delete

      static Element delete(@Nonnull Class<? extends Data> table, @Nonnull String alias)
      Generates a DELETE element for the specified table class with an alias.

      This method creates a DELETE clause for the provided table record, applying the specified alias. It is designed to be used within SQL string templates to dynamically construct DELETE queries based on the table's structure.

      Example usage in a string template: 

      
 DELETE \{delete(MyTable.class, "t")} FROM \{from(MyTable.class, "t")}
 WHERE \{where(record)}

      Here, record is an instance of the Data class containing the criteria for deletion.

      Note: In most databases, specifying the table in the DELETE clause with an alias is not necessary; the DELETE statement can be constructed with only a FROM clause and an alias:

      
 DELETE FROM \{from(MyTable.class, "t")}
 WHERE \{where(record)}
      Parameters:
      table - the Class object representing the table record.
      alias - the alias to use for the table in the query. The alias must not require escaping.
      Returns:
      an Element representing the DELETE clause for the specified table with an alias.

    • table

      static Element table(@Nonnull Class<? extends Data> table)
      Generates a Table element for the specified table class.

      This method creates a representation of a database table, which can be used in SQL string templates to refer to the table in queries. It is useful when you need to explicitly specify the table in parts of your query where the SQL template engine does not automatically resolve the table based on context.

      Example usage in a string template: 

      
 SELECT * FROM \{table(MyTable.class)}

      For convenience, you can also use the shorthand notation. If the SQL template engine cannot resolve \{MyTable.class} into a specific element based on its placement in the query (e.g., after SELECT, FROM, etc.), it will default to creating a Table element: 

      
 ... \{MyTable.class} ...

      However, if \{MyTable.class} is followed by a dot '.', the SQL template engine will resolve it into an alias element, representing the alias of the table in the query: 

      
 SELECT \{MyTable.class}.column_name FROM \{MyTable.class}

      As per the resolution rules:

      • If \{MyTable.class} is placed after keywords like SELECT, FROM, INSERT INTO, UPDATE, or DELETE, the SQL template engine resolves it into the appropriate element (e.g., SELECT element, FROM element).
      • If \{MyTable.class} is not in such a placement and is not followed by a dot '.', it is resolved into a table element.
      • If \{MyTable.class} is followed by a dot '.', it is resolved into an alias element.
      Parameters:
      table - the Class object representing the table record.
      Returns:
      an Element representing the table.

    • table

      static Element table(@Nonnull Class<? extends Data> table, @Nonnull String alias)
      Generates a Table element with an alias for the specified table class.

      This method creates a representation of a database table with the specified alias, which can be used in SQL string templates to refer to the table in queries. This is useful when you need to assign an alias to a table for use in your query.

      Example usage in a string template: 

      
 SELECT * FROM \{table(MyTable.class, "t")}

      You can refer to the table alias in your query as follows: 

      
 SELECT \{alias(MyTable.class)}.column_name FROM \{table(MyTable.class, "t")}
      Parameters:
      table - the Class object representing the table record.
      alias - the alias to use for the table in the query. The alias must not require escaping.
      Returns:
      an Element representing the table with an alias.

    • alias

      static Element alias(@Nonnull Class<? extends Data> table)
      Generates an alias element for the specified table class.

      This method returns the alias of the table as used in the query. It is useful when you need to refer to the table's alias, especially in situations where the SQL template engine cannot automatically determine the appropriate element based on context.

      Example usage in a string template: 

      
 SELECT \{alias(MyTable.class)}.column_name FROM \{table(MyTable.class, "t")}

      According to the resolution rules, if \{MyTable.class} is followed by a dot '.', the SQL template engine automatically resolves it into an alias element: 

      
 SELECT \{MyTable.class}.column_name FROM \{MyTable.class}

      As per the resolution rules:

      • If \{MyTable.class} is placed after keywords like SELECT, FROM, INSERT INTO, UPDATE, or DELETE, the SQL template engine resolves it into the appropriate element (e.g., SELECT element, FROM element).
      • If \{MyTable.class} is not in such a placement and is not followed by a dot '.', it is resolved into a table element.
      • If \{MyTable.class} is followed by a dot '.', it is resolved into an alias element.
      Parameters:
      table - the Class object representing the table record.
      Returns:
      an Element representing the table's alias.

    • alias

      static Element alias(@Nonnull Class<? extends Data> table, @Nonnull ResolveScope scope)
      Generates an alias element for the specified table class.

      This method returns the alias of the table as used in the query. It is useful when you need to refer to the table's alias, especially in situations where the SQL template engine cannot automatically determine the appropriate element based on context.

      Example usage in a string template: 

      
 SELECT \{alias(MyTable.class)}.column_name FROM \{table(MyTable.class, "t")}

      According to the resolution rules, if \{MyTable.class} is followed by a dot '.', the SQL template engine automatically resolves it into an alias element: 

      
 SELECT \{MyTable.class}.column_name FROM \{MyTable.class}

      As per the resolution rules:

      • If \{MyTable.class} is placed after keywords like SELECT, FROM, INSERT INTO, UPDATE, or DELETE, the SQL template engine resolves it into the appropriate element (e.g., SELECT element, FROM element).
      • If \{MyTable.class} is not in such a placement and is not followed by a dot '.', it is resolved into a table element.
      • If \{MyTable.class} is followed by a dot '.', it is resolved into an alias element.
      Parameters:
      table - the Class object representing the table record.
      scope - the ResolveScope to use when resolving the alias. Use CASCADE to include local and outer aliases, LOCAL to include local aliases only, and OUTER to include outer aliases only.
      Returns:
      an Element representing the table's alias.

    • column

      static Element column(@Nonnull Metamodel<?,?> path)
      Generates a column element for a column specified by the given metamodel in a type safe manner.

      The path is constructed by concatenating the names of the fields that lead to the target table from the root table. The componentName is the name of the record component that is mapped to the database column. If a record uses inline records, the componentName is also constructed by concatenating the fields leading to the record component.

      Example usage in a string template where MyTable is referenced twice: 

      
 // Define a record with two references to MyTable
 record Table(int id, MyTable child, MyTable parent) {}

 // In the SQL template
 SELECT \{column(MyTable_.child.name)} FROM \{MyTable.class}

      In this example, the path "child" specifies that we are referring to the child field of the Table record, which is of type MyTable. This distinguishes it from the parent field, which is also of type MyTable. The "name" componentName refers to the name record component of MyTable.

      Parameters:
      path - specifies the database column for which the column is to be generated.
      Returns:
      an Element representing the table's column with the specified path.
      Since:
      1.2

    • column

      static Element column(@Nonnull Metamodel<?,?> path, @Nonnull ResolveScope scope)
      Generates a column element for a column specified by the given metamodel in a type safe manner.

      The path is constructed by concatenating the names of the fields that lead to the target table from the root table. The componentName is the name of the record component that is mapped to the database column. If a record uses inline records, the componentName is also constructed by concatenating the fields leading to the record component.

      Example usage in a string template where MyTable is referenced twice: 

      
 // Define a record with two references to MyTable
 record Table(int id, MyTable child, MyTable parent) {}

 // In the SQL template
 SELECT \{column(MyTable_.child.name)} FROM \{MyTable.class}

      In this example, the path "child" specifies that we are referring to the child field of the Table record, which is of type MyTable. This distinguishes it from the parent field, which is also of type MyTable. The "name" componentName refers to the name record component of MyTable.

      Parameters:
      path - specifies the database column for which the column is to be generated.
      scope - the ResolveScope to use when resolving the alias. Use CASCADE to include local and outer aliases, LOCAL to include local aliases only, and OUTER to include outer aliases only.
      Returns:
      an Element representing the table's column with the specified path.
      Since:
      1.2

    • param

      static Element param(@Nullable Object value)
      Generates a parameter element for the specified value, to be used in SQL queries.

      This method creates a positional parameter for use in SQL string templates. The parameter can be of any object type and may be null. It is intended to be used in places where you need to bind a value to a SQL query parameter.

      Example usage in a string template: 

      
 SELECT *
 FROM \{MyTable.class}
 WHERE status = \{param(1)}

      For convenience, you can also use the shorthand notation, where the SQL template engine automatically detects that a parameter is required: 

      
 SELECT *
 FROM \{MyTable.class}
 WHERE status = \{1}

      As per the resolution rules:

      • If \{1}, or any other primitive or object, is placed after keywords like VALUES, SET or WHERE, the SQL template engine resolves it into the appropriate element (e.g., VALUES element, SET element).
      • If \{1} is not in such a placement, it is resolved into a param element.
      Parameters:
      value - the value to be used as a parameter in the SQL query; may be null.
      Returns:
      an Element representing the parameter.

    • param

      static Element param(@Nonnull String name, @Nullable Object value)
      Generates a named parameter element for the specified value, to be used in SQL queries.

      This method creates a named parameter for use in SQL string templates. Named parameters are useful when you want to explicitly specify the parameter's name in the query, improving readability and maintainability, especially in complex queries.

      Example usage in a string template: 

      
 SELECT *
 FROM \{MyTable.class}
 WHERE status = \{param("status", 1)}

      In the query, the parameter will be referred to by its name status.

      Parameters:
      name - the name of the parameter; must not be null.
      value - the value to be used as a parameter in the SQL query; may be null.
      Returns:
      an Element representing the named parameter.

    • param

      static <P> Element param(@Nullable P value, @Nonnull Function<? super P,?> converter)
      Generates a parameter element for the specified value with a converter function.

      This method allows you to provide a custom converter function to transform the value into a database-compatible format. This is useful when the value needs to be converted before being set as a parameter in the SQL query, such as formatting dates or custom objects.

      Example usage in a string template: 

      
 SELECT *
 FROM \{MyTable.class}
 WHERE created_at = \{param(dateValue, date -> new java.sql.Date(date.getTime()))}
      Type Parameters:
      P - the type of the value to be converted.
      Parameters:
      value - the value to be used as a parameter in the SQL query; may be null.
      converter - a Function that converts the value to a database-compatible format; must not be null.
      Returns:
      an Element representing the parameter with a converter applied.

    • param

      static <P> Element param(@Nonnull String name, @Nullable P value, @Nonnull Function<? super P,?> converter)
      Generates a named parameter element for the specified value with a converter function.

      This method allows you to provide a custom converter function to transform the value into a database-compatible format and assign a name to the parameter. Named parameters enhance query readability and are particularly useful in complex queries.

      Example usage in a string template: 

      
 SELECT *
 FROM \{MyTable.class}
 WHERE created_at = \{param("createdAt", dateValue, date -> new java.sql.Date(date.getTime()))}
      Type Parameters:
      P - the type of the value to be converted.
      Parameters:
      name - the name of the parameter; must not be null.
      value - the value to be used as a parameter in the SQL query; may be null.
      converter - a Function that converts the value to a database-compatible format; must not be null.
      Returns:
      an Element representing the named parameter with a converter applied.

    • param

      static Element param(@Nonnull Date value, @Nonnull TemporalType temporalType)
      Generates a parameter element for the specified Date value with a temporal type.

      This method creates a positional parameter for a Date value, converting it to the appropriate SQL type based on the provided TemporalType. It is useful when you need to specify how the date should be interpreted in the database (as DATE, TIME, or TIMESTAMP).

      Example usage in a string template: 

      
 SELECT *
 FROM \{MyTable.class}
 WHERE event_date = \{param(dateValue, TemporalType.DATE)}
      Parameters:
      value - the Date value to be used as a parameter; must not be null.
      temporalType - the TemporalType specifying how the date should be handled; must not be null.
      Returns:
      an Element representing the date parameter with the specified temporal type.

    • param

      static Element param(@Nonnull String name, @Nonnull Date value, @Nonnull TemporalType temporalType)
      Generates a named parameter element for the specified Date value with a temporal type.

      This method creates a named parameter for a Date value, converting it to the appropriate SQL type based on the provided TemporalType. Named parameters improve query readability and are especially useful in complex queries.

      Example usage in a string template: 

      
 SELECT *
 FROM \{MyTable.class}
 WHERE event_date = \{param("eventDate", dateValue, TemporalType.DATE)}
      Parameters:
      name - the name of the parameter; must not be null.
      value - the Date value to be used as a parameter; must not be null.
      temporalType - the TemporalType specifying how the date should be handled; must not be null.
      Returns:
      an Element representing the named date parameter with the specified temporal type.

    • param

      static Element param(@Nonnull Calendar value, @Nonnull TemporalType temporalType)
      Generates a parameter element for the specified Calendar value with a temporal type.

      This method creates a positional parameter for a Calendar value, converting it to the appropriate SQL type based on the provided TemporalType. It is useful when working with calendar instances that need to be interpreted in the database as specific temporal types.

      Example usage in a string template: 

      
 SELECT *
 FROM \{MyTable.class}
 WHERE event_time = \{param(calendarValue, TemporalType.TIMESTAMP)}
      Parameters:
      value - the Calendar value to be used as a parameter; must not be null.
      temporalType - the TemporalType specifying how the calendar should be handled; must not be null.
      Returns:
      an Element representing the calendar parameter with the specified temporal type.

    • param

      static Element param(@Nonnull String name, @Nonnull Calendar value, @Nonnull TemporalType temporalType)
      Generates a named parameter element for the specified Calendar value with a temporal type.

      This method creates a named parameter for a Calendar value, converting it to the appropriate SQL type based on the provided TemporalType. Named parameters enhance query readability and are particularly useful in complex queries.

      Example usage in a string template: 

      
 SELECT *
 FROM \{MyTable.class}
 WHERE event_time = \{param("eventTime", calendarValue, TemporalType.TIMESTAMP)}
      Parameters:
      name - the name of the parameter; must not be null.
      value - the Calendar value to be used as a parameter; must not be null.
      temporalType - the TemporalType specifying how the calendar should be handled; must not be null.
      Returns:
      an Element representing the named calendar parameter with the specified temporal type.

    • bindVar

      static Element bindVar(@Nonnull BindVars bindVars, @Nonnull Function<Data,?> extractor)
      Creates a new var element that can be used to specify individual bind variables in the query.
      Parameters:
      bindVars - the bind variables instance used for parameter binding.
      extractor - the function used to extract the value from the record for the bind variable.
      Returns:
      a new Element representing the bind variable.

    • subquery

      static Element subquery(@Nonnull QueryBuilder<?,?,?> builder, boolean correlate)
      Creates a new subquery element using a query builder.
      Parameters:
      builder - the query builder used to construct the subquery; must not be null.
      correlate - a flag indicating whether the subquery should correlate with the outer query. If true, the subquery can reference elements from the outer query. If false, the subquery is independent and does not access the outer query.
      Returns:
      a new Subquery element based on the provided query builder and correlation flag.

    • subquery

      static Element subquery(@Nonnull StringTemplatePREVIEW template, boolean correlate)
      Creates a new subquery element using a string template.
      Parameters:
      template - the string template representing the subquery; must not be null.
      correlate - a flag indicating whether the subquery should correlate with the outer query. If true, the subquery can reference elements from the outer query. If false, the subquery is independent and does not access the outer query.
      Returns:
      a new Subquery element based on the provided template and correlation flag.

    • unsafe

      static Element unsafe(@Nonnull String sql)
      Injects raw SQL into the query without any processing or sanitization.

      This method allows you to insert arbitrary SQL code directly into your query. It bypasses any automatic parameter binding or SQL generation provided by the SQL template engine. As a result, it can be potentially unsafe and may expose your application to SQL injection attacks if not used carefully.

      Warning: Use this method only when you are certain that the SQL string being injected is safe and originates from a trusted source. Avoid using user-supplied input with this method.

      Example usage in a string template: 

      
 SELECT * FROM \{User.class} WHERE \{unsafe("city = 'Sunnyvale'")}

      In this example, the SQL fragment "city = 'Sunnyvale'" is injected directly into the query.

      Parameters:
      sql - the raw SQL string to inject into the query.
      Returns:
      an Element that represents the raw SQL code to be inserted into the query.