Procedures & Functions

Note	All examples are from the Chinook demo

1. Overview

Codion’s procedure and function support provides a type-safe abstraction for executing database operations that go beyond simple CRUD. While you can implement procedures and functions by directly calling database stored procedures, Codion encourages implementing business logic in Java using the EntityConnection API.

ProcedureType: A typed identifier for a procedure that performs an operation without returning a value.
DatabaseProcedure: The implementation interface for procedures, taking a connection and optional argument.
FunctionType: A typed identifier for a function that performs an operation and returns a result.
DatabaseFunction: The implementation interface for functions, taking a connection and optional argument, returning a result.

Both procedures and functions are:

Registered with the domain model
Executed via EntityConnection
Type-safe with compile-time checking

Tip	Transaction control should be external to procedures and functions. Let the caller manage transactions rather than implementing transaction control within the procedure or function itself.

2. API Definition

Procedures and functions are defined as constants within the domain API, typically in the same interface that defines the related entity. The type parameters specify:

C - The connection type (usually EntityConnection)
T - The argument type (can be Void for no argument)
R - The return type (functions only)

2.1. Function with Custom Parameter Object

The RAISE_PRICE function demonstrates a function with a custom parameter record and collection return type:

FunctionType<EntityConnection, RaisePriceParameters, Collection<Entity>> RAISE_PRICE = functionType("chinook.raise_price");

2.2. Procedure with Collection Parameter

The UPDATE_TOTALS procedure demonstrates a procedure with a collection parameter type:

ProcedureType<EntityConnection, Collection<Long>> UPDATE_TOTALS = procedureType("chinook.update_totals");

2.3. Function Returning an Entity

The RANDOM_PLAYLIST function demonstrates a function that creates data and returns the resulting entity:

FunctionType<EntityConnection, RandomPlaylistParameters, Entity> RANDOM_PLAYLIST = functionType("chinook.random_playlist");

3. Implementation

Procedure and function implementations are registered with the domain model in the DomainModel constructor using the add() method. Implementations can be:

Inline lambda expressions for simple operations
Named classes for complex logic
Calls to database stored procedures/functions

3.1. Registration

Procedures and functions are registered alongside entity definitions:

public ChinookImpl() {
  super(DOMAIN);
  add(artist(), album(), employee(), customer(), genre(), preferences(), mediaType(),
          track(), invoice(), invoiceLine(), playlist(), playlistTrack(), artistRevenue());
  add(Customer.REPORT, classPathReport(ChinookImpl.class, "customer_report.jasper"));
  add(Track.RAISE_PRICE, new RaisePrice());
  add(Invoice.UPDATE_TOTALS, new UpdateTotals());
  add(Playlist.RANDOM_PLAYLIST, new CreateRandomPlaylist(entities()));
}

3.2. Function Implementation

RaisePrice

private static final class RaisePrice implements DatabaseFunction<EntityConnection, RaisePriceParameters, Collection<Entity>> {

  @Override
  public Collection<Entity> execute(EntityConnection entityConnection,
                                    RaisePriceParameters parameters) {
    Select select = where(Track.ID.in(parameters.trackIds()))
            .forUpdate()
            .build();

    return entityConnection.updateSelect(entityConnection.select(select).stream()
            .map(track -> raisePrice(track, parameters.priceIncrease()))
            .toList());
  }

  private static Entity raisePrice(Entity track, BigDecimal priceIncrease) {
    track.set(Track.UNITPRICE, track.get(Track.UNITPRICE).add(priceIncrease));

    return track;
  }
}

3.3. Procedure Implementation

UpdateTotals

private static final class UpdateTotals implements DatabaseProcedure<EntityConnection, Collection<Long>> {

  @Override
  public void execute(EntityConnection connection,
                      Collection<Long> invoiceIds) {
    Collection<Entity> invoices =
            connection.select(where(Invoice.ID.in(invoiceIds))
                    .forUpdate()
                    .build());

    connection.update(invoices.stream()
            .map(UpdateTotals::updateTotal)
            .filter(Entity::modified)
            .toList());
  }

  private static Entity updateTotal(Entity invoice) {
    invoice.set(Invoice.TOTAL, invoice.optional(Invoice.CALCULATED_TOTAL).orElse(BigDecimal.ZERO));

    return invoice;
  }
}

3.4. Complex Function Implementation

CreateRandomPlaylist

private static final class CreateRandomPlaylist implements DatabaseFunction<EntityConnection, RandomPlaylistParameters, Entity> {

  private final Entities entities;

  private CreateRandomPlaylist(Entities entities) {
    this.entities = entities;
  }

  @Override
  public Entity execute(EntityConnection connection,
                        RandomPlaylistParameters parameters) {
    List<Long> trackIds = randomTrackIds(connection, parameters.noOfTracks(), parameters.genres());

    return insertPlaylist(connection, parameters.playlistName(), trackIds);
  }

  private Entity insertPlaylist(EntityConnection connection, String playlistName,
                                List<Long> trackIds) {
    Entity playlist = connection.insertSelect(createPlaylist(playlistName));

    connection.insert(createPlaylistTracks(playlist.primaryKey().value(), trackIds));

    return playlist;
  }

  private Entity createPlaylist(String playlistName) {
    return entities.entity(Playlist.TYPE)
            .with(Playlist.NAME, playlistName)
            .build();
  }

  private List<Entity> createPlaylistTracks(Long playlistId, List<Long> trackIds) {
    return trackIds.stream()
            .map(trackId -> createPlaylistTrack(playlistId, trackId))
            .toList();
  }

  private Entity createPlaylistTrack(Long playlistId, Long trackId) {
    return entities.entity(PlaylistTrack.TYPE)
            .with(PlaylistTrack.PLAYLIST_ID, playlistId)
            .with(PlaylistTrack.TRACK_ID, trackId)
            .build();
  }

  private static List<Long> randomTrackIds(EntityConnection connection, int noOfTracks,
                                           Collection<Entity> genres) {
    return connection.select(Track.ID,
            where(Track.GENRE_FK.in(genres))
                    .orderBy(ascending(Track.RANDOM))
                    .limit(noOfTracks)
                    .build());
  }
}

4. Usage

Procedures and functions are executed via EntityConnection.execute(). The connection is passed to the implementation, which can use it for database operations.

4.1. Executing a Function

public void raisePriceOfSelected(BigDecimal increase) {
  if (selection().empty().not().is()) {
    Collection<Long> trackIds = Entity.values(Track.ID, selection().items().get());
    Collection<Entity> result = connection()
            .execute(Track.RAISE_PRICE, new RaisePriceParameters(trackIds, increase));
    replace(result);
  }
}

4.2. Executing a Procedure

private static Collection<Entity> updateTotals(Collection<Entity> invoiceLines, EntityConnection connection) {
  // Get the IDs of the invoices that need their totals updated
  Collection<Long> invoiceIds = distinct(InvoiceLine.INVOICE_ID, invoiceLines);
  // Execute the UPDATE_TOTALS procedure
  connection.execute(Invoice.UPDATE_TOTALS, invoiceIds);

  return invoiceLines;
}

4.3. Transactional Execution

Use EntityConnection.transaction() to execute procedures or functions, when multiple operations must succeed or fail together:

public void createRandomPlaylist(RandomPlaylistParameters parameters) {
  EntityConnection connection = connection();
  Entity randomPlaylist = transaction(connection, () -> connection.execute(Playlist.RANDOM_PLAYLIST, parameters));
  items().included().add(0, randomPlaylist);
  selection().item().set(randomPlaylist);
}

5. HTTP/JSON Serialization

When using HTTP-based connections with JSON serialization enabled (via HttpEntityConnection), procedures and functions are executed by serializing arguments and return values as JSON. This requires registering the argument and return types with the EntityObjectMapper.

5.1. Why Type Registration is Needed

Jackson’s ObjectMapper requires target types to deserialize JSON. While FunctionType and ProcedureType carry generic type parameters, these are erased at runtime. The type registry provides this information to the JSON serialization layer.

5.2. EntityObjectMapperFactory

Create an EntityObjectMapperFactory implementation and define your procedure and function types:

public final class ChinookObjectMapperFactory extends DefaultEntityObjectMapperFactory {

  public ChinookObjectMapperFactory() {
    super(Chinook.DOMAIN);
  }

  @Override
  public EntityObjectMapper entityObjectMapper(Entities entities) {
    EntityObjectMapper objectMapper = super.entityObjectMapper(entities);
    objectMapper.parameter(Invoice.UPDATE_TOTALS).set(new TypeReference<>() {});
    objectMapper.parameter(Track.RAISE_PRICE).set(RaisePriceParameters.class);
    objectMapper.parameter(Playlist.RANDOM_PLAYLIST).set(RandomPlaylistParameters.class);
    objectMapper.parameter(Customer.REPORT).set(new TypeReference<>() {});

    return objectMapper;
  }
}

5.3. Service Registration

provides is.codion.framework.json.domain.EntityObjectMapperFactory
        with is.codion.demos.chinook.domain.ChinookObjectMapperFactory;

or by creating a file at

src/main/resources/META-INF/services/is.codion.framework.json.domain.EntityObjectMapperFactory

Containing the fully qualified class name:

is.codion.demos.chinook.domain.ChinookObjectMapperFactory

5.4. HTTP Connection Protocol

When a procedure or function is executed via HTTP:

Client serializes the argument to JSON
HTTP POST sends the request to the server
Server deserializes using the registered argument type
Server executes the procedure/function
Server serializes the result (functions only)
Client deserializes using the registered return type

Note	Only HTTP connections with JSON serialization enabled require type registration.