Pular para o conteúdo principal

Database Query Server using Quarkus



We are updating our application to Quarkus and I have this requirement of supporting datasouces that users of our application can create.

This is already supported in Widlfly, but does not seem to be supported on Quarkus. Before implementing this in our system we created a PoC which we share in this post.


The problem

Agroal is the part of Quarkus responsible for DataSources and pool connections. It is easy to configure datasources in Quarkus, but some properties only works during development time, after the JAR for distribution is built then you can't change some properties.

The solution is programatically create datasources so the application is flexible in a way that we can add datasets using system properties. We also must make sure that all supported data base drivers are in the application so users just needs to setup properties and no other action is required.

All drivers dependencies

Datasource from property

We can create datasources using a map of properties. The properties keys are defined in class AgroalPropertiesReader. When creating a properties reader we can specify a prefix which will be used to read the properties from the source.


To build the prefix we need all datasources mapped by the user followed by the properties setup for each mapped datasource. We can simply support the same properties as AgroalPropertiesReader. Here's a sample configuration


From Java we use Microprofile config to collect the datasource properties to a map, set the correct prefix and then build the properties that will be used to create a datasource. This is done right after the application is started so any error in configuration prevents the service to run.




Running Queries


Now that we map all datasources we simply have to expose it via REST to run queries using pure JDBC code. The trick is to transform any query result to a suitable data structure that can be translated to JSON, for this we use a MAP of LIST (the best data structure ever), where the key is the column name and the list are the rows for that specific column:



Now we can start our application and send requests to execute queries using HTTP requests:


curl --silent --data 'select id, variableId, value from VariableInstanceLog' http://localhost:8080/datasource/ds1/query



Finally you can run the built JAR using system properties and it will connect to any database:

java -Ddatasources=ds1,ds2 \

-Ddatasource.ds1.jdbcUrl=jdbc:mariadb://localhost:3306/jbpmdb \

-Ddatasource.ds1.providerClassName=org.mariadb.jdbc.Driver \

-Ddatasource.ds1.maxSize=10 \

-Ddatasource.ds1.principal=jbpm \

-Ddatasource.ds1.credential=jbpm \

-Ddatasource.ds2.jdbcUrl=jdbc:mariadb://localhost:3306/test \

-Ddatasource.ds2.providerClassName=org.mariadb.jdbc.Driver \

-Ddatasource.ds2.maxSize=10 \

-Ddatasource.ds2.principal=repasse \

-Ddatasource.ds2.credential=repasse \

-jar target/configurable-datasource-1.0.0-SNAPSHOT-runner.jar


Conclusion

In this post we show how we can use Agroal and quarkus to create an application that can run queries on any of the supported databases. The code is in my github. 

This application has room for a lot of improvements:


  • Create a UI
  • Better return error messages from REST endpoint
  • Add tests
  • Extend for other commands (INSERT, UPDATE and so on)
  • Support new data sources creation after the application is running (This could be useful for creating a generic Database Client)


Feel free to send PRs if you implement any of these improvements!




Marcadores:

Comentários

Postar um comentário

Postagens mais visitadas deste blog

Dancing lights with Arduino - The idea

I have been having fun with Arduino these days! In this article I am going to show how did I use an electret mic with Arduino to create a Dancing Lights circuit. Dancing Lights   I used to be an eletronician before starting the IT college. I had my own electronics maintenance office to fix television, radios, etc. In my free time I used to create electronic projects to sell and I made a few "reais" selling a version of Dancing lights, but it was too limited: it simply animated lamps using a relay in the output of a 4017 CMOS IC. The circuit was a decimal counter  controlled by a 555. 4017 decimal counter. Source in the image When I met Arduino a few years ago, I was skeptical because I said: I can do this with IC, why should I use a microcontroller. I thought that Arduino was for kids. But now my pride is gone and I am having a lot of fun with Arduino :-) The implementation of Dancing Lights with Arduino uses an electret mic to capture the sound and light leds...
Postar um comentário
Leia mais

Simplest JavaFX ComboBox autocomplete

Based on this Brazilian community post , I've created a sample Combobox auto complete. What it basically does is: When user type with the combobox selected, it will work on a temporary string to store the typed text; Each key typed leads to the combobox to be showed and updated If backspace is type, we update the filter Each key typed shows the combo box items, when the combobox is hidden, the filter is cleaned and the tooltip is hidden:   The class code and a sample application is below. I also added the source to my personal github , sent me PR to improve it and there are a lot of things to improve, like space and accents support.
2 comentários
Leia mais

Creating custom Work Item Handler in BPM Suite/jBPM 6

Hi all! In this post I am going to share my experience creating a work item handler for BPM Suite 6.0.3 (which is very similar to jbpm as explained in a previous post ). The Hello World Work Item Handler Let's create a really simple WorkItemHandler just to print "Hello World" in the console. Of course you can implement more feature and make it receive parameters, access database, web services, etc. But in this post we will keep it simple. Really simple. First thing to do is to create a maven project for my WorkItemHandler. Here's the pom.xml of my Maven project: Notice that we are importing jbpm dependency so we can find the java interface that is used to create work item handlers. Now we can start coding. Let's create a class name HelloWorkItemHandler in package org.jugvale.jbpm, here is the simple code: Now we can build our project and go to BPM Suite to configure it so we can use this workitem. Registering the WorkItemHandler UPDATE...
17 comentários
Leia mais