Developers are used to working with databases through SQL, and it has been this way for decades. Time has shown that in the enterprise world, developers mostly keep and manage data in relational databases and use object-oriented practices in software because most enterprise programming languages, including Java, are object oriented.
Java provides a low-level way to access relational data in databases. It also provides a way to convert relational data manually to objects through object-relational mapping (ORM). However, that process is seldom used because it can be automated with libraries such as Hibernate and EclipseLink. Or work with any of them to make portable code based on the Jakarta Persistence specification.
However, there are many scenarios where data is not relational, and ORM can introduce significant overhead. That’s why different NoSQL solutions, such as MongoDB and CouchDB, have gained popularity. Usually, they are fairly fast, and the interaction with them is done using the JSON format, which is very helpful, especially in the microservices world.
With the introduction of MySQL Document Store, Java developers can now utilize both SQL relational tables and schemaless JSON collections. MySQL uses the X DevAPI to enable create, read, update, and delete (CRUD) operations. This API facilitates the natural manipulation of JSON documents. Additionally, the X protocol is optimized for both CRUD operations and SQL API functions.
Put another way, 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 a table or using JSON.
How does MySQL Document Store work? To demonstrate, I’ll use Helidon MP (the version of Helidon for MicroProfile) and create a small application that gets Pokémons out of MySQL Document Store and provides them as a REST service. You may try the example by downloading it from my GitHub repository.
The application needs only one dependency for MySQL, which you can set up as follows:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
Then, get all of the following Spring configurations into the application:
demo.user=root
demo.password=rootroot
demo.host=localhost
demo.port=33060
demo.schema=mn_demo
demo.collection=pokemon
This is easily achievable using the MicroProfile @ConfigProperty
annotation. Inject the values into the constructor. Use the ClientFactory object to create the MySQL Document Store client, as follows:
@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} }");
}
Now that the client is set up, you can extract the data from the database. Create a session based on the provided schema and collection values; you can filter or limit the result as needed.
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, you should transform this DocResult into a JSON string. For this, use the following function:
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;
}
Note that the sample data is already present in the database. Please follow the README.md
file to set up MySQL correctly and import the data.
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);
Because you are working on a collection, there is a FunctionalInterface, which has a Collection parameter that can be called; it returns a generic result.
This interface is used in a generic function that sets up a session and gets a schema and a corresponding collection. After the work is done, the session is closed. (You’ll create and close sessions on each operation for this particular example; you might not want to do that in practice.)
You can provide the data as a JSON string to your customers, exposed as a typical REST endpoint, as follows:
@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);
}
Now run the following command to run 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}]}
You can see that the documents are returned upon this request.
If you want to add a new Pokémon, 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();
}
Use the collections’ add method as follows:
public AddResult addPokemon(String json) {
return callInSession(col -> col.add(json).execute()
);
}
The result of the execution will be returned.
Updating the name of a Pokémon is very straightforward; the following code will return the result of the execution, which will be used in the corresponding method in the Resource class.
public Result updatePokemonName(String id, String name) {
return callInSession(col -> {
ModifyStatement modifyStatement = col.modify("_id='" + id + "'").set("name", name);
return modifyStatement.execute();
}
);
}
And, finally, deleting a Pokémon is also easy.
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.
MySQL Document Store offers full atomicity, consistency, isolation, and durability (ACID) compliance and multidocument transaction support for schemaless JSON documents. With InnoDB as the storage engine, you’ll see reliable data consistency and performance benefits.
MySQL Document Store also leverages group replication and the InnoDB cluster for scaling and high availability. Backups and security features such as transparent data encryption (TDE), auditing, and advanced authentication are also available. You can easily perform CRUD operations and utilize SQL to extract data from JSON documents. MySQL Document Store provides a simple and CRUD API that can be used with Helidon.
Dmitry Aleksandrov (@bercut2000) is a software developer at Oracle, Java Champion, and Oracle Groundbreaker currently working on Project Helidon. He has more than 17 years experience mainly in Java Enterprise in banking/telecom, and is interested in dynamic languages on JVM and features such as massive computations on GPUs. A true believer in open source and community driven initiatives, he is a co-lead of the Bulgarian Java User Group and co-organizer of jPrime Conf. Dmitry is a blogger and also a frequent speaker at local events as well as conferences like JavaOne/CodeOne, Devoxx/Voxxed and Joker/JPoint.
Previous Post