Skip to content

Introduction to the Reactive MySQL Vertx client library

Hi! It is true, that data access is probably the most common operation, that any application has to perform. When we talk about Vertx world, things do not seem so straightforward, like in the Spring universe. Java uses JDBC to work with relational databases, however it is a blocking API and its usage certanly violates non-blocking nature of Vertx. One of solution, as we explored earlier is to isolate blocking code in worker threads. Other option is to use “Vertx-specific” clients.

I suggest you to take a look on new reactive MySQL and Postgres clients. While both of them are still in technical preview mode, I don’t think that they would be changed really dramatically. This post focuses on Reactive MySQL client, however core principles should be applicable to PostgreSQL version as well.

Connect to database

This section describes ways to connect to a MySQL database. There are several approaches: the simplest one is to use a connection URI, while you also can configure pool and connection options.

Quick and dirty

In order to use MySQL connections, you need to obtain io.vertx.mysqlclient.MySQLPool instance. As I mentioned before, there are various ways. Here I will show you the quickest (and the dirtiest) one. All what you need is Vertx object and MySQL connection URI. NB, that this is not JDBC URL. The pattern of a connection uri is following:

mysql://[user]:[password]@[hostname]:[port]/[schema]

If you use MySQL database on localhost, you can connect to it from Vertx like this:

String uri = "mysql://user:secret@localhost:3306/booksdb";
MySQLPool pool = MySQLPool.pool(vertx, uri);

// use pool ...

This way use default PoolOptions and MySQLConnectOptions params. NB don’t forget to release pool, when you don’t need it more:

pool.close();

Ok, that was the simplest approach. If you need to customize your connection, take a look on next sections.

Configure a pool

Vertx Reactive MySQL client uses a concept of pool, that means that a pooled client uses a connection pool and any operation will borrow a connection from the pool to execute the operation and release it to the pool. You can configure a connection pool with io.vertx.sqlclient.PoolOptions object:

String uri = "mysql://user:secret@localhost:3306/booksdb";

PoolOptions poolOptions = new PoolOptions();
poolOptions.setMaxSize(10);

MySQLPool pool = MySQLPool.pool(vertx, uri, poolOptions);

// use pool...

This method allows you to configure a maximum size of pool. In the above code snippet we set it up to 10 connections. Both these methods that we observed up to know rely on connection uris to construct connection options, so you need to pass options that you need as part of the connection uri. Take a look on MySQL documentation on connection uris to learn more about allowed params. However, as we in the Java world, we can specify MySQL connection options in code.

Connection options

In order to configure MySQLPool you need to create an instance of io.vertx.mysqlclient.MySQLConnectOptions. This object allows developers to set a number of properties, including:

  • Charset
  • Enable/disable SSL/TLS
  • Collation
  • Username
  • Password
  • Hostname
  • Port
  • Timeout
  • Enable/disable prepared statements cache

Full list of available options you can get from javadoc. Take a look on the following code snippet, that demonstrates usage of MySQLConnectOptions:

MySQLConnectOptions connectOptions = new MySQLConnectOptions();

connectOptions.setUser("user");
connectOptions.setHost("localhost");
connectOptions.setPassword("secret");
connectOptions.setPort(3306);
connectOptions.setDatabase("booksdb");

PoolOptions poolOptions = new PoolOptions().setMaxSize(5);

MySQLPool pool = MySQLPool.pool(vertx, connectOptions, poolOptions);

// use pool...

Note, that you don’t need to specify properties using setters. You can use a static method to create MySQLConnectOptions from the provided connection uri:

String uri = "mysql://user:secret@localhost:3306/booksdb";
MySQLConnectOptions connectOptions = MySQLConnectOptions.fromUri(uri);

We observed available ways to establish a connection via MySQLPool. Now, let observe how to run queries with reactive Vertx MySQL client.

Run queries

You can run queries directly from pool or by obtaining SQLConnection object.

Directly from pool

This approach is simpler, and you should use it only if you don’t need a transaction or run single queries. In this case, the pool will use one of its connection to run the query and return the result back. Let observe how to do it:

// 1. Create a pool

String uri = "mysql://user:secret@localhost:3306/booksdb";
MySQLPool pool = MySQLPool.pool(vertx, uri);

// 2. Run query

pool.query("SELECT book_title FROM books WHERE book_author='Gabriel Garcia Marquez'", res->{
	if (res.succeded()){
		RowSet<Row> results = res.result();
		results.forEach(row->{
			String title = row.getString("book_title");
			System.out.println(title);
		});
	} else {
		res.cause().printStackTrace();
	}
});

// 3. close pool

pool.close();

In this example we retrieve from the database all books written by Gabriel Garcia Marquez. As the async result, Vertx passes RowSet object. It is collection of Row items – single rows of result set. We can iterate through this set using forEach method.

Using connection

Another approach is by using connection. It is handy, when you have to execute sequential queries. Connections are obtained from pool. Take a look on the code snippet below:

// 1. Create a pool

String uri = "mysql://user:secret@localhost:3306/booksdb";
MySQLPool pool = MySQLPool.pool(vertx, uri);

// 2. Get connection

pool.getConnection(res1->{
	if (res1.succeded()){

		// 3. Get connection

		SQLConnection connection = res1.result();
		connection.query("SELECT book_title FROM books WHERE book_author='Gabriel Garcia Marquez'", res2->{
			if (res2.succeded()){
				RowSet<Row> results = res2.result();
				results.forEach(row->{
					String title = row.getString("book_title");
					System.out.println(title);
				});
			} else {
				res2.cause().printStackTrace();
			}
		});
	} else {
		res1.cause().printStackTrace();
	}
});

// 4. Close pool

pool.close();

NB that both these examples and official documentation illustrate database operations using query methods. Like the recommended way to use JDBC is with Prepared statements, in Vertx you should use preparedQuery methods of pool and connection. This method allows you to prepare query and specify arguments. Here is the demonstation of it:

pool.getConnection(res1->{
	if (res1.succeded()){
		SQLConnection connection = res1.result();
		Tuple args = Tuple.of("Gabriel Garcia Marquez");
		connection.prepareQuery("SELECT book_title FROM books WHERE book_author=?", args, res2->{
			if (res2.succeded()){
				RowSet<Row> results = res2.result();
				results.forEach(row->{
					String title = row.getString("book_title");
					System.out.println(title);
				});
			} else {
				res2.cause().printStackTrace();
			}
		});
	} else {
		res1.cause().printStackTrace();
	}
});

Arguments are specified in io.vertx.sqlclient.Tuple object. It works with various data types, including:

  • Boolean
  • Buffer
  • Double
  • Float
  • Integer
  • LocalDate/LocalDateTime
  • Long
  • Short
  • Temporal
  • String
  • BigDecimal
  • UUID

Tuples also accept plain objects (with addValue method) and arrays. In the next section we will write a very simple API that demonstrates using MySQL reactive client in Vertx.

Reactive MySQL client example

In order to illustrate the topic of this post, I suggest you to build CRUD application that works with books. I use random list generator to generate book data. You need to have Vertx.Core, Vertx.Web and MySQL reactive client dependencies in classpath. The easiest way is to use Vertx Starter and create a new project.

Data model

First step is to define data model. We need to create Book entity object as well specify a table schema. The entity is immutable POJO (I also use Lombok to reduce boilerplate code):

@AllArgsConstructor
public class Book{

	@Getter private UUID id;
	@Getter private String title;
	@Getter private String author;
	@Getter private int year;

}

NB: immutables limit an usage of Json methods. When we will get body from POST request, we will do this manually, as Json relies on setters. We also will create a new table in the database with following schema:

Graph 1. MySQL table schema

Due to the fact that we use domain object Book, I think that it is good idea to use mapper design pattern to isolate it from database transfer objects Row and Tuple.

Mapper pattern

This pattern allows us to achieve following goals:

  • Reduce code
  • Increase abstraction
  • Isolate domain object from database logic

We isolate a creating of books in the special class that provides translations between Vertx MySQL client objects and our domain model. This graph below illustrates an idea:

Graph 2. Mapper (Translator) design pattern

Create a new class BookMapper. It uses two static methods allows you to convert row to book and create tuple with arguments from book:

public class BookMapper{

	public static Book from (Row row){
		UUID id = UUID.fromString(row.getString("book_id"));
		String title = row.getString("book_title");
		String author = row.getString("book_author");
		int year = row.getInteger("book_year");
		return new Book(id, title, author, year);
	}

	public static Tuple to (Book book){
		return Tuple.of(book.getId(), book.getTitle(), book.getAuthor(), book.getYear());
	}
}

Using of mapper is entirely optional and it is up to stick to this solution or not. Although, it increases an abstraction level and it is better, than creating constructor for book with Row object as an argument. Now, let write methods to manipulate data. Create BookService verticle, that for simplicity would serve as API router and as DAO at same time. We will start from inserting new record.

Insert a book

We will use the simplest (and dirtiest) way to connect to MySQL database – with just connection uri. As we run local database, this is enough. First, define API routes and get a pool object:

public void start(Promise<Void> promise) throws Exception {
	pool = MySQLPool.pool(vertx, DB_URI);
	HttpServer server = vertx.createHttpServer();
	Router router = Router.router(vertx);
	router.route("/books/*").handler(BodyHandler.create());
	router.get("/books/one/:id").handler(this::getOneBook);
	router.get("/books/all").handler(this::getAllBooks);
	router.post("/books/new").handler(this::addBook);
	router.delete("/books/:id").handler(this::deleteBook);
	server.requestHandler(router);
	server.listen(4567, res->{
		if (res.succeeded()){
			System.out.println("App started");
			promise.complete();
		} else {
			System.out.println(res.cause().getLocalizedMessage());
			promise.fail(res.cause());
		}
	});
}

NB don’t forget to create BodyHandler! Create a method for adding book addBook:

private void addBook(RoutingContext context){
	JsonObject body = context.getBodyAsJson();
	UUID id = UUID.fromString(body.getString("id"));
	String title = body.getString("title");
	String author = body.getString("author");
	int year = body.getInteger("year");
	Book book = new Book(id, title, author, year);
	Tuple data = BookMapper.to(book);
	pool.getConnection(res1->{
		if (res1.succeeded()){
			SqlConnection con = res1.result();
			con.preparedQuery("INSERT INTO books (book_id, book_title, book_author, book_year) VALUES (?,?,?,?)", data, res2->{
				if (res2.succeeded()){
					con.close();
					context.response().setStatusCode(200).end("Success");
				} else {
					con.close();
					System.out.println(res2.cause().getMessage());
					context.response().setStatusCode(500).end(res2.cause().getMessage());
				}
			});
		} else {
			System.out.println(res1.cause().getMessage());
			context.response().setStatusCode(500).end(); 
		}
	});
}

What should note in the above code snippet? First, you could observe that we don’t use Json.decodeValue method to get a domain object from request body. Because we use immutable POJO without setters it would not work. Instead, we have to manually extract data from JsonObject and create an instance of book. Second things is that we reduce tuple creation the mapper class.

Let add some random generated books with this method, so next we will get all of books in the database.

Get all books

This method retrieve all records available in Books table:

private void getAllBooks(RoutingContext context){
	pool.getConnection(res1->{
		if (res1.succeeded()){
			SqlConnection con = res1.result();
			con.query("SELECT book_id, book_title, book_author, book_year FROM books", res2->{
				if (res2.succeeded()){
					List<Book> books = new ArrayList<>();
					RowSet<Row> rows = res2.result();
					rows.forEach(row->{
						Book book = BookMapper.from(row);
						books.add(book);
					});
					JsonArray result = new JsonArray(books);
					context.response().setStatusCode(200).end(result.encode());
				} else {
					con.close();
					System.out.println(res2.cause().getMessage());
					context.response().setStatusCode(500).end(); 
				}
			});
		} else {
			System.out.println(res1.cause().getMessage());
			context.response().setStatusCode(500).end(); 
		}
	});
}

Here we also use the mapper. Without it we have to manually extract data from row and then create a new book.

Get one book

Currently the client is in “technical preview” mode, so maybe things will change in the future. For now, however, you can get one row. So we will use forEach method with a difference, that after we get a book we will return it to user, as we assume that there is only one book. Also row set with size == 0 states that there is no book and we need to return 404 error to user:

private void getOneBook(RoutingContext context){
	UUID id = UUID.fromString(context.pathParam("id"));
	pool.getConnection(res1->{
		if (res1.succeeded()){
			SqlConnection con = res1.result();
			con.preparedQuery("SELECT book_id, book_title, book_author, book_year FROM books where book_id=?", Tuple.of(id), res2->{
				if (res2.succeeded()){
					RowSet<Row> results = res2.result();
					if (results.size()==0){
						con.close();
						context.response().setStatusCode(404).end("Not found");
					}
					results.forEach(row->{
						Book book = BookMapper.from(row);
						con.close();
						context.response().setStatusCode(200).end(JsonObject.mapFrom(book).encode());
					});
				} else {
					con.close();
					System.out.println(res1.cause().getMessage());
					context.response().setStatusCode(500).end();
				}
			});
		} else {
			System.out.println(res1.cause().getMessage());
			context.response().setStatusCode(500).end();
		}
	});
}

What is missed it Delete route. Let add it.

Remove a book

Finally, let implement a method to remove an item:

private void deleteBook(RoutingContext context){
	UUID id = UUID.fromString(context.pathParam("id"));
	pool.getConnection(res1->{
		if (res1.succeeded()){
			SqlConnection con = res1.result();
			con.preparedQuery("DELETE FROM books WHERE book_id=?", Tuple.of(id), res2->{
				if (res2.succeeded()){
					con.close();
					context.response().setStatusCode(200).end("Success");
				} else {
					con.close();
					System.out.println(res2.cause().getMessage());
					context.response().setStatusCode(500).end(); 
				}
			});
		} else {
			System.out.println(res1.cause().getMessage());
			context.response().setStatusCode(500).end(); 
		}
	});
}

This is a simple update query that only accepts book id, so we don’t need to use the mapper class. While the Reactive Vertx MySQL client is in a technical preview mode, I suggest you to try it. This is not “one-size-fits-all” and maybe it would not work for all cases. However, for small services it can be a good replacement for JDBC library as allows to write non-blocking code from main verticle without necessity to create a dedicated worker verticle for database access. I will wait a final release of this library and update this post accordingly, if changes would be really big.

Conclusion

In this post we observed reacitve client for MySQL databases. This library allows you to write non-blocking apps without tricks, like using of separate worker verticle for database operations. We looked on how to establish connections, how to run queries and then build a simple REST API that uses this client. As it is still in technical preview stage, things can change, and I will update this post after the final release. So, don’t forget to subscribe to newsletter and follow me in social networks. Have a nice day!

References

  • Nick Mayne The Translator/Mapper Pattern (2019) read here
  • Reactive MySQL Client Vertx Documentation, read here
Copy link
Powered by Social Snap