Create and Connect a Spring Application to an Oracle Cloud Infrastructure Autonomous Database

This guide describes how to create a Spring Boot 3 application using GCN. The application presents REST endpoints and stores data in an Oracle Cloud Infrastructure Autonomous Database using Micronaut Data.

Oracle Cloud Infrastructure Autonomous Database is a fully automated database service that makes it easy for all organizations to develop and deploy application workloads regardless of complexity, scale, or criticality. All Oracle Cloud Infrastructure accounts (whether free or paid) get two Always Free Oracle Autonomous Databases. You can use these databases for transaction processing, data warehousing, Oracle APEX application development, or JSON-based application development. For current regional availability, see the Always Free Cloud Services table in Cloud Regions—Infrastructure and Platform Services.

Micronaut Data is a database access toolkit that uses ahead-of-time compilation to pre-compute 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 #

Follow the steps below to create the application from scratch. However, you can also download the 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 Spring Boot Application #

Generate a Spring Boot application using Spring Initializr with Spring Web.

1.1. Add Dependencies #

Edit your build file as described in Using the Micronaut Spring Boot Starter to use Micronaut Features within a Spring Boot application.

build.gradle

Add the Oracle Public Repository to the repositories block:

maven { url "https://maven.oracle.com/public" }

Add the following dependencies:

implementation enforcedPlatform("cloud.graal.gcn:gcn-bom:4.2.1.2")
implementation platform("io.micronaut.platform:micronaut-platform:4.2.1-oracle-00001")
annotationProcessor platform('io.micronaut.platform:micronaut-platform:4.2.1-oracle-00001')
annotationProcessor("io.micronaut:micronaut-inject-java")
implementation("io.micronaut.spring:micronaut-spring-boot-starter")

pom.xml

Add the Micronaut version to the properties element:

<micronaut.version>4.2.1-oracle-00001</micronaut.version>
<micronaut.platform.version>4.2.1-oracle-00001</micronaut.platform.version>
<micronaut.data.version>4.3.1-oracle-00001</micronaut.data.version>

Add the following repositories element:

<repositories>
   <repository>
      <id>gcn</id>
      <url>https://maven.oracle.com/public</url>
   </repository>
   <repository>
      <id>central</id>
      <url>https://repo.maven.apache.org/maven2</url>
   </repository>
</repositories>

Add the following dependencyManagement element:

<dependencyManagement>
    <dependencies>
      <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-framework-bom</artifactId>
        <version>${spring-framework.version}</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
      <dependency>
        <groupId>cloud.graal.gcn</groupId>
        <artifactId>gcn-bom</artifactId>
        <version>4.2.1.2</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
      <dependency>
        <groupId>io.micronaut.platform</groupId>
        <artifactId>micronaut-platform</artifactId>
        <version>${micronaut.platform.version}</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
    </dependencies>
</dependencyManagement>

Add the following plugin to the build/plugins element:

<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-compiler-plugin</artifactId>
    <configuration>
      <annotationProcessorPaths combine.children="append">
        <path>
          <groupId>io.micronaut</groupId>
          <artifactId>micronaut-inject-java</artifactId>
          <version>${micronaut.version}</version>
        </path>
        <path>
          <groupId>io.micronaut.data</groupId>
          <artifactId>micronaut-data-processor</artifactId>
          <version>${micronaut.data.version}</version>
        </path>
      </annotationProcessorPaths>
      <compilerArgs>
        <arg>-Amicronaut.processing.group=com.example</arg>
        <arg>-Amicronaut.processing.module=gcnguide</arg>
      </compilerArgs>
    </configuration>
  </plugin>
</plugins>

Add this dependency to your dependencies element:

<dependency>
    <groupId>io.micronaut.spring</groupId>
    <artifactId>micronaut-spring-boot-starter</artifactId>
    <scope>compile</scope>
</dependency>

Additionally, add the following dependencies to use Micronaut Data and Oracle Autonomous Database:

build.gradle

annotationProcessor("io.micronaut.data:micronaut-data-processor")
implementation("io.micronaut.data:micronaut-data-jdbc")
implementation("io.micronaut.sql:micronaut-jdbc-hikari")
implementation("io.micronaut.oraclecloud:micronaut-oraclecloud-atp")
implementation("io.micronaut.oraclecloud:micronaut-oraclecloud-httpclient-netty")
implementation("io.micronaut.flyway:micronaut-flyway")
implementation("jakarta.validation:jakarta.validation-api")
testImplementation("org.testcontainers:oracle-xe")

pom.xml

<dependency>
  <groupId>io.micronaut.data</groupId>
  <artifactId>micronaut-data-jdbc</artifactId>
  <scope>compile</scope>
</dependency>
<dependency>
  <groupId>io.micronaut.sql</groupId>
  <artifactId>micronaut-jdbc-hikari</artifactId>
  <scope>compile</scope>
</dependency>
<dependency>
  <groupId>io.micronaut.oraclecloud</groupId>
  <artifactId>micronaut-oraclecloud-atp</artifactId>
  <scope>compile</scope>
</dependency>
<dependency>
  <groupId>io.micronaut.oraclecloud</groupId>
  <artifactId>micronaut-oraclecloud-httpclient-netty</artifactId>
  <scope>compile</scope>
</dependency>
<dependency>
  <groupId>io.micronaut.flyway</groupId>
  <artifactId>micronaut-flyway</artifactId>
  <scope>compile</scope>
</dependency>
<dependency>
  <groupId>jakarta.validation</groupId>
  <artifactId>jakarta.validation-api</artifactId>
  <scope>compile</scope>
</dependency>
<dependency>
  <groupId>org.testcontainers</groupId>
  <artifactId>oracle-xe</artifactId>
  <scope>test</scope>
</dependency>

1.2. Enable Micronaut #

Add the @EnableMicronaut annotation for Spring Boot to start the Micronaut context. Edit the file named src/main/java/com/example/Application.java as follows:

package com.example;

import io.micronaut.spring.boot.starter.EnableMicronaut;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@EnableMicronaut
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

1.3. Domain Entity #

Create a Genre entity record to represent the database data in the src/main/java/com/example/domain/Genre.java file.

package com.example.domain;

import io.micronaut.core.annotation.Nullable;
import io.micronaut.data.annotation.GeneratedValue;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;
import jakarta.validation.constraints.NotNull;

@MappedEntity
public record Genre(
    @Nullable @GeneratedValue @Id Long id,
    @NotNull String name) {
}

1.4. Update Command #

Create a GenreUpdateCommand record to encapsulate the update operations in src/main/java/com/example/GenreUpdateCommand.java, as follows:

package com.example;

import jakarta.validation.constraints.NotBlank;
import jakarta.validation.constraints.NotNull;

public record GenreUpdateCommand(
    @NotNull Long id,
    @NotBlank String name){
}

1.5. Repository Interface #

A repository interface defines the operations to access the database. Micronaut Data implements the interface at compilation time. Create a repository interface in src/main/java/com/example/GenreRepository.java:

package com.example;

import com.example.domain.Genre;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.exceptions.DataAccessException;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;
import jakarta.validation.constraints.NotBlank;
import jakarta.validation.constraints.NotNull;

import jakarta.transaction.Transactional;

@JdbcRepository(dialect = Dialect.ORACLE) // <1>
public interface GenreRepository extends CrudRepository<Genre, Long> { // <2>

    Genre save(@NotBlank String name);

    @Transactional
    default Genre saveWithException(@NotBlank String name) {
        save(name);
        throw new DataAccessException("test exception");
    }

    long update(@NotNull @Id Long id, @NotBlank String name);
}

1 @JdbcRepository with a specific dialect.

2 Genre, the entity to treat as the root entity for the purposes of querying, is established either from the method signature or from the generic type parameter specified to the GenericRepository interface.

The repository extends from PageableRepository. It inherits the hierarchy PageableRepositoryCrudRepositoryGenericRepository.

Repository Description
PageableRepository A repository that supports pagination. It provides findAll(Pageable) and findAll(Sort).
CrudRepository A repository interface for performing CRUD (Create, Read, Update, Delete).
It provides methods such as findAll(), save(Genre), deleteById(Long), and findById(Long).
GenericRepository A root interface that features no methods but defines the entity type and ID type as generic arguments.

1.6. Controller #

Create the main controller that exposes a resource with common CRUD operations in src/main/java/com/example/GenreController.java:

package com.example;

import com.example.domain.Genre;
import io.micronaut.data.exceptions.DataAccessException;
import jakarta.validation.Valid;
import jakarta.validation.constraints.NotBlank;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestController;

import java.net.URI;
import java.util.Optional;

@RestController
@RequestMapping("/genres") // <1>
class GenreController {

    protected final GenreRepository genreRepository;

    GenreController(GenreRepository genreRepository) { // <2>
        this.genreRepository = genreRepository;
    }

    @GetMapping("/{id}") // <3>
    public Optional<Genre> show(@PathVariable Long id) {
        return genreRepository.findById(id);
    }

    @PutMapping // <4>
    public ResponseEntity<Void> update(@RequestBody @Valid GenreUpdateCommand command) { // <5>
        genreRepository.update(command.id(), command.name());

        return ResponseEntity.noContent()
                .header(HttpHeaders.LOCATION, location(command.id()).getPath())
                .build();
    }

    @GetMapping("/list") // <6>
    public Iterable<Genre> list() {
        return genreRepository.findAll();
    }

    @PostMapping // <7>
    public ResponseEntity<Genre> save(@RequestBody @NotBlank String name) {
        Genre genre = genreRepository.save(name);

        return ResponseEntity
                .created(location(genre.id()))
                .body(genre);
    }

    @PostMapping("/ex") // <8>
    public ResponseEntity<Genre> saveExceptions(@RequestBody @NotBlank String name) {
        try {
            Genre genre = genreRepository.saveWithException(name);
            return ResponseEntity
                    .created(location(genre.id()))
                    .body(genre);
        } catch (DataAccessException e) {
            return ResponseEntity.noContent().build();
        }
    }

    @DeleteMapping("/{id}") // <9>
    @ResponseStatus(HttpStatus.NO_CONTENT)
    public void delete(@PathVariable Long id) {
        genreRepository.deleteById(id);
    }

    protected URI location(Long id) {
        return URI.create("/genres/" + id);
    }
    
}

1 The class is defined as a controller mapped to the path /genres.

2 Uses constructor injection to inject a bean of type GenreRepository.

3 Maps a GET request to /genres/{id}, which attempts to show a genre. This illustrates the use of a URL path variable.

4 Maps a PUT request to /genres, which attempts to update a genre.

5 Adds @Valid to any method parameter that requires validation. Uses a POJO supplied as a JSON payload in the request to populate the command.

6 Maps a GET request to /genres/list, which returns a list of genres. This mapping illustrates URL parameters being mapped to a single POJO.

7 Maps a POST request to /genres, which attempts to save a genre.

8 Maps a POST request to /ex, which generates an exception.

9 Maps a DELETE request to /genres/{id}, which attempts to remove a genre. This illustrates the use of a URL path variable.

1.7. Write Tests to Verify Application Logic #

Create a test class for the controller in src/test/java/com/example/GenreControllerTest.java, as follows:

package com.example;

import com.example.domain.Genre;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.web.server.LocalServerPort;
import org.springframework.boot.web.client.RestTemplateBuilder;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.client.RestTemplate;

import java.util.ArrayList;
import java.util.List;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNull;

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class GenreControllerTest {
    @LocalServerPort
    int port;

    @Autowired
    private RestTemplateBuilder builder;

    private RestTemplate template;

    @BeforeEach
    void setUp() {
        template = builder.rootUri("http://localhost:" + port).build();
    }

    @Test
    public void testFindNonExistingGenreReturnsNull() {
        assertNull(template.getForObject("/genres/99", Genre.class));
    }

    @Test
    public void testGenreCrudOperations() {

        List<Long> genreIds = new ArrayList<>();

        ResponseEntity<Genre> response = template.postForEntity("/genres", "DevOps", Genre.class);
        genreIds.add(entityId(response));

        assertEquals(HttpStatus.CREATED, response.getStatusCode());

        response = template.postForEntity("/genres", "Microservices", Genre.class);

        assertEquals(HttpStatus.CREATED, response.getStatusCode());

        Long id = entityId(response);
        genreIds.add(id);

        Genre genre = template.getForObject("/genres/" + id, Genre.class);

        assertEquals("Microservices", genre.name());

        template.put("/genres", new GenreUpdateCommand(id, "Micro-services"));

        genre = template.getForObject("/genres/" + id, Genre.class);
        assertEquals("Micro-services", genre.name());

        List<?> genres = template.getForObject("/genres/list", List.class);
        assertEquals(2, genres.size());

        response = template.postForEntity("/genres/ex", "Microservices", Genre.class);

        assertEquals(HttpStatus.NO_CONTENT, response.getStatusCode());

        genres = template.getForObject("/genres/list", List.class);
        assertEquals(2, genres.size());

        for(Long genreId : genreIds) {
            template.delete("/genres/" + genreId);
        }
        genres = template.getForObject("/genres/list", List.class);
        assertEquals(0, genres.size());
    }

    private Long entityId(ResponseEntity<Genre> response) {
        String path = "/genres/";
        String value = response.getHeaders().getLocation().getPath();
        if (value == null) {
            return null;
        }
        int index = value.indexOf(path);
        if (index != -1) {
            return Long.valueOf(value.substring(index + path.length()));
        }
        return null;
    }
}

2. Provision an Oracle Autonomous Database #

  1. In the Oracle Cloud Console, open the navigation menu, click Oracle Database. Under Autonomous Database, click Autonomous Transaction Processing.

  2. Click Create Autonomous Database and accept the default name for the database.

  3. Select Transaction Processing and Serverless. If you are using a trial account, make sure you select Always Free.

  4. Create an admin password (must be at least 12 characters and contain a number and an uppercase letter) and select Secure access from everywhere as the network access type.

  5. Click Create Autonomous Database to create your database.

  6. On the Autonomous Database Details screen click Copy to copy the OCID of the database. (This is a unique identifier which you will need later.)

2.1. Create a User #

  1. When the database has been provisioned, on the Autonomous Database Details page, click Database Actions. (If necessary, log in with username “ADMIN” and the admin password you created earlier.)

  2. Click SQL to open the SQL console.

  3. Copy and paste the following SQL commands into the worksheet:

     CREATE USER gcn_user IDENTIFIED BY "XXXXXXXXX";
     GRANT CONNECT, RESOURCE TO gcn_user;
     GRANT UNLIMITED TABLESPACE TO gcn_user;
    

    Create a schema user password (must be at least 12 characters and contain a number and an uppercase letter) and replace the text “XXXXXXXXX” with that password.

  4. Click Run Script to run the SQL commands.

3. Configuration #

Before you can test or run your application, provide configuration for Flyway and the application’s datasources.

3.1. Configure Flyway #

To create the database schema, use Flyway. Micronaut integrates with Flyway to automate schema changes, significantly simplify schema management tasks, such as migrating, rolling back, and reproducing in multiple environments.

Configure Flyway to perform migrations on the default datasources. Add the following to the src/main/resources/application.properties file:

flyway.datasources.default.enabled=true

Flyway migration is automatically triggered before your application starts. Flyway reads migration file(s) in the resources/db/migration/ directory, runs them if necessary, and verifies that the configured datasource is consistent with them.

Create a new Flyway migration SQL script in src/main/resources/db/migration/V1__create-schema.sql with the following contents

CREATE TABLE "GENRE" (
  "ID" NUMBER(19) PRIMARY KEY NOT NULL,
  "NAME" VARCHAR(255) NOT NULL
);
CREATE SEQUENCE "GENRE_SEQ" MINVALUE 1 START WITH 1 NOCACHE NOCYCLE;

During application startup, Flyway runs the commands in the SQL file and creates the schema needed for the application.

3.2. Configure Datasources #

The connection information and credentials for an Oracle Cloud Infrastructure Autonomous Database are stored in an Oracle Wallet. See Micronaut Oracle Cloud integration for more details and options for working with Oracle Cloud Infrastructure.

Create a properties file named src/main/resources/application.properties.

And add the following configurations:

datasources.default.ocid=<ocid> # 1
datasources.default.walletPassword=<wallet_password> # 2
datasources.default.username=<username> #3
datasources.default.password=<password> # 4
oci.config.profile=DEFAULT # 5

1 Replace <ocid> with the OCID of the database you copied when creating the database.

2 Replace <wallet_password> with a password to encrypt the wallet keys (must be at least eight characters and include at least one letter and either one numeric or special character).

3 Replace the <username> with “gcn_user” (the name of the user you created).

4 Replace the <password> with the “gcn_user” password you created.

5 Change the profile name if you’re not using the default, and optionally add a value for the path to the config file if necessary as described in the Authentication section of the Micronaut Oracle Cloud documentation.

4. Run the Tests (Optional) #

Note: The docker daemon must be running before running the tests.

Before running the test, add a configuration to run against a temporary database.

  1. Create a test properties file, src/test/resources/application.properties, and add the following contents:

     datasources.default.url=jdbc:tc:oracle:thin:@/xe
     datasources.default.driverClassName=org.testcontainers.jdbc.ContainerDatabaseDriver
     datasources.default.username=system
     datasources.default.password=oracle
    
     flyway.datasources.default.enabled=true
    

Note: Currently, there is no Oracle Database port for AArch64, hence Oracle XE container images cannot run. For more information, see Oracle XE on Apple M chips.

  1. Run the tests as follows:

    ./gradlew test

    Then open the file build/reports/tests/test/index.html in a browser to view the results.

    ./mvnw test

5. Run the Application #

To run the application, use the following command, which starts the application on port 8080.

./gradlew bootRun
./mvnw spring-boot:run

6. Test the Application #

Test the application by accessing its REST endpoints.

Run this command to test creating and storing a new Genre in the database:

curl -i -X "POST" "http://localhost:8080/genres" \
     -H 'Content-Type: application/json; charset=utf-8' \
     -d "Test"

You should see a response similar to:

{"id":1,"name":"Test"}

Confirm that the new Genre is saved in the database by listing its contents:

curl -i http://localhost:8080/genres/list

You should see a response similar to:

[{"id":1,"name":"Test"}]

Retrieve a single Genre from the database as follows:

curl -i http://localhost:8080/genres/1

You should see a response similar to:

{"id":1,"name":"Test"}

Confirm that you can update a Genre as follows:

curl -i -X "PUT" "http://localhost:8080/genres" \
     -H 'Content-Type: application/json; charset=utf-8' \
     -d $'{ "id": "1",  "name": "Updated" }'

Delete the Genre you added and then list the contents of the database to confirm that it has been deleted:

curl -i -X "DELETE" http://localhost:8080/genres/1
curl -i http://localhost:8080/genres/list

7. Clean Up Cloud Resources #

When you finish using the database, you can terminate it from the Oracle Cloud Console. For more information, see Terminate an Autonomous Database Instance.

Summary #

This guide demonstrated how to use GCN to create a Spring database application that stores data in an Oracle Cloud Infrastructure Autonomous Database using Micronaut Data.