About
SQL in Vertx known as Data Access
Service
- jdbc_client - connection pool, asynchronous API - https://vertx.io/docs/vertx-jdbc-client/java/ - JDBC compliant database using an asynchronous API
- https://vertx.io/docs/vertx-mysql-client/java/ - Reactive MySQL Client
- https://vertx.io/docs/vertx-pg-client/java/ - Reactive PostgreSQL Client
- https://vertx.io/docs/vertx-mysql-postgresql-client/java/ - uses Mauricio Linhares async driver to interact with the MySQL or PostgreSQL databases in a non blocking way.
Simple Interface
- sql_common - single connection operations (get a connection from the connection pool, perform your action, close and return the connection to the connection pool)
Libraries
Sql Common
Steps:
- You obtain a connection to the database via the service interface for the specific SQL service that you are using (e.g. JDBC/MySQL/PostgreSQL).
- Query
connection.query("SELECT * FROM USERS", ar -> {
if (ar.succeeded()) {
if (ar.succeeded()) {
ResultSet result = ar.result();
} else {
// Failed!
}
// NOTE that you don't need to worry about
// the connection management (e.g.: close)
}
});
Jdbc Client
jdbc-examples - JDBCExample.java
- Get a client
dbClient = JDBCClient.createShared(vertx, new JsonObject()
.put("url", url)
.put("driver_class", jdbcDriver)
.put("max_pool_size", jdbcPoolSize);
- execute a query
dbClient.getConnection(ar -> {
if (ar.failed()) {
LOGGER.error("Could not open a database connection", ar.cause());
promise.fail(ar.cause());
} else {
SQLConnection connection = ar.result();
connection.execute(sqlQueries.get(SqlQuery.CREATE_PAGES_TABLE), create -> {
connection.close();
if (create.failed()) {
LOGGER.error("Database preparation error", create.cause());
promise.fail(create.cause());
} else {
vertx.eventBus().consumer(config().getString(CONFIG_WIKIDB_QUEUE, "wikidb.queue"), this::onMessage);
promise.complete();
}
});
}
});
- Update / Delete with SQL - Parameter (Bind | Substitution) (Marker | Variable)
JsonArray data = new JsonArray().add(message.body().getString("id"));
dbClient.updateWithParams('delete from Pages where Id = ?', data, res -> {
if (res.succeeded()) {
message.reply("ok");
} else {
reportQueryError(message, res.cause());
}
});