Module is.codion.framework.domain

Package is.codion.framework.domain.entity.condition

@NullMarked package is.codion.framework.domain.entity.condition
Provides a type-safe condition API for building SQL WHERE clauses programmatically.

Overview

The condition framework enables type-safe query construction through a fluent API that mirrors SQL operators while leveraging Java's type system for compile-time safety. Conditions are the primary mechanism for filtering data when querying entities.

Core Concepts

Condition Types

  • ColumnConditions - Conditions based on column values (equality, comparison, patterns, nullity)
  • ForeignKeyConditions - Conditions based on foreign key relationships
  • CustomCondition - Complex conditions that cannot be expressed with standard operators
  • Combination Conditions - AND/OR combinations of other conditions
  • All Condition - Represents no filtering (SELECT all rows)

Basic Usage

Note: Column and ForeignKey implement their respective condition factory interfaces, allowing you to create conditions directly from attributes. 

// Column conditions - created directly from Column attributes
Condition nameStartsWithA = Customer.LASTNAME.like("A%");
Condition fromUSA = Customer.COUNTRY.equalTo("USA");
Condition hasEmail = Customer.EMAIL.isNotNull();

// Foreign key conditions
Entity peacock = connection.selectSingle(Employee.LASTNAME.equalTo("Peacock"));
Condition supportedByPeacock = Customer.SUPPORTREP_FK.equalTo(peacock);

// Combining conditions
Condition condition = and(
				nameStartsWithA,
				fromUSA,
				hasEmail,
				supportedByPeacock);

// Using conditions in queries
List<Entity> customers = connection.select(condition);

Column Condition Examples

// Equality
Condition teenSpirit = Track.NAME.equalTo("Smells Like Teen Spirit");
Condition rated = Track.RATING.equalTo(5);

// Comparison
Condition longTracks = Track.MILLISECONDS.greaterThan(180_000);
Condition totals = Invoice.TOTAL.between(BigDecimal.valueOf(10), BigDecimal.valueOf(100));

// Pattern matching
Condition theArtists = Artist.NAME.like("The %");
Condition zeppelin = Artist.NAME.likeIgnoreCase("%zeppelin%");

// Nullity
Condition noPhone = Customer.PHONE.isNull();
Condition hasEmail = Customer.EMAIL.isNotNull();

// Multiple values
Condition genres = Track.GENRE_ID.in(1L, 2L, 3L);
Condition ratings = Album.RATING.notIn(1, 2, 3);

Foreign Key Condition Examples

// Single entity reference
Entity metal = connection.selectSingle(Genre.NAME.equalTo("Metal"));
Condition metalTracks = Track.GENRE_FK.equalTo(metal);

// Multiple entity references
List<Entity> artists = connection.select(Artist.NAME.like("A%"));
Condition byArtists = Album.ARTIST_FK.in(artists);

// Null foreign key
Condition noGenre = Track.GENRE_FK.isNull();

Custom Conditions

For complex queries that cannot be expressed with standard operators, use ConditionType to define custom SQL conditions: 

// Define a custom condition type for finding tracks not in a playlist
interface Track {
	EntityType TYPE = DOMAIN.entityType("chinook.track");

	Column<Long> ID = TYPE.longColumn("id");
	Column<String> NAME = TYPE.stringColumn("name");

	// Define a custom condition for complex subquery logic
	ConditionType NOT_IN_PLAYLIST = TYPE.conditionType("not_in_playlist");
}

// In the entity definition, provide the SQL generation logic
EntityDefinition track() {
	return Track.TYPE.as()
					.attributes(
									Track.ID.as()
													.primaryKey(),
									Track.NAME.as()
													.column()
													.caption("Name"))
					.condition(Track.NOT_IN_PLAYLIST, (columns, values) ->
									"track.id NOT IN (SELECT track_id FROM chinook.playlisttrack WHERE playlist_id = ?)")
					.build();
}

// Usage - find tracks not in a specific playlist
List<Entity> tracks(EntityConnection connection, Long playlistId) {
	return connection.select(Track.NOT_IN_PLAYLIST.get(Playlist.ID, playlistId));
}

Condition Combinations

// AND combination
Condition longExpensiveTracks = and(
				Track.MILLISECONDS.greaterThan(300_000),
				Track.UNITPRICE.greaterThan(BigDecimal.valueOf(0.99)));

// OR combination
Condition popularTracks = or(
				Track.RATING.greaterThanOrEqualTo(8),
				Track.PLAY_COUNT.greaterThan(100));

// Complex nesting
Condition condition = and(
				longExpensiveTracks,
				popularTracks,
				Track.COMPOSER.isNotNull());

Advanced Features

Case Sensitivity

// Case-insensitive operations
Condition zeppelin = Artist.NAME.equalToIgnoreCase("led zeppelin");
Condition loveAlbums = Album.TITLE.likeIgnoreCase("%love%");

All Condition

// Select all rows (no WHERE clause)
List<Entity> customers = connection.select(all(Customer.TYPE));

// Useful for conditional filtering
Condition condition = searchText.isEmpty() ?
				all(Track.TYPE) :
				Track.NAME.like("%" + searchText + "%");

Best Practices

  • Use column-specific methods for type safety (equalTo, greaterThan, etc.)
  • Prefer foreign key conditions over joining on ID columns
  • Use custom conditions for complex SQL that doesn't fit the standard API
  • Combine conditions logically to build readable queries
  • Leverage case-insensitive operations when appropriate
See Also: