Domain & Database

ForeignKey CAPITAL_FK = TYPE.foreignKey("capital_fk", CAPITAL, City.ID);

interface Parent {
  EntityType<Entity> TYPE = DOMAIN.entityType("parent");

  Column<Integer> ID_1 = TYPE.integerColumn("id1");
  Column<Integer> ID_2 = TYPE.integerColumn("id2");
}

interface Child {
  EntityType<Entity> TYPE = DOMAIN.entityType("child");

  Column<Integer> PARENT_ID_1 = TYPE.integerColumn("parent_id1");
  Column<Integer> PARENT_ID_2 = TYPE.integerColumn("parent_id2");

  ForeignKey PARENT_FK = TYPE.foreignKey("parent",
          PARENT_ID_1, Parent.ID_1,
          PARENT_ID_2, Parent.ID_2);
}

public interface World {

  DomainType DOMAIN = DomainType.domainType(World.class);

  interface City {
    EntityType TYPE = DOMAIN.entityType("world.city");

    Column<Integer> ID = TYPE.integerColumn("id");
    Column<String> NAME = TYPE.stringColumn("name");
    Column<String> COUNTRY_CODE = TYPE.stringColumn("countrycode");
    Column<String> DISTRICT = TYPE.stringColumn("district");
    Column<Integer> POPULATION = TYPE.integerColumn("population");

    ForeignKey COUNTRY_FK = TYPE.foreignKey("country_fk", COUNTRY_CODE, Country.CODE);
  }

  interface Country {
    EntityType TYPE = DOMAIN.entityType("world.country");

    Column<String> CODE = TYPE.stringColumn("code");
    Column<String> NAME = TYPE.stringColumn("name");
    Column<String> CONTINENT = TYPE.stringColumn("continent");
    Column<String> REGION = TYPE.stringColumn("region");
    Column<Double> SURFACEAREA = TYPE.doubleColumn("surfacearea");
    Column<Integer> INDEPYEAR = TYPE.integerColumn("indepyear");
    Column<Integer> POPULATION = TYPE.integerColumn("population");
    Column<Double> LIFE_EXPECTANCY = TYPE.doubleColumn("lifeexpectancy");
    Column<Double> GNP = TYPE.doubleColumn("gnp");
    Column<Double> GNPOLD = TYPE.doubleColumn("gnpold");
    Column<String> LOCALNAME = TYPE.stringColumn("localname");
    Column<String> GOVERNMENTFORM = TYPE.stringColumn("governmentform");
    Column<String> HEADOFSTATE = TYPE.stringColumn("headofstate");
    Column<Integer> CAPITAL = TYPE.integerColumn("capital");
    Column<String> CODE_2 = TYPE.stringColumn("code2");
    Column<Integer> CAPITAL_POPULATION = TYPE.integerColumn("capital_population");
    Column<Integer> NO_OF_CITIES = TYPE.integerColumn("no_of_cities");
    Column<Integer> NO_OF_LANGUAGES = TYPE.integerColumn("no_of_languages");
    Column<byte[]> FLAG = TYPE.byteArrayColumn("flag");

    ForeignKey CAPITAL_FK = TYPE.foreignKey("capital_fk", CAPITAL, City.ID);
  }

  interface CountryLanguage {
    EntityType TYPE = DOMAIN.entityType("world.countrylanguage");

    Column<String> COUNTRY_CODE = TYPE.stringColumn("countrycode");
    Column<String> LANGUAGE = TYPE.stringColumn("language");
    Column<Boolean> IS_OFFICIAL = TYPE.booleanColumn("isofficial");
    Column<Double> PERCENTAGE = TYPE.doubleColumn("percentage");
    Column<Integer> NO_OF_SPEAKERS = TYPE.integerColumn("noOfSpeakers");

    ForeignKey COUNTRY_FK = TYPE.foreignKey("country_fk", COUNTRY_CODE, Country.CODE);
  }

  interface Continent {
    EntityType TYPE = DOMAIN.entityType("continent");

    Column<String> NAME = TYPE.stringColumn("continent");
    Column<Integer> SURFACE_AREA = TYPE.integerColumn("sum(surfacearea)");
    Column<Long> POPULATION = TYPE.longColumn("sum(population)");
    Column<Double> MIN_LIFE_EXPECTANCY = TYPE.doubleColumn("min(lifeexpectancy)");
    Column<Double> MAX_LIFE_EXPECTANCY = TYPE.doubleColumn("max(lifeexpectancy)");
    Column<Integer> MIN_INDEPENDENCE_YEAR = TYPE.integerColumn("min(indepyear)");
    Column<Integer> MAX_INDEPENDENCE_YEAR = TYPE.integerColumn("max(indepyear)");
    Column<Double> GNP = TYPE.doubleColumn("sum(gnp)");
  }
}

Country.CAPITAL_POPULATION.define()
        .denormalized(Country.CAPITAL_FK, City.POPULATION)
        .caption("Capital pop.")
        .numberFormatGrouping(true),

CountryLanguage.NO_OF_SPEAKERS.define()
        .derived(CountryLanguage.COUNTRY_FK, CountryLanguage.PERCENTAGE)
        .provider(new NoOfSpeakersProvider())
        .caption("No. of speakers")
        .numberFormatGrouping(true),

final class NoOfSpeakersProvider implements DerivedAttribute.Provider<Integer> {

  @Serial
  private static final long serialVersionUID = 1;

  @Override
  public Integer get(SourceValues values) {
    Double percentage = values.get(CountryLanguage.PERCENTAGE);
    Entity country = values.get(CountryLanguage.COUNTRY_FK);
    if (percentage != null && country != null && !country.isNull(Country.POPULATION)) {
      return Double.valueOf(country.get(Country.POPULATION) * (percentage / 100)).intValue();
    }

    return null;
  }
}

Country.REGION.define()
        .column()
        .caption("Region")
        .nullable(false)
        .maximumLength(26),
Country.SURFACEAREA.define()
        .column()
        .caption("Surface area")
        .nullable(false)
        .numberFormatGrouping(true)
        .maximumFractionDigits(2),
Country.INDEPYEAR.define()
        .column()
        .caption("Indep. year")
        .valueRange(-2000, 2500),
Country.INDEPYEAR_SEARCHABLE.define()
        .column()
        .expression("to_char(indepyear)")
        .searchable(true)
        .readOnly(true),
Country.POPULATION.define()
        .column()
        .caption("Population")
        .nullable(false)
        .numberFormatGrouping(true),
Country.LIFE_EXPECTANCY.define()
        .column()
        .caption("Life expectancy")
        .maximumFractionDigits(1)
        .valueRange(0, 99),

Country.CODE.define()
        .primaryKey()
        .caption("Code")
        .updatable(true)
        .maximumLength(3),

CountryLanguage.COUNTRY_CODE.define()
        .primaryKey(0)
        .updatable(true),
CountryLanguage.LANGUAGE.define()
        .primaryKey(1)
        .caption("Language")
        .updatable(true),

Country.NO_OF_CITIES.define()
        .subquery("""
                SELECT COUNT(*)
                FROM world.city
                WHERE city.countrycode = country.code""")
        .caption("No. of cities"),

CountryLanguage.IS_OFFICIAL.define()
        .column()
        .caption("Official")
        .hasDatabaseDefault(true)
        .nullable(false),

Customer.ACTIVE.define()
        .booleanColumn("Is active", Integer.class, 1, 0)

Customer.ACTIVE.define()
        .booleanColumn("Is active", String.class, "true", "false")

Customer.ACTIVE.define()
        .booleanColumn("Is active", Character.class, 'T', 'F')

entity.set(Customer.ACTIVE, true);

Boolean isActive = entity.get(Customer.ACTIVE);

private static final List<Item<String>> CONTINENT_ITEMS = List.of(
        item("Africa"), item("Antarctica"), item("Asia"),
        item("Europe"), item("North America"), item("Oceania"),
        item("South America"));

Country.CONTINENT.define()
        .column()
        .items(CONTINENT_ITEMS)
        .caption("Continent")
        .nullable(false),

.keyGenerator(identity())

//Auto increment column in the 'store.customer' table
.keyGenerator(automatic("store.customer"));

//Trigger and a sequence named 'store.customer_seq'
.keyGenerator(automatic("store.customer_seq"));

.keyGenerator(sequence("world.city_seq"))

.keyGenerator(queried("""
        select next_id
        from store.id_values
        where table_name = 'store.customer'"""))

private static final class UUIDKeyGenerator implements KeyGenerator {

  @Override
  public void beforeInsert(Entity entity, DatabaseConnection connection) {
    entity.set(Customer.ID, UUID.randomUUID().toString());
  }
}

public class StoreTest extends DomainTest {

  private static final Store DOMAIN = new Store();

  public StoreTest() {
    super(DOMAIN, StoreEntityFactory::new);
  }

  @Test
  public void customer() {
    test(Customer.TYPE);
  }

  @Test
  public void address() {
    test(Address.TYPE);
  }

  @Test
  public void customerAddress() {
    test(CustomerAddress.TYPE);
  }

  private static final class StoreEntityFactory extends DefaultEntityFactory {

    private StoreEntityFactory(EntityConnection connection) {
      super(connection);
    }

    @Override
    public Optional<Entity> entity(ForeignKey foreignKey) {
      // See if the currently running test requires an ADDRESS entity
      if (foreignKey.referencedType().equals(Address.TYPE)) {
        return Optional.of(connection().insertSelect(entities().entity(Address.TYPE)
                .with(Address.ID, 21L)
                .with(Address.STREET, "One Way")
                .with(Address.CITY, "Sin City")
                .build()));
      }

      return super.entity(foreignKey);
    }

    @Override
    public Entity entity(EntityType entityType) {
      if (entityType.equals(Address.TYPE)) {
        // Initialize an entity representing a record in the
        // STORE.ADDRESS table, to use for testing
        return entities().entity(Address.TYPE)
                .with(Address.ID, 42L)
                .with(Address.STREET, "Street")
                .with(Address.CITY, "City")
                .with(Address.VALID, true)
                .build();
      }
      else if (entityType.equals(Customer.TYPE)) {
        // Initialize an entity representing a record in the
        // STORE.CUSTOMER table, to use for testing
        return entities().entity(Customer.TYPE)
                .with(Customer.ID, UUID.randomUUID().toString())
                .with(Customer.FIRST_NAME, "Robert")
                .with(Customer.LAST_NAME, "Ford")
                .with(Customer.ACTIVE, true)
                .build();
      }
      else if (entityType.equals(CustomerAddress.TYPE)) {
        return entities().entity(CustomerAddress.TYPE)
                .with(CustomerAddress.CUSTOMER_FK, entity(CustomerAddress.CUSTOMER_FK).orElseThrow())
                .with(CustomerAddress.ADDRESS_FK, entity(CustomerAddress.ADDRESS_FK).orElseThrow())
                .build();
      }

      return super.entity(entityType);
    }

    @Override
    public void modify(Entity entity) {
      if (entity.type().equals(Address.TYPE)) {
        entity.set(Address.STREET, "New Street");
        entity.set(Address.CITY, "New City");
      }
      else if (entity.type().equals(Customer.TYPE)) {
        // It is sufficient to change the value of a
        // single property, but the more, the merrier
        entity.set(Customer.FIRST_NAME, "Jesse");
        entity.set(Customer.LAST_NAME, "James");
        entity.set(Customer.ACTIVE, false);
      }
    }
  }
}

Select selectLiveMetallicaAlbums =
        Select.where(liveMetallicaAlbums)
                .orderBy(OrderBy.descending(Album.NUMBER_OF_TRACKS))
                .build();

List<Entity> albums =
        connection.select(selectLiveMetallicaAlbums);

Update removeLiveMetallicaAlbumCovers =
        Update.where(liveMetallicaAlbums)
                .set(Album.COVER, null)
                .build();

int updateCount =
        connection.update(removeLiveMetallicaAlbumCovers);

Count countAlbumsWithCover =
        Count.where(Album.COVER.isNotNull());

int count = connection.count(countAlbumsWithCover);

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

EntityConnection connection = connectionProvider.connection();

Entities entities = connection.entities();

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

Entity artist = connection.select(key);

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

EntityConnection connection = connectionProvider.connection();

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

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);
}

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

EntityConnection connection = connectionProvider.connection();

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

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

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

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

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

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

// 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;
}