Search Library

This document details how to query or search objects in Oracle Health Insurance. It mentions the operations that perform different kind of queries.

Warnings

Use searching in your dynamic logic with care, especially when the number of rows that you retrieve is potentially big (> 50 as an heuristic):

Searching means data retrieval from the database. The performance of this data retrieval depends upon the number of rows and the available access paths (do indexes exist?).
The data retrieved from the database is loaded into memory. Each object may have relations to other objects which are loaded as well. Therefore it is strongly recommended not to load big sets at once, but instead load them in pages. The Pagination section describes how to do that.

Model

Search Library makes use of SearchBuilder. The SearchBuilder object uses a builder pattern to build up the search DSL and perform the search. The object returns the filtered results from the underlying data store.

SearchBuilder

The SearchBuilder has the following aspects:

Searched Object

To specify the type of object on which the search is performed. Use the following syntax:

SearchBuilder searchBuilder = new SearchBuilder(Claim.class)

Claim.class refers to the object that you are querying.

A Search Expression

Search Expressions are the basic building blocks of a search. Something similar to a search expression in an SQL WHERE statement when searching for an object with code='A', name like '%john%' . One or more search expressions are needed when doing a search. A search expression has a construct like attribute.operator(operand).

Attribute

Attribute can be a direct attribute on the object that is searched on, or it can be a joined attribute. Following are some examples to show direct or joined attributes using SearchBuilder.

Search on claim using claim code.

List<Claim> claims = new SearchBuilder(Claim.class).by("code").eq("CLAIMCODE").execute()

Search on claim using claimLine code.
```
List < Claim > claims = new SearchBuilder(Claim.class)
  .join("claimLineList")
  .by("code").eq("CLAIMLINECODE")
  .execute()
```
The searchbuilder uses join to connect the claimLineList in Claim object and the search expression is built is then built on the joined ClaimLine.

Search on claim using payer code that is on the brand of the claim.

List < Claim > claims = new SearchBuilder(Claim.class)
  .join("brand")
  .join("payer")
  .by("code").eq("PAYERCODE")
  .execute()

Operator

Following is the table detailing all the possible operators for a search expression in a SearchBuilder.

Table 1. Operator
Operator	Description	Example
eq	Checks for equality, if the argument passed in is null, will check for isNull	by("name").eq("king")
eqIgnoreCase	Checks for equality ignoring the case.	by("name").eqIgnoreCase("king")
neq	Checks for non equality, if the argument passed in is null, will check for isNotNull	by("name").neq("john")
lt	Checks for less than	by("startDate").lt("2015-11-11")
lte	Checks for less than equal to	by("length").lte(30)
gt	Checks for greater than	by("length").gt(30)
gte	Checks for greater than equal to	by("startDate").gte("2015-11-11")
in	Checks the value is one of the given values.	by("code").in("AB","CD","XY")
between	Checks if the value is between the passed in arguments.	by("age").between(6,10)
like	Applies the like operation, supporting `%` and `_` as wildcards. It is also possible to pass in a second argument to specify an escape character, which can be put in front of a wildcard character to indicate that it should be treated literally, and not as a wildcard.	by("name").like("J%") finds names starting with J by("name").like("b_g") finds "big" and "bug" by("name").like("G/_%","/") finds names starting with G_ where _ is taken literally
likeIgnoreCase	Same as like, except it will now ignore the case	by("name").likeIgnoreCase("J%25") by("name").likeic("g/_%","/")
notIn	Checks for not in.	by("code").notIn("AB","CD","XY")

Joining Search Expressions

Next aspect of SearchBuilder is to provide an ability to join search expressions. The operations provided to achieve that are following:

And

SearchExpression1.and().SearchExpression2

Example: Search on claim by the brand code and by the start date.

List < Claim > claims = new SearchBuilder(Claim.class)
  .join("brand")
  .by("code").eq("BRANDCODE")
  .and()
  .by("startDate").gte("2015-01-01")
  .execute()

Using multiple join statements in a search query doesn’t result in equivalent queries to the database. The SearchBuilder combines the join statements to prevent multiple database join operations on the same table. For example, the following code searches a countryRegion object by using two join statements on country table:

def countryRegion = new SearchBuilder(CountryRegion.class)
.join("country").by("active").eq("true")
.and()
.join("country").by("code").eq("CODE")
.execute()

The SearchBuilder combines the join statements on two attributes - code and active - and prevents joining the two tables in the database. Thus, you can use multiple joins on the SearchBuilder without worrying about impact on performance.

Or

SearchExpression1.or().SearchExpression2

Example: Search on claim by the brand code or by the 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

In order to provide for grouping different search expressions together, SearchBuilder has group and end operations. Use group to indicate to the builder that the search expressions forthcoming are in a group and use end to indicate that the group has ended. It is also possible to have nested groups.

Example: Search on claim by start date greater than some date and endDate is less than some date or is null.

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

SearchBuilder also provides an ability to use subquery using inSubQuery and notInSubQuery.

Example 1a: Search on claim where related claim message list has ABC as a message.

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: Search on claim where related claim message list does not have ABC as a message.

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()

Let’s see the sub parts of inSubQuery and notInSubQuery.

inSubQuery()/notInSubQuery() and subQueryEnd(): These constructs tell the builder that a sub query has started (in or not in) and ended respectively.
select(): This tells the builder on the projection of the subquery select. In our example, it is the id on the Message. It can be a joined select also.
from(<CLASS>): This tells the builder on which table to make the select from in the subQuery construct.
Next comes the search expressions for subQuery select.

Exists Query Expressions

SearchBuilder also provides an ability to search whether a keyword exists using the existsQuery and notExistsQuery.

Example 1a: Search on claim with brand XYZ and exists an access restriction ACRE on 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: Search on claim with brand XYZ and not exists an access restriction ACRE on 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()

Let’s see the sub parts of existsQuery and notExistsQuery.

existsQuery()/notExistsQuery() and existsQueryEnd(): These constructs tell the builder that an exists query has started (in or not in) and ended respectively.
select(boolean): This tells the builder on the select part of exists query. Value true means select id, and false means select 1.
from(<CLASS>): This tells the builder on which table to make the select from in the existsQuery construct.
Next comes the search expressions for existsQuery select.

Sorting

SearchBuilder uses orderBy(<ATTR>) to sort the queried objects.

Example 1

This can be a single attribute or a joined attribute. Orderby on direct attribute on claim.

List < Claim > claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("code").execute()

Example 2

Joined Attribute

List < Claim > claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("claimLineList.code").execute()

Example 3

Nulls First

List < Claim > claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("claimLineList.code NULLS FIRST").execute()

Example 4

Nulls Last

List < Claim > claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("claimLineList.code NULLS LAST").execute()

Example 5

Sort by ascending order, this is by default.

List < Claim > claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("claimLineList.code").ascending().execute()

Example 6

Sort by descending order.

List < Claim > claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("claimLineList.code").descending().execute()

Example 7

Sort by attribute with outer join.

List < Claim > claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("claimLineList(+).code").descending().execute()

Pagination

SearchBuilder has setPage() and setPageSize() to paginate the queried objects.

Example 1

Search on claim by start date, ordered on code and only fetch five claims.

List < Claim > claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("code").setPageSize(5).execute()

Example 2

Search on claim by start date, ordered on code and only fetch five to ten claims.

List < Claim > claims = new SearchBuilder(Claim.class)
  .by("startDate").gte("2015-01-01")
  .orderBy("code")
  .setPageSize(5).setPage(2).execute()

Executing the Search

After providing all the aspects to the SearchBuilder, depending on the requirement, you need to use execute() to perform the search and get the results. It returns a list of objects that the search that matches the criteria.