Java / Gradle

Many-to-Many with Micronaut Data JDBC and Oracle

Learn how to map a many-to-many association with Micronaut Data JDBC and Oracle.

Sergio del Amo

On this guide

In this section

Getting Started

In this guide, we will create a Micronaut application written in Java.

What you will need

To complete this guide, you will need the following:

Some time on your hands
A decent text editor or IDE (e.g. IntelliJ IDEA)
JDK 21 or greater installed with JAVA_HOME configured appropriately

Solution

We recommend that you follow the instructions in the next sections and create the application step by step. However, you can go right to the completed example.

Download and unzip the source

Many-To-Many Relationship

A relationship is a connection between two types of entities. In the case of a many-to-many relationship, both sides can relate to multiple instances of the other side.

In this guide, you are going to create a many-to-many relationship between User and Role entities using Micronaut Data JDBC. A user can have many roles and the same role can be applied to multiple users.

The application consumes a database schema with the following structure:

Writing the Application

Create an application using the Micronaut Command Line Interface or with Micronaut Launch.

mn create-app example.micronaut.micronautguide \
    --features=data-jdbc,liquibase,oracle,validation,graalvm \
    --build=gradle \
    --lang=java \
    --test=junit

Note	If you don’t specify the `--build` argument, Gradle with the Kotlin DSL is used as the build tool. If you don’t specify the `--lang` argument, Java is used as the language. If you don’t specify the `--test` argument, JUnit is used for Java and Kotlin, and Spock is used for Groovy.

The previous command creates a Micronaut application with the default package example.micronaut in a directory named micronautguide.

If you use Micronaut Launch, select Micronaut Application as application type and add data-jdbc, liquibase, oracle, validation, and graalvm features.

Note	If you have an existing Micronaut application and want to add the functionality described here, you can view the dependency and configuration changes from the specified features, and apply those changes to your application.

Diff

Setup IntelliJ IDEA to develop Micronaut Applications.

The previous command creates a Micronaut application with the default package example.micronaut in a directory named micronautguide.

Note	If you have an existing Micronaut application and want to add the functionality described here, you can view the dependency and configuration changes from the specified features, and apply those changes to your application.

Micronaut Data JDBC

Add Micronaut Data JDBC dependencies to the project:

build.gradle

annotationProcessor("io.micronaut.data:micronaut-data-processor")
implementation("io.micronaut.data:micronaut-data-jdbc")
implementation("io.micronaut.sql:micronaut-jdbc-hikari")

Oracle Driver

Add also the Oracle Driver

build.gradle

runtimeOnly("com.oracle.database.jdbc:ojdbc11")

Database Configuration

And the database configuration:

java/src/main/resources/application.properties

datasources.default.schema-generate=NONE
datasources.default.driver-class-name=oracle.jdbc.OracleDriver 
datasources.default.db-type=oracle 
datasources.default.dialect=ORACLE

1	Use Oracle driver.
2	In order for the database to be properly detected by Micronaut Test Resources.
3	Configure the Oracle dialect.

Database Migration with Liquibase

We need a way to create the database schema. For that, we use Micronaut integration with Liquibase.

Add the following snippet to include the necessary dependencies:

build.gradle

implementation("io.micronaut.liquibase:micronaut-liquibase")

Configure the database migrations directory for Liquibase in application.properties.

java/src/main/resources/application.properties

liquibase.datasources.default.change-log=classpath\:db/liquibase-changelog.xml

Create the following files with the database schema creation:

src/main/resources/db/liquibase-changelog.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
  <include file="changelog/01-schema.xml" relativeToChangelogFile="true"/>
</databaseChangeLog>

java/src/main/resources/db/changelog/01-schema.xml

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
  <changeSet id="01" author="username">
      <createSequence sequenceName="users_seq" startValue="1" incrementBy="1"/>
      <createSequence sequenceName="role_seq" startValue="1" incrementBy="1"/>

      <createTable tableName="users">
          <column name="id" type="BIGINT" autoIncrement="true">
              <constraints primaryKey="true" primaryKeyName="pk_user" nullable="false"/>
          </column>
          <column name="username" type="VARCHAR(255)">
              <constraints nullable="false" unique="true" uniqueConstraintName="uk_user_username"/>
          </column>
      </createTable>

      <createTable tableName="role">
          <column name="id" type="BIGINT" autoIncrement="true">
              <constraints primaryKey="true" primaryKeyName="pk_role" nullable="false"/>
          </column>
          <column name="authority" type="VARCHAR(255)">
              <constraints nullable="false" unique="true" uniqueConstraintName="uk_role_authority"/>
          </column>
      </createTable>

      <createTable tableName="user_role">
          <column name="user_id" type="BIGINT">
              <constraints nullable="false"/>
          </column>
          <column name="role_id" type="BIGINT">
              <constraints nullable="false"/>
          </column>
      </createTable>

      <addPrimaryKey tableName="user_role" constraintName="pk_user_role" columnNames="user_id, role_id"/>

      <addForeignKeyConstraint
              baseTableName="user_role"
              baseColumnNames="user_id"
              constraintName="fk_user_role_user"
              referencedTableName="users"
              referencedColumnNames="id"
              onDelete="CASCADE"/>

      <addForeignKeyConstraint
              baseTableName="user_role"
              baseColumnNames="role_id"
              constraintName="fk_user_role_role"
              referencedTableName="role"
              referencedColumnNames="id"
              onDelete="CASCADE"/>
  </changeSet>
</databaseChangeLog>

Entities

User

java/src/main/java/example/micronaut/UserEntity.java

package example.micronaut;

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.NotBlank;

@MappedEntity("users") // 
public record UserEntity(@Nullable
                   @Id // 
                   @GeneratedValue(GeneratedValue.Type.SEQUENCE) // 
                   Long id,

                   @NotBlank // 
                   String username
                   )  {
}

1	Annotate the class with `@MappedEntity` to map the class to the table defined in the schema.
2	Specifies the ID of an entity
3	Specifies that the property value is generated by the database and not included in inserts
4	Use `jakarta.validation.constraints` Constraints to ensure the data matches your expectations.

Role

Create a Role domain class to store authorities within the application.

java/src/main/java/example/micronaut/Role.java

package example.micronaut;

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.NotBlank;

@MappedEntity // 
public record Role(@Nullable
                   @Id // 
                   @GeneratedValue(GeneratedValue.Type.SEQUENCE) // 
                   Long id,

                   @NotBlank // 
                   String authority) {
}

1	Annotate the class with `@MappedEntity` to map the class to the table defined in the schema.
2	Specifies the ID of an entity
3	Specifies that the property value is generated by the database and not included in inserts
4	Use `jakarta.validation.constraints` Constraints to ensure the data matches your expectations.

UserRole

Create a UserRole entity that stores a many-to-many relationship between User and Role.

src/main/java/example/micronaut/UserRole.java

package example.micronaut;

import io.micronaut.data.annotation.EmbeddedId;
import io.micronaut.data.annotation.MappedEntity;

@MappedEntity // 
public class UserRole {

    @EmbeddedId // 
    private final UserRoleId id;

    public UserRole(UserRoleId id) {
        this.id = id;
    }

    public UserRole(UserEntity user, Role role) {
        this(new UserRoleId(user, role));
    }

    public UserRoleId getId() {
        return id;
    }
}

1	Annotate the class with `@MappedEntity` to map the class to the table defined in the schema.
2	Composite primary keys can be defined using `@EmbeddedId` annotation.

src/main/java/example/micronaut/UserRoleId.java

package example.micronaut;

import io.micronaut.data.annotation.Embeddable;
import io.micronaut.data.annotation.Relation;

import java.util.Objects;

@Embeddable // 
public class UserRoleId {

    @Relation(value = Relation.Kind.MANY_TO_ONE) // 
    private final UserEntity user;

    @Relation(value = Relation.Kind.MANY_TO_ONE) // 
    private final Role role;

    public UserRoleId(UserEntity user, Role role) {
        this.user = user;
        this.role = role;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (o == null || getClass() != o.getClass()) {
            return false;
        }
        UserRoleId userRoleId = (UserRoleId) o;
        return role.id().equals(userRoleId.getRole().id()) &&
                user.id().equals(userRoleId.getUser().id());
    }

    @Override
    public int hashCode() {
        return Objects.hash(role.id(), user.id());
    }

    public UserEntity getUser() {
        return user;
    }

    public Role getRole() {
        return role;
    }
}

1	Specifies that the bean is embeddable.
2	You can specify a relationship (one-to-one, one-to-many, etc.) with the `@Relation` annotation.

Projection

Create a User record that represents a user and their assigned roles.

src/main/java/example/micronaut/User.java

package example.micronaut;

import io.micronaut.core.annotation.Introspected;
import io.micronaut.core.annotation.NonNull;
import io.micronaut.core.annotation.Nullable;
import jakarta.validation.constraints.NotBlank;
import java.util.List;

@Introspected // 
public record User(@NonNull @NotBlank Long id,
                   @NonNull @NotBlank String username,
                   @Nullable List<String> authorities) {
}

1	Annotate the class with `@Introspected` to generate `BeanIntrospection` metadata at compilation time. This information can be used, for example, to render the POJO as JSON using Jackson without using reflection.

JDBC Repositories

Create the following JDBC repositories:

User Repository

java/src/main/java/example/micronaut/UserJdbcRepository.java

package example.micronaut;

import io.micronaut.data.annotation.Query;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;

import java.util.Optional;

@JdbcRepository(dialect = Dialect.ORACLE) // 
public interface UserJdbcRepository extends CrudRepository<UserEntity, Long> { // 
    UserEntity save(String username);

    // 
    @Query(value = """
            SELECT
                u.id,
                u.username,
                LISTAGG(r.authority, ',') WITHIN GROUP (ORDER BY r.authority) AS authorities
            FROM users u
            LEFT JOIN user_role ur ON ur.user_id = u.id
            LEFT JOIN role r ON r.id = ur.role_id
            WHERE u.username = :username
            GROUP BY u.id, u.username
            """)
    Optional<User> findByUsername(String username);
}

1	`@JdbcRepository` with a specific dialect.
2	By extending `CrudRepository` you enable automatic generation of CRUD (Create, Read, Update, Delete) operations.
3	You can use the `@Query` annotation to specify an explicit query.

Role Repository

java/src/main/java/example/micronaut/RoleJdbcRepository.java

package example.micronaut;

import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;
import java.util.Optional;

@JdbcRepository(dialect = Dialect.ORACLE) // 
public interface RoleJdbcRepository extends CrudRepository<Role, Long> { // 
    Role save(String authority);

    Optional<Role> findByAuthority(String authority);

    void deleteByAuthority(String authority);
}

1	`@JdbcRepository` with a specific dialect.
2	By extending `CrudRepository` you enable automatic generation of CRUD (Create, Read, Update, Delete) operations.

UserRole Repository

java/src/main/java/example/micronaut/UserRoleJdbcRepository.java

package example.micronaut;

import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;

@JdbcRepository(dialect = Dialect.ORACLE) // 
public interface UserRoleJdbcRepository extends CrudRepository<UserRole, UserRoleId> { // 
}

1	`@JdbcRepository` with a specific dialect.
2	By extending `CrudRepository` you enable automatic generation of CRUD (Create, Read, Update, Delete) operations.

A domain class fulfills the M in the Model View Controller (MVC) pattern and represents a persistent entity that is mapped onto an underlying database table.

Test

Add a test that verifies the many-to-many relationship:

src/test/java/example/micronaut/ManyToManyTest.java

package example.micronaut;

import io.micronaut.test.extensions.junit5.annotation.MicronautTest;
import org.junit.jupiter.api.Test;

import java.util.List;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertNotNull;

@MicronautTest(startApplication = false) // 
class ManyToManyTest {

    private static final String ROLE_USER = "ROLE_USER";
    private static final String ROLE_ADMIN = "ROLE_ADMIN";
    private static final String U_SERGIO = "sergio";
    private static final String U_TIM = "tim";

    @Test
    void testManyToManyPersistence(RoleJdbcRepository roleRepo,
              UserJdbcRepository userRepo,
              UserRoleJdbcRepository userRoleRepo) {
        Role roleUser = roleRepo.save(ROLE_USER);
        Role roleAdmin = roleRepo.save(ROLE_ADMIN);

        assertFalse(userRepo.findByUsername(U_SERGIO).isPresent());
        UserEntity sergio = userRepo.save(U_SERGIO);
        assertUser(userRepo.findByUsername(U_SERGIO).orElse(null), U_SERGIO, null);
        userRoleRepo.save(new UserRole(sergio, roleUser));
        userRoleRepo.save(new UserRole(sergio, roleAdmin));
        assertUser(userRepo.findByUsername(U_SERGIO).orElse(null), U_SERGIO, List.of(ROLE_ADMIN, ROLE_USER));

        UserEntity tim = userRepo.save(U_TIM);
        userRoleRepo.save(new UserRole(tim, roleUser));
        assertUser(userRepo.findByUsername(U_TIM).orElse(null), U_TIM, List.of(ROLE_USER));

        userRoleRepo.delete(new UserRole(tim, roleUser));
        userRoleRepo.delete(new UserRole(sergio, roleUser));
        userRoleRepo.delete(new UserRole(sergio, roleAdmin));

        userRepo.delete(sergio);
        userRepo.delete(tim);

        roleRepo.deleteByAuthority(ROLE_ADMIN);
        roleRepo.deleteByAuthority(ROLE_USER);
    }

    void assertUser(User user, String expectedUsername, List<String> expectedAuthorities) {
        assertNotNull(user);
        assertNotNull(user.id());
        assertEquals(expectedUsername, user.username());
        assertEquals(expectedAuthorities, user.authorities());
    }
}

1	Annotate the class with `@MicronautTest` so the Micronaut framework will initialize the application context. This test does not need the embedded server. Set `startApplication` to `false` to avoid starting it.

Testing the Application

To run the tests:

./gradlew test

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

If you run the test, you will see an Oracle Database container being started by Micronaut Test Resources through integration with Testcontainers to provide throwaway containers for testing.

Micronaut Test Resources Goals

Micronaut Test Resources aims to achieve the following goals:

zero-configuration: without adding any configuration, test resources should be spawned and the application configured to use them. Configuration is only required for advanced use cases.

classpath isolation: use of test resources shouldn’t leak into your application classpath, nor your test classpath

compatible with GraalVM native: if you build a native binary, or run tests in native mode, test resources should be available

easy to use: the Micronaut build plugins for Gradle and Maven should handle the complexity of figuring out the dependencies for you

extensible: you can implement your own test resources, in case the built-in ones do not cover your use case

technology agnostic: while lots of test resources use Testcontainers under the hood, you can use any other technology to create resources

Native Tests

The io.micronaut.application Micronaut Gradle Plugin automatically integrates with GraalVM by applying the Gradle plugin for GraalVM Native Image building.

This plugin supports running tests on the JUnit Platform as native images. This means that tests will be compiled and executed as native code.

To execute the tests, execute:

./gradlew nativeTest

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

INFO: A test may be disabled within a GraalVM native image via the @DisabledInNativeImage annotation.

Next Steps

Explore more features with Micronaut Guides.

License

Note	All guides are released with an Apache License 2.0 for the code and a Creative Commons Attribution 4.0 license for the writing and media (images).