Table of Contents

One-to-Many with Micronaut Data JDBC

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

1. Getting Started

In this guide, we will create a Python application built with Pyronaut.

2. What you will need

To complete this guide, you will need the following:

  • Some time on your hands

  • GraalPy installed and the Pyronaut CLI available locally

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 Pyronaut CLI (Command Line Interface) or Pyronaut Launch

pyronaut create example.micronaut.micronautguide --features=data-jdbc,liquibase,h2

5.1. Data Source configuration

Define the datasource in config/application.toml.

config/application.toml
[datasources.default]
dialect = "H2"
driver-class-name = "org.h2.Driver"
url = "jdbc:h2:mem:devDb;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE"
username = "sa"
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:

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

config/application.toml
[liquibase.datasources.default]
change-log = "classpath:db/liquibase-changelog.xml"

Create the following files with the database schema creation:

config/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/example/micronaut/contact_entity.py
from __future__ import annotations

from dataclasses import dataclass, field
from typing import TYPE_CHECKING, Annotated

from micronaut.data.annotation import GeneratedValue, Id, MappedEntity, Relation

if TYPE_CHECKING:
    from .phone_entity import PhoneEntity


@dataclass
@MappedEntity("contact")  (1)
class ContactEntity:
    id: Annotated[int | None, Id, GeneratedValue]    (2) (3) (4)
    firstName: str
    lastName: str
    phones: Annotated[
        list[PhoneEntity],
        Relation(value=Relation.Kind.ONE_TO_MANY, mappedBy="contact"),
    ] = field(default_factory=list)  (5)
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:

src/example/micronaut/phone_entity.py
from __future__ import annotations

from dataclasses import dataclass
from typing import TYPE_CHECKING, Annotated

from micronaut.data.annotation import GeneratedValue, Id, MappedEntity, Relation

if TYPE_CHECKING:
    from .contact_entity import ContactEntity


@dataclass
@MappedEntity("phone")  (1)
class PhoneEntity:
    id: Annotated[int | None, Id, GeneratedValue]    (2) (3) (4)
    phone: str
    contact: Annotated[ContactEntity, Relation(value=Relation.Kind.MANY_TO_ONE)]  (5)
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.

7. Projections

Create one projection to return a complete view, phones included, of the contact:

src/example/micronaut/contact_complete.py
from dataclasses import dataclass

from micronaut.core.annotation import Introspected


@dataclass
@Introspected  (1)
class ContactComplete:
    id: int | None
    firstName: str
    lastName: str
    phones: list[str] | None = None
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 projection to preview a contact, no phones:

src/example/micronaut/contact_preview.py
from dataclasses import dataclass

from micronaut.core.annotation import Introspected


@dataclass
@Introspected  (1)
class ContactPreview:
    id: int | None
    firstName: str
    lastName: str
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/example/micronaut/phone_repository.py
from micronaut.data.jdbc.annotation import JdbcRepository
from micronaut.data.model.query.builder.sql import Dialect
from micronaut.data.repository import CrudRepository

from .phone_entity import PhoneEntity


@JdbcRepository(dialect=Dialect.H2)  (1)
class PhoneRepository(CrudRepository[PhoneEntity, int]):  (2)
    pass
1 @JdbcRepository with a specific dialect.
2 By extending CrudRepository you enable automatic generation of CRUD (Create, Read, Update, Delete) operations.
src/example/micronaut/contact_repository.py
from java.util import Optional
from micronaut.data.annotation import Join, Query
from micronaut.data.jdbc.annotation import JdbcRepository
from micronaut.data.model.query.builder.sql import Dialect
from micronaut.data.repository import CrudRepository

from .contact_complete import ContactComplete
from .contact_entity import ContactEntity
from .contact_preview import ContactPreview


@JdbcRepository(dialect=Dialect.H2)  (1)
class ContactRepository(CrudRepository[ContactEntity, int]):  (2)
    @Join(value="phones", type=Join.Type.LEFT_FETCH)  (3)
    def getById(self, id: int) -> Optional[ContactEntity]: ...

    @Query(value="select id, first_name, last_name from contact where id = :id")  (4)
    def findPreviewById(self, id: int) -> Optional[ContactPreview]: ...

    @Query(value="""
        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)
    def findCompleteById(self, id: int) -> Optional[ContactComplete]: ...
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 illustrate the association queries:

tests/example/micronaut/test_contact_repository.py
import pytest

from pyronaut.test import MicronautTest, micronaut_test_fixture

from example.micronaut.contact_entity import ContactEntity
from example.micronaut.phone_entity import PhoneEntity


@pytest.fixture
def my_context(request):
    fixture = micronaut_test_fixture(
        request,
        MicronautTest(start_application=False, transactional=False),  (1)
    )
    yield fixture
    fixture.stop()


@pytest.fixture
def contact_repository(my_context):
    return my_context["example.micronaut.ContactRepository"]  (2)


@pytest.fixture
def phone_repository(my_context):
    return my_context["example.micronaut.PhoneRepository"]  (3)


def test_associations_querying(contact_repository, phone_repository):
    first_name = "Sergio"
    last_name = "del Amo"
    contact_count = contact_repository.count()
    contact = contact_repository.save(ContactEntity(None, first_name, last_name))
    assert contact_repository.count() == contact_count + 1

    preview = contact_repository.findPreviewById(contact.id).orElse(None)
    assert preview is not None
    assert preview.id == contact.id
    assert preview.firstName == first_name
    assert preview.lastName == last_name

    contact_with_join = contact_repository.getById(contact.id).orElse(None)
    assert contact_with_join is not None
    assert contact_with_join.id == contact.id
    assert contact_with_join.firstName == first_name
    assert contact_with_join.lastName == last_name
    assert list(contact_with_join.phones) == []

    complete = contact_repository.findCompleteById(contact.id).orElse(None)
    assert complete is not None
    assert complete.id == contact.id
    assert complete.firstName == first_name
    assert complete.lastName == last_name
    assert complete.phones is None

    american_phone = "+14155552671"
    uk_phone = "+442071838750"
    phone_count = phone_repository.count()
    contact_reference = ContactEntity(contact.id, first_name, last_name)
    us_phone = phone_repository.save(PhoneEntity(None, american_phone, contact_reference))
    uk_phone_entity = phone_repository.save(PhoneEntity(None, uk_phone, contact_reference))
    assert phone_repository.count() == phone_count + 2

    preview = contact_repository.findPreviewById(contact.id).orElse(None)
    assert preview is not None
    assert preview.id == contact.id
    assert preview.firstName == first_name
    assert preview.lastName == last_name

    contact_without_join = contact_repository.findById(contact.id).orElse(None)
    assert contact_without_join is not None
    assert list(contact_without_join.phones) == []

    contact_with_join = contact_repository.getById(contact.id).orElse(None)
    assert contact_with_join is not None
    phones = list(contact_with_join.phones)
    assert {phone.phone for phone in phones} == {american_phone, uk_phone}
    assert {phone.id for phone in phones} == {us_phone.id, uk_phone_entity.id}

    complete = contact_repository.findCompleteById(contact.id).orElse(None)
    assert complete is not None
    assert set(complete.phones) == {american_phone, uk_phone}

    phone_repository.deleteById(us_phone.id)
    phone_repository.deleteById(uk_phone_entity.id)
    contact_repository.deleteById(contact.id)
    assert phone_repository.count() == phone_count
    assert contact_repository.count() == contact_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.

10. Testing the Application

To run the tests:

pyronaut install
pyronaut validate-config
pyronaut test

11. Next Steps

Explore more features with Micronaut Guides.

Read more about Micronaut Data.

12. License

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