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: