Vert.x PG Client is the client for PostgreSQL with simple APIs to communicate with the database. It is a reactive and non-blocking client for handling the database connections with a single threaded API.

Since YugabyteDB is wire compatible with PostgreSQL, Vert.x PG Client works with YugabyteDB as well.

Prerequisites

This tutorial assumes that:

  • YugabyteDB up and running. Download and install the latest version of YugabyteDB by following the steps in Quick start.

    Note

    The executeBatch() API of the Vert.x PG Client is supported in the YugabyteDB version - 2.15.2.0-b0 onwards.
  • Java Development Kit (JDK) 1.8, or later, is installed.
  • Apache Maven 3.3 or later, is installed.

Create and configure the Java project

  1. Create a project called "vertx-pg-example".

    $ mvn archetype:generate \
        -DgroupId=com.yugabyte \
        -DartifactId=vertx-pg-example \
        -DarchetypeArtifactId=maven-archetype-quickstart \
        -DinteractiveMode=false
    
    $ cd vertx-pg-example
    
  2. Add the following properties in the pom.xml file within the <project> element.

    <properties>
      <maven.compiler.source>1.8</maven.compiler.source>
      <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    
  3. Add the following dependency for the Vert.x PG Client within the <dependencies> element in the pom.xml file.

    <dependency>
        <groupId>io.vertx</groupId>
        <artifactId>vertx-pg-client</artifactId>
        <version>4.3.2</version>
    </dependency>
    
  4. Install the added dependency.

    $ mvn install
    

Create the sample Java application

  1. Copy the following Java code to a new file src/main/java/com/yugabyte/vertxPgExample.java:

    package com.yugabyte;
    
    import io.vertx.core.Promise;
    import io.vertx.core.Vertx;
    import io.vertx.pgclient.PgConnectOptions;
    import io.vertx.pgclient.PgPool;
    import io.vertx.sqlclient.PoolOptions;
    import io.vertx.sqlclient.Tuple;
    import io.vertx.sqlclient.Row;
    import io.vertx.sqlclient.RowStream;
    
    public class vertxPgExample {
        public static void main(String[] args) {
    
            PgConnectOptions options = new PgConnectOptions()
                .setPort(5433)
                .setHost("127.0.0.1")
                .setDatabase("yugabyte")
                .setUser("yugabyte")
                .setPassword("yugabyte");
    
            Vertx vertx = Vertx.vertx();
            // creating the PgPool with configuration as option and maxsize 10.
            PgPool pool = PgPool.pool(vertx, options, new PoolOptions().setMaxSize(10));
    
            //getting a connection from the pool and running the example on that
            pool.getConnection().compose(connection -> {
                Promise<Void> promise = Promise.promise();
                // create a test table
                connection.query("create table test(id int primary key, name text)").execute()
                        .compose(v -> {
                            // insert some test data
                            return connection.query("insert into test values (1, 'Hello'), (2, 'World'), (3,'Example'), (4, 'Vertx'), (5, 'Yugabyte')").execute();
                        })
                        .compose(v -> {
                            // prepare the query
                            return connection.prepare("select * from test order by id");
                        })
                        .map(preparedStatement -> {
                            // create a stream for the prepared statement
                            return preparedStatement.createStream(50, Tuple.tuple());
                        })
                        .onComplete(ar -> {
                            if (ar.succeeded()) {
                                RowStream<Row> stream = ar.result();
                                stream
                                        .exceptionHandler(promise::fail)
                                        .endHandler(promise::complete)
                                        .handler(row -> System.out.println(row.toJson())); // Printing each row as JSON
                            } else {
                                promise.fail(ar.cause());
                            }
                        });
                return promise.future().onComplete(v -> {
                    // close the connection
                    connection.close();
                });
            }).onComplete(ar -> {
                if (ar.succeeded()) {
                    System.out.println("Example ran successfully!");
                } else {
                    ar.cause().printStackTrace();
                }
            });
    
        }
    }
    
  2. Run the program.

    $ mvn -q package exec:java -DskipTests -Dexec.mainClass=com.yugabyte.vertxPgExample
    

    You should see the following as the output:

    {"id":1,"name":"Hello"}
    {"id":2,"name":"World"}
    {"id":3,"name":"Example"}
    {"id":4,"name":"Vertx"}
    {"id":5,"name":"Yugabyte"}
    Example ran successfully!
    

Limitation

Pub/sub feature of Vert.x PG client is currently not supported with YugabyteDB. This limitation will go away when LISTEN/NOTIFY support is added to YugabyteDB. See GitHub issue.