Java / Gradle

One-to-Many with Micronaut Data JDBC

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

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

One-to-Many Relationship

In this tutorial, you develop a one-to-many relationship, as illustrated in the following tables.

Table 1. Table: Contact
id	first_name	last_name
1	Sergio	del Amo

Table 2. Table: Phone
id	phone	contact_id
1	+14155552671	1
2	+442071838750	1

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,h2,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, h2, 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.

Data Source configuration

Define the datasource in src/main/resources/application.properties.

src/main/resources/application.properties

datasources.default.dialect=H2
datasources.default.driver-class-name=org.h2.Driver
datasources.default.url=jdbc\:h2\:mem\:devDb;LOCK_TIMEOUT\=10000;DB_CLOSE_ON_EXIT\=FALSE
datasources.default.username=sa
datasources.default.password=

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.

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>

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">
    <createTable tableName="contact">
      <column name="id" type="BIGINT" autoIncrement="true">
        <constraints nullable="false"
                     unique="true"
                     primaryKey="true"
                     primaryKeyName="pk_contact"/>
      </column>

      <column name="first_name" type="VARCHAR(255)">
        <constraints nullable="true"/>
      </column>

      <column name="last_name" type="VARCHAR(255)">
        <constraints nullable="true"/>
      </column>
    </createTable>

    <createTable tableName="phone">
      <column name="id" type="BIGINT" autoIncrement="true">
        <constraints nullable="false"
                     unique="true"
                     primaryKey="true"
                     primaryKeyName="pk_phone"/>
      </column>
      <column name="phone" type="VARCHAR(20)">
        <constraints nullable="false"/>
      </column>

      <column name="contact_id" type="BIGINT">
        <constraints nullable="false"/>
      </column>
    </createTable>

    <addForeignKeyConstraint baseTableName="phone"
                             baseColumnNames="contact_id"
                             constraintName="fk_phone_contact"
                             referencedTableName="contact"
                             referencedColumnNames="id"/>
    <rollback>
      <dropTable tableName="phone"/>
      <dropTable tableName="contact"/>
    </rollback>
  </changeSet>
</databaseChangeLog>

Entities

Create an entity mapping the table contact:

java/src/main/java/example/micronaut/ContactEntity.java

package example.micronaut;

import io.micronaut.core.annotation.Nullable;
import io.micronaut.core.util.StringUtils;
import io.micronaut.data.annotation.GeneratedValue;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;
import io.micronaut.data.annotation.Relation;

import java.util.List;

@MappedEntity("contact") // 
public record ContactEntity(
        @Id // 
        @GeneratedValue // 
        @Nullable // 
        Long id,

        @Nullable
        String firstName,

        @Nullable
        String lastName,

        @Nullable
        @Relation(value = Relation.Kind.ONE_TO_MANY, mappedBy = "contact") // 
        List<PhoneEntity>phones
) {
}

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	Since Records have immutable constructor arguments, those arguments need to be marked as `@Nullable`, and you should pass null for those arguments.
5	You can specify a relationship (one-to-one, one-to-many, etc.) with the `@Relation` annotation.

Create an entity mapping the table phone:

java/src/main/java/example/micronaut/PhoneEntity.java

package example.micronaut;

import io.micronaut.core.annotation.NonNull;
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 io.micronaut.data.annotation.Relation;
@MappedEntity("phone") // 
public record PhoneEntity(
        @Id // 
        @GeneratedValue  // 
        @Nullable // 
        Long id,

        @NonNull
        String phone,

        @Nullable
        @Relation(value = Relation.Kind.MANY_TO_ONE) // 
        ContactEntity contact
) {
}

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	Since Records have immutable constructor arguments, those arguments need to be marked as `@Nullable`, and you should pass null for those arguments.
5	You can specify a relationship (one-to-one, one-to-many, etc.) with the `@Relation` annotation.

Projections

Create one Java record to project a complete view, phones included, of the contact:

java/src/main/java/example/micronaut/ContactComplete.java

package example.micronaut;

import io.micronaut.core.annotation.Introspected;
import io.micronaut.core.annotation.NonNull;
import io.micronaut.core.annotation.Nullable;

import java.util.Set;

@Introspected // 
public record ContactComplete(
        @NonNull Long id,
        @Nullable String firstName,
        @Nullable String lastName,
        @Nullable Set<String> phones) {
}

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.

Create one Java record to preview a contact, no phones:

java/src/main/java/example/micronaut/ContactPreview.java

package example.micronaut;

import io.micronaut.core.annotation.Introspected;
import io.micronaut.core.annotation.NonNull;
import io.micronaut.core.annotation.Nullable;

@Introspected // 
public record ContactPreview(
        @NonNull Long id,
        @Nullable String firstName,
        @Nullable String lastName
) {
}

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.

Repositories

java/src/main/java/example/micronaut/PhoneRepository.java

package example.micronaut;

import io.micronaut.core.annotation.NonNull;
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.H2) // 
public interface PhoneRepository extends CrudRepository<PhoneEntity, Long> { // 
    void deleteByContact(@NonNull ContactEntity contact);
}

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

java/src/main/java/example/micronaut/ContactRepository.java

package example.micronaut;

import io.micronaut.core.annotation.NonNull;
import io.micronaut.data.annotation.Join;
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.H2) // 
public interface ContactRepository extends CrudRepository<ContactEntity, Long> { // 
    @Join(value = "phones", type = Join.Type.LEFT_FETCH) // 
    Optional<ContactEntity> getById(@NonNull Long id);

    @Query("select id, first_name, last_name from contact where id = :id") // 
    Optional<ContactPreview> findPreviewById(@NonNull Long id);

    @Query("""
select c.id, c.first_name, c.last_name, group_concat(p.phone) as phones
 from contact c
 left outer join phone p on c.id = p.contact_id
 where c.id = :id
 group by c.id""") // 
    Optional<ContactComplete> findCompleteById(@NonNull Long id);
}

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 `@Join` annotation on your repository interface to specify that a `JOIN LEFT FETCH` should be executed to retrieve the associated `phones`.
4	You can use the `@Query` annotation to specify an explicit query.

Tests

The following tests illustrate the association queries:

java/src/test/java/example/micronaut/ContactRepositoryTest.java

package example.micronaut;

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

import java.util.*;

import static org.junit.jupiter.api.Assertions.*;

@MicronautTest(startApplication = false, transactional = false) // 
class ContactRepositoryTest {

    @Inject
    ContactRepository contactRepository; // 

    @Inject
    PhoneRepository phoneRepository;  // 

    @Test
    void testAssociationsQuerying() {
        String firstName = "Sergio";
        String lastName = "Sergio";
        long contactCount = contactRepository.count();
        ContactEntity e = contactRepository.save(new ContactEntity(null, firstName, lastName, null));
        assertEquals(1 + contactCount, contactRepository.count());

        Optional<ContactPreview> preview = contactRepository.findPreviewById(e.id());
        assertTrue(preview.isPresent());
        assertEquals(new ContactPreview(e.id(), firstName, lastName), preview.get());

        // Query with @Join
        Optional<ContactEntity> contactEntity = contactRepository.getById(e.id());
        assertTrue(contactEntity.isPresent());
        ContactEntity expected = new ContactEntity(contactEntity.get().id(),
                firstName,
                lastName,
                Collections.emptyList());
        assertEquals(expected, contactEntity.get());

        Optional<ContactComplete> complete = contactRepository.findCompleteById(e.id());
        assertTrue(complete.isPresent());
        assertEquals(new ContactComplete(e.id(), firstName, lastName, null), complete.get());

        String americanPhone = "+14155552671";
        String ukPhone = "+442071838750";
        long phoneCount = phoneRepository.count();
        ContactEntity contactReference = new ContactEntity(e.id(), null, null, null);
        PhoneEntity usPhoneEntity = phoneRepository.save(new PhoneEntity(null, americanPhone, contactReference));
        PhoneEntity ukPhoneEntity =phoneRepository.save(new PhoneEntity(null, ukPhone, contactReference));
        assertEquals(2 + phoneCount, phoneRepository.count());

        // Projection without join with @Query
        preview = contactRepository.findPreviewById(e.id());
        assertTrue(preview.isPresent());
        assertEquals(new ContactPreview(e.id(), firstName, lastName), preview.get());

        // findById without @Join
        contactEntity = contactRepository.findById(e.id());
        assertTrue(contactEntity.isPresent());
        assertEquals(new ContactEntity(contactEntity.get().id(), firstName, lastName, Collections.emptyList()), contactEntity.get());

        // Query with @Join
        contactEntity = contactRepository.getById(e.id());
        assertTrue(contactEntity.isPresent());
        expected = new ContactEntity(contactEntity.get().id(),
                firstName,
                lastName,
                List.of(
                        new PhoneEntity(usPhoneEntity.id(), usPhoneEntity.phone(), new ContactEntity(e.id(), e.firstName(), e.lastName(), Collections.emptyList())),
                        new PhoneEntity(ukPhoneEntity.id(), ukPhoneEntity.phone(), new ContactEntity(e.id(), e.firstName(), e.lastName(), Collections.emptyList()))));
        assertEquals(expected, contactEntity.get());

        // Projection with join with @Query
        complete = contactRepository.findCompleteById(e.id());
        assertTrue(complete.isPresent());
        assertEquals(new ContactComplete(e.id(), firstName, lastName, Set.of(americanPhone, ukPhone)), complete.get());

        //cleanup
        phoneRepository.deleteByContact(contactReference);
        contactRepository.deleteById(e.id());
        assertEquals(phoneCount, phoneRepository.count());
        assertEquals(contactCount, contactRepository.count());
    }

}

1	Annotate the class with `@MicronautTest` so the Micronaut framework will initialize the application context and the embedded server. By default, each `@Test` method will be wrapped in a transaction that will be rolled back when the test finishes. This behaviour is is changed by setting `transaction` to `false`.
2	Injection for `ContactRepository`.
3	Injection for `PhoneRepository`.

Testing the Application

To run the tests:

./gradlew test

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

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.

Help with the Micronaut Framework

The Micronaut Foundation sponsored the creation of this Guide. A variety of consulting and support services are available.

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).