Connect an application YSQL

• YSQL
• YCQL

• YugabyteDB JDBC Smart Driver
• YugabyteDB R2DBC Smart Driver
• PostgreSQL JDBC Driver
• Vert.x Pg Client

The YugabyteDB R2DBC Smart Driver is an asynchronous Java driver for YSQL built on the PostgreSQL R2DBC driver, with additional connection load balancing features.

CRUD operations

The following sections demonstrate how to perform common tasks required for Java application development using the YugabyteDB R2DBC smart driver.

To start building your application, make sure you have met the prerequisites.

Step 1: Set up the client dependency

Maven dependency

If you are using Maven, add the following to your pom.xml of your project.

<dependency>
  <groupId>com.yugabyte</groupId>
  <artifactId>r2dbc-postgresql</artifactId>
  <version>1.1.0-yb-1-ea</version>
</dependency>

Install the added dependency using mvn install.

Step 2: Set up the database connection

The following table describes the connection parameters required to connect, including smart driver parameters for uniform and topology load balancing.

You can provide the connection details in one of the following ways:

• URL

  "r2dbc:postgresql://username:password@addHost:port/database?loadBalanceHosts=true"
  

• Configuration builder

  PostgresqlConnectionFactory connectionFactory = new PostgresqlConnectionFactory(PostgresqlConnectionConfiguration.builder()
        .addHost("127.0.0.3")
        .username("yugabyte")
        .password("yugabyte")
        .database("yugabyte")
        .loadBalanceHosts(true)
        .ybServersRefreshInterval(10)
        .build());
  

After the driver establishes the initial connection, it fetches the list of available servers from the cluster, and load-balances subsequent connection requests across these servers.

Use multiple addresses

You can specify multiple hosts in the connection string to provide alternative options during the initial connection in case the primary address fails.

You can add multiple hosts in the configuration builder using the addHost() function as follows:

PostgresqlConnectionFactory connectionFactory = new PostgresqlConnectionFactory(PostgresqlConnectionConfiguration.builder()
        .addHost("host1", port1)
        .addHost("host2", port2)
        .username("yugabyte")
        .password("yugabyte")
        .database("yugabyte")
        .loadBalanceHosts(true)
        .ybServersRefreshInterval(10)
        .build());

The hosts are only used during the initial connection attempt. If the first host is down when the driver is connecting, the driver attempts to connect to the next host in the string, and so on.

Step 2: Write your application

1. Create a new Java class called QuickStartApp.java in the base package directory of your project as follows:

   touch ./src/main/java/com/yugabyte/QuickStartApp.java
   

2. Copy the following code to set up a YugabyteDB table and query the table contents from the Java client.

   package org.example;
   import io.r2dbc.postgresql.PostgresqlConnectionConfiguration;
   import io.r2dbc.postgresql.PostgresqlConnectionFactory;
   import io.r2dbc.postgresql.api.PostgresqlConnection;
   import io.r2dbc.spi.*;
   import reactor.core.publisher.Flux;
   import reactor.core.publisher.Mono;
   public class QuickStartApp {
       public static void main(String[] args) {
           // Configure connection to Postgres
           PostgresqlConnectionConfiguration config = PostgresqlConnectionConfiguration.builder()
               .addHost("127.0.0.1")
               .username("yugabyte")
               .password("yugabyte")
               .database("yugabyte")
               .loadBalanceHosts(true)
               .ybServersRefreshInterval(10)
               .build();
           // Create a connection factory
           PostgresqlConnectionFactory connectionFactory = new PostgresqlConnectionFactory(config);
           // Connect to the database
           Mono<PostgresqlConnection> connectionMono = connectionFactory.create();
           // Perform database operations
           connectionMono
               .flatMapMany(connection -> {
                   // Create a table
                   return executeStatement(connection, "CREATE TABLE IF NOT EXISTS employees (id SERIAL PRIMARY KEY, name VARCHAR(255), age int, language VARCHAR(255))")
                       .thenMany(Flux.range(1, 5)
                           .flatMap(i -> executeStatement(connection, "INSERT INTO employees (id,name,age,language) VALUES (" + i + ", 'John', " + (i + 35) + ", 'JAVA')")));
               })
               .thenMany(connectionMono.flatMapMany(connection -> {
                   // Retrieve inserted data
                   return connection.createStatement("SELECT * FROM employees")
                       .execute()
                           .flatMap(result -> {
                               return Flux.from(result.map((row, metadata) -> {
                                   int id = row.get("id", Integer.class);
                                   String name = row.get("name", String.class);
                                   int age = row.get("age", Integer.class);
                                   String lang = row.get("language", String.class);
                                   return "ID: " + id + ", Name: " + name + ", Age: " + age + ", Language: " + lang ;
                               }));
                           });
               }))
               .doOnNext(System.out::println)
               .blockLast(); // Block to keep the program running until all operations are completed
       }
       private static Mono<? extends Result> executeStatement(PostgresqlConnection connection, String sql) {
           Statement statement = connection.createStatement(sql);
           return Flux.from(statement.execute()).next();
       }
   }
   

Run the application

Run the project QuickStartApp.java using the following command:

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

You should see output similar to the following:

ID: 5, Name: John, Age: 40, Language: JAVA
ID: 1, Name: John, Age: 36, Language: JAVA
ID: 4, Name: John, Age: 39, Language: JAVA
ID: 2, Name: John, Age: 37, Language: JAVA
ID: 3, Name: John, Age: 38, Language: JAVA

Learn more

• YugabyteDB smart drivers for YSQL
• Smart Driver architecture
• Develop Spring Boot applications using the YugabyteDB JDBC Driver
• Build Java applications using Hibernate ORM
• Build Java applications using Ebean ORM