Skip to content

Instantly share code, notes, and snippets.

@vietj

vietj/templates.adoc

Last active May 11, 2020 14:33
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save vietj/db18ad21ea242f7260a8a824f6d6d3a8 to your computer and use it in GitHub Desktop.
Save vietj/db18ad21ea242f7260a8a824f6d6d3a8 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
templates.adoc

SQL Client Templates

Table of Contents

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.0.0-SNAPSHOT</version>
</dependency>

  • Gradle (in your build.gradle file):

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

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 function to achieve row level mapping instead:

Function<Row, 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);
    });
  });

Parameters mapping

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

You can provide a function:

Function<User, Map<String, Object>> PARAMETERS_USER_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");
  });

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-template dependencies to your build.

Here a configuration example for Maven:

<dependency>
 <groupId>io.vertx</groupId>
 <artifactId>vertx-codegen</artifactId>
 <version>4.0.0-SNAPSHOT</version>
 <classifier>processor</classifier>
</dependency>
<dependency>
 <groupId>io.vertx</groupId>
 <artifactId>vertx-sql-client-template</artifactId>
 <version>4.0.0-SNAPSHOT</version>
</dependency>

This feature can also be used in Gradle:

annotationProcessor "io.vertx:vertx-codegen:4.0.0-SNAPSHOT:processor"
compile "io.vertx:vertx-sql-client-template:4.0.0-SNAPSHOT"

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:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment