index.adoc

SQL Client Templates

Table of Contents

• Usage
• Getting started
• Streaming
• Cursor
• Template syntax
• Row mapping
• Anemic JSON row mapping
• Parameters mapping
• Anemic JSON parameters mapping
• Mapping with Jackson databind
  • Java Date/Time API mapping
• Mapping with Vert.x data objects
  • Code generation
  • Row mapping
  • Parameters mapping
  • Java enum types mapping
  • Naming format

SQL Client Templates is a small library designed to facilitate the execution of SQL queries.

Usage

To use SQL Client Templates add the following dependency to the dependencies section of your build descriptor:

• Maven (in your pom.xml):

<dependency>
  <groupId>${maven.groupId}</groupId>
  <artifactId>${maven.artifactId}</artifactId>
  <version>${maven.version}</version>
</dependency>

• Gradle (in your build.gradle file):

dependencies {
  implementation '${maven.groupId}:${maven.artifactId}:${maven.version}'
}

Getting started

Here is the simplest way to use an SQL template.

A SQL template consumes named parameters and thus takes (by default) a map as parameters sources instead of a tuple.

A SQL template produces (by default) a RowSet<Row> like a client PreparedQuery. In fact the template is a thin wrapper for a PreparedQuery.

{@link examples.TemplateExamples#queryExample}

When you need to perform an insert or update operation, and you do not care of the result, you can use {@link io.vertx.sqlclient.templates.SqlTemplate#forUpdate} instead:

{@link examples.TemplateExamples#insertExample}

Tip

Creating a template instance with {@link io.vertx.sqlclient.templates.SqlTemplate#forQuery} or {@link io.vertx.sqlclient.templates.SqlTemplate#forUpdate} involves parsing the query, and that doesn’t come for free. To avoid paying the price of computing the actual query repeatedly, consider reusing your template instances. Typically, you will store an instance as a verticle field.

Tip	When your template must be executed inside a transaction, you might create a temporary instance using {@link io.vertx.sqlclient.templates.SqlTemplate#withClient}: {@link examples.TemplateExamples#templateInTransaction}

Streaming

When dealing with large result sets, you can use {@link io.vertx.sqlclient.templates.SqlTemplateStream} to read rows progressively using a cursor with a configurable fetch size, instead of loading all rows in memory at once.

Note	Streaming requires a {@link io.vertx.sqlclient.SqlConnection}. Some databases (e.g. PostgreSQL) also require an active transaction for cursors.

{@link examples.TemplateExamples#streamExample}

You can use mapTo to map each row emitted by the stream to a custom type:

{@link examples.TemplateExamples#streamWithMapToExample}

Cursor

If you need finer control over row fetching, you can use a cursor-based template with {@link io.vertx.sqlclient.templates.SqlTemplate#forCursor}. This gives you a {@link io.vertx.sqlclient.Cursor} that allows you to read rows in batches.

Note	Cursors require a {@link io.vertx.sqlclient.SqlConnection}. Some databases (e.g. PostgreSQL) also require an active transaction for cursors.

{@link examples.TemplateExamples#cursorExample}

Template syntax

The template syntax uses #{XXX} syntax where XXX is a valid java identifier string (without the keyword restriction).

You can use the backslash char \ to escape any character, i.e. \{foo} will be interpreted as #{foo} string without a foo parameter.

Row mapping

By default, templates produce {@link io.vertx.sqlclient.Row} as result type.

You can provide a custom {@link io.vertx.sqlclient.templates.RowMapper} to achieve row level mapping instead:

{@link examples.TemplateExamples#rowUserMapper}

to achieve row level mapping instead:

{@link examples.TemplateExamples#bindingRowWithCustomMapper}

Anemic JSON row mapping

Anemic JSON row mapping is a trivial mapping between template row columns and a JSON object using the {@link io.vertx.sqlclient.Row#toJson()}

{@link examples.TemplateExamples#bindingRowWithAnemicJsonMapper}

Parameters mapping

Templates consume Map<String, Object> as default input.

You can provide a custom mapper:

{@link examples.TemplateExamples#paramsUserMapper}

to achieve parameter mapping instead:

{@link examples.TemplateExamples#bindingParamsWithCustomMapper}

You can also perform batching easily:

{@link examples.TemplateExamples#batchBindingParamsWithCustomMapper}

Anemic JSON parameters mapping

Anemic JSON parameters mapping is a trivial mapping between template parameters and a JSON object:

{@link examples.TemplateExamples#bindingParamsWithAnemicJsonMapper}

Mapping with Jackson databind

You can do mapping using Jackson databind capabilities.

You need to add the Jackson databind dependency to the dependencies section of your build descriptor:

• Maven (in your pom.xml):

<dependency>
  <groupId>com.fasterxml.jackson.core</groupId>
  <artifactId>jackson-databind</artifactId>
  <version>${jackson.version}</version>
</dependency>

• Gradle (in your build.gradle file):

dependencies {
  compile 'com.fasterxml.jackson.core:jackson-databind:${jackson.version}'
}

Row mapping is achieved by creating a JsonObject using the row key/value pairs and then calling {@link io.vertx.core.json.JsonObject#mapTo} to map it to any Java class with Jackson databind.

{@link examples.TemplateExamples#bindingRowWithJacksonDatabind}

Likewise, parameters mapping is achieved by mapping the object to a JsonObject using {@link io.vertx.core.json.JsonObject#mapFrom} and then using the key/value pairs to produce template parameters.

{@link examples.TemplateExamples#bindingParamsWithJacksonDatabind}

Java Date/Time API mapping

You can map java.time types with the jackson-modules-java8 Jackson extension.

You need to add the Jackson JSR 310 datatype dependency to the dependencies section of your build descriptor:

• Maven (in your pom.xml):

<dependency>
  <groupId>com.fasterxml.jackson.datatype</groupId>
  <artifactId>jackson-datatype-jsr310</artifactId>
  <version>${jackson.version}</version>
</dependency>

• Gradle (in your build.gradle file):

dependencies {
  compile 'com.fasterxml.jackson.datatype:jackson-datatype-jsr310:${jackson.version}'
}

Then you need to register the time module to the Jackson ObjectMapper:

ObjectMapper mapper = io.vertx.core.json.jackson.DatabindCodec.mapper();

mapper.registerModule(new JavaTimeModule());

You can now use java.time types such as LocalDateTime:

public class LocalDateTimePojo {

  public LocalDateTime localDateTime;

}

Mapping with Vert.x data objects

The SQL Client Templates component can generate mapping function for Vert.x data objects.

A Vert.x data object is a simple Java bean class annotated with the @DataObject annotation.

{@link examples.TemplateExamples#baseDataObject}

Code generation

Any data object annotated by {@link io.vertx.sqlclient.templates.annotations.RowMapped} or {@link io.vertx.sqlclient.templates.annotations.ParametersMapped} will trigger the generation of a corresponding mapper class.

The codegen annotation processor generates these classes at compilation time. It is a feature of the Java compiler so no extra step is required, it is just a matter of configuring correctly your build.

Add the io.vertx:vertx-codegen:processor and io.vertx:${maven.artifactId} dependencies to your build.

Here a configuration example for Maven:

<dependency>
  <groupId>io.vertx</groupId>
  <artifactId>vertx-codegen</artifactId>
  <version>${maven.version}</version>
  <classifier>processor</classifier>
</dependency>
<dependency>
  <groupId>io.vertx</groupId>
  <artifactId>${maven.artifactId}</artifactId>
  <version>${maven.version}</version>
</dependency>

This feature can also be used in Gradle:

annotationProcessor "io.vertx:vertx-codegen:${maven.version}:processor"
annotationProcessor "io.vertx:vertx-sql-client-templates:${maven.version}"
compile "io.vertx:${maven.artifactId}:${maven.version}"
compile "io.vertx:vertx-codegen-json:${maven.version}"

IDEs usually provide support for annotation processors.

The codegen processor classifier adds to the jar the automatic configuration of the service proxy annotation processor via the META-INF/services plugin mechanism.

If you want you can use it too with the regular jar, but you need then to declare the annotation processor explicitly, for instance in Maven:

<plugin>
  <artifactId>maven-compiler-plugin</artifactId>
  <configuration>
    <annotationProcessors>
      <annotationProcessor>io.vertx.codegen.CodeGenProcessor</annotationProcessor>
    </annotationProcessors>
  </configuration>
</plugin>

Important

The codegen processor requires a package-info.java file annotated with @io.vertx.codegen.annotations.ModuleGen at the root of the package of the annotated classes. Here’s an example: package-info.java file @ModuleGen(name = "templates", groupPackage = "org.acme") package org.acme.templates; import io.vertx.codegen.annotations.ModuleGen;

Row mapping

You can generate a row mapper by annotating your data object by {@link io.vertx.sqlclient.templates.annotations.RowMapped}.

{@link examples.TemplateExamples#rowMappedDataObject}

By default, each column name is bound after the data object properties, e.g. the userName property binds to the userName column.

You can use custom names thanks to the {@link io.vertx.sqlclient.templates.annotations.Column} annotation.

{@link examples.TemplateExamples#rowMappedDataObjectOverrideName}

You can annotate the field, the getter or the setter.

The generated mapper can be used to perform row mapping like explained in row mapping chapter.

{@link examples.TemplateExamples#bindingRowWithRowMapper}

Parameters mapping

You can generate a parameters mapper by annotating your data object by {@link io.vertx.sqlclient.templates.annotations.ParametersMapped}.

{@link examples.TemplateExamples#paramsMappedDataObject}

By default, each parameter is bound after the data object properties, e.g. the userName property binds to the userName parameter.

You can use custom names thanks to the {@link io.vertx.sqlclient.templates.annotations.TemplateParameter} annotation.

{@link examples.TemplateExamples#paramsMappedDataObjectOverrideName}

You can annotate the field, the getter or the setter.

The generated mapper can be used to perform param mapping like explained in parameter mapping chapter.

{@link examples.TemplateExamples#bindingParamsWithParamsMapper}

Java enum types mapping

You can map Java enum types when the client supports it (e.g. the Reactive PostgreSQL client).

Usually Java enum types are mapped to string / numbers and possibly custom database enumerated types.

Naming format

The default template use the same case for parameters and columns. You can override the default names in the Column and TemplateParameter annotations and use the formatting you like.

You can also configure a specific formatting case of a mapper in the RowMapped and ParametersMapped annotations:

{@link examples.TemplateExamples#customFormatter}

The following cases can be used:

• {@link io.vertx.codegen.format.CamelCase} : FirstName

• {@link io.vertx.codegen.format.LowerCamelCase} : firstName - like camel case but starts with a lower case, this is the default case

• {@link io.vertx.codegen.format.SnakeCase} : first_name

• {@link io.vertx.codegen.format.KebabCase} : first-name

• {@link io.vertx.codegen.format.QualifiedCase} : first.name