Java Database connectivity using Quarkus through Docker
Today, it is hard to find an application which doesn’t use data. Therefore, it has become essential for many applications to obtain a connection to a relational database. One of the most common ways of creating a database connection is using a datasource and configuring a JDBC driver. ‘Agroal’ is the datasource and connection pool configuration management extension used in Quarkus for JDBC programs. It is a modern connection pool implementation and integrates well with transaction, security and other systems of Quarkus.
What is a connection pool?
A connection pool is a set of reusable connections for a database. Since creating a new connection is costly, the server maintains a pool of connections. When an application needs a connection, the connection is obtained from the pool and when the application closes a connection, the connection is returned to the pool.
Since now we have a basic understanding, let’s create a sample application. The guide will cover how to create a simple Quarkus application with a datasource using configuration properties and connect the application container to a MySQL database container.
First, let’s look at how to set up a MySQL database container.
Setting up a MySQL database container
- Use the following command to pull the latest MySQL image from the docker hub.
docker pull mysql
- Following command deploys the MySQL container which is having the container name `mysqltest`. -e can be used to specify the runtime variables. MYSQL_ROOT_PASSWORD is used to set the password for the MySQL user root. MYSQL_DATABASE=test creates a new database `test` in the MySQL container. -d is used to run the container in background and print the container ID.
docker run --name=mysqltest -e MYSQL_ROOT_PASSWORD=Password -e MYSQL_DATABASE=test -d mysql
- Following command can be used to check the running MySQL container.
- You can find the IP of the container using the below command. It is required when the database connection is created using Quarkus.
docker inspect mysqltest
Accessing the MySQL container using a web interface through docker
- PhpMyAdmin can be used to access MySQL database. Use below command to pull the latest phpMyAdmin image from the Docker Hub.
docker pull phpmyadmin/phpmyadmin
- Use the following command to run phpMyAdmin container which is linked with created MySQL container.
docker run --name myadmin -d --link mysqltest:db -p 8081:80 phpmyadmin/phpmyadmin
- Now you can access the MySQL database through the browser with http://localhost:8081/. Provide the user as ‘root’ and password as ‘Password’ to access the database via phpmyadmin.
Note: If you are using MySQL 8.0, you may have to face the following error, which is prompted when you are trying to access the database through phpMyAdmin.
It may happen since clients/connectors do not support caching_sha2_password plugin. More information can be found here.
The issue can be resolved using the below commands. Here, you log into MySQL console with root user and change the Authentication Plugin with the password.
docker exec -it mysqltest bash
mysql -u root -pPassword
ALTER USER root IDENTIFIED WITH mysql_native_password BY 'Password';
Since now we are done with setting up the database containers, let’s create the Quarkus application.
Creating the Quarkus application with Java Database connectivity
- Create a new Quarkus project using the below command. (You should have JDK 8 or 11+ and Apache Maven 3.5.3+ installed.)
Set the project groupId [org.acme.quarkus.sample]: org.acme
Set the project artifactId [my-quarkus-project]: quarkus-datasource
Set the project version [1.0-SNAPSHOT]: 1.0-SNAPSHOT
Do you want to create a REST resource? (y/n) [no]: y
Set the resource classname [org.acme.HelloResource]: MysqlResource
Set the resource path [/mysql]: mysqlResource
- Open the created project from your IDE.
- Add quarkus-agroal dependency.
mvn quarkus:add-extension -Dextensions="agroal"
Quarkus uses Agroal extension to manage the datasource.
- Add jdbc-mysql dependency.
mvn quarkus:add-extension -Dextensions="jdbc-mysql"
This is the Quarkus extension for MySQL relational database driver.
- Configure Agroal using the src/main/resources/application.properties file.
quarkus.datasource.users.driver = com.mysql.cj.jdbc.Driver
quarkus.datasource.users.new-connection-sql=CREATE TABLE IF NOT EXISTS TABLE1 (id INTEGER not NULL, first VARCHAR(255), PRIMARY KEY ( id ))
Here, “users” is the datasource name. Following are the connection pool configurations used for the MySQL database.
- Driver class: “com.mysql.cj.jdbc.Driver” is the datasource driver class name for the MySQL database.
- Connection URL: “jdbc:mysql://172.17.0.2:3306/test” is the datasource URL, where jdbc is the API, mysql is the database, 172.17.0.2 is the IP of the MySQL database container, 3306 is the port number and test is the database name.
- Username: This is the datasource username. The default username for the MySQL database is root.
- Password: This is the password given by the user when the MySQL container is deployed.
- Min-size: The datasource pool minimum size
- Max-size: The datasource pool maximum size
- New-connection-sql: Query executed when first using a connection
There are many other datasource configurations supported by Quarkus, which can be found through the Quarkus documentation.
- Inject the datasource in the created MysqlResource class, which can be found in src\main\java\org\acme directory.
AgroalDataSource is a subtype of javax.sql.Datasource. Therefore javax.sql.Datasource also can be used.
- Use getConnection() method to establish the connection with the database.
Connection conn = dataSource.getConnection();
- Use createStatement() method to create statement. This statement object is responsible for executing queries.
Statement stmt = conn.createStatement();
- Use executeUpdate() and executeQuery() methods to execute queries to the database.
stmt.executeUpdate("INSERT INTO TABLE1 " + "VALUES (100, 'Rash')");
The executeQuery() method returns the ResultSet object which can be used to get the records from a table.
ResultSet rs=stmt.executeQuery("select * from table1");
- Use close() method to close the statement and the connection.
Now you have successfully created the application.
Deploying the application inside a container
- Package the application using `mvn clean package` command.
- Use the Dockerfile.jvm file to containerize the application using OpenJDK Hotspot virtual machine.
- Build the Docker image using `docker build -f src/main/docker/Dockerfile.jvm -t quarkus/mysql-app .`
- Run the container using `docker run --name myapp -i --rm -p 8080:8080 quarkus/mysql-app`
- Access the application through the browser with http://localhost:8080/.
Congratulations! You have successfully deployed the application.
You can download a sample Quarkus application with Java Database connectivity from my Github: https://github.com/Rashmini/quarkus-datasource.
Happy Coding !!!