SQL search and filter support

Last modified 14 Sep 2026 07:59 UTC
Since 4.11
This functionality is available since version 4.11.
Table of Contents

The SQL framework provides built-in search for every object class, translating ConnId filters to SQL predicates.

This article is part of the SQL connector development reference and guidance materials. See How to develop connectors using the SQL framework for the section introduction.

No configuration is required: for each writable or read-only object class, a built-in search handler is registered. It selects the mapped columns (__UID__, __NAME__ and all returnedByDefault attributes) with an optional WHERE clause and executes the query in pages of 200 rows.

ConnId AndFilter, OrFilter and NotFilter are translated to SQL AND / OR / negation; each AttributeFilter is resolved through the attribute’s column mapping.

Filter translation

The built-in search handler provides automatic ConnId filter translation to SQL.

Each row of the table lists a ConnId filter operation, the SQL predicate the translator generates for it, and the column type families the operation is supported for — the last column refers to the SQL type family of the filtered column (String columns means string-typed columns such as VARCHAR; Numeric columns means numeric types such as INTEGER or BIGINT).

ConnId filter Translated to Supported by

Equals

column = ? (IS NULL for empty value)

All column types

Contains

column LIKE '%?%'

String columns

StartsWith

column LIKE '?%'

String columns

EndsWith

column LIKE '%?'

String columns

GreaterThan

column > ?

Numeric columns, timestamp/date/time columns

GreaterThanOrEqual

column >= ?

Numeric columns, timestamp/date/time columns

LessThan

column < ?

Numeric columns, timestamp/date/time columns

LessThanOrEqual

column <= ?

Numeric columns, timestamp/date/time columns

NotEquals / Present / NotPresent

not supported by the built-in translator

— (use Custom search implementation or a where {} block)

  • composite (multi-column) UIDs only support Equals; the UID value is split by the . separator and each part is compared for equality against its own column (e.g. tenant-1.account-42 becomes tenant = '1' AND account = '42'), so a column value must not contain the separator (a value with the wrong number of parts fails with IllegalArgumentException)

  • filters on an attribute that exists on the object class but is not mapped to a SQL column produce a ConnectorException; filters on an attribute that does not exist on the object class at all fail with UnsupportedOperationException

  • LIKE operands are not escaped for the LIKE wildcards (%, _, \); avoid using them in filter values — for example, you cannot select only the rows whose value contains the literal text % voters

  • filters on joined attributes of read-only joined object classes are translated against the joined table like any column filter

Search by UID

Searching by UID works out of the box — the UID attribute is always resolvable and filterable. For composite UIDs, pass the joined value (e.g., tenant-1.account-42).

Refining the built-in WHERE clause

Add a fixed predicate on top of the filter-derived WHERE clause (applied to every search, empty filter included). Use it to permanently restrict the rows an object class sees — for example, hiding soft-deleted records (deleted_at IS NULL), limiting an object class to the rows of one tenant, or excluding legacy rows the application no longer reads. The end result: every search against the object class (including searches without any filter) returns only the rows that satisfy both the fixed predicate and the caller’s filter.

objectClass("User") {
    search {
        sql {
            builtIn {
                enabled true
                where { e ->
                    e.col("status").ne("deleted")
                    e.col("legacy").eq(false)
                }
            }
        }
    }
}

Inside the where { e -> } closure (delegated to SqlWherePredicateBuilder):

Method Description

e.col("name") / e.column("name")

Typed column reference, resolved from the discovered table metadata (unknown column → IllegalArgumentException).

.eq(value)

Adds column = value; eq(null) yields IS NULL.

.ne(value)

Adds column != value.

e.add(predicate)

Adds a raw QueryDSL BooleanExpression directly.
The builder exposes only eq and ne. For other comparison operators (gt, lt, ge, le, between, like, …​) build a raw QueryDSL predicate — see the QueryDSL BooleanExpression documentation for the full list of predicate methods (the framework uses QueryDSL 5.0.0) — and pass it with e.add(…​). For queries with arbitrary predicates, a custom query is usually the cleaner option: its table reference (a.tableRef()) can be used to build typed column paths directly.
Always use the explicit eq/ne method calls — Groovy’s == operator maps to Java equals() and will not produce an SQL predicate.

All predicates are combined with AND. To disable the built-in search entirely, use enabled false; see Custom search implementation to replace it with a custom query.

Was this page helpful?
YES NO
Thanks for your feedback