Create and Connect a Micronaut Application to the Oracle Cloud Infrastructure MySQL HeatWave Service

This guide describes how to use GCN to create a Micronaut database application. The application presents REST endpoints and stores data in an Oracle Cloud Infrastructure MySQL database using Micronaut Data.

MySQL HeatWave is a fully-managed Oracle Cloud Infrastructure database service developed and supported by the MySQL team in Oracle.

Micronaut Data is a database access toolkit that uses ahead-of-time compilation to precompute queries for repository interfaces that are then executed by a thin, lightweight runtime layer. Micronaut Data supports the following backends: JPA (Hibernate) and Hibernate Reactive; SQL (JDBC, R2DBC); and MongoDB.

Prerequisites #

JDK 17 or higher. See Setting up Your Desktop.
An Oracle Cloud Infrastructure account. See Setting up Your Cloud Accounts.
The Oracle Cloud Infrastructure CLI installed with local access configured.
An Oracle Cloud Infrastructure compartment. Ensure appropriate permission is granted to your user account to manage Oracle MySQL HeatWave, Compute instances, Virtual Cloud Networks, and Stacks in the compartment.
An SSH key. See Generate SSH keys.
The GCN CLI. See Setting up Your Desktop. (Optional.)

Follow the steps below to create the application from scratch. However, you can also download the completed example:

Gradle
Maven

Gradle Oracle Cloud Infrastructure MySQL Database Example Download completed example

Maven Oracle Cloud Infrastructure MySQL Database Example Download completed example

A note regarding your development environment

Consider using Visual Studio Code that provides native support for developing applications with the Graal Cloud Native Tools extension.

Note: If you use IntelliJ IDEA, enable annotation processing.

Windows platform: The GCN guides are compatible with Gradle only. Maven support is coming soon.

1. Create the Application #

Create an application using the GCN Launcher.

Open the GCN Launcher in advanced mode.
Create a new project using the following selections.
- Project Type: Application (Default)
- Project Name: oci-db-demo
- Base Package: com.example (Default)
- Clouds: OCI
- Language: Java (Default)
- Build Tool: Gradle (Groovy) or Maven
- Test Framework: JUnit (Default)
- Java Version: 17 (Default)
- Micronaut Version: (Default)
- Cloud Services: Database
- Features: GraalVM Native Image and MySQL
- Sample Code: Yes (Default)
Click Generate Project, then click Download Zip. The GCN Launcher creates a Java project with the default package com.example in a directory named oci-db-demo. The application ZIP file will be downloaded in your default downloads directory.

Alternatively, use the GCN CLI as follows:

Gradle
Maven

gcn create-app com.example.oci-db-demo \
    --clouds=oci \
    --services=database \
    --features=graalvm,mysql \
    --build=gradle \
    --jdk=17 \
    --lang=java

gcn create-app com.example.oci-db-demo \
    --clouds=oci \
    --services=database \
    --features=graalvm,mysql \
    --build=maven \
    --jdk=17 \
    --lang=java

The GCN Launcher creates a multimodule project with two subprojects: oci for Oracle Cloud Infrastructure, and lib for common code and configuration shared across cloud platforms. You develop the application logic in the lib subproject, and keep the Oracle Cloud Infrastructure-specific configurations in the oci subproject. If you enable sample code generation, the GCN Launcher creates the main controller, repository interface, entity, service classes, and tests for you. Consider checking this guide where each sample class is closely examined.

2. Set Up Oracle Cloud Infrastructure #

This guide requires the following Oracle Cloud Infrastructure resources (known as a “stack”):

A MySQL DB System
A Compute instance to host your application (because there is no direct way to externally connect to a MySQL DB System)
A Virtual Cloud Network
- A private subnet allowing traffic between the MySQL DB System and Compute instance
- A public subnet open to the internet

Instead of creating the stack manually, use the following steps to provision it using Oracle Cloud Infrastructure Resource Manager:

Download the Terraform configuration GCN MySQL to your default downloads directory.
Follow the steps in Creating a Stack from a Zip File.
- In the “ssh_public_key” field of the Configure variables panel, paste the contents of your public key.
- In the Review panel, select Run apply.
Click Create.

Note: It can take up to 20 minutes to provision the stack. The Log provides details of progress.
When the job completes, click Outputs in the list of the job’s resources and make note of the IP addresses:
- compute_instance_private_ip (the private IP address of the Compute instance)
- compute_instance_public_ip (the public IP address of the Compute instance)
- mysql_db_private_ip (the private IP address of the MySQL DB System)

In a local terminal, create an environment variable for the public IP address of the Compute instance, as follows:

export COMPUTE_INSTANCE_PUBLIC_IP=<compute_instance_public_ip>

set COMPUTE_INSTANCE_PUBLIC_IP=<compute_instance_public_ip>

$COMPUTE_INSTANCE_PUBLIC_IP = "<compute_instance_public_ip>"

Replacing <compute_instance_public_ip> with its value.

3. Configure a MySQL Database #

The GCN Launcher defined default datasources in the oci/src/main/resources/application.properties file. The GCN Launcher also included and enabled Flyway to perform migrations on the default datasources. It uses the Micronaut integration with Flyway to automate schema changes and significantly simplify schema management tasks, such as migrating, rolling back, and reproducing in multiple environments.

Flyway will create the database tables the first time the application starts, but you must first create the database and a database user.

3.1. Start MySQL Shell #

Using your existing local terminal, use ssh to connect to your newly created Compute instance, providing the instance’s Public IP address:
```
 ssh -i /path/to/ssh-key opc@$COMPUTE_INSTANCE_PUBLIC_IP
```
For more information, see Connecting to Your Linux Instance Using SSH.
Start the MySQL shell using the admin username (“admin”) and password (“Welcome_1”), and the private IP address of the MySQL DB System:
```
 mysqlsh --host <mysql_db_private_ip> -u admin -p
```
Switch to “SQL mode” in the shell, as follows:
```
 \sql
```

3.2. Create a Database and User #

From the MySQL shell, create the database (use any valid database name, for example, “gcnDB”):
```
 CREATE DATABASE gcnDB;
```
Create a database user. Use any valid MySQL username, for example, “guide_user”, and a valid password (at least eight characters long, with at least one numeric character, one lowercase character, one uppercase character, and one non-alphanumeric character):
```
 CREATE USER 'guide_user'@'<compute_instance_private_ip>' IDENTIFIED BY '<password>';
```
Where <compute_instance_private_ip> is the private IP address of your Compute instance as noted earlier.

Grant access to the database for the new user:

 GRANT ALL ON gcnDB.* TO 'guide_user'@'<compute_instance_private_ip>';

Exit the MySQL shell by entering “\quit”.
Close the ssh session by entering “exit”.

4. Run the Java Application #

With almost everything in place, you can run the application.

From your existing local terminal, copy the ZIP file to the Compute instance using scp:

Gradle
Maven

scp -i /path/to/ssh-key \
       /path/to/downloads/oci-db-demo-java-gradle.zip \
       opc@$COMPUTE_INSTANCE_PUBLIC_IP:

scp -i /path/to/ssh-key \
       /path/to/downloads/oci-db-demo-java-maven.zip \
       opc@$COMPUTE_INSTANCE_PUBLIC_IP:

Use ssh to reconnect to your Compute instance (ssh -i /path/to/ssh-key opc@$COMPUTE_INSTANCE_PUBLIC_IP) and unzip the ZIP file using unzip, then cd into the newly created directory.
Set values for additional datasources named datasources.default.url, datasources.default.username, and datasources.default.password by exporting them as environment variables as follows:
```
export DATASOURCES_DEFAULT_URL=jdbc:mysql://<mysql_db_private_ip>:3306/gcnDB # 1
export DATASOURCES_DEFAULT_USERNAME=<username> # 2
export DATASOURCES_DEFAULT_PASSWORD=<password> # 3
```
```
set DATASOURCES_DEFAULT_URL=jdbc:mysql://<mysql_db_private_ip>:3306/gcnDB # 1
set DATASOURCES_DEFAULT_USERNAME=<username> # 2
set DATASOURCES_DEFAULT_PASSWORD=<password> # 3
```
```
$ENV:DATASOURCES_DEFAULT_URL = "jdbc:mysql://<mysql_db_private_ip>:3306/gcnDB" # 1
$ENV:DATASOURCES_DEFAULT_USERNAME = "<username>" # 2
$ENV:DATASOURCES_DEFAULT_PASSWORD = "<password>" # 3
```
1 Set the value of the datasources.default.url property as the private IP address of the MySQL DB System combined with the name of the database.

2 Set the value of the datasources.default.username property as the username of the user you created, for example, “guide_user”.

3 Set the value of the datasources.default.password property as the password of the user you created.
To run the Java application on the Compute instance, use the following command from within the ssh session (which starts the application on port 8080):
- Gradle
- Maven
```
./gradlew :oci:run
```
```
./mvnw install -pl lib -am
./mvnw mn:run -pl oci
```

Open a new local terminal, then run this command to test that you can create and store a new Genre in the database:

 curl -X "POST" "http://<compute_instance_public_ip>:8080/genres" \
      -H 'Content-Type: application/json; charset=utf-8' \
      -d $'{ "name": "music" }'

Replacing <compute_instance_public_ip> with its value.

Then list the genres:

 curl http://<compute_instance_public_ip>:8080/genres/list

You have successfully shown that the Java application presents REST endpoints and stores data in the Oracle MySQL database.

5. Package with GraalVM Native Image #

GCN supports compiling a Java application ahead-of-time into a native executable using GraalVM Native Image. You can use the Gradle plugin for GraalVM Native Image building/Maven plugin for GraalVM Native Image building. Packaged as a native executable, it significantly reduces application startup time and memory footprint.

To generate a native executable on the Compute instance, run the following command in your existing ssh session:

Gradle
Maven

./gradlew :oci:nativeCompile

The native executable is created in the oci/build/native/nativeCompile/ directory and can be run with the following command:

oci/build/native/nativeCompile/oci

./mvnw install -pl lib -am
./mvnw package -pl oci -Dpackaging=native-image

The native executable is created in the oci/target/ directory and can be run with the following command:

oci/target/oci

6. Run and Test the Native Executable #

In the ssh session, run the native executable.
Perform the same tests as in step 4.

7. Clean Up Cloud Resources #

After you have finished this guide, clean up the resources you created:

Destroy the resources associated with a stack by following the steps in Creating a Destroy Job.
Follow the instructions in Deleting a Stack to delete the stack.

Summary #

This guide demonstrated how to use GCN to create a database application that stores data in an Oracle MySQL Database. You also learned how to package this application into a native executable.