io.vertx.sqlclient.SqlClient Java Examples

public void collector02Example(SqlClient client) {

    // Create a collector projecting a row set to a (last_name_1,last_name_2,...)
    Collector<Row, ?, String> collector = Collectors.mapping(
      row -> row.getString("last_name"),
      Collectors.joining(",", "(", ")")
    );

    // Run the query with the collector
    client.query("SELECT * FROM users").collecting(collector).execute(ar -> {
        if (ar.succeeded()) {
          SqlResult<String> result = ar.result();

          // Get the string created by the collector
          String list = result.value();
          System.out.println("Got " + list);
        } else {
          System.out.println("Failure: " + ar.cause().getMessage());
        }
      });
  }
 

public void collector02Example(SqlClient client) {

    // Create a collector projecting a row set to a (last_name_1,last_name_2,...)
    Collector<Row, ?, String> collector = Collectors.mapping(
      row -> row.getString("last_name"),
      Collectors.joining(",", "(", ")")
    );

    // Run the query with the collector
    client.query("SELECT * FROM users").collecting(collector).execute(ar -> {
        if (ar.succeeded()) {
          SqlResult<String> result = ar.result();

          // Get the string created by the collector
          String list = result.value();
          System.out.println("Got " + list);
        } else {
          System.out.println("Failure: " + ar.cause().getMessage());
        }
      });
  }
 

public void collector01Example(SqlClient client) {

    // Create a collector projecting a row set to a map
    Collector<Row, ?, Map<Long, String>> collector = Collectors.toMap(
      row -> row.getLong("id"),
      row -> row.getString("last_name"));

    // Run the query with the collector
    client.query("SELECT * FROM users").collecting(collector).execute(ar -> {
        if (ar.succeeded()) {
          SqlResult<Map<Long, String>> result = ar.result();

          // Get the map created by the collector
          Map<Long, String> map = result.value();
          System.out.println("Got " + map);
        } else {
          System.out.println("Failure: " + ar.cause().getMessage());
        }
      });
  }
 

public void geometryExample03(SqlClient client) {
  client
    .query("SELECT g FROM geom;")
    .execute(ar -> {
    if (ar.succeeded()) {
      // Fetch the spatial data as a Vert.x Data Object
      RowSet<Row> result = ar.result();
      for (Row row : result) {
        Point point = row.get(Point.class, 0);
        System.out.println("Point x: " + point.getX());
        System.out.println("Point y: " + point.getY());
      }
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

public void example9(SqlAuthentication jdbcAuth, SqlClient sqlClient) {

    String hash = jdbcAuth.hash(
      "pkdbf2", // hashing algorithm (OWASP recommended)
      VertxContextPRNG.current().nextString(32), // secure random salt
      "sausages" // password
    );

    // save to the database
    sqlClient
      .preparedQuery("INSERT INTO user (username, password) VALUES (?, ?)")
      .execute(Tuple.of("tim", hash))
      .onSuccess(rowset -> {
        // password updated
      });
  }
 

public void enumType01Example(SqlClient client) {
  client
    .preparedQuery("INSERT INTO colors VALUES ($1)")
    .execute(Tuple.of(Color.red))
    .flatMap(res ->
      client
        .preparedQuery("SELECT color FROM colors")
        .execute()
    ).onComplete(res -> {
      if (res.succeeded()) {
        RowSet<Row> rows = res.result();
        for (Row row : rows) {
          System.out.println(row.get(Color.class, "color"));
        }
      }
  });
}
 

public void collector01Example(SqlClient client) {

    // Create a collector projecting a row set to a map
    Collector<Row, ?, Map<Long, String>> collector = Collectors.toMap(
      row -> row.getLong("id"),
      row -> row.getString("last_name"));

    // Run the query with the collector
    client.query("SELECT * FROM users")
      .collecting(collector)
      .execute(ar -> {
      if (ar.succeeded()) {
        SqlResult<Map<Long, String>> result = ar.result();

        // Get the map created by the collector
        Map<Long, String> map = result.value();
        System.out.println("Got " + map);
      } else {
        System.out.println("Failure: " + ar.cause().getMessage());
      }
    });
  }
 

public void collector02Example(SqlClient client) {

    // Create a collector projecting a row set to a (last_name_1,last_name_2,...)
    Collector<Row, ?, String> collector = Collectors.mapping(
      row -> row.getString("last_name"),
      Collectors.joining(",", "(", ")")
    );

    // Run the query with the collector
    client.query("SELECT * FROM users").collecting(collector).execute(ar -> {
        if (ar.succeeded()) {
          SqlResult<String> result = ar.result();

          // Get the string created by the collector
          String list = result.value();
          System.out.println("Got " + list);
        } else {
          System.out.println("Failure: " + ar.cause().getMessage());
        }
      });
  }
 

public void booleanExample01(SqlClient client) {
  client
    .query("SELECT graduated FROM students WHERE id = 0")
    .execute(ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rowSet = ar.result();
      for (Row row : rowSet) {
        int pos = row.getColumnIndex("graduated");
        Byte value = row.get(Byte.class, pos);
        Boolean graduated = row.getBoolean("graduated");
      }
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

public void queries08(SqlClient client) {

    // Add commands to the batch
    List<Tuple> batch = new ArrayList<>();
    batch.add(Tuple.of("julien", "Julien Viet"));
    batch.add(Tuple.of("emad", "Emad Alblueshi"));

    // Execute the prepared batch
    client
      .preparedQuery("INSERT INTO USERS (id, name) VALUES (?, ?)")
      .executeBatch(batch, res -> {
      if (res.succeeded()) {

        // Process rows
        RowSet<Row> rows = res.result();
      } else {
        System.out.println("Batch failed " + res.cause());
      }
    });
  }
 

public void queries08(SqlClient client) {

    // Add commands to the batch
    List<Tuple> batch = new ArrayList<>();
    batch.add(Tuple.of("julien", "Julient Viet"));
    batch.add(Tuple.of("emad", "Emad Alblueshi"));
    batch.add(Tuple.of("andy", "Andy Guibert"));

    // Execute the prepared batch
    client
      .preparedQuery("INSERT INTO USERS (id, name) VALUES ($1, $2)")
      .executeBatch(batch, res -> {
      if (res.succeeded()) {

        // Process rows
        RowSet<Row> rows = res.result();
      } else {
        System.out.println("Batch failed " + res.cause());
      }
    });
  }
 

public void queries08(SqlClient client) {

    // Add commands to the batch
    List<Tuple> batch = new ArrayList<>();
    batch.add(Tuple.of("julien", "Julien Viet"));
    batch.add(Tuple.of("emad", "Emad Alblueshi"));

    // Execute the prepared batch
    client
      .preparedQuery("INSERT INTO USERS (id, name) VALUES ($1, $2)")
      .executeBatch(batch, res -> {
      if (res.succeeded()) {

        // Process rows
        RowSet<Row> rows = res.result();
      } else {
        System.out.println("Batch failed " + res.cause());
      }
    });
  }
 

public void collector01Example(SqlClient client) {

    // Create a collector projecting a row set to a map
    Collector<Row, ?, Map<Long, String>> collector = Collectors.toMap(
      row -> row.getLong("id"),
      row -> row.getString("last_name"));

    // Run the query with the collector
    client.query("SELECT * FROM users")
      .collecting(collector)
      .execute(ar -> {
      if (ar.succeeded()) {
        SqlResult<Map<Long, String>> result = ar.result();

        // Get the map created by the collector
        Map<Long, String> map = result.value();
        System.out.println("Got " + map);
      } else {
        System.out.println("Failure: " + ar.cause().getMessage());
      }
    });
  }
 

public void queries04(SqlClient client) {
  client
    .preparedQuery("INSERT INTO users (first_name, last_name) VALUES (?, ?)")
    .execute(Tuple.of("Julien", "Viet"), ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rows = ar.result();
      System.out.println(rows.rowCount());
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

public void booleanExample02(SqlClient client) {
  client
    .preparedQuery("UPDATE students SET graduated = ? WHERE id = 0")
    .execute(Tuple.of(true), ar -> {
    if (ar.succeeded()) {
      System.out.println("Updated with the boolean value");
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

public void geometryExample02(SqlClient client) {
  client
    .query("SELECT ST_AsBinary(g) FROM geom;")
    .execute(ar -> {
    if (ar.succeeded()) {
      // Fetch the spatial data in WKB format
      RowSet<Row> result = ar.result();
      for (Row row : result) {
        Buffer wkbValue = row.getBuffer(0);
      }
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

public void geometryExample01(SqlClient client) {
  client
    .query("SELECT ST_AsText(g) FROM geom;")
    .execute(ar -> {
    if (ar.succeeded()) {
      // Fetch the spatial data in WKT format
      RowSet<Row> result = ar.result();
      for (Row row : result) {
        String wktString = row.getString(0);
      }
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

public void enumeratedType01Example(SqlClient client) {
  client
    .preparedQuery("INSERT INTO colors VALUES (?)")
    .execute(Tuple.of(Color.red),  res -> {
      // ...
    });
}
 

public void geometryExample04(SqlClient client) {
  Point point = new Point(0, 1.5, 1.5);
  // Send as a WKB representation
  client
    .preparedQuery("INSERT INTO geom VALUES (ST_GeomFromWKB(?))")
    .execute(Tuple.of(point), ar -> {
    if (ar.succeeded()) {
      System.out.println("Success");
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

public void enumeratedType02Example(SqlClient client) {
  client
    .preparedQuery("SELECT color FROM colors")
    .execute()
    .onComplete(res -> {
    if (res.succeeded()) {
      RowSet<Row> rows = res.result();
      for (Row row : rows) {
        System.out.println(row.get(Color.class, "color"));
      }
    }
  });
}
 

public void storedProcedureExample(SqlClient client) {
  client.query("CREATE PROCEDURE multi() BEGIN\n" +
    "  SELECT 1;\n" +
    "  SELECT 1;\n" +
    "  INSERT INTO ins VALUES (1);\n" +
    "  INSERT INTO ins VALUES (2);\n" +
    "END;").execute(ar1 -> {
    if (ar1.succeeded()) {
      // create stored procedure success
      client
        .query("CALL multi();")
        .execute(ar2 -> {
        if (ar2.succeeded()) {
          // handle the result
          RowSet<Row> result1 = ar2.result();
          Row row1 = result1.iterator().next();
          System.out.println("First result: " + row1.getInteger(0));

          RowSet<Row> result2 = result1.next();
          Row row2 = result2.iterator().next();
          System.out.println("Second result: " + row2.getInteger(0));

          RowSet<Row> result3 = result2.next();
          System.out.println("Affected rows: " + result3.rowCount());
        } else {
          System.out.println("Failure: " + ar2.cause().getMessage());
        }
      });
    } else {
      System.out.println("Failure: " + ar1.cause().getMessage());
    }
  });
}
 

public void example5(Vertx vertx, SqlClient sqlClient) {

    SqlAuthenticationOptions options = new SqlAuthenticationOptions();
    // SQL client can be any of the known implementations
    // *. Postgres
    // *. MySQL
    // *. etc...
    AuthenticationProvider authenticationProvider =
      SqlAuthentication.create(sqlClient, options);
  }
 

public void queries01(SqlClient client) {
  client
    .query("SELECT * FROM users WHERE id='julien'")
    .execute(ar -> {
    if (ar.succeeded()) {
      RowSet<Row> result = ar.result();
      System.out.println("Got " + result.size() + " rows ");
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

public void queries04(SqlClient client) {
  client
    .preparedQuery("INSERT INTO users (first_name, last_name) VALUES ($1, $2)")
    .execute(Tuple.of("Julien", "Viet"),  ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rows = ar.result();
      System.out.println(rows.rowCount());
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

public void queries02(SqlClient client) {
  client
    .preparedQuery("SELECT * FROM users WHERE id=$1")
    .execute(Tuple.of("julien"), ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rows = ar.result();
      System.out.println("Got " + rows.size() + " rows ");
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

public void queries01(SqlClient client) {
  client
    .query("SELECT * FROM users WHERE id='julien'")
    .execute(ar -> {
    if (ar.succeeded()) {
      RowSet<Row> result = ar.result();
      System.out.println("Got " + result.size() + " rows ");
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
}
 

public SqlTemplateImpl(SqlClient client,
                       SqlTemplate sqlTemplate,
                       Function<PreparedQuery<RowSet<Row>>,
                       PreparedQuery<R>> queryMapper,
                       Function<T, Map<String, Object>> paramsMapper) {
  this.client = client;
  this.sqlTemplate = sqlTemplate;
  this.queryMapper = queryMapper;
  this.paramsMapper = paramsMapper;
}
 

public void bindingParamsWithParamsMapper(SqlClient client) {
  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"));
      });
    });
}
 

public void bindingRowWithRowMapper(SqlClient client) {
  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());
      });
    });
}
 

private static void insertIntoTestTable(TestContext ctx, SqlClient client, int amount, Runnable completionHandler) {
  AtomicInteger count = new AtomicInteger();
  for (int i = 0; i < 10; i++) {
    client
      .query("INSERT INTO mutable (id, val) VALUES (" + i + ", 'Whatever-" + i + "')")
      .execute(ctx.asyncAssertSuccess(r1 -> {
      ctx.assertEquals(1, r1.rowCount());
      if (count.incrementAndGet() == amount) {
        completionHandler.run();
      }
    }));
  }
}