Table of Contents

One-To-Many with Micronaut Data JDBC

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

Authors: Sergio del Amo

Micronaut Version: 4.6.3

1. Getting Started

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

2. What you will need

To complete this guide, you will need the following:

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

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

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

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.

5.1. 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=

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

6. Entities

Create an entity mapping the table contact:

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") (1)
public record ContactEntity(
        @Id (2)
        @GeneratedValue (3)
        @Nullable (4)
        Long id,

        @Nullable
        String firstName,

        @Nullable
        String lastName,

        @Nullable
        @Relation(value = Relation.Kind.ONE_TO_MANY, mappedBy = "contact") (5)
        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](https://docs.oracle.com/en/java/javase/14/language/records.html) 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:

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") (1)
public record PhoneEntity(
        @Id (2)
        @GeneratedValue  (3)
        @Nullable (4)
        Long id,

        @NonNull
        String phone,

        @Nullable
        @Relation(value = Relation.Kind.MANY_TO_ONE) (5)
        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](https://docs.oracle.com/en/java/javase/14/language/records.html) 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.

7. Projections

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

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 (1)
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:

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

8. Repositories

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) (1)
public interface PhoneRepository extends CrudRepository<PhoneEntity, Long> { (2)
    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.
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) (1)
public interface ContactRepository extends CrudRepository<ContactEntity, Long> { (2)
    @Join(value = "phones", type = Join.Type.LEFT_FETCH) (3)
    Optional<ContactEntity> getById(@NonNull Long id);

    @Query("select id, first_name, last_name from contact where id = :id") (4)
    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""") (5)
    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.

9. Tests

The following tests illustrates the association queries:

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) (1)
class ContactRepositoryTest {

    @Inject
    ContactRepository contactRepository; (2)

    @Inject
    PhoneRepository phoneRepository;  (3)

    @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 ContactRepository.

10. Testing the Application

To run the tests:

./gradlew test

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

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

12. Next steps

Explore more features with Micronaut Guides.

Read more about Micronaut Data

13. Help with the Micronaut Framework

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

14. License

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