Vert.x doesn't have a module to use JPA specifications, so we will not create an entity.

Instead, we need to have an object that transfers the attributes of a football player from and to a database. For this reason, we create a data transfer object, named FootballPlayer, in order to do this:

package com.packtpub.vertx.footballplayermicroservice.model;

import java.io.Serializable;

import java.math.BigInteger;

public class FootballPlayer implements Serializable {

    private static final long serialVersionUID = -92346781936044228L;

    private Integer id;

    private String name;

    private String surname;

    private int age;

    private String team;

    private String position;

    private BigInteger price;

    public FootballPlayer() {
    }

    public FootballPlayer(Integer id, String name, String surname, int age,
        String team, String position, BigInteger price) {
        this.id = id;
        this.name = name;
        this.surname = surname;
        this.age = age;
        this.team = team;
        this.position = position;
        this.price = price;
    }

    // Getters and Setters
    ...
}

The second step is to create a configuration file with the parameters needed to establish a JDBC connection with the database. In the src/main/conf directory, put a file named my-application-conf.json with the following parameters:

•  url: The JDBC URL to connect to the database
•  driver_class: The JDBC driver needed to use JDBC API
•  user: The username used to connect to the database
•  password: The password used to connect to the database

This is a simple scenario—in a production environment, you must mask the JDBC credentials.

The following is the snapshot of the file:

{
    "url": "jdbc:postgresql://localhost:5532/football_players_registry",
    "driver_class": "org.postgresql.Driver",
    "user": "postgres",
    "password": "postgresPwd"
}

Then we will create, in the src/main/resources folder, two files, schema.sql and data.sql, in order to create the required table structure and preload data into our database.

You will find the code in the GitHub repository.

The final step is to build a class responsible for interacting with the database in order to perform the following operations:

•  Create database tables
•  Preload a set of data
•  CRUD operations plus the findAll method

The following snippet shows our class:

public class FootballPlayerDAO {

    public Future<FootballPlayer> insert(SQLConnection connection,
        FootballPlayer footballPlayer, boolean closeConnection) {
        
        Future<FootballPlayer> future = Future.future();
        String sql = "INSERT INTO football_player (name, surname, age, team,
            position, price) VALUES (?, ?, ?, ?, ?, ?)";

        connection.updateWithParams(sql, new JsonArray().add(footballPlayer.getName())
            .add(footballPlayer.getSurname())
            .add(footballPlayer.getAge()).add(footballPlayer.getTeam())
            .add(footballPlayer.getPosition())
            .add(footballPlayer.getPrice().intValue()),
             ar -> { 
                 if (closeConnection) {
                     connection.close();
                 }
                 future.handle(ar.map(res -> new FootballPlayer(res.getKeys().getInteger(0),
                     footballPlayer.getName(), footballPlayer.getSurname(),
                     footballPlayer.getAge(), footballPlayer.getTeam(),
                     footballPlayer.getPosition(), footballPlayer.getPrice())));
             });
         return future;
     }

     public Future<SQLConnection> connect(JDBCClient jdbc) {
         Future<SQLConnection> future = Future.future();
         jdbc.getConnection(ar -> future.handle(ar.map(c -> c.setOptions(
             new SQLOptions().setAutoGeneratedKeys(true))))
         );
         return future;
     }

...

}

As you can see, Vert.x uses a different approach for database interaction. The schema is different from the traditional one that you usually use with the Java JDBC API.

The following is globally the traditional approach:

String sql = "SELECT * FROM MY_TABLE";
ResultSet rs = stmt.executeQuery(sql);

This then becomes as follows:

connection.query("SELECT * FROM Products", result -> {
// do something with the result
});

In Vert.x, but also in general in all reactive architectures, all operations are asynchronous and they are handled by the Future class.