This is an internal documentation. There is a good chance you’re looking for something else. See Disclaimer.
There are currently 5 different query builders for different query types:
The EntityQueryBuilderImpl builds queries that return Entity instances. It is convenient to use the Entity interface, but it might have a performance impact when too much data is loaded or too many queries are executed.
The SinglePathQueryBuilderImpl can be used to fetch exactly one property or path of an entity (for example to get all primary keys of a given query). This avoids unnecessarily loading the entire Entity.
The CriteriaCountQueryBuilder can be used to execute
Internally, JPA Criteria Queries are used. The reason for the query builder classes is that the user should not have access to the Session object to make sure that all query interceptors (security and more) are always applied.
Parts of the JPA Criteria API can still be used however, for example to specify conditions. A tutorial can be found here: https://www.ibm.com/developerworks/java/library/j-typesafejpa/
The metamodel classes are currently not available, which means typesafe queries are not possible at the moment.
QueryBuilderFactory helper methods¶
EntityList entityList = queryBuilderFactory.findKeys("User") .where(isTrue()) .build() .toEntityList()
Evaluate if an entity matches the condition using
Condition condition = isTrue(); queryBuilderFactory.evaluate(condition, entity)
QueryBuilderBase#where(Predicate...)to add a JPA Predicate instance
The PredicateBuilder is a functional interface that can be used to create Predicate instances using lambda expressions that can be passed to
QueryBuilderBase#where(PredicateBuilder). The CriteriaBuilder, Root, FieldAccessor, SubqueryFactory and the query hints are passed as parameters into the lambda expression.
Node or Condition instances (created by the Conditions API) can also be passed to
QueryBuilderBase#where(Condition...). This API is also used by the security conditions. A Condition is first converted into a Node instance using the ConditionFactory and then transformed into a Predicate using the PredicateFactory.
Conditions added through the
whereInsecure()methods are added in
isInsecureflag passed to
QueryBuilderInterceptor#fieldUsedInQueryCondition()is set to true) - this means that no ACL conditions will be added to any joins or subqueries that are present in the condition. The separate
whereInsecure()method is necessary for security reasons to control where insecure conditions may be used, otherwise any user could execute insecure queries, for example through the REST API. The
insecureTQL keywords are no longer supported and will be ignored. This was necessary with the introduction of the query builder interceptors for joins because there was no way to mark a join as insecure (which caused huge ACL and constriction conditions).
It also invokes the
QueryBuilderInterceptor#buildConditionFor() method of all interceptors when
the query initialization has been completed and adds the created conditions to the list of predicates.
QueryBuilderInterceptor#buildConditionFor() method should be called when the query builder is created; not when it is executed. For example it is expected
that if a query that is created in privileged mode, it should remain privileged even if the privileged mode is no longer active
when the query is executed.
QueryBuilderBase#build() should be called by the user when the query builder configuration is completed
and returns an object that allows to access the results. The returned object depends on the subclass and is defined by
It initializes a CriteriaQuery, CriteriaBuilder,
Root and SubqueryFactory
entityClass (the entity that should be queried) and
queryType (the result type of the query) constructor parameters.
This class also contains a map of parameters that are manually added to the query by the user and provides a helper method to apply the parameters to the query.
A condition like
field("name").is(value) might be mapped with a ParameterExpression
even though the user specified the value directly. These parameters are collected and added to the query by the ParameterCollector.
The parameter collector is a visitor for Node objects. It sets an unique name to all parameter nodes and collects their values.
It is important that only one parameter collector is used per query. Otherwise the parameter names are not unique and
the parameter values get overwritten. This means that all Node instances
QueryBuilderBase#addCondition() must not have been already been processed by a parameter collector.
Before the query is executed the parameters collected by the ParameterCollector
as well as parameters that are manually passed to
AbstractCriteriaBuilder#addParameter#addParameter() are applied to the
Query instance (see
If the parameter value does not match the parameter type it is attempted to convert the value using
If a Collection is used as a parameter value
Query#setParameterList() is used which can be
substantially faster for large parameter lists.
There are also global parameters that are applied to every query if a parameter with a certain name exists.
These are provided by the ParameterProvider interface.
An example would be the parameter
currentUser (see PrincipalNameFactory).
There are two different options:
createSubquery()creates a subquery that is correlated to main query (based on a given association). This can for example be used to create
createUncorrelatedSubquery()can be used to create any other subquery that is not correlated to the main query. The selection and target entity can be freely chosen.
Both methods return an instance of SubqueryBuilderImpl which supports similar functionality as the standard query builder.
The CriteriaQueryBuilderImpl is a base class for ‘standard’ query builders that expect multiple result rows and adds support for offset, limit and ordering.
There is a special ordering expression that can order the results by a given list of keys.
This is created using
OrderingUtils#orderByKeys() and results in a
ORDER BY CASE WHEN ... clause.
OrderingUtils#orderByKeys() is only supported for non-distinct queries. However this should not be a problem
as this ordering is usually combined with a
The CriteriaQueryBuilderImpl defines that all
subclasses must return an implementation of CriteriaQueryWrapper
build() method and provides a base implementation (
It also defines the
QT type parameter of its superclass to
Object. That means that the hibernate queries always
Object instances. This is necessary because sometime we need to expand the user selection (see below).
The CriteriaQueryWrapper interface defines the following methods:
getResultList()returns a list of results
firstResult()returns the first result that was found
uniqueResult()returns exactly one result or null. If the query returns multiple rows, an exception will be thrown. Optionally a LockModeType can be passed to this method, which allows pessimistic locking of an entity.
uniqueResult() will throw an exception if no result was found. However there are
uniqueResultOptional() methods for the case when a result is not required.
distinct()to configure if the query should be executed with the
DISTINCTkeyword. By default, all queries with joins on to-many relations are executed with the
DISTINCTkeyword (also see this note for more information about the default behaviour).
Because a join in TQL is always a
LEFT JOIN all standard queries with joins on to-many relations need to be
DISTINCT to avoid duplicate results.
However some LockModeType cause a
SELECT FOR UPDATE which does not support
distinct queries. In that case, distinct queries need to be manually disabled by calling
Before v3.9, all queries were executed with the
DISTINCT keyword, no matter if it was actually needed or not.
This can slow down queries substantially. Therefore, since v3.9, the
DISTINCT keyword is only added if there
can be duplicate results (which is the case if there are outer joins on to-many relations).
This new default behaviour for the
DISTINCT keyword can be reverted to the old behaviour (< v3.9) by the
nice2.persist.queryDistinctStrategy=DISTINCT_ALWAYS. However, this should be used as a
temporary workaround only, if there occur unexpected issues with the new default behaviour.
The AbstractCriteriaQueryWrapper is the base implementation of CriteriaQueryWrapper and provides the following functionality:
It requires a transformation Function which converts a result row (which is always
Object) into the desired target type (subclasses must override
getResultList() is called, the following steps are taken:
The final ordering clause is created: If no explicit ordering is defined for the query, the default ordering defined in the entity model is used. In addition, the primary key is always added as the last sorting parameter (unless it already is part of the sorting clause). This is necessary to guarantee a consistent ordering when
OFFSETis used (otherwise the order might be partially random if there are many rows with same value in the order column). The default ordering is insecure added to the query to avoid performance issues (for any other sorting it is checked if the user has access to the fields and relations).
The final Selection of the query is determined: The user defined selection is provided by the subclass (abstract method
getSelection()), however it might have to be expanded:
According to the SQL Standard all columns that are part of the
ORDER BYclause must also be part of the select clause if it is a
DISTINCTquery. The missing columns are automatically added to the selection (
expandSelection(List<Order> order)) and are removed again before the results are processed (
SELECT CASEexpression is used in the ordering clause, it also needs to be added to the selection. However in this case the
ORDER BYexpression needs to be replaced with a literal reference to the selection (
ORDER BY 1for example), otherwise PostgreSQL does not recognize that both of these expressions are the same. Since by default all literals will be rendered as parameters we need to explicitly use
CriteriaBuilderWrapper#inlineLiteral()that uses an InlineLiteralExpression which overrides the default LiteralHandlingMode to
AUTO(we do not use
INLINEto make sure that strings are never inlined, as this would be an SQL injection risk).
Due to a bug in hibernate an array selection of size 1 is not returned as array. As this breaks our code we add a dummy selection (the literal ‘1’) if the the selection size is 1.
The query is then executed and the results returned after they have been processed by the transformation function (see above).
uniqueResult() works similarly, but as we expect only one result, we do not have to worry about the ordering clause.
It provides a method called
clearSelection() that re-initializes the selection. However this method cannot remove joins that
were created by the previous selection and is used internally only.
This class also provides the CriteriaQueryWrapper implementation
for its subclasses: CustomSelectionCriteriaQueryWrapper.
getSelection() returns the selection created by
It provides a protected method
mapResults() that initializes the result structure and processes the query results using
This is necessary because the CustomSelection
may add additional paths (for internal processing) and some paths need to evaluated in an additional query (to-many paths for example).
The SinglePathQueryBuilderImpl can be used to
query for exactly one path of an entity. The constructor takes a
Class<T> parameter which defines the return type
of the query.
setPath(String) method needs to be called to define which path should be selected.
It is verified if the selected path matches the return type, otherwise an exception will be thrown.
An exception is also thrown if
setPath(String) is never called.
It returns a CustomSelectionCriteriaQueryWrapper
build() method with a mapping function that returns the first element of the result array.
It also provides a simple implementation of ResultRowMapper.
Because the result is always the selected path of type
mapToManyPath() methods can simply return
the values provided by the given ValueProvider.
The primary key field is automatically added to the selection based on the given entity class and the results will be converted into an instance of PrimaryKey.
The PathQueryBuilderImpl can be used to
query for multiple paths of an entity and always returns a container type like
The constructor of this class requires an instance of ResultRowMapper
that supports the return type
There currently are two different implementations available:
ArrayResultRowMapper converts query results into a flat structure using an
Object. The order in the array depends on the order the paths were given to
MapResultRowMapper converts each row into a Map. This creates a nested structure and is useful to group fields by their relation paths.
CustomResultRowMapperFactory supports custom beans. Any java class that is annotated with QueryBuilderResult is supported. If the field name of the bean matches the entity field name, it will be mapped automatically, otherwise the ResultPath annotation must be used to specify the mapping. It is also possible to map a sub-path of the result to a nested java bean using the NestedResultPath. The nested bean supports the same features as the main bean (but the class level annotation is not necessary). To-many paths are supported using a List or Set.
addPathToSelection() can be called multiple times to add paths to the selection.
At least one path needs to be added otherwise an exception will be thrown.
It inherits directly from AbstractCriteriaBuilder
because it does not return an
Object and also returns a different object from its
The CustomSelection is used by some query builders that select only certain paths (not entire entities).
It is not sufficient to simply add all requested paths to the JPA selection due to the following reasons:
Security: It must be possible to intercept field selection. The query only adds the security conditions of the target entity by default. But it does not check field permissions and also a path may point to a different entity that needs to be checked as well.
Paths pointing to a to-many property would return multiple rows per target entity. Even if the data would be merged later, it would make
A custom selection contains a SelectionRegistry.
The selection registry keeps track of all ‘requested paths’ (paths that should be included in the final
returned from the query builder) and all ‘query paths’ (paths that are included in the query).
Not all ‘requested paths’ will generate a ‘query path’ (for example to-many paths are evaluated in a separate query) and
the ‘query paths’ may contain additional paths that are required for internal processing, but won’t be returned from the
The selection registry maintains maps that keep track which query/requested path is at which position in the result arrays.
It also makes sure that there are no duplicated ‘query paths’ (for example when the same internal path is required by
All the query paths can be converted into a JPA Selection by the
SelectionPathHandler#processSelection() is called just before the JPA Selection
is created. The SelectionRegistry is passed
as an argument and can be used to add all necessary query paths to the query.
SelectionPathHandler#processResults() is called after the query has been executed. Both the list of results of the query
and the target (that will be returned from the query builder) are passed as arguments. The task of the handler is to
copy the query results into the target array. The SelectionRegistry
contains the source and target indices of the paths. In addition an instance of ResultRowMapper
is passed to this method as well.
The ResultRowMapper does the actual mapping to the final result structure and has the following methods:
createInstanceOfResultType()creates an instance of the result container (like
Map). May also be null if there is only a single value and no container.
mapToOnePath()maps to-one paths to the result container. It has the following parameters:
pathsall the paths that should be mapped
queryResultProvideran instance of ValueProvider that allows to access the result of the current row for a given path
resultan instance of the result container. The results should be mapped to this object.
rootSelectionRegistrycan be used to access the index of a given path to be able to insert it in the correct position of the result container
mapToManyPath()maps to-many paths to the result container. It has the same parameters as
mapToOnePath(), except that it receives a list of ValueProvider
The ToOneSelectionPathHandler is responsible for all ‘to-one’ paths. It is relatively straight-forward: the paths can be included in the query and after the query execution the paths can simply mapped to the target array.
The ToManySelectionPathHandler handles all ‘to-many’ paths. These paths cannot be selected directly in the query. For each base path a separate query is generated that retrieves the values of these paths for all rows. The rows are then mapped to the target array using the primary key of the root entity, that is selected by both queries.
There are special implementations for
binaryfields, because the
_nice_binarytable is not mapped by hibernate at the moment and cannot be queried directly. They use the BinaryDataAccessor to efficiently load BinaryData instances, which are then merged into the target array.
Query Builder Interceptor¶
The QueryBuilderInterceptor participates in the query building process.
This method is called for every query root and for every subquery and can add additional conditions to the query.
BusinessUnitQueryBuilderInterceptormakes sure that only entities belonging to the current business unit are returned
SecureQueryInterceptoradds additional conditions based on the security policy
The method takes an instance of QueryBuilderType
which signifies by what kind of query builder it is called. Currently
DELETE are supported. The
SecureQueryInterceptor uses this information to apply the correct security conditions depending on the query type.
The argument QueryBuilderSituation indicates whether the returned conditions will be applied to a (sub)query or a join.
This method will be called whenever a field is used in a query condition, for example
where username == 'user'.
SecureQueryInterceptor will return conditions based on
entityPath rules and will throw
an exception when a field is used that is marked as
privileged-only in the field model.
This method is only used when a CustomSelection
is used. It is called once for each ‘base path’ (a path without field) of the query.
So for example when the paths
relAddress.city are selected,
the method is called once for
The method may return an SelectionInterceptor, which allows modification of the selection and inspection & replacement of the query results.
beforeQueryExecution(SelectionData) is called before the relevant query is executed and allows adding additional
One use case is to add the primary key of a ‘base path’ to the selection in order to be able to check access permissions.
handleQueryResults() gives access to the query results and also allows overriding the query results.
The use case of the
SecureQueryInterceptor is to find all primary keys of a base path using
then check access permissions and overwrite the value with null if access is denied (using
Interceptors for Joins¶
The QueryBuilderInterceptor is also called for joins that are used in conditions (in addition to subqueries and the root entity) to make sure that the conditions cannot be used to bypass ACL rules.
For example the query
find User where relUser_status.unique_id == "active" should not return any results
if the principal does not have access to the related
User_status entity or the
relUser_status field of the
Unlike additional conditions for the root entity, additional conditions for joins cannot just be added to the query builder:
(relUser_status.unique_id == "active" or username is not null) would become
(relUser_status.unique_id == "active" or username is not null) and <interceptor-condition>.
This would never return any results if the condition added by the interceptor evaluates to false, even if the second part of the OR
clause is true.
Therefore the condition needs to be combined only with the clause that contains the join:
(relUser_status.unique_id == "active" and <interceptor-condition>) or username is not null.
Due to this, large
OR clauses should be replaced with an
IN clause, as the
OR clause can become very inefficient:
where value = 1 AND <interceptor-condition> OR value = 2 AND <interceptor-condition> ... versus
where value IN (1,2,...) AND <interceptor-condition>.
The wrapper overrides methods like
The creation of the actual predicate is delegated to the ‘real’ criteria builder
All expressions that are passed to the criteria builder (see below) are then processed by the interceptors and the resulting Node instances will be converted to Predicate instances using a derived PredicateFactory. The predicate factory needs to be derived to use the current join as the query root (as the conditions are based on this entity, not the query root) and to use the real criteria builder to avoid endless recursion.
The actual predicate is then combined with the interceptor predicates and an AND predicate is returned from the call (only if there are any interceptor predicates, otherwise just the actual predicate is returned directly).
unwrap()method may be used to access the underlying criteria builder. However this should only be used internally when necessary, as conditions created using the underlying criteria builder directly won’t contain any security conditions.
Conditions are collected from the following expressions:
A path might for example look like
relEntity.relEntity2.field. The Path instance always references the last
path element. If it is an instance of From, the last path element is
a relation, otherwise it is a field.
For the example path
relUser.relAddress.city the conditions of the following interceptor calls
fieldUsedInQueryCondition("Address", "city")(this call only applies when the path points to a field)
All parameter expressions of the function call are recursively evaluated (see above how Path expression are evaluated).
A (correlated) subquery might be created for example from the following condition
exists(relUser.relAddress.relStatus where ... ).
In this example the
relStatus join is the ‘root’ of the subquery: conditions of the
Status entity do not need to be added to the join,
they will already be added to the subquery. However it is necessary to check the field of that join (
relAddress join is the ‘correlated’ join. Conditions up to this join will be collected (see above how Path
expression are evaluated).
So for the above example the following interceptor calls are made:
Joins and fields in the ORDER BY clause¶
It is also necessary to secure the
ORDER BY clauses, it should not be possible to order by a field or relation
that is not accessible.
For that purpose the CriteriaBuilderWrapper
also overrides the
desc methods and returns a modified order by clause that uses a
SELECT CASE ... WHEN ... expression.
Conditions are collected for the
ORDER BY expression in the same way as described for conditions above.
The collected conditions are then wrapped in the following way:
ORDER BY name becomes
ORDER BY SELECT CASE <interceptor-condition> THEN name OTHERWISE null which means that
rows where the
ORDER BY clause is not accessible will be ordered like if the
ORDER BY clause would evaluate to NULL.
Custom JDBC Functions¶
In addition to the contributed functions, the GlobSqlFunction
is registered as well. It implements the
glob function, which is internally used when the
Operator#LIKE is specified.
LIKE internally but is also replacing
_ so that both placeholders are supported.
Each function must provide a SQLFunction which contains the SQL template.
Typically the SQLFunctionTemplate can be used for this.
An instance of SqlWriter is provided to facilitate writing the SQL query. The
sql writer is obtained from
Context#createSqlWriter() and is automatically configured based on the current Dialect.
The abstract base class AbstractJdbcFunction provides support to create the sql function templates:
The arguments of the Condition are passed to the criteria builder in the same order.
If the order of arguments is different in the sql template or a parameter is used multiple times, the
needs to be overwritten by the JdbcFunction. The arguments
are then reordered and/or duplicated by the FuncallArgumentProcessor
before the query is processed.
Each JDBC Function must implement the
validateArguments() function which should check whether the given arguments (paths in particular)
are compatible with the function. If an incompatible path is given to the function, the content of that path might be visible in
the log file, which is a security issue.
A QueryFunction can be used to implement a custom function that can be used in the query language. The query functions are applied by the ConditionFactory when the Node tree is processed and can manipulate its nodes.
An example would be the FulltextSearchFunction:
It executes the fulltext search when the query is compiled and replaces the query function node with an
that includes the primary keys of the results of the search.
The QueryVisitor visits the node tree and collects the entity model, condition and ordering data, which in turn will be wrapped in a HibernateQueryAdapter that is returned to the user.
The query visitor handles the following funcall nodes:
Keywords.FIND: The entity model that should be queried
Keywords.ORDER: Each child node represents an order path and direction
Keywords.WHERE: The condition of the query.
The condition (the WHERE part of the query) is processed by the ConditionFactory before it is added to the conditions list. The condition factory applies the following visitors:
The node tree is parsed using different NodeVisitor implementations, that all extend from AbstractNodeVisitor.
This is the base class that all visitor implementations use. It defines an abstract method (
should return a Predicate instance for the current node.
For example the LogicalNodeVisitor converts
an AndNode, OrNode or
NotNode into a CompoundPredicate.
Additionally the base class provides helper methods to handle child nodes (
These helper methods create a new visitor for the given node and pass it to
processVisitor(), which processes the node
with the new visitor. It also calls
Cursor#next() to make sure that nested calls are only handled by the newly created visitor.
Each child node is processed in isolation by its own visitor instance and its results are then aggregated by the parent visitor.
A FuncallNode may be a placeholder for different types of nodes:
a JdbcFunction call
The actual work is done in QueryBuilderJoinHelper:
Iteration over all path parts (
relUser.relAddress.valuewould be three different parts)
If the part is an association a join to the target entity is performed
If it is a field, the path to that field is returned
If the path points to a primary key that is referenced in a many to one association, the foreign key field is returned
instead of performing an unnecessary join (which results in
address.fk_user = ? instead of
INNER JOIN user ON user.pk = address.fk_user WHERE user.pk = ?
for performance reasons.
For this special case the interceptors (security and so on) are called for entity path
corresponds to the foreign key field on the database which is ultimately accessed and potentially also avoids an unnecessary join
due to conditions added by the interceptor.
When a join is created it corresponds to an actual JOIN in the SQL. Therefore it should be tried to reuse the join instances if the same entity is going to be joined multiple times.
The RootNodeVisitor is the entry point which handles the root node. It simply delegates to the visitor that can handle the root node and returns the predicate of that visitor.
This visitor collects all predicates of its child nodes (including other logical nodes) and nests them into an
The ExistsNodeVisitor handles
a FuncallNode with the
These nodes represent an
The first child node is always a PathNode that references the
relation path which is queried by the subquery. Thus the
visitPath() method first creates an instance of
Subquery through the SubqueryFactory.
The path node might contain multiple relation paths which leads to nested
All exists predicates are collected on a stack until the path is parsed completely. The (optional)
condition is added to the top element of the stack (the one that was added last). While the predicates are removed
from the stack an exists condition is added (referencing the predicate that was removed before itself).
The last element removed from the stack is returned from the visitor.
The InNodeVisitor is used for handling
The values of the
IN clause can either be specified as literals or parameters. The parameter names or literal values
are collected, converted to Expression and then passed as parameters
to an InPredicate.
The IsTrueNodeVisitor creates a boolean
Either based on a Path that points to a boolean or a literal expression.
The latter may be used by the security framework to deny any access (
This makes it possible to integrate the new features with the old query builder (this was primarily created for the PermissionMatrixEvaluationService).
Currently the following nodes are supported:
PathNoderepresents a path to a certain field
A count expression represented by a
LiteralNoderepresents an explicit literal expression
ParameterNoderepresents a parameter expression
FuncallNoderepresents any sql function call
Obviously both nodes need to be of the same type, otherwise hibernate will throw an exception.
Since both the
ParameterNode and the
LiteralNode can be converted to a different type (if a suitable converter
exists), the ‘other side’ of the equation is evaluated first and then it is attempted to convert the literal or parameter
using the TypeManager to the type of the ‘other side’ (if necessary).
LIKE operator is handled specially as it is not translated into a SQL
LIKE but mapped to our custom
Both sides of the equation are
converted to lower case to simulate
If a localized field is part of a query it needs to be resolved for the current locale before the query is parsed. This is achieved by the EntityInterceptorVisitor which is executed before the query is parsed by the predicate factory.
All path nodes are processed by the FieldResolver and all virtual fields are replaced.
Delete query builder¶
The CriteriaDeleteBuilderImpl is a special query builder implementation that can be used to delete multiple entities by query without the need to load every single entity.
The query selects the primary keys of all entities that may be deleted (the correct security conditions are added by the
For each result a proxy is created, marked as deleted and the
entityDeleting() event is fired. The reason for the proxy is
to avoid loading the entire entity unless it is absolutely necessary (for example when the entity data is accessed by a listener).
Entity#markDeleted() is used. This is an internal method that can be invoked without initializing the proxy
(as opposed to
delete()) and causes
getState() to correctly return
After the invocation of the listeners the proxy instances are scheduled for deletion with the EntityTransactionContext.
QueryDefinition / QueryConfigurator¶
An instance can be obtained from the method
Query#toQueryDefinition() which can then be converted to a
QueryConfigurator which can be
applied to the new query builder using
This was primarily developed to be able to combine the EntityExplorerActionSelectionService with the new query builders.
When a query builder instance is created using the PersistenceService
it is possible to pass query hints in the form of a
QueryHints are additional information for the query builder which can lead to an optimized query.
Currently there is only one supported hint:
QUERY_BY_KEYS defines all primary keys which might possibly be returned from the query. It is usually combined with a
The hints are passed to the PredicateBuilder which can use it build an optimized condition.