EntityConnection

Codion’s EntityConnection is the primary interface for executing database operations — including querying, modifying, transaction control, calling procedures and functions and filling reports. It exposes a small, explicit API for working with Entity instances and makes no assumptions about your database engine or schema design.

Codion’s database layer is intentionally minimal. It does not perform SQL joins, nor does it rely on DB-specific features — except where needed for primary key generation via KeyGenerator strategies.

Instead, it gives you predictable, queryable access to individual Entity objects and their associated foreign keys — controlled through a feature called reference depth.

The Chinook domain model is used in the examples below.

1. Selecting

By default, when you select a row using EntityConnection you receive an Entity instance along with a single level of foreign key references, that is a so-called reference depth of one. This means that selecting a track you get all the entities referenced via foreign keys as well.

The reference depth can be configured on a foreign key basis when defining entities. A reference depth of zero means that no foreign key references are fetched, and a value larger than one means that not only is the foreign key reference fetched but also its foreign key references, until the defined depth has been reached. A negative reference depth means no limit with the whole dependency graph fetched. This limiting of foreign key reference depth can be turned off, meaning the full reference graph is always fetched, via a system property:

codion.db.limitForeignKeyReferenceDepth=false

or the LocalEntityConnection.LIMIT_FOREIGN_KEY_REFERENCE_DEPTH configuration value:

LocalEntityConnection.LIMIT_FOREIGN_KEY_REFERENCE_DEPTH.set(false);

or on a connection instance via limitForeignKeyReferenceDepth(boolean limitForeignKeyReferenceDepth)

connection.limitForeignKeyReferenceDepth(false);

You can specify that the foreign key should not be populated by default by using a reference depth of 0.

InvoiceLine.INVOICE_FK.define()
        .foreignKey()
        .referenceDepth(0)
        .hidden(true),

Or you can specify that the foreign key should be populated along with one more level by using a reference depth of 2.

Track.ALBUM_FK.define()
        .foreignKey()
        .referenceDepth(2)
        .attributes(Album.ARTIST_FK, Album.TITLE),

EntityConnection connection = connectionProvider.connection();

List<Entity> tracks = connection.select(Track.NAME.like("Bad%"));

Entity track = tracks.get(0);

Entity genre = track.get(Track.GENRE_FK);
Entity mediaType = track.get(Track.MEDIATYPE_FK);
Entity album = track.get(Track.ALBUM_FK);

// reference depth for Track.ALBUM_FK is 2, which means two levels of
// references are fetched, so we have the artist here as well
Entity artist = album.get(Album.ARTIST_FK);

The reference depth can also be configured on a query basis, either for the whole query or one or more foreign keys.

EntityConnection connection = connectionProvider.connection();

List<Entity> tracks = connection.select(
        Select.where(Track.NAME.like("Bad%"))
                .referenceDepth(0)
                .build());

Entity track = tracks.get(0);

// reference depth is 0, so this 'genre' instance is null
Entity genre = track.get(Track.GENRE_FK);

// using track.entity(Track.GENRE_FK) you get a 'genre'
// instance containing only the primary key, since the condition
// reference depth limit prevented it from being selected
genre = track.entity(Track.GENRE_FK);

EntityConnection connection = connectionProvider.connection();

List<Entity> tracks = connection.select(
        Select.where(Track.NAME.like("Bad%"))
                .referenceDepth(Track.ALBUM_FK, 0)
                .build());

Entity track = tracks.get(0);

Entity genre = track.get(Track.GENRE_FK);
Entity mediaType = track.get(Track.MEDIATYPE_FK);

// this 'album' instance is null, since the condition
// reference depth limit prevented it from being selected
Entity album = track.get(Track.ALBUM_FK);

// using track.entity(Track.ALBUM_FK) you get an 'album'
// instance containing only the primary key, since the condition
// reference depth limit prevented it from being selected
album = track.entity(Track.ALBUM_FK);

1.1. Reference Depth Values:

0 – Do not fetch foreign key references
1 (default) – Fetch directly referenced foreign key entities
N – Fetch up to N levels deep
-1 – Fetch entire reference graph (no limit)

1.2. The N+1 problem

Selecting tracks performs four queries (track + album, mediatype and genre), but that number of queries is the same whether you select one or a thousand tracks.

1.3. Selecting entities

select(Condition condition)

select(Select select)

selectSingle(Condition condition)

selectSingle(Select select)

EntityConnection connection = connectionProvider.connection();

List<Entity> artists = connection.select(
        Artist.NAME.like("The %"));

List<Entity> nonLiveAlbums = connection.select(and(
        Album.ARTIST_FK.in(artists),
        Album.TITLE.likeIgnoreCase("%live%")));

Entity aliceInChains = connection.selectSingle(
        Artist.NAME.equalTo("Alice In Chains"));

List<Entity> aliceInChainsAlbums = connection.select(
        Album.ARTIST_FK.equalTo(aliceInChains));

Entity metal = connection.selectSingle(
        Genre.NAME.equalToIgnoreCase("metal"));

List<Entity> metalTracks = connection.select(
        Select.where(Track.GENRE_FK.equalTo(metal))
                .attributes(Track.NAME, Track.ALBUM_FK)
                .orderBy(descending(Track.NAME))
                .build());

Long classicalPlaylistId = connection.select(
        Playlist.ID, Playlist.NAME.equalTo("Classical")).get(0);

List<Entity> nonClassicalTracks = connection.select(
        Track.NOT_IN_PLAYLIST.get(Playlist.ID, classicalPlaylistId));

select(Key key)

EntityConnection connection = connectionProvider.connection();

Entities entities = connection.entities();

Entity.Key key = entities.primaryKey(Artist.TYPE, 42L);

Entity artist = connection.select(key);

select(Collection<Key> keys)

EntityConnection connection = connectionProvider.connection();

Entities entities = connection.entities();

Entity.Key key42 = entities.primaryKey(Artist.TYPE, 42L);
Entity.Key key43 = entities.primaryKey(Artist.TYPE, 43L);

Collection<Entity> artists = connection.select(List.of(key42, key43));

1.4. Selecting values

For selecting the values of a single column.

select(Column<T> column)

select(Column<T> column, Condition condition)

select(Column<T> column, Select select)

EntityConnection connection = connectionProvider.connection();

List<String> customerUsStates =
        connection.select(Customer.STATE,
                Customer.COUNTRY.equalTo("USA"));

1.5. iterator

LocalEntityConnection provides a way to iterate over a result set, instead of loading it into memory.

iterator(Condition condition)

iterator(Select select)

LocalEntityConnection connection = connectionProvider.connection();

try (ResultIterator<Entity> iterator =
             connection.iterator(Customer.EMAIL.isNotNull())) {
  while (iterator.hasNext()) {
    System.out.println(iterator.next().get(Customer.EMAIL));
  }
}
catch (SQLException e) {
  throw new DatabaseException(e);
}

1.6. dependencies

For selecting entities that depend on a set of entities via foreign keys.

dependencies(Collection<Entity> entities)

EntityConnection connection = connectionProvider.connection();

List<Entity> employees = connection.select(all(Employee.TYPE));

Map<EntityType, Collection<Entity>> dependencies = connection.dependencies(employees);

Collection<Entity> customersDependingOnEmployees = dependencies.get(Customer.TYPE);

1.7. count

For selecting the row count given a count condition.

count(Count count)

EntityConnection connection = connectionProvider.connection();

int numberOfItStaff = connection.count(
        Count.where(Employee.TITLE.equalTo("IT Staff")));

2. Modifying

2.1. insert

For inserting rows.

EntityConnection connection = connectionProvider.connection();

Entities entities = connection.entities();

Entity myBand = entities.entity(Artist.TYPE)
        .with(Artist.NAME, "My Band")
        .build();

myBand = connection.insertSelect(myBand);

Entity firstAlbum = entities.entity(Album.TYPE)
        .with(Album.ARTIST_FK, myBand)
        .with(Album.TITLE, "First album")
        .build();
Entity secondAlbum = entities.entity(Album.TYPE)
        .with(Album.ARTIST_FK, myBand)
        .with(Album.TITLE, "Second album")
        .build();

Collection<Entity.Key> albumKeys =
        connection.insert(List.of(firstAlbum, secondAlbum));

2.2. update

For updating one or more entity instances.

Important

These methods throw an exception if any of the entities is unmodified.

EntityConnection connection = connectionProvider.connection();

Entity myBand = connection.selectSingle(
        Artist.NAME.equalTo("My Band"));

myBand.set(Artist.NAME, "Proper Name");

myBand = connection.updateSelect(myBand);

List<Entity> customersWithoutPhoneNo =
        connection.select(Customer.PHONE.isNull());

customersWithoutPhoneNo.forEach(customer ->
        customer.set(Customer.PHONE, "<none>"));

connection.update(customersWithoutPhoneNo);

2.2.1. Optimistic locking

The framework performs optimistic locking during updates using the methods above. This is done by selecting the entities being updated FOR UPDATE (when supported by the underlying database) and comparing all original values to the current row values, throwing an exception if one or more values differ or the row is missing. Optimistic locking is field-based: any difference between original and current values causes an update to fail.

entity.set(Album.TITLE, "New Title");
connection.update(entity); // fails if the row has been changed by someone else

Note	Excluding attributes when selecting entities results in those attributes (lazy loaded ones for example) not being included when optimistic locking is performed on subsequent updates, since optimistic locking relies on the original attribute value being available for making a comparison.

Optimistic locking can be turned off system-wide using a system property:

codion.db.optimisticLocking=false

or by using the LocalEntityConnection.OPTIMISTIC_LOCKING configuration value:

LocalEntityConnection.OPTIMISTIC_LOCKING.set(false);

or on a connection instance via optimisticLocking():

connection.optimisticLocking(false);

or on a per-entity basis via EntityDefinition.Builder.optimisticLocking(false).

For updating by condition.

update(Update update)

EntityConnection connection = connectionProvider.connection();

connection.update(
        Update.where(Artist.NAME.equalTo("Azymuth"))
                .set(Artist.NAME, "Azymouth")
                .build());

int updateCount = connection.update(
        Update.where(Customer.EMAIL.isNull())
                .set(Customer.EMAIL, "<none>")
                .build());

2.3. delete

For deleting existing rows.

delete(Condition condition)

EntityConnection connection = connectionProvider.connection();

Entity aquaman = connection.selectSingle(
        Artist.NAME.equalTo("Aquaman"));

List<Long> aquamanAlbumIds = connection.select(Album.ID,
        Album.ARTIST_FK.equalTo(aquaman));

List<Long> aquamanTrackIds = connection.select(Track.ID,
        Track.ALBUM_ID.in(aquamanAlbumIds));

int playlistTracksDeleted = connection.delete(
        PlaylistTrack.TRACK_ID.in(aquamanTrackIds));

int tracksDeleted = connection.delete(
        Track.ALBUM_ID.in(aquamanAlbumIds));

int albumsDeleted = connection.delete(
        Album.ARTIST_FK.equalTo(aquaman));

EntityConnection connection = connectionProvider.connection();

Entity audioslave = connection.selectSingle(
        Artist.NAME.equalTo("Audioslave"));

List<Entity> albums = connection.select(
        Album.ARTIST_FK.equalTo(audioslave));
List<Entity> tracks = connection.select(
        Track.ALBUM_FK.in(albums));
List<Entity> playlistTracks = connection.select(
        PlaylistTrack.TRACK_FK.in(tracks));
List<Entity> invoiceLines = connection.select(
        InvoiceLine.TRACK_FK.in(tracks));

List<Entity> toDelete = new ArrayList<>();
toDelete.addAll(invoiceLines);
toDelete.addAll(playlistTracks);
toDelete.addAll(tracks);
toDelete.addAll(albums);
toDelete.add(audioslave);

connection.delete(Entity.primaryKeys(toDelete));

3. Procedures & Functions

Functions return a single value.
Procedures perform logic with no return value.
Both are executed through the same API: EntityConnection.execute(…).

3.1. Function

EntityConnection connection = connectionProvider.connection();

List<Long> trackIds = List.of(123L, 1234L);
BigDecimal priceIncrease = BigDecimal.valueOf(0.1);

Collection<Entity> modifiedTracks =
        connection.execute(Track.RAISE_PRICE,
                new RaisePriceParameters(trackIds, priceIncrease));

Collection<Entity> updatedInvoices =
        connection.execute(Invoice.UPDATE_TOTALS, List.of(1234L, 3412L));

String playlistName = "Random playlist";
int numberOfTracks = 100;
Collection<Entity> playlistGenres = connection.select(
        Genre.NAME.in("Classical", "Soundtrack"));

Entity playlist = connection.execute(Playlist.RANDOM_PLAYLIST,
        new RandomPlaylistParameters(playlistName, numberOfTracks, playlistGenres));

3.2. Procedure

4. Reporting

4.1. report

report(ReportType reportType, P reportParameters)

EntityConnection connection = connectionProvider.connection();

Map<String, Object> reportParameters = new HashMap<>();
reportParameters.put("CUSTOMER_IDS", List.of(42, 43, 45));

JasperPrint jasperPrint = connection.report(Customer.REPORT, reportParameters);

5. Transaction control

5.1. Transactional

Codion encourages declarative transaction boundaries using lambdas or anonymous classes. This ensures transaction safety (commit/rollback) with minimal boilerplate.

Most use cases are covered by:

EntityConnection.transaction(EntityConnection, Transactional transactional) – no return value
EntityConnection.transaction(EntityConnection, TransactionalResult transactional) – with return value

These methods perform a commit on success and rollback on failure.

Note	Nested transactions are not supported and will cause an IllegalStateException to be thrown, causing the outer transaction to be rolled back.

5.1.1. Transaction without a result

EntityConnection connection = connectionProvider.connection();

EntityConnection.transaction(connection, () -> {
  Entities entities = connection.entities();

  Entity artist = entities.entity(Artist.TYPE)
          .with(Artist.NAME, "The Band")
          .build();
  artist = connection.insertSelect(artist);

  Entity album = entities.entity(Album.TYPE)
          .with(Album.ARTIST_FK, artist)
          .with(Album.TITLE, "The Album")
          .build();

  connection.insert(album);
});

Same example using an anonymous class

EntityConnection connection = connectionProvider.connection();

Transactional transactional = new Transactional() {

  @Override
  public void execute() {
    Entities entities = connection.entities();

    Entity artist = entities.entity(Artist.TYPE)
            .with(Artist.NAME, "The Band")
            .build();
    artist = connection.insertSelect(artist);

    Entity album = entities.entity(Album.TYPE)
            .with(Album.ARTIST_FK, artist)
            .with(Album.TITLE, "The Album")
            .build();

    connection.insert(album);
  }
};

EntityConnection.transaction(connection, transactional);

5.1.2. Transaction with a result

EntityConnection connection = connectionProvider.connection();

Entity.Key albumKey = EntityConnection.transaction(connection, () -> {
  Entities entities = connection.entities();

  Entity artist = entities.entity(Artist.TYPE)
          .with(Artist.NAME, "The Band")
          .build();
  artist = connection.insertSelect(artist);

  Entity album = entities.entity(Album.TYPE)
          .with(Album.ARTIST_FK, artist)
          .with(Album.TITLE, "The Album")
          .build();

  return connection.insert(album);
});

Same example using an anonymous class

EntityConnection connection = connectionProvider.connection();

TransactionalResult<Entity.Key> transactional = new TransactionalResult<Entity.Key>() {

  @Override
  public Entity.Key execute() {
    Entities entities = connection.entities();

    Entity artist = entities.entity(Artist.TYPE)
            .with(Artist.NAME, "The Band")
            .build();
    artist = connection.insertSelect(artist);

    Entity album = entities.entity(Album.TYPE)
            .with(Album.ARTIST_FK, artist)
            .with(Album.TITLE, "The Album")
            .build();

    return connection.insert(album);
  }
};

Entity.Key albumKey = EntityConnection.transaction(connection, transactional);

5.2. Transaction

For a more fine-grained transaction control and the ability to rollback, transactions can be started and ended manually, note that this is more complex and thereby error-prone and should not be used unless the method described above does not work for your use-case.

// This example demonstrates full manual transaction control, including rollback safety
// and protection against leaving transactions open in the presence of unexpected failures.
EntityConnection connection = connectionProvider.connection();

Entities entities = connection.entities();

// It is very important to start the transaction here, outside the try/catch block,
// otherwise, trying to start a transaction on a connection already with an open transaction
// (which is a bug in itself), would cause the current transaction to be rolled back
// in the Exception catch block, which is probably not what you want.
connection.startTransaction();
try {
  Entity artist = entities.entity(Artist.TYPE)
          .with(Artist.NAME, "The Band")
          .build();
  connection.insert(artist);

  Entity album = entities.entity(Album.TYPE)
          .with(Album.ARTIST_FK, artist)
          .with(Album.TITLE, "The Album")
          .build();
  connection.insert(album);

  connection.commitTransaction();
}
catch (DatabaseException e) {
  connection.rollbackTransaction();
  throw e;
}
catch (RuntimeException e) {
  // It is a good practice, but not necessary, to catch RuntimeException,
  // in order to not wrap a RuntimeException in another RuntimeException.
  connection.rollbackTransaction();
  throw e;
}
catch (Exception e) {
  // Always include a catch for the top level Exception, otherwise unexpected
  // exceptions may cause a transaction to remain open, which is a very serious bug.
  connection.rollbackTransaction();
  throw new RuntimeException(e);
}
catch (Throwable e) {
  // It's rare, but including a catch for Throwable ensures rollback safety
  // even in the face of serious errors (e.g., OutOfMemoryError, LinkageError).
  connection.rollbackTransaction();
  throw e;
}

6. LocalEntityConnection

An EntityConnection implementation based on a direct connection to the database, provides access to the underlying JDBC connection.

7. RemoteEntityConnection

An EntityConnection implementation based on an RMI connection. Requires a server.

8. HttpEntityConnection

An EntityConnection implementation based on HTTP. Requires a server.