DatabaseFailoverExample with Vertx

DatabaseFailoverExample.java

import io.vertx.core.Vertx;
import io.vertx.ext.jdbc.JDBCClient;
import io.vertx.ext.jdbc.spi.impl.C3P0DataSourceProvider;
import io.vertx.core.json.JsonObject;

public class DatabaseFailoverExample {

    private static final String DB1_URL = "jdbc:mysql://db1:3306/mydb";
    private static final String DB2_URL = "jdbc:mysql://db2:3306/mydb";
    private static final int TIMEOUT = 5000; // Timeout for DB connection attempts (5 seconds)

    private Vertx vertx;
    private JDBCClient clientDb1;
    private JDBCClient clientDb2;

    public DatabaseFailoverExample(Vertx vertx) {
        this.vertx = vertx;

        JsonObject db1Config = new JsonObject()
            .put("url", DB1_URL)
            .put("driver_class", "com.mysql.cj.jdbc.Driver")
            .put("user", "username")
            .put("password", "password")
            .put("max_pool_size", 30)
            .put("idle_timeout", 300000); // Set appropriate values for your setup

        JsonObject db2Config = new JsonObject()
            .put("url", DB2_URL)
            .put("driver_class", "com.mysql.cj.jdbc.Driver")
            .put("user", "username")
            .put("password", "password")
            .put("max_pool_size", 30)
            .put("idle_timeout", 300000);

        // Initialize the JDBC clients
        clientDb1 = JDBCClient.createShared(vertx, db1Config);
        clientDb2 = JDBCClient.createShared(vertx, db2Config);
    }

    public void executeQueryWithFailover(String query) {
        executeQueryWithTimeout(query, clientDb1, clientDb2);
    }

    private void executeQueryWithTimeout(String query, JDBCClient primaryDb, JDBCClient secondaryDb) {
        primaryDb.getConnection(conn -> {
            if (conn.succeeded()) {
                conn.result().query(query, res -> {
                    if (res.succeeded()) {
                        // Handle successful query
                        System.out.println("Query Result: " + res.result().getRows());
                    } else {
                        // Query failed on DB1, now trying DB2
                        System.out.println("Query failed on DB1. Attempting DB2...");
                        executeQueryOnSecondary(query, secondaryDb);
                    }
                });
            } else {
                // Timeout or connection failed on DB1, now trying DB2
                System.out.println("Timeout or connection failed on DB1. Attempting DB2...");
                executeQueryOnSecondary(query, secondaryDb);
            }
        });
    }

    private void executeQueryOnSecondary(String query, JDBCClient secondaryDb) {
        secondaryDb.getConnection(conn -> {
            if (conn.succeeded()) {
                conn.result().query(query, res -> {
                    if (res.succeeded()) {
                        // Handle successful query
                        System.out.println("Query Result: " + res.result().getRows());
                    } else {
                        // Query failed on DB2 as well, handle failure
                        System.out.println("Query failed on both DBs.");
                    }
                });
            } else {
                // Connection failed on DB2 as well, handle failure
                System.out.println("Connection failed on DB2.");
            }
        });
    }

    public static void main(String[] args) {
        Vertx vertx = Vertx.vertx();
        DatabaseFailoverExample example = new DatabaseFailoverExample(vertx);

        // Sample query
        example.executeQueryWithFailover("SELECT * FROM my_table");
    }
}