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

  • ColumnCondition - Conditions based on column values (equality, comparison, patterns, nullity)
  • ForeignKeyCondition - 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.NAME.like("A%");
Condition ageOver18 = Customer.AGE.greaterThan(18);
Condition hasEmail = Customer.EMAIL.isNotNull();

// Foreign key conditions
Entity usa = connection.selectSingle(Country.CODE.equalTo("US"));
Condition fromUSA = Customer.COUNTRY_FK.equalTo(usa);

// Combining conditions
Condition complexCondition = and(
    nameStartsWithA,
    ageOver18,
    hasEmail,
    fromUSA
);

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

Column Condition Examples

// Equality
Track.NAME.equalTo("Yesterday")
Track.RATING.equalTo(5)

// Comparison
Track.DURATION.greaterThan(180)
Invoice.TOTAL.between(10.0, 100.0)

// Pattern matching
Artist.NAME.like("The %")
Artist.NAME.likeIgnoreCase("%beatles%")

// Nullity
Customer.PHONE.isNull()
Customer.EMAIL.isNotNull()

// Multiple values
Track.GENRE_ID.in(1, 2, 3)
Album.YEAR.notIn(1990, 1991, 1992)

Foreign Key Condition Examples

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

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

// Null foreign key (orphaned records)
Condition noAlbum = Track.ALBUM_FK.isNull();

Custom Conditions

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

// Define in entity interface
ConditionType NOT_IN_PLAYLIST = TYPE.conditionType("not_in_playlist");

// Implement in entity definition
.condition(Track.NOT_IN_PLAYLIST, (columns, values) ->
    "track.id NOT IN (SELECT track_id FROM playlist_track WHERE playlist_id = ?)")

// Use in queries
List<Entity> tracks = connection.select(
    Track.NOT_IN_PLAYLIST.get(Playlist.ID, playlistId));

Condition Combinations

// AND combination
Condition activeCustomers = and(
    Customer.ACTIVE.equalTo(true),
    Customer.LAST_ORDER_DATE.greaterThan(oneYearAgo)
);

// OR combination
Condition importantCustomers = or(
    Customer.TOTAL_PURCHASES.greaterThan(10000),
    Customer.VIP.equalTo(true)
);

// Complex nesting
Condition targetCustomers = and(
    activeCustomers,
    importantCustomers,
    Customer.EMAIL.isNotNull()
);

Advanced Features

Case Sensitivity

// Case-insensitive operations
Artist.NAME.equalToIgnoreCase("the beatles")
Album.TITLE.likeIgnoreCase("%love%")

All Condition

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

// Useful for conditional filtering
Condition filter = searchTerm.isEmpty() ?
    all(Product.TYPE) :
    Product.NAME.like("%" + searchTerm + "%");

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: