# SQL Client Templates

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>io.vertx</groupId>
     <artifactId>vertx-sql-client-templates</artifactId>
     <version>4.5.17</version>
    </dependency>

- Gradle (in your `build.gradle` file):

    dependencies {
     implementation 'io.vertx:vertx-sql-client-templates:4.5.17'
    }

## 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`.

    Map<String, Object> parameters = Collections.singletonMap("id", 1);

    SqlTemplate
      .forQuery(client, "SELECT * FROM users WHERE id=#{id}")
      .execute(parameters)
      .onSuccess(users -> {
        users.forEach(row -> {
          System.out.println(row.getString("first_name") + " " + row.getString("last_name"));
        });
      });

When you need to perform an insert or update operation and you do not care of the result, you can use `SqlTemplate.forUpdate` instead:

    Map<String, Object> parameters = new HashMap<>();
    parameters.put("id", 1);
    parameters.put("firstName", "Dale");
    parameters.put("lastName", "Cooper");

    SqlTemplate
      .forUpdate(client, "INSERT INTO users VALUES (#{id},#{firstName},#{lastName})")
      .execute(parameters)
      .onSuccess(v -> {
        System.out.println("Successful update");
      });

## 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 `Row` as result type.

You can provide a custom `RowMapper` to achieve row level mapping instead:

    RowMapper<User> ROW_USER_MAPPER = row -> {
      User user = new User();
      user.id = row.getInteger("id");
      user.firstName = row.getString("firstName");
      user.lastName = row.getString("lastName");
      return user;
    };

to achieve row level mapping instead:

    SqlTemplate
      .forQuery(client, "SELECT * FROM users WHERE id=#{id}")
      .mapTo(ROW_USER_MAPPER)
      .execute(Collections.singletonMap("id", 1))
      .onSuccess(users -> {
        users.forEach(user -> {
          System.out.println(user.firstName + " " + user.lastName);
        });
      });

## Anemic JSON row mapping

Anemic JSON row mapping is a trivial mapping between template row columns and a JSON object using the `toJson`

    SqlTemplate
      .forQuery(client, "SELECT * FROM users WHERE id=#{id}")
      .mapTo(Row::toJson)
      .execute(Collections.singletonMap("id", 1))
      .onSuccess(users -> {
        users.forEach(user -> {
          System.out.println(user.encode());
        });
      });

## Parameters mapping

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

You can provide a custom mapper:

    TupleMapper<User> PARAMETERS_USER_MAPPER = TupleMapper.mapper(user -> {
      Map<String, Object> parameters = new HashMap<>();
      parameters.put("id", user.id);
      parameters.put("firstName", user.firstName);
      parameters.put("lastName", user.lastName);
      return parameters;
    });

to achieve parameter mapping instead:

    User user = new User();
    user.id = 1;
    user.firstName = "Dale";
    user.firstName = "Cooper";

    SqlTemplate
      .forUpdate(client, "INSERT INTO users VALUES (#{id},#{firstName},#{lastName})")
      .mapFrom(PARAMETERS_USER_MAPPER)
      .execute(user)
      .onSuccess(res -> {
        System.out.println("User inserted");
      });

You can also perform batching easily:

    SqlTemplate
      .forUpdate(client, "INSERT INTO users VALUES (#{id},#{firstName},#{lastName})")
      .mapFrom(PARAMETERS_USER_MAPPER)
      .executeBatch(users)
      .onSuccess(res -> {
        System.out.println("Users inserted");
      });

## Anemic JSON parameters mapping

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

    JsonObject user = new JsonObject();
    user.put("id", 1);
    user.put("firstName", "Dale");
    user.put("lastName", "Cooper");

    SqlTemplate
      .forUpdate(client, "INSERT INTO users VALUES (#{id},#{firstName},#{lastName})")
      .mapFrom(TupleMapper.jsonObject())
      .execute(user)
      .onSuccess(res -> {
        System.out.println("User inserted");
      });

## 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 `mapTo` to map it to any Java class with Jackson databind.

    SqlTemplate
      .forQuery(client, "SELECT * FROM users WHERE id=#{id}")
      .mapTo(User.class)
      .execute(Collections.singletonMap("id", 1))
      .onSuccess(users -> {
        users.forEach(user -> {
          System.out.println(user.firstName + " " + user.lastName);
        });
      });

Likewise parameters mapping is achieved by mapping the object to a `JsonObject` using `JsonObject.mapFrom` and then using the key/value pairs to produce template parameters.

    User u = new User();
    u.id = 1;

    SqlTemplate
      .forUpdate(client, "INSERT INTO users VALUES (#{id},#{firstName},#{lastName})")
      .mapFrom(User.class)
      .execute(u)
      .onSuccess(res -> {
        System.out.println("User inserted");
      });

### 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.

    @DataObject
    class UserDataObject {

      private long id;
      private String firstName;
      private String lastName;

      public long getId() {
        return id;
      }

      public void setId(long id) {
        this.id = id;
      }

      public String getFirstName() {
        return firstName;
      }

      public void setFirstName(String firstName) {
        this.firstName = firstName;
      }

      public String getLastName() {
        return lastName;
      }

      public void setLastName(String lastName) {
        this.lastName = lastName;
      }
    }

### Code generation

Any data object annotated by `@RowMapped` or `@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:

Just add the `io.vertx:vertx-codegen:processor` and `io.vertx:vertx-sql-client-templates` dependencies to your build.

Here a configuration example for Maven:

    <dependency>
     <groupId>io.vertx</groupId>
     <artifactId>vertx-codegen</artifactId>
     <version>4.5.17</version>
     <classifier>processor</classifier>
    </dependency>
    <dependency>
     <groupId>io.vertx</groupId>
     <artifactId>vertx-sql-client-templates</artifactId>
     <version>4.5.17</version>
    </dependency>

This feature can also be used in Gradle:

    annotationProcessor "io.vertx:vertx-codegen:4.5.17:processor"
    compile "io.vertx:vertx-sql-client-templates:4.5.17"

IDEs usually provide usually 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>

### Row mapping

You can generate a row mapper by annotating your data object by `@RowMapped`.

    @DataObject
    @RowMapped
    class UserDataObject {

      private long id;
      private String firstName;
      private String lastName;

      public long getId() {
        return id;
      }

      public void setId(long id) {
        this.id = id;
      }

      public String getFirstName() {
        return firstName;
      }

      public void setFirstName(String firstName) {
        this.firstName = firstName;
      }

      public String getLastName() {
        return lastName;
      }

      public void setLastName(String lastName) {
        this.lastName = lastName;
      }
    }

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 `@Column` annotation.

    @DataObject
    @RowMapped
    class UserDataObject {

      private long id;
      @Column(name = "first_name")
      private String firstName;
      @Column(name = "last_name")
      private String lastName;

      public long getId() {
        return id;
      }

      public void setId(long id) {
        this.id = id;
      }

      public String getFirstName() {
        return firstName;
      }

      public void setFirstName(String firstName) {
        this.firstName = firstName;
      }

      public String getLastName() {
        return lastName;
      }

      public void setLastName(String lastName) {
        this.lastName = lastName;
      }
    }

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.

    SqlTemplate
      .forQuery(client, "SELECT * FROM users WHERE id=#{id}")
      .mapTo(UserDataObjectRowMapper.INSTANCE)
      .execute(Collections.singletonMap("id", 1))
      .onSuccess(users -> {
        users.forEach(user -> {
          System.out.println(user.getFirstName() + " " + user.getLastName());
        });
      });

### Parameters mapping

You can generate a parameters mapper by annotating your data object by `@ParametersMapped`.

    @DataObject
    @ParametersMapped
    class UserDataObject {

      private long id;
      private String firstName;
      private String lastName;

      public long getId() {
        return id;
      }

      public void setId(long id) {
        this.id = id;
      }

      public String getFirstName() {
        return firstName;
      }

      public void setFirstName(String firstName) {
        this.firstName = firstName;
      }

      public String getLastName() {
        return lastName;
      }

      public void setLastName(String lastName) {
        this.lastName = lastName;
      }
    }

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 `@TemplateParameter` annotation.

    @DataObject
    @ParametersMapped
    class UserDataObject {

      private long id;
      @TemplateParameter(name = "first_name")
      private String firstName;
      @TemplateParameter(name = "last_name")
      private String lastName;

      public long getId() {
        return id;
      }

      public void setId(long id) {
        this.id = id;
      }

      public String getFirstName() {
        return firstName;
      }

      public void setFirstName(String firstName) {
        this.firstName = firstName;
      }

      public String getLastName() {
        return lastName;
      }

      public void setLastName(String lastName) {
        this.lastName = lastName;
      }
    }

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.

    UserDataObject user = new UserDataObject().setId(1);

    SqlTemplate
      .forQuery(client, "SELECT * FROM users WHERE id=#{id}")
      .mapFrom(UserDataObjectParamMapper.INSTANCE)
      .execute(user)
      .onSuccess(users -> {
        users.forEach(row -> {
          System.out.println(row.getString("firstName") + " " + row.getString("lastName"));
        });
      });

### 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:

    @DataObject
    @RowMapped(formatter = SnakeCase.class)
    @ParametersMapped(formatter = QualifiedCase.class)
    class UserDataObject {
      // ...
    }

The following cases can be used:

- `CamelCase` : `FirstName`

- `LowerCamelCase` : `firstName` - like camel case but starts with a lower case, this is the default case

- `SnakeCase` : `first_name`

- `KebabCase` : `first-name`

- `QualifiedCase` : `first.name`