cayenne-query

Write idiomatic Cayenne 5.0 queries — ObjectSelect, SelectById, SQLSelect, expressions, prefetch, pagination, aggregates.

Required reading

• ${CLAUDE_PLUGIN_ROOT}/references/query-api.md — every query mechanism with examples (ObjectSelect, SQLSelect/SQLExec, Expression, ColumnSelect, SelectById).

Step 1 — Identify the query shape

Primary-key lookups: prefer SelectById

When the user is fetching a single entity by its PK, use SelectById rather than ObjectSelect.where(PK.eq(...)):

Artist a = SelectById.query(Artist.class, 42).selectOne(ctx);

SelectById can hit the ObjectContext's session cache without firing a SQL query when the row is already loaded. ObjectSelect.where(...) always goes to the database. For composite PKs, pass a Map<String, Object> of PK column → value.

Step 2 — Filter with Property constants

cgen generates Property<T> constants on each entity superclass. Use them — they're type-checked at compile time:

ObjectSelect.query(Artist.class)
    .where(Artist.ARTIST_NAME.likeIgnoreCase("p%"))
    .and(Artist.DATE_OF_BIRTH.between(d1, d2))
    .select(ctx);

Only fall back to ExpressionFactory.matchExp("artistName", ...) or Expression.fromString(...) when:

• The filter is dynamic (field name comes from runtime input), or
• The user explicitly wants string-based expressions.

query-api.md lists all common predicate methods on Property.

Step 3 — Handle relationships (prefetch)

When the query traverses a relationship in a loop, always add a prefetch to avoid N+1:

// Bad — fires one query per artist when paintings are accessed
for (Artist a : artists) { a.getPaintings().size(); }

// Good
List<Artist> artists = ObjectSelect.query(Artist.class)
    .prefetch(Artist.PAINTINGS.disjoint())
    .select(ctx);

Pick:

• .joint() — to-one relationships, or to-many with small fan-out. Single SQL join.
• .disjoint() — to-many, modest size. Two queries.
• .disjointById() — to-many, very large parent set. Two queries, keyed by PKs.

Step 4 — Apply ordering, paging, and caching as needed

ObjectSelect.query(Artist.class)
    .orderBy(Artist.ARTIST_NAME.asc())
    .pageSize(50)             // server-side pagination
    .limit(1000)
    .localCache()             // per-context cache, or .sharedCache("group-name")
    .select(ctx);

Use sharedCache for reference data (rarely changes, read often). Use pageSize to avoid loading entire result sets.

Step 5 — Raw SQL when needed

List<Artist> hits = SQLSelect.query(Artist.class,
        "SELECT * FROM ARTIST WHERE ARTIST_NAME LIKE #bind($pattern)")
    .params(Map.of("pattern", userInput + "%"))
    .select(ctx);

Always use #bind($name) placeholders. Never concatenate user input into SQL. Cayenne's SQLTemplate is Velocity-based — see query-api.md for #bind, #bindEqual, #chain, and adapter-specific SQL with <sql adapter-class="...">.

Anti-patterns

• Parameter String concatenation in raw SQL. Use #bind($name). Concatenation is a SQL injection vector and may also result in invalid syntax.
• Using selectOne when multiple may match. It throws. Use selectFirst if "any one" is okay.
• Loading large result sets without pagination. Use .pageSize(n) or iterator() and process incrementally.
• N+1 from missing prefetch. If iterating entities and accessing relationships per-entity, add .prefetch(...).
• Using Expression.fromString(...) or ExpressionFactory.matchExp("fieldName", ...) for static queries. Prefer typed Property constants (Artist.ARTIST_NAME.eq(...)) — they catch typos at compile time and survive model refactors.
• Mutating fetched objects without committing. ObjectContext.commitChanges() is required for changes to persist.