Search Library
This page explains query or search operations through a dynamic logic in Oracle Health Insurance. It mentions the different operations to perform unique searches.
Be careful with dynamic logic searches that return a high number of rows (more than 50).
|
Model
The search library uses a search builder. The builder operates through the SearchBuilder
object to build up the search and perform the search operation using a builder pattern.
The object returns the filtered objects from the underlying data store.
The Search Builder
The search builder has the following aspects:
Searched Object
The searched object specifies the object to search. Following is an example that searches an object:
SearchBuilder searchBuilder = new SearchBuilder(Claim.class)
The Claim.class
argument refers to the object that is being queried.
Search Expression
A search expression is the basic building block of a search. It replicates a SQL WHERE statement that searches an object with code='A' and name like %john%. A search requires one or more search expressions. A search expression has a syntax like attribute.operator(operand).
- Attribute
-
An attribute defines the direct or joined attribute of an object to perform the search. Following are examples to search within direct or joined attributes:
-
Search a claim for a matching claim code
List<Claim> claims = new SearchBuilder(Claim.class).by("code").eq("CLAIMCODE").execute()
-
Search a claim for a claim line code
List<Claim> claims = new SearchBuilder(Claim.class) .join("claimLineList") .by("code").eq("CLAIMLINECODE") .execute()
The search builder uses a join operation to connect the
claimLineList
and theClaim
objects. The search expression is then built on the joined claim line. -
Search a claim for a payer code, also a brand on the claim
List<Claim> claims = new SearchBuilder(Claim.class) .join("brand") .join("payer") .by("code").eq("PAYERCODE") .execute()
-
- Operator
-
Following is a list of available operators for a search expression within a search builder:
Operator | Description | Example |
---|---|---|
|
Checks for matching values. Checks for null values for an empty argument. |
|
|
Checks for matching values and ignores the case. |
|
|
Checks for unmatched values. Checks for non-empty fields for an empty argument. |
|
|
Checks for values less than the specified argument. |
by("startDate").lt("2015-11-11") |
|
Checks for a value less than or equal to the specified argument. |
by("length").lte(30) |
` gt` |
Checks for values greater than the specified argument. |
by("length").gt(30) |
` gte` |
Checks for values greater than or equal to the specified argument. |
by("startDate").gte("2015-11-11") |
|
Checks for values that match the list in the passed argument. |
by("code").in("AB","CD","XY") |
|
Checks for a value within the range of the passed arguments. |
by("age").between(6,10) |
|
Check if the values that contain the string passed as argument. Works like the |
|
|
Works same as the |
|
|
Checks for values that are not in the list passed in the argument. |
|
Joining Search Expressions
This aspect of the search builder joins search expressions. Following are the operations that join search expressions:
- The
and
Operation
SearchExpression1.and().SearchExpression2
The following example searches a claim for a specified brand code and a specified start date:
List<Claim> claims = new SearchBuilder(Claim.class)
.join("brand")
.by("code").eq("BRANDCODE")
.and()
.by("startDate").gte("2015-01-01")
.execute()
Multiple join statements do not result in multiple queries to the database.
The search builder combines the join statements to prevent multiple database join operations on the same table.
The following example searches a def countryRegion = new SearchBuilder(CountryRegion.class) .join("country").by("active").eq("true") .and() .join("country").by("code").eq("CODE") .execute() Here, the search builder combines the join statements on two attributes - |
- The
or
Operation
SearchExpression1.or().SearchExpression2
The following example searches a claim on either a brand code or a start date:
List<Claim> claims = new SearchBuilder(Claim.class)
.join("brand")
.by("code").eq("BRANDCODE")
.or()
.by("startDate").gte("2015-01-01")
.execute()
Searching Reference Sheets
Consider a reference sheet refSheetA
using a dynamic record definition with two fields, field1
and field2
, as definition.
To search the reference sheet lines based on a single criteria, in this case searching for lines with field1 having the value A, use the following dynamic logic:
def search = new SearchBuilder(ReferenceSheetLine.class)
.join("refSheetA").by("field1").eq("A")
.execute()
To search the reference sheet lines based on multiple criteria, in this case searching for lines with field1 having the value A and field2 having value B, use the following dynamic logic.
Note that you must use .join()
for every criteria/search-field.
def search = new SearchBuilder(ReferenceSheetLine.class)
.join("refSheetA").by("field1").eq("A")
.and()
.join("refSheetA").by("field2").eq("B")
.execute()
Grouping Search Expressions
The search builder has the group
and end
operations to group different search expressions together. The
group
operations inform the search builder about the start of grouped expressions and the end
operation denotes end of a group. It is also possible to have nested groups.
The following example searches a claim on a start date greater than the specified date and an empty end date, or an end date less than a specified date:
List<Claim> claims = new SearchBuilder(Claim.class)
.by("startDate").gte("2015-01-01")
.and()
.group()
.by("endDate").lte("2017-01-01").or().by("endDate").eq(null)
.end().execute()
Sub-Query Expressions
The search builder searches with a subquery using the inSubQuery
and notInSubQuery
operations.
- Example 1a
-
The following example searches for a claim with ABC in its message within a claim message list:
List<Claim> claims = new SearchBuilder(Claim.class)
.join("claimMessageList").join("message").by("id")
.inSubQuery().select().("id")
.from(Message.class)
.by("code").eq("ABC")
.subQueryEnd().execute()
- Example 2
-
The following example searches for a claim with ABC in a message within a claim message list:
List<Claim> claims = new SearchBuilder(Claim.class)
.join("claimMessageList").join("message").by("id")
.notInSubQuery().select().("id")
.from(Message.class)
.by("code").eq("ABC")
.subQueryEnd().execute()
Following are the subparts of the inSubQuery
and notInSubQuery
operations:
-
inSubQuery()
/notInSubQuery()
: Informs the builder when a query (in or not in) is started. -
subQueryEnd()
: Informs the builder when a subquery has ended. -
select()
: Informs the builder of the scope of the select operation. In the example, it is theid
on the message. This operation also takes a join operation as an argument. -
from(<CLASS>)
: Informs the builder about the table name to perform the search as part of the subquery. -
Next comes the search expressions for the select operation within the subquery.
Exists Query Expressions
The search builder searches for a keyword using the existsQuery
and notExistsQuery
operations.
- Example 1a
-
The following example searches a claim with the
XYZ
brand and ends an access restriction ACRE for that brand:
List<Claim> claims = new SearchBuilder(Claim.class)
.join("brand").("code").eq("XYZ").and()
.existsQuery().select(true)
.from(Brand.class)
.by("code").eq("XYZ").and()
.join("accessRestriction").by("code").eq("ACRE")
.existsQueryEnd().execute()
- Example 2
-
The following example searches a claim with the brand XYZ and retains the access restriction ACRE for that brand:
List<Claim> claims = new SearchBuilder(Claim.class)
.join("brand").("code").eq("XYZ").and()
.notExistsQuery().select(true)
.from(Brand.class)
.by("code").eq("XYZ").and()
.join("accessRestriction").by("code").eq("ACRE")
.existsQueryEnd().execute()
Following are the subparts of the existsQuery
and notExistsQuery
operations:
-
existsQuery()
/notExistsQuery()
: Suggests start of an exists query (in or not in) to the builder. -
existsQueryEnd()
: Suggests end of an exists query to the builder. -
select(boolean)
: Suggests to the builder that the select operation is part of the exists query. Atrue
selectsid
and afalse
selects1
. -
from(<CLASS>)
: Suggests a table to the builder for theexistsQuery
operation. -
Search expression: The expression to select fields using the
existsQuery
operation.
Sorting
The search builder uses orderBy(<ATTR>)
to sort the queried objects. The search can be on a single or a joined attribute.
The following example orders the result on a direct attribute (claim):
+
List<Claim> claims = new SearchBuilder(Claim.class)
.by("startDate").gte("2015-01-01")
.orderBy("code").execute()
The following examples searches on a joined attribute:
+
List<Claim> claims = new SearchBuilder(Claim.class)
.by("startDate").gte("2015-01-01")
.orderBy("claimLineList.code").execute()
The following example returns null results first:
+
List<Claim> claims = new SearchBuilder(Claim.class)
.by("startDate").gte("2015-01-01")
.orderBy("claimLineList.code NULLS FIRST").execute()
The following example returns null results last:
+
List<Claim> claims = new SearchBuilder(Claim.class)
.by("startDate").gte("2015-01-01")
.orderBy("claimLineList.code NULLS LAST").execute()
The following example sorts the results in an ascending order, which is default for every search:
+
List<Claim> claims = new SearchBuilder(Claim.class)
.by("startDate").gte("2015-01-01")
.orderBy("claimLineList.code").ascending().execute()
The following example sorts the results in a descending order:
+
List<Claim> claims = new SearchBuilder(Claim.class)
.by("startDate").gte("2015-01-01")
.orderBy("claimLineList.code").descending().execute()
The following example sorts the results by attribute with outer join:
+
List<Claim> claims = new SearchBuilder(Claim.class)
.by("startDate").gte("2015-01-01")
.orderBy("claimLineList(+).code").descending().execute()
Pagination
The search builder has setPage()
and setPageSize()
operations to paginate the results.
- Example 1
-
The following example searches for five claims on start date and orders the results by code:
List<Claim> claims = new SearchBuilder(Claim.class) .by("startDate").gte("2015-01-01") .orderBy("code").setPageSize(5).execute()
- Example 2
-
The following example searches the next five claims (six to ten) by start date and orders the results by their code:
List<Claim> claims = new SearchBuilder(Claim.class) .by("startDate").gte("2015-01-01") .orderBy("code") .setPageSize(5).setPage(2).execute()
- Example 3
-
The following example performs a read-only search on claims based on their start date and orders the results on their claim’s code. The results display five claims at a time until there are none left:
SearchBuilder searchBuilder = new SearchBuilder(Claim.class) for (int i = 1; i <= 10; i++ ) { claims = searchBuilder.by("startDate").gte("2015-01-01") .orderBy("code") .setReadOnly(true) .setPageSize(5).setPage(i).execute() if (claims.isEmpty()) { break } // do something with these claims }
Pagination reduces memory footprint only when the |
Any collections returned by the search must be defined within a loop to prevent memory outage. The following logic checks for failed exchanges based on an integration code and paginates with three results: operator = "continue" i = 1 while(operator == "continue"){ previousExchanges = new SearchBuilder(Exchange.class) .by("id").neq(exchangeStep.exchange.id).and() .join("integration").by("code").eq(exchangeStep.exchange.integration.code) .setPageSize(3) .setPage .execute() if(previousExchanges.isEmpty()) { operator = "stop" } else { for(previousExchange in previousExchanges){ if(['F', 'T'].contains(previousExchange.exchangeStatus)) { operator = "stop" break; } } i++ } } |