public interface ExpressionList<T>
An ExpressionList is returned from Query.where()
.
The ExpressionList has a list of convenience methods that create the standard expressions and add them to this list.
The ExpressionList also duplicates methods that are found on the Query such as findList() and orderBy(). The purpose of these methods is provide a fluid API. The upside of this approach is that you can build and execute a query via chained methods. The down side is that this ExpressionList object has more methods than you would initially expect (the ones duplicated from Query).
Query.where()
Modifier and Type | Method and Description |
---|---|
ExpressionList<T> |
add(Expression expr)
Add an Expression to the list.
|
ExpressionList<T> |
addAll(ExpressionList<T> exprList)
Add a list of Expressions to this ExpressionList.s
|
ExpressionList<T> |
allEq(Map<String,Object> propertyMap)
All Equal - Map containing property names and their values.
|
Junction<T> |
and()
Start a list of expressions that will be joined by AND's
returning the expression list the expressions are added to.
|
ExpressionList<T> |
and(Expression expOne,
Expression expTwo)
And - join two expressions with a logical and.
|
Query<T> |
apply(FetchPath fetchPath)
Apply the path properties to the query replacing the select and fetch clauses.
|
ExpressionList<T> |
arrayContains(String propertyName,
Object... values)
Array property contains entries with the given values.
|
ExpressionList<T> |
arrayIsEmpty(String propertyName)
Array is empty - for the given array property.
|
ExpressionList<T> |
arrayIsNotEmpty(String propertyName)
Array is not empty - for the given array property.
|
ExpressionList<T> |
arrayNotContains(String propertyName,
Object... values)
Array does not contain the given values.
|
Query<T> |
asDraft()
Execute the query against the draft set of tables.
|
<D> DtoQuery<D> |
asDto(Class<D> dtoClass)
Convert the query to a DTO bean query.
|
Query<T> |
asOf(Timestamp asOf)
Perform an 'As of' query using history tables to return the object graph
as of a time in the past.
|
UpdateQuery<T> |
asUpdate()
Return the underlying query as an UpdateQuery.
|
ExpressionList<T> |
between(String propertyName,
Object value1,
Object value2)
Between - property between the two given values.
|
ExpressionList<T> |
betweenProperties(String lowProperty,
String highProperty,
Object value)
Between - value between the two properties.
|
ExpressionList<T> |
bitwiseAll(String propertyName,
long flags)
Add expression for ALL of the given bit flags to be set.
|
ExpressionList<T> |
bitwiseAnd(String propertyName,
long flags,
long match)
Add bitwise AND expression of the given bit flags to compare with the match/mask.
|
ExpressionList<T> |
bitwiseAny(String propertyName,
long flags)
Add expression for ANY of the given bit flags to be set.
|
ExpressionList<T> |
bitwiseNot(String propertyName,
long flags)
Add expression for the given bit flags to be NOT set.
|
Junction<T> |
conjunction()
Start (and return) a list of expressions that will be joined by AND's.
|
ExpressionList<T> |
contains(String propertyName,
String value)
Contains - property like %value%.
|
int |
delete()
Execute as a delete query deleting the 'root level' beans that match the predicates
in the query.
|
int |
delete(Transaction transaction)
Execute as a delete query deleting the 'root level' beans that match the predicates
in the query.
|
Junction<T> |
disjunction()
Start (and return) a list of expressions that will be joined by OR's.
|
ExpressionList<T> |
endAnd()
End a AND junction - synonym for endJunction().
|
ExpressionList<T> |
endJunction()
End a junction returning the parent expression list.
|
ExpressionList<T> |
endNot()
End a NOT junction - synonym for endJunction().
|
ExpressionList<T> |
endOr()
End a AND junction - synonym for endJunction().
|
ExpressionList<T> |
endsWith(String propertyName,
String value)
Ends With - property like %value.
|
ExpressionList<T> |
eq(String propertyName,
Object value)
Equal To - property is equal to a given value.
|
ExpressionList<T> |
eqOrNull(String propertyName,
Object value)
Equal To or Null - property is equal to a given value or null.
|
ExpressionList<T> |
exampleLike(Object example)
A "Query By Example" type of expression.
|
boolean |
exists()
Execute the query returning true if a row is found.
|
ExpressionList<T> |
exists(Query<?> subQuery)
Exists expression
|
ExpressionList<T> |
filterMany(String prop)
Add some filter predicate expressions to the many property.
|
int |
findCount()
Return the count of entities this query should return.
|
void |
findEach(java.util.function.Consumer<T> consumer)
Execute the query process the beans one at a time.
|
void |
findEachWhile(java.util.function.Predicate<T> consumer)
Execute the query processing the beans one at a time with the ability to
stop processing before reading all the beans.
|
FutureRowCount<T> |
findFutureCount()
Execute find row count query in a background thread.
|
FutureIds<T> |
findFutureIds()
Execute find Id's query in a background thread.
|
FutureList<T> |
findFutureList()
Execute find list query in a background thread.
|
<A> List<A> |
findIds()
Execute the query returning the list of Id's.
|
QueryIterator<T> |
findIterate()
Execute the query iterating over the results.
|
List<T> |
findList()
Execute the query returning a list.
|
<K> Map<K,T> |
findMap()
Execute the query returning a map.
|
T |
findOne()
Execute the query returning a single bean or null (if no matching
bean is found).
|
Optional<T> |
findOneOrEmpty()
Execute the query returning an optional bean.
|
PagedList<T> |
findPagedList()
Return a PagedList for this query using firstRow and maxRows.
|
Set<T> |
findSet()
Execute the query returning a set.
|
default <A> A |
findSingleAttribute()
Execute a query returning a single value of a single property/column.
|
<A> List<A> |
findSingleAttributeList()
Execute the query returning a list of values for a single property.
|
List<Version<T>> |
findVersions()
Return versions of a @History entity bean.
|
List<Version<T>> |
findVersionsBetween(Timestamp start,
Timestamp end)
Return versions of a @History entity bean between the 2 timestamps.
|
Query<T> |
forUpdate()
Execute using "for update" clause which results in the DB locking the record.
|
Query<T> |
forUpdateNoWait()
Execute using "for update" clause with No Wait option.
|
Query<T> |
forUpdateSkipLocked()
Execute using "for update" clause with Skip Locked option.
|
ExpressionList<T> |
ge(String propertyName,
Object value)
Greater Than or Equal to - property greater than or equal to the given
value.
|
ExpressionList<T> |
gt(String propertyName,
Object value)
Greater Than - property greater than the given value.
|
ExpressionList<T> |
gtOrNull(String propertyName,
Object value)
Greater Than or Null - property greater than the given value or null.
|
ExpressionList<T> |
having()
Add expressions to the having clause.
|
ExpressionList<T> |
icontains(String propertyName,
String value)
Case insensitive Contains - property like %value%.
|
ExpressionList<T> |
idEq(Object value)
Id Equal to - ID property is equal to the value.
|
ExpressionList<T> |
idIn(Collection<?> idValues)
Id IN a collection of id values.
|
ExpressionList<T> |
idIn(Object... idValues)
Id IN a list of id values.
|
ExpressionList<T> |
iendsWith(String propertyName,
String value)
Case insensitive Ends With - property like %value.
|
ExpressionList<T> |
ieq(String propertyName,
String value)
Case Insensitive Equal To - property equal to the given value (typically
using a lower() function to make it case insensitive).
|
ExpressionList<T> |
iexampleLike(Object example)
Case insensitive version of
exampleLike(Object) |
ExpressionList<T> |
ilike(String propertyName,
String value)
Case insensitive Like - property like value where the value contains the
SQL wild card characters % (percentage) and _ (underscore).
|
ExpressionList<T> |
in(String propertyName,
Collection<?> values)
In - property has a value in the collection of values.
|
ExpressionList<T> |
in(String propertyName,
Object... values)
In - property has a value in the array of values.
|
ExpressionList<T> |
in(String propertyName,
Query<?> subQuery)
In - using a subQuery.
|
ExpressionList<T> |
ine(String propertyName,
String value)
Case Insensitive Not Equal To - property not equal to the given value (typically
using a lower() function to make it case insensitive).
|
ExpressionList<T> |
inOrEmpty(String propertyName,
Collection<?> values)
In where null or empty values means that no predicate is added to the query.
|
ExpressionList<T> |
inPairs(Pairs pairs)
In expression using pairs of value objects.
|
ExpressionList<T> |
inRange(String propertyName,
Object value1,
Object value2)
In Range - property >= value1 and property < value2.
|
ExpressionList<T> |
inRangeWith(String lowProperty,
String highProperty,
Object value)
Value in Range between 2 properties.
|
ExpressionList<T> |
isEmpty(String propertyName)
Is empty expression for collection properties.
|
default ExpressionList<T> |
isIn(String propertyName,
Collection<?> values)
In - property has a value in the collection of values.
|
default ExpressionList<T> |
isIn(String propertyName,
Object... values)
In - property has a value in the array of values.
|
default ExpressionList<T> |
isIn(String propertyName,
Query<?> subQuery)
In - using a subQuery.
|
ExpressionList<T> |
isNotEmpty(String propertyName)
Is not empty expression for collection properties.
|
ExpressionList<T> |
isNotNull(String propertyName)
Is Not Null - property is not null.
|
ExpressionList<T> |
isNull(String propertyName)
Is Null - property is null.
|
ExpressionList<T> |
istartsWith(String propertyName,
String value)
Case insensitive Starts With - property like value%.
|
ExpressionList<T> |
jsonBetween(String propertyName,
String path,
Object lowerValue,
Object upperValue)
Between - for the given path in a JSON document.
|
ExpressionList<T> |
jsonEqualTo(String propertyName,
String path,
Object value)
Equal to expression for the value at the given path in the JSON document.
|
ExpressionList<T> |
jsonExists(String propertyName,
String path)
Path exists - for the given path in a JSON document.
|
ExpressionList<T> |
jsonGreaterOrEqual(String propertyName,
String path,
Object value)
Greater than or equal to - for the given path in a JSON document.
|
ExpressionList<T> |
jsonGreaterThan(String propertyName,
String path,
Object value)
Greater than - for the given path in a JSON document.
|
ExpressionList<T> |
jsonLessOrEqualTo(String propertyName,
String path,
Object value)
Less than or equal to - for the given path in a JSON document.
|
ExpressionList<T> |
jsonLessThan(String propertyName,
String path,
Object value)
Less than - for the given path in a JSON document.
|
ExpressionList<T> |
jsonNotEqualTo(String propertyName,
String path,
Object value)
Not Equal to - for the given path in a JSON document.
|
ExpressionList<T> |
jsonNotExists(String propertyName,
String path)
Path does not exist - for the given path in a JSON document.
|
ExpressionList<T> |
le(String propertyName,
Object value)
Less Than or Equal to - property less than or equal to the given value.
|
ExpressionList<T> |
like(String propertyName,
String value)
Like - property like value where the value contains the SQL wild card
characters % (percentage) and _ (underscore).
|
ExpressionList<T> |
lt(String propertyName,
Object value)
Less Than - property less than the given value.
|
ExpressionList<T> |
ltOrNull(String propertyName,
Object value)
Less Than or Null - property less than the given value or null.
|
ExpressionList<T> |
match(String propertyName,
String search)
Add a match expression.
|
ExpressionList<T> |
match(String propertyName,
String search,
Match options)
Add a match expression with options.
|
ExpressionList<T> |
multiMatch(String search,
MultiMatch options)
Add a multi-match expression using options.
|
ExpressionList<T> |
multiMatch(String search,
String... properties)
Add a multi-match expression.
|
Junction<T> |
must()
Start a list of expressions that will be joined by MUST.
|
Junction<T> |
mustNot()
Start a list of expressions that will be joined by MUST NOT.
|
ExpressionList<T> |
ne(String propertyName,
Object value)
Not Equal To - property not equal to the given value.
|
Junction<T> |
not()
Return a list of expressions that will be wrapped by NOT.
|
ExpressionList<T> |
not(Expression exp)
Negate the expression (prefix it with NOT).
|
ExpressionList<T> |
notExists(Query<?> subQuery)
Not exists expression
|
ExpressionList<T> |
notIn(String propertyName,
Collection<?> values)
Not In - property has a value in the collection of values.
|
ExpressionList<T> |
notIn(String propertyName,
Object... values)
Not In - property has a value in the array of values.
|
ExpressionList<T> |
notIn(String propertyName,
Query<?> subQuery)
Not In - using a subQuery.
|
Junction<T> |
or()
Return a list of expressions that will be joined by OR's.
|
ExpressionList<T> |
or(Expression expOne,
Expression expTwo)
Or - join two expressions with a logical or.
|
OrderBy<T> |
order()
Return the OrderBy so that you can append an ascending or descending
property to the order by clause.
|
Query<T> |
order(String orderByClause)
Set the order by clause replacing the existing order by clause if there is
one.
|
OrderBy<T> |
orderBy()
Return the OrderBy so that you can append an ascending or descending
property to the order by clause.
|
Query<T> |
orderBy(String orderBy)
Add an orderBy clause to the query.
|
Query<T> |
query()
Return the query that owns this expression list.
|
ExpressionList<T> |
raw(String raw)
Add raw expression with no parameters.
|
ExpressionList<T> |
raw(String raw,
Object... values)
Add raw expression with an array of parameters.
|
ExpressionList<T> |
raw(String raw,
Object value)
Add raw expression with a single parameter.
|
ExpressionList<T> |
rawOrEmpty(String raw,
Collection<?> values)
Only add the raw expression if the values is not null or empty.
|
Query<T> |
select(FetchGroup<T> fetchGroup)
Apply the fetchGroup which defines what part of the object graph to load.
|
Query<T> |
select(String properties)
Specify specific properties to fetch on the main/root bean (aka partial
object).
|
Query<T> |
setBeanCacheMode(CacheMode beanCacheMode)
Set the mode to use the bean cache when executing this query.
|
Query<T> |
setCountDistinct(CountDistinctOrder orderBy)
Extended version for setDistinct in conjunction with "findSingleAttributeList";
|
Query<T> |
setDisableLazyLoading(boolean disableLazyLoading)
Set true if you want to disable lazy loading.
|
Query<T> |
setDisableReadAuditing()
Disable read auditing for this query.
|
Query<T> |
setDistinct(boolean distinct)
Set whether this query uses DISTINCT.
|
Query<T> |
setDocIndexName(String indexName)
Set the index(es) to search for a document store which uses partitions.
|
Query<T> |
setFirstRow(int firstRow)
Set the first row to fetch.
|
Query<T> |
setIncludeSoftDeletes()
Execute the query including soft deleted rows.
|
Query<T> |
setLabel(String label)
Set a label on the query (to help identify query execution statistics).
|
Query<T> |
setMapKey(String mapKey)
Set the name of the property which values become the key of a map.
|
Query<T> |
setMaxRows(int maxRows)
Set the maximum number of rows to fetch.
|
Query<T> |
setOrderBy(String orderBy)
Add an orderBy clause to the query.
|
Query<T> |
setUseCache(boolean useCache)
Set to true when this query should use the bean cache.
|
Query<T> |
setUseDocStore(boolean useDocsStore)
Set to true if this query should execute against the doc store.
|
default Query<T> |
setUseQueryCache(boolean enabled)
|
Query<T> |
setUseQueryCache(CacheMode useCache)
Set the
CacheMode to use the query cache for executing this query. |
Junction<T> |
should()
Start a list of expressions that will be joined by SHOULD.
|
ExpressionList<T> |
startsWith(String propertyName,
String value)
Starts With - property like value%.
|
ExpressionList<T> |
textCommonTerms(String search,
TextCommonTerms options)
Add common terms expression.
|
ExpressionList<T> |
textQueryString(String search,
TextQueryString options)
Add a query string expression.
|
ExpressionList<T> |
textSimple(String search,
TextSimple options)
Add a simple query string expression.
|
int |
update()
Execute as a update query.
|
int |
update(Transaction transaction)
Execute as a update query with the given transaction.
|
ExpressionList<T> |
where()
Add another expression to the where clause.
|
Query<T> query()
This is a convenience method solely to support a fluid API where the methods are chained together. Adding expressions returns this expression list and this method can be used after that to return back the original query so that further things can be added to it.
Query<T> order(String orderByClause)
This follows SQL syntax using commas between each property with the optional asc and desc keywords representing ascending and descending order respectively.
This is EXACTLY the same as orderBy(String)
.
OrderBy<T> order()
This will never return a null. If no order by clause exists then an 'empty' OrderBy object is returned.
OrderBy<T> orderBy()
This will never return a null. If no order by clause exists then an 'empty' OrderBy object is returned.
Query<T> orderBy(String orderBy)
Query.orderBy(String)
Query<T> setOrderBy(String orderBy)
Query.orderBy(String)
Query<T> apply(FetchPath fetchPath)
Query<T> asOf(Timestamp asOf)
To perform this query the DB must have underlying history tables.
asOf
- the date time in the past at which you want to view the data<D> DtoQuery<D> asDto(Class<D> dtoClass)
We effectively use the underlying ORM query to build the SQL and then execute and map it into DTO beans.
UpdateQuery<T> asUpdate()
Typically this is used with query beans to covert a query bean query into an UpdateQuery like the examples below.
int rowsUpdated = new QCustomer()
.name.startsWith("Rob")
.asUpdate()
.set("active", false)
.update();;
int rowsUpdated = new QContact()
.notes.note.startsWith("Make Inactive")
.email.endsWith("@foo.com")
.customer.id.equalTo(42)
.asUpdate()
.set("inactive", true)
.setRaw("email = lower(email)")
.update();
Query<T> forUpdate()
Query<T> forUpdateNoWait()
This is typically a Postgres and Oracle only option at this stage.
Query<T> forUpdateSkipLocked()
This is typically a Postgres and Oracle only option at this stage.
Query<T> setIncludeSoftDeletes()
int delete()
Note that if the query includes joins then the generated delete statement may not be optimal depending on the database platform.
int delete(Transaction transaction)
Note that if the query includes joins then the generated delete statement may not be optimal depending on the database platform.
int update()
UpdateQuery
int update(Transaction transaction)
UpdateQuery
boolean exists()
The query is executed using max rows of 1 and will only select the id property. This method is really just a convenient way to optimise a query to perform a 'does a row exist in the db' check.
boolean userExists = query().where().eq("email", "rob@foo.com").exists();
boolean userExists = new QContact().email.equalTo("rob@foo.com").exists();
QueryIterator<T> findIterate()
Query.findIterate()
void findEach(java.util.function.Consumer<T> consumer)
Query.findEach(Consumer)
void findEachWhile(java.util.function.Predicate<T> consumer)
Query.findEachWhile(Predicate)
@Nonnull List<T> findList()
Query.findList()
@Nonnull <A> List<A> findIds()
Query.findIds()
int findCount()
This is the number of 'top level' or 'root level' entities.
@Nonnull Set<T> findSet()
Query.findSet()
@Nonnull <K> Map<K,T> findMap()
Query.findMap()
@Nonnull <A> List<A> findSingleAttributeList()
List<String> names =
DB.find(Customer.class)
.select("name")
.orderBy().asc("name")
.findSingleAttributeList();
List<String> names =
DB.find(Customer.class)
.setDistinct(true)
.select("name")
.where().eq("status", Customer.Status.NEW)
.orderBy().asc("name")
.setMaxRows(100)
.findSingleAttributeList();
default <A> A findSingleAttribute()
String name =
DB.find(Customer.class)
.select("name")
.where().eq("id", 42)
.findSingleAttribute();
@Nullable T findOne()
If more than 1 row is found for this query then a NonUniqueResultException is thrown.
javax.persistence.NonUniqueResultException
- if more than one result was foundQuery.findOne()
@Nonnull Optional<T> findOneOrEmpty()
@Nonnull FutureRowCount<T> findFutureCount()
This returns a Future object which can be used to cancel, check the execution status (isDone etc) and get the value (with or without a timeout).
@Nonnull FutureIds<T> findFutureIds()
This returns a Future object which can be used to cancel, check the execution status (isDone etc) and get the value (with or without a timeout).
@Nonnull FutureList<T> findFutureList()
This returns a Future object which can be used to cancel, check the execution status (isDone etc) and get the value (with or without a timeout).
@Nonnull PagedList<T> findPagedList()
The benefit of using this over findList() is that it provides functionality to get the total row count etc.
If maxRows is not set on the query prior to calling findPagedList() then a PersistenceException is thrown.
PagedList<Order> pagedList = DB.find(Order.class)
.setFirstRow(50)
.setMaxRows(20)
.findPagedList();
// fetch the total row count in the background
pagedList.loadRowCount();
List<Order> orders = pagedList.getList();
int totalRowCount = pagedList.getTotalRowCount();
Query.findPagedList()
@Nonnull List<Version<T>> findVersions()
Generally this query is expected to be a find by id or unique predicates query. It will execute the query against the history returning the versions of the bean.
@Nonnull List<Version<T>> findVersionsBetween(Timestamp start, Timestamp end)
Generally this query is expected to be a find by id or unique predicates query. It will execute the query against the history returning the versions of the bean.
@Nonnull ExpressionList<T> filterMany(String prop)
Query<T> select(String properties)
Query.select(String)
Query<T> select(FetchGroup<T> fetchGroup)
Query<T> setDistinct(boolean distinct)
The select() clause MUST be specified when setDistinct(true) is set. The reason for this is that generally ORM queries include the "id" property and this doesn't make sense for distinct queries.
List<Customer> customers =
DB.find(Customer.class)
.setDistinct(true)
.select("name") // only select the customer name
.findList();
Query<T> setDocIndexName(String indexName)
For example, when executing a query against ElasticSearch with daily indexes we can explicitly specify the indexes to search against.
indexName
- The index or indexes to search againstQuery.setDocIndexName(String)
Query<T> setFirstRow(int firstRow)
Query.setFirstRow(int)
Query<T> setMaxRows(int maxRows)
Query.setMaxRows(int)
Query<T> setMapKey(String mapKey)
Query.setMapKey(String)
Query<T> setUseCache(boolean useCache)
This is now the same as setUseBeanCache(CacheMode.ON) and will be deprecated.
Query.setUseCache(boolean)
Query<T> setBeanCacheMode(CacheMode beanCacheMode)
Query.setBeanCacheMode(CacheMode)
Query<T> setUseQueryCache(CacheMode useCache)
CacheMode
to use the query cache for executing this query.Query.setUseQueryCache(boolean)
Query<T> setCountDistinct(CountDistinctOrder orderBy)
List<CountedValue<Order.Status>> orderStatusCount =
DB.find(Order.class)
.select("status")
.where()
.gt("orderDate", LocalDate.now().minusMonths(3))
// fetch as single attribute with a COUNT
.setCountDistinct(CountDistinctOrder.COUNT_DESC_ATTR_ASC)
.findSingleAttributeList();
for (CountedValue<Order.Status> entry : orderStatusCount) {
System.out.println(" count:" + entry.getCount()+" orderStatus:" + entry.getValue() );
}
// produces
count:3 orderStatus:NEW
count:1 orderStatus:SHIPPED
count:1 orderStatus:COMPLETE
default Query<T> setUseQueryCache(boolean enabled)
Query.setUseQueryCache(CacheMode)
Query<T> setUseDocStore(boolean useDocsStore)
When setting this you may also consider disabling lazy loading.
Query<T> setDisableLazyLoading(boolean disableLazyLoading)
That is, once the object graph is returned further lazy loading is disabled.
Query<T> setDisableReadAuditing()
This is intended to be used when the query is not a user initiated query and instead part of the internal processing in an application to load a cache or document store etc. In these cases we don't want the query to be part of read auditing.
Query<T> setLabel(String label)
ExpressionList<T> having()
The having clause is only used for queries based on raw sql (via SqlSelect annotation etc).
ExpressionList<T> where()
ExpressionList<T> jsonExists(String propertyName, String path)
where().jsonExists("content", "path.other")
propertyName
- the property that holds a JSON documentpath
- the nested path in the JSON document in dot notationExpressionList<T> jsonNotExists(String propertyName, String path)
where().jsonNotExists("content", "path.other")
propertyName
- the property that holds a JSON documentpath
- the nested path in the JSON document in dot notationExpressionList<T> jsonEqualTo(String propertyName, String path, Object value)
where().jsonEqualTo("content", "path.other", 34)
propertyName
- the property that holds a JSON documentpath
- the nested path in the JSON document in dot notationvalue
- the value used to test against the document path's valueExpressionList<T> jsonNotEqualTo(String propertyName, String path, Object value)
where().jsonNotEqualTo("content", "path.other", 34)
propertyName
- the property that holds a JSON documentpath
- the nested path in the JSON document in dot notationvalue
- the value used to test against the document path's valueExpressionList<T> jsonGreaterThan(String propertyName, String path, Object value)
where().jsonGreaterThan("content", "path.other", 34)
ExpressionList<T> jsonGreaterOrEqual(String propertyName, String path, Object value)
where().jsonGreaterOrEqual("content", "path.other", 34)
ExpressionList<T> jsonLessThan(String propertyName, String path, Object value)
where().jsonLessThan("content", "path.other", 34)
ExpressionList<T> jsonLessOrEqualTo(String propertyName, String path, Object value)
where().jsonLessOrEqualTo("content", "path.other", 34)
ExpressionList<T> jsonBetween(String propertyName, String path, Object lowerValue, Object upperValue)
where().jsonBetween("content", "orderDate", lowerDateTime, upperDateTime)
ExpressionList<T> add(Expression expr)
ExpressionList<T> addAll(ExpressionList<T> exprList)
ExpressionList<T> eq(String propertyName, Object value)
ExpressionList<T> eqOrNull(String propertyName, Object value)
ExpressionList<T> ne(String propertyName, Object value)
ExpressionList<T> ieq(String propertyName, String value)
ExpressionList<T> ine(String propertyName, String value)
ExpressionList<T> inRangeWith(String lowProperty, String highProperty, Object value)
.startDate.inRangeWith(endDate, now)
// which equates to
startDate <= now and (endDate > now or endDate is null)
This is a convenience expression combining a number of simple expressions. The most common use of this could be called "effective dating" where 2 date or timestamp columns represent the date range in which
ExpressionList<T> inRange(String propertyName, Object value1, Object value2)
Unlike Between inRange is "half open" and usually more useful for use with dates or timestamps.
ExpressionList<T> between(String propertyName, Object value1, Object value2)
ExpressionList<T> betweenProperties(String lowProperty, String highProperty, Object value)
ExpressionList<T> gt(String propertyName, Object value)
ExpressionList<T> gtOrNull(String propertyName, Object value)
ExpressionList<T> ge(String propertyName, Object value)
ExpressionList<T> lt(String propertyName, Object value)
ExpressionList<T> ltOrNull(String propertyName, Object value)
ExpressionList<T> le(String propertyName, Object value)
ExpressionList<T> isNull(String propertyName)
ExpressionList<T> isNotNull(String propertyName)
ExpressionList<T> exampleLike(Object example)
Pass in an example entity and for each non-null scalar properties an expression is added.
By Default this case sensitive, will ignore numeric zero values and will use a Like for string values (you must put in your own wildcards).
To get control over the options you can create an ExampleExpression and set those options such as case insensitive etc.
// create an example bean and set the properties
// with the query parameters you want
Customer example = new Customer();
example.setName("Rob%");
example.setNotes("%something%");
List<Customer> list =
DB.find(Customer.class)
.where().exampleLike(example)
.findList();
Similarly you can create an ExampleExpression
Customer example = new Customer();
example.setName("Rob%");
example.setNotes("%something%");
// create a ExampleExpression with more control
ExampleExpression qbe = new ExampleExpression(example, true, LikeType.EQUAL_TO).includeZeros();
List<Customer> list = DB.find(Customer.class).where().add(qbe).findList();
ExpressionList<T> iexampleLike(Object example)
exampleLike(Object)
ExpressionList<T> like(String propertyName, String value)
ExpressionList<T> ilike(String propertyName, String value)
ExpressionList<T> startsWith(String propertyName, String value)
ExpressionList<T> istartsWith(String propertyName, String value)
ExpressionList<T> endsWith(String propertyName, String value)
ExpressionList<T> iendsWith(String propertyName, String value)
ExpressionList<T> contains(String propertyName, String value)
ExpressionList<T> icontains(String propertyName, String value)
ExpressionList<T> inPairs(Pairs pairs)
ExpressionList<T> in(String propertyName, Query<?> subQuery)
ExpressionList<T> in(String propertyName, Object... values)
ExpressionList<T> in(String propertyName, Collection<?> values)
ExpressionList<T> inOrEmpty(String propertyName, Collection<?> values)
That is, only add the IN predicate if the values are not null or empty.
Without this we typically need to code an if
block to only add
the IN predicate if the collection is not empty like:
query.where() // add some predicates
.eq("status", Status.NEW);
if (ids != null && !ids.isEmpty()) {
query.where().in("customer.id", ids);
}
query.findList();
query.where()
.eq("status", Status.NEW)
.inOrEmpty("customer.id", ids)
.findList();
default ExpressionList<T> isIn(String propertyName, Query<?> subQuery)
This is exactly the same as in() and provided due to "in" being a Kotlin keyword (and hence to avoid the slightly ugly escaping when using in() in Kotlin)
default ExpressionList<T> isIn(String propertyName, Object... values)
This is exactly the same as in() and provided due to "in" being a Kotlin keyword (and hence to avoid the slightly ugly escaping when using in() in Kotlin)
default ExpressionList<T> isIn(String propertyName, Collection<?> values)
This is exactly the same as in() and provided due to "in" being a Kotlin keyword (and hence to avoid the slightly ugly escaping when using in() in Kotlin)
ExpressionList<T> notIn(String propertyName, Object... values)
ExpressionList<T> notIn(String propertyName, Collection<?> values)
ExpressionList<T> notIn(String propertyName, Query<?> subQuery)
ExpressionList<T> isEmpty(String propertyName)
ExpressionList<T> isNotEmpty(String propertyName)
ExpressionList<T> exists(Query<?> subQuery)
ExpressionList<T> notExists(Query<?> subQuery)
ExpressionList<T> idIn(Object... idValues)
ExpressionList<T> idIn(Collection<?> idValues)
ExpressionList<T> idEq(Object value)
ExpressionList<T> allEq(Map<String,Object> propertyMap)
Expression where all the property names in the map are equal to the corresponding value.
propertyMap
- a map keyed by property names.ExpressionList<T> arrayContains(String propertyName, Object... values)
ExpressionList<T> arrayNotContains(String propertyName, Object... values)
Array support is effectively limited to Postgres at this time.
ExpressionList<T> arrayIsEmpty(String propertyName)
Array support is effectively limited to Postgres at this time.
ExpressionList<T> arrayIsNotEmpty(String propertyName)
Array support is effectively limited to Postgres at this time.
ExpressionList<T> bitwiseAny(String propertyName, long flags)
where().bitwiseAny("flags", BwFlags.HAS_BULK + BwFlags.HAS_COLOUR)
propertyName
- The property that holds the flags valueflags
- The flags we are looking forExpressionList<T> bitwiseAll(String propertyName, long flags)
where().bitwiseAll("flags", BwFlags.HAS_BULK + BwFlags.HAS_COLOUR)
propertyName
- The property that holds the flags valueflags
- The flags we are looking forExpressionList<T> bitwiseNot(String propertyName, long flags)
where().bitwiseNot("flags", BwFlags.HAS_COLOUR)
propertyName
- The property that holds the flags valueflags
- The flags we are looking forExpressionList<T> bitwiseAnd(String propertyName, long flags, long match)
// Flags Bulk + Size = Size
// ... meaning Bulk is not set and Size is set
long selectedFlags = BwFlags.HAS_BULK + BwFlags.HAS_SIZE;
long mask = BwFlags.HAS_SIZE; // Only Size flag set
where().bitwiseAnd("flags", selectedFlags, mask)
propertyName
- The property that holds the flags valueflags
- The flags we are looking forExpressionList<T> raw(String raw, Object value)
The raw expression should contain a single ? at the location of the parameter.
When properties in the clause are fully qualified as table-column names then they are not translated. logical property name names (not fully qualified) will still be translated to their physical name.
// use a database function
raw("add_days(orderDate, 10) < ?", someDate)
.raw("t0.customer_id in (select customer_id from customer_group where group_id = any(?::uuid[]))", groupIds)
ExpressionList<T> raw(String raw, Object... values)
The raw expression should contain the same number of ? as there are parameters.
When properties in the clause are fully qualified as table-column names then they are not translated. logical property name names (not fully qualified) will still be translated to their physical name.
ExpressionList<T> raw(String raw)
When properties in the clause are fully qualified as table-column names then they are not translated. logical property name names (not fully qualified) will still be translated to their physical name.
raw("orderQty < shipQty")
.raw("t0.customer_id in (select customer_id from customer_group where group_id = any(?::uuid[]))", groupIds)
ExpressionList<T> rawOrEmpty(String raw, Collection<?> values)
This is a pure convenience expression to make it nicer to deal with the pattern where we use raw() expression with a subquery and only want to add the subquery predicate when the collection of values is not empty.
query.where() // add some predicates
.eq("status", Status.NEW);
// common pattern - we can use rawOrEmpty() instead
if (orderIds != null && !orderIds.isEmpty()) {
query.where().raw("t0.customer_id in (select o.customer_id from orders o where o.id in (?1))", orderIds);
}
query.findList();
?1
bind parameter to get "parameter expansion"
for each element in the collection.
query.where()
.eq("status", Status.NEW)
// only add the expression if orderIds is not empty
.rawOrEmpty("t0.customer_id in (select o.customer_id from orders o where o.id in (?1))", orderIds);
.findList();
ANY
expression and array parameter binding
rather than IN
.
query.where()
.eq("status", Status.NEW)
.rawOrEmpty("t0.customer_id in (select o.customer_id from orders o where o.id = any(?))", orderIds);
.findList();
Note that we need to cast the Postgres array for UUID types like:
" ... = any(?::uuid[])"
raw
- The raw expression that is typically a subqueryvalues
- The values which is typically a list or set of id values.ExpressionList<T> match(String propertyName, String search)
propertyName
- The property name for the matchsearch
- The search valueExpressionList<T> match(String propertyName, String search, Match options)
propertyName
- The property name for the matchsearch
- The search valueExpressionList<T> multiMatch(String search, String... properties)
ExpressionList<T> multiMatch(String search, MultiMatch options)
ExpressionList<T> textSimple(String search, TextSimple options)
ExpressionList<T> textQueryString(String search, TextQueryString options)
ExpressionList<T> textCommonTerms(String search, TextCommonTerms options)
ExpressionList<T> and(Expression expOne, Expression expTwo)
ExpressionList<T> or(Expression expOne, Expression expTwo)
ExpressionList<T> not(Expression exp)
Junction<T> and()
This is exactly the same as conjunction();
Use endAnd() or endJunction() to end the AND junction.
Note that a where() clause defaults to an AND junction so typically you only explicitly need to use the and() junction when it is nested inside an or() or not() junction.
// Example: Nested and()
.where()
.or()
.and() // nested and
.startsWith("name", "r")
.eq("anniversary", onAfter)
.endAnd()
.and()
.eq("status", Customer.Status.ACTIVE)
.gt("id", 0)
.endAnd()
.orderBy().asc("name")
.findList();
Junction<T> or()
Use endOr() or endJunction() to end the OR junction.
// Example: (status active OR anniversary is null)
.where()
.or()
.eq("status", Customer.Status.ACTIVE)
.isNull("anniversary")
.orderBy().asc("name")
.findList();
// Example: Use or() to join
// two nested and() expressions
.where()
.or()
.and()
.startsWith("name", "r")
.eq("anniversary", onAfter)
.endAnd()
.and()
.eq("status", Customer.Status.ACTIVE)
.gt("id", 0)
.endAnd()
.orderBy().asc("name")
.findList();
Junction<T> not()
Use endNot() or endJunction() to end expressions being added to the NOT expression list.
.where()
.not()
.gt("id", 1)
.eq("anniversary", onAfter)
.endNot()
// Example: nested not()
.where()
.eq("status", Customer.Status.ACTIVE)
.not()
.gt("id", 1)
.eq("anniversary", onAfter)
.endNot()
.orderBy()
.asc("name")
.findList();
Junction<T> conjunction()
This is the same as and().
Junction<T> disjunction()
This is the same as or().
Junction<T> must()
This automatically makes the query a useDocStore(true) query that will execute against the document store (ElasticSearch etc).
This is logically similar to and().
Junction<T> should()
This automatically makes the query a useDocStore(true) query that will execute against the document store (ElasticSearch etc).
This is logically similar to or().
Junction<T> mustNot()
This automatically makes the query a useDocStore(true) query that will execute against the document store (ElasticSearch etc).
This is logically similar to not().
ExpressionList<T> endJunction()
Ends a and(), or(), not(), must(), mustNot() or should() junction such that you get the parent expression.
Alternatively you can always use where() to return the top level expression list.
ExpressionList<T> endAnd()
ExpressionList<T> endOr()
ExpressionList<T> endNot()
Copyright © 2019. All rights reserved.