SQL Client Templates

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

Method of use

Add the following dependencies to your project before using SQL Client Templates

Maven (in yourpom.xml) :

<dependency>
 <groupId>io.vertx</groupId>
 <artifactId>vertx-sql-client-templates</artifactId>
 <version>4.0.0. Beta3</version>
</dependency>
Copy the code

Gradle (inbuild.gradleFile) :

dependencies {
 implementation 'IO. Vertx: vertx - SQL - the client - templates: 4.0.0. Beta3'
}
Copy the code

start

Here is the easiest way to use SQL templates

An SQL template uses named parameters, so it uses map instead of tuple as the parameter source by default

An SQL template by default generates a RowSet

just as the PreparedQuery method in client does. In fact, the template is just a thin wrapper around PreparedQuery

(Translator: Similar to mybatis)

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"));
    });
  });
Copy the code

When you need to perform an INSERT or update operation and you don’t care about 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");
  });
Copy the code

Template syntax

Template syntax uses the #{XXX} syntax, where XXX is a valid Java identifier string (without keyword constraints).

You can use \ to escape, such that \{foo} is interpreted as a #{foo} string instead of a foo ‘argument.

Get #{foo} instead of being replaced with? Replace with the actual value)

Line mapping

Templates typically produce Row as the result type

You can also provide a function for row-level mapping

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;
};
Copy the code

Implement row-level mapping

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);
    });
  });
Copy the code

Parameter mapping

Templates can use Map

as their default input
,object>

You can provide such 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;
};
Copy the code

Implement parameter mapping

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");
  });
Copy the code

You can also perform batch operations this way

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

Use Jackson Databind for mapping

You can map using the Jackson Databind feature

You need to add Jackson Databind dependencies to your project

Maven (in your pom.xml) :

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

Gradle (in your build.gradle file):

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

Row-level mapping is implemented using the JsonObject of row key-value pairs, and then the mapTo method is called to map the row results to any Java type using 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);
    });
  });
Copy the code

Similarly, parameter mapping is done by mapping objects to JsonObject using jsonObject.mapFrom. The template parameters are then generated using key/value pairs.

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");
  });
Copy the code

Java Date/Time mapping API

You can use the Jackson extension of jackson-modules-Java8 to map java.time types

You need to add Jackson JSR 31 Datatype dependencies to your dependencies

Maven (in your pom.xml) :

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

Gradle (in your build.gradle file):

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

You can then register the time module with the Jackson ObjectMapper

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

mapper.registerModule(new JavaTimeModule());
Copy the code

You can now use a java.time type like LocalDateTime

public class LocalDateTimePojo {

 public LocalDateTime localDateTime;

}
Copy the code

Use vert. x data Objects for mapping

The SQL Client Template component generates mapping functions for vert. x data objects

A vert. x data Object is a Java bean annotated with @DataObject

@DataObject
class UserDataObject {

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

  public long getId(a) {
    return id;
  }

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

  public String getFirstName(a) {
    return firstName;
  }

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

  public String getLastName(a) {
    return lastName;
  }

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

Code generation

Any data objectd annotated with @Rowmapped or @Paramtersmapped will trigger the corresponding mapping class

The CodeGen annotation processor generates these classes at compile time. This is a feature of the Java compiler, so no additional steps are required, just proper configuration

Just add IO. Vertx: vertx-codeGen: Processor and IO. Vertx: vertx-SQL-client-templates to your project

Here is an example using Maven configuration

<dependency>
 <groupId>io.vertx</groupId>
 <artifactId>vertx-codegen</artifactId>
 <version>4.0.0. Beta3</version>
 <classifier>processor</classifier>
</dependency>
<dependency>
 <groupId>io.vertx</groupId>
 <artifactId>vertx-sql-client-templates</artifactId>
 <version>4.0.0. Beta3</version>
</dependency>
Copy the code

This feature is also available in Gradle

annotationProcessor "IO. Vertx: vertx - codegen: 4.0.0. Beta3: processor"
compile "IO. Vertx: vertx - SQL - the client - templates: 4.0.0. Beta3." "
Copy the code

Ides typically provide support for these annotation handlers

The CodeGen processor classifier adds the automatic configuration of the service broker annotation processor to the JAR through the meta-INF/Services plug-in mechanism.

You can also use it in regular JARS if you want, but you need to explicitly declare the annotation handler, as in Maven:

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

Line mapping

You can generate row maps by adding @rowMapped annotations to your data object

@DataObject
@RowMapped
class UserDataObject {

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

  public long getId(a) {
    return id;
  }

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

  public String getFirstName(a) {
    return firstName;
  }

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

  public String getLastName(a) {
    return lastName;
  }

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

You can use custom names thanks to the @Column annotation.

By default, each column name is bound to the data object property, for example, the userName field is bound to the userName column

You can use custom Column names by using 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(a) {
    return id;
  }

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

  public String getFirstName(a) {
    return firstName;
  }

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

  public String getLastName(a) {
    return lastName;
  }

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

You can put annotations on fields, getters, setters

The generated mapper can be used to perform a row mapping, as explained in the 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());
    });
  });
Copy the code

Parameter mapping

You can also generate parameter mappings by annotating your Data object with @parametersmapped

@DataObject
@ParametersMapped
class UserDataObject {

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

  public long getId(a) {
    return id;
  }

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

  public String getFirstName(a) {
    return firstName;
  }

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

  public String getLastName(a) {
    return lastName;
  }

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

By default, each parameter is bound to the data object property, for example, the userName field is bound to the userName parameter

You can also customize the name with 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(a) {
    return id;
  }

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

  public String getFirstName(a) {
    return firstName;
  }

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

  public String getLastName(a) {
    return lastName;
  }

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

You can put annotations on fields, getters, setters

The generated mapper can be used to perform row mapping, as explained in the 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"));
    });
  });
Copy the code

Java enumeration type mappings

You can use Java enumeration class mapping (e.g., Reactive PostgreSQL Client) when your SQL client supports it.

Typically Java enumeration types are mapped to string/number and possibly custom database enumeration types.

Naming format

By default, template parameters and column names are the same

You can override the default names in the Column and TemplateParameter annotations and use whatever format you like.

You can also configure specific formatting use cases for the mapper in Rowmapped and ParametersMapped annotations

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

Here is an example of what can be used

CamelCase : FirstName
LowerCamelCase : firstName– Similar to the hump, but with a lower case beginning, which is used by default
SnakeCase : first_name
KebabCase : first-name
QualifiedCase : first.name

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

SQL Client Templates translation

SQL Client Templates

Method of use

start

Template syntax

Line mapping

Parameter mapping

Use Jackson Databind for mapping

Java Date/Time mapping API

Use vert. x data Objects for mapping

Code generation

Line mapping

Parameter mapping

Java enumeration type mappings

Naming format

SQL Client Templates translation

SQL Client Templates

Method of use

start

Template syntax

Line mapping

Parameter mapping

Use Jackson Databind for mapping

Java Date/Time mapping API

Use vert. x data Objects for mapping

Code generation

Line mapping

Parameter mapping

Java enumeration type mappings

Naming format

Related Posts

Front and back end separate development pattern

Stream#foreach

SpringBoot asynchrony uses the @async principle and thread pool configuration