Written by Mitia Alexandrov

We are used to working with databases through SQL. It has been this way for decades. Time has shown that in the enterprise world, we mostly keep and manage data in relational databases and use object-oriented practices in our software since most enterprise programming languages are object-oriented, including Java.
Although Java provides a low-level way to access relational data in DBs, and the ability to convert it manually to objects, we do it very seldom since this can be automated with libraries like Hibernate or EclipseLink. Or work with any of them, making portable code based on Jakarta Persistence specification.

But there are many scenarios when the data we are working with is not relational, and the mentioned above ORM may be a significant overhead. That’s why different NoSQL solutions, such as MongoDB or CouchDB, have gained massive popularity. Usually, they are pretty fast, and the interaction with them is done using JSON format, which makes it very handy, especially in the MicroServices world.

With the introduction of MySQL Document Store, developers can now utilize both SQL relational tables and schema-less JSON collections using the MySQL Document Store. MySQL has developed the X Dev API to enable CRUD operations and offers a fluent API that facilitates the natural manipulation of JSON documents. Additionally, the X Protocol is optimized for both CRUD operations and SQL API functions.

MySQL Document Store eliminates the need for a separate NoSQL document database. Developers can mix and match relational data and JSON documents in the same database and application. Both data models can be queried in the same application, and results can be formatted as table, tabular, or JSON.

Let us try it with Helidon MP. We will create a small application that gets Pokemons out of the MySQL Document Store and provides it as a REST service.

We need only one dependency for MySQL, and we are set!

 

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
</dependency>

 

First of all, we need to get all config springs to our application:

 

demo.user=root
demo.password=rootroot
demo.host=localhost
demo.port=33060
demo.schema=mn_demo
demo.collection=pokemon

 

This is easily achievable with MicroProfile config @ConfigProperty annotation. We inject the values into the constructor. We then use the ClientFactory object to create the MySQL Document Store Client.

 

@Inject
public PokemonService(@ConfigProperty(name = “demo.user”) String user,
                     @ConfigProperty(name = “demo.password”) String password,
                     @ConfigProperty(name = “demo.host”) String host,
                     @ConfigProperty(name = “demo.port”) String port,
                     @ConfigProperty(name = “demo.schema”) String schema,
                     @ConfigProperty(name = “demo.collection”) String collection) {
   this.url = “mysqlx://” + user + “:” + password + “@” + host + “:” + port + “/”;
   this.schema = schema;
   this.collection = collection;
   this.cli = clientFactory.getClient(this.url, “{\”pooling\”:{\”enabled\”:true, \”maxSize\”:8,\”maxIdleTime\”:30000, \”queueTimeout\”:10000} }”);
}

 

As the client is set up, we can extract the data from the database. We create a Session based on provided Schema and Collection values. We can filter or limit the result.

 

public ArrayList<Object> getPokemons(Integer limit) throws Exception {
   return callInSession(col -> {
       DocResult result = col.find().limit(limit).execute();
       return processResults(result.fetchAll());
   });
}

 

The result is provided as a DocResult object. To produce “clean” JSON results, we should transform this DocResult to JSON String. For this, we use the function below:

 

private ArrayList<Object> processResults(List<DbDoc> docs) throws JsonProcessingException {
   ArrayList<Object> result = new ArrayList<>();
   for (DbDoc doc : docs) {
       result.add(objectMapper.readTree(doc.toString()));
   }
   return result;
}

 

*The data is already present in the database. Please follow the README.md to set up MySQL correctly and import the data.

 

CallInSession

 

private <R> R callInSession(CallInSession<R> caller) {
   Session session = cli.getSession();
   Schema schema = session.getSchema(this.schema);
   Collection col = schema.getCollection(this.collection);
   R result = caller.call(col);
   session.close();
   return result;
}
@FunctionalInterface
private interface CallInSession<R> {
   R call(Collection collection);

 

Since we are working on a collection, there is a FunctionalInterface, which has a Collection parameter, which can be called, and return a generic result.

This interface is used in a generic function, which sets up a session and gets a schema and a corresponding collection. After the work is done, the session is closed. We will create and close sessions on each operation for this particular example.

Now we can just provide the data as a JSON String to our customers, exposed as a typical REST endpoint:

 

@GET
@Path(“/{limit}”)
@Produces(MediaType.APPLICATION_JSON)
public String listLimit(@PathParam(“limit”) Integer limit ) throws Exception{
   ArrayList<Object> pokemons = pokemonService.getPokemons(!Objects.isNull(limit) ? limit : defaultLimit );
   return getResult(pokemons);
}

 

Ok, so now, if we run and test the application:

 

➤  curl localhost:8080/pokemons/3                                                                                                    
{count=3, pokemons=[{“_id”:“0000645de62f0000000000000001”,“name”:“Bulbasaur”,“type”:12}, {“_id”:“0000645de62f0000000000000002”,“name”:“Charmander”,“type”:10}, {“_id”:“0000645de62f0000000 000000003”, name“:”Squirtle“,”type“:11}]}

 

We can see that the documents are returned on our request.

.     .     .

Now let us do some CRUD operations.

If we want to add new Pokemon, we should create a new method in the Resource. It will receive a JSON object and pass it to the Service. The result of the execution will be the number of rows affected:

 

@POST
public long addPokemon(JsonObject jsonObject){
   return pokemonService
           .addPokemon(jsonObject.toString())
           .getAffectedItemsCount();
}

 

Then we will just use the collections method add, and execute statement:

 

public AddResult addPokemon(String json) {
   return callInSession(col -> col.add(json).execute()
   );
}

 

The result of execution will be returned.

Now, if we want to update the name of a Pokemon, it is very straightforward:

 

public Result updatePokemonName(String id, String name) {
   return callInSession(col -> {
               ModifyStatement modifyStatement = col.modify(“_id='” + id + “‘”).set(“name”, name);
               return modifyStatement.execute();
           }
   );
}

 

It will return the result of the execution, which will be used in the corresponding method in the Resource class.

And finally, to delete a Pokemon. The code is straightforward:

 

public Result removePokemon(String id) {
   return callInSession(col -> col.removeOne(id)
   );
}

 

As with the update, a Result object will be returned, which will then be used in the Resource to get the number of the affected documents.

.     .     .

The MySQL Document Store offers full ACID compliance and multi-document transaction support for schema-less JSON documents. With InnoDB as the storage engine, users get reliable data consistency and performance benefits. The Document Store leverages Group Replication and InnoDB Cluster for scaling and high availability. Backups and security features like TDE, auditing, and advanced authentication are also available. Developers can easily perform CRUD operations and utilize SQL to extract data from JSON documents. The Document Store provides a simple and fluent CRUD API that can be used with Helidon.

You may try the example https://github.com/dalexandrov/Helidon-MySql-DocStore

To stay informed — follow our official Twitter, Mastodon, and LinkedIn for updates! If you have further questions, please use https://stackoverflow.com/tags/helidon.

.     .     .

Source, by Mitia Alexandrov