Interface SqlTemplate<I,R>

public interface SqlTemplate<I,R>
An SQL template.

SQL templates are useful for interacting with a relational database.

SQL templates execute queries using named instead of positional parameters. Query execution is parameterized by a map of string to objects instead of a Tuple. The default source of parameters is a simple map, a user defined mapping can be used instead given it maps the source to such a map.

SQL template default results are Row, a user defined mapping can be used instead, mapping the result set Row to a RowSet of the mapped type.

  • Method Details

    • forQuery

      static SqlTemplate<Map<String,Object>, RowSet<Row>> forQuery(SqlClient client, String template)
      Create an SQL template for query purpose consuming map parameters and returning Row.
      Parameters:
      client - the wrapped SQL client
      template - the template query string
      Returns:
      the template

    • forUpdate

      static SqlTemplate<Map<String,Object>, SqlResult<Void>> forUpdate(SqlClient client, String template)
      Create an SQL template for query purpose consuming map parameters and returning void.
      Parameters:
      client - the wrapped SQL client
      template - the template update string
      Returns:
      the template

    • sql

      String sql()
      Returns:
      the computed SQL for this template

    • mapFrom

      <T> SqlTemplate<T,R> mapFrom(TupleMapper<T> mapper)
      Set a parameters user defined mapping function.

      At query execution, the mapper is called to map the parameters object to a Tuple that configures the prepared query.

      Parameters:
      mapper - the mapping function
      Returns:
      a new template

    • mapFrom

      default <T> SqlTemplate<T,R> mapFrom(Class<T> type)
      Set a parameters user defined class mapping.

      At query execution, the parameters object is is mapped to a Map<String, Object> that configures the prepared query.

      This feature relies on JsonObject.mapFrom(Object) feature. This likely requires to use Jackson databind in the project.

      Parameters:
      type - the mapping type
      Returns:
      a new template

    • mapTo

      <U> SqlTemplate<I, RowSet<U>> mapTo(RowMapper<U> mapper)
      Set a row user defined mapping function.

      When the query execution completes, the mapper function is called to map the resulting rows to objects.

      Parameters:
      mapper - the mapping function
      Returns:
      a new template

    • mapTo

      <U> SqlTemplate<I, RowSet<U>> mapTo(Class<U> type)
      Set a row user defined mapping function.

      When the query execution completes, resulting rows are mapped to type instances.

      This feature relies on JsonObject.mapFrom(Object) feature. This likely requires to use Jackson databind in the project.

      Parameters:
      type - the mapping type
      Returns:
      a new template

    • collecting

      <U> SqlTemplate<I, SqlResult<U>> collecting(Collector<Row,?,U> collector)
      Set a collector that will process the output and produce a custom result.
      Parameters:
      collector - the collector
      Returns:
      a new template

    • withClient

      SqlTemplate<I,R> withClient(SqlClient client)
      Returns a new template, using the specified client.

      This method does not compute the template query again, so it can be useful to execute a template on a specific SqlConnection. For example, after starting a transaction: 

        // Typically stored as a verticle field
  // So that heavy computation of the template happens once
  SqlTemplateinvalid input: '<'Mapinvalid input: '<'String, Object>, RowSet> template = SqlTemplate
    .forQuery(pool, "SELECT id, randomnumber FROM tmp_world")
    .mapTo(World.class);

  // Executing the template inside a transaction
  Futureinvalid input: '<'RowSet> future = pool.withTransaction(conn -> template.withClient(conn).execute(Map.of()));
      Parameters:
      client - the client that will execute requests
      Returns:
      a new template

    • execute

      Future<R> execute(I params)
      Execute the query with the parameters
      Parameters:
      params - the query parameters
      Returns:
      a future notified with the result

    • executeBatch

      Future<R> executeBatch(List<I> batch)
      Execute a batch query with the batch.

      Each item in the batch is mapped to a tuple.

      Parameters:
      batch - the batch
      Returns:
      a future notified with the result