Search Library

This document will detail on how objects can be queried/searched in the Oracle Health Insurance applications. It will also detail the apis to be used to perform different kind of queries.

Model

Search Library makes use of SearchBuilder. The SearchBuilder object uses a builder pattern to build up the search DSL and executing the search(calling api execute on the builder) on it, will return the filtered results from the underlying data store.

SearchBuilder API

The api of SearchBuilder has many aspects, detailed below.

Searched Object

To specify the type of objects that are searched. Using SearchBuilder api, it can be done as

SearchBuilder searchBuilder = new SearchBuilder(Claim.class);

Claim.class refers to the object that needs to be queried.

A Search Expression

Search Expressions are the basic building blocks of a search. You can think of a search expression as statement in an SQL WHERE clause. Examples of search expressions are 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 api.

Search on claim using claim code.

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

Search on claim using claimLine code. In the example below, the api "join" is used to join the claimLineList in Claim object and the search expression is built is then built on the joined ClaimLine.

List<Claim> claims = new SearchBuilder(Claim.class)
                           .join("claimLineList")
                           .by("code").eq("CLAIMLINECODE")
                           .execute();

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 api.

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

Operator

Description

Example

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'")

Checks for less than

by("startDate").lt("2015-11-11")

lte

Checks for less than equal to

by("length").lte(30)

Checks for greater than

by("length").gt(30)

gte

Checks for greater than equal to

by("startDate").gte("2015-11-11")

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)

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 api is to provide an ability to join search expressions. The apis 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();

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

Grouping Search Expressions

In order to provide for grouping different search expressions together, SearchBuilder has api group and end. 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();

Subquery Expressions

SearchBuilder also provides an ability to use subqueries. The api to be used in such a scenario is inSubQuery and notInSubQuery. Let’s first see an example and then let’s go into details for each part of such a construct.

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

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 message 'ABC'

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 use exists/not exists. The api to be used in such a scenario is existsQuery and notExistsQuery. Let’s first see an example and then let’s go into details for each part of such a construct.

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

In order to provide for sorting on the queried objects, SearchBuilder has api orderBy(<ATTR>).

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

In order to provide for pagination on the queried objects, SearchBuilder has api setPage() and setPageSize().

Example 1: Search on claim by start date, ordered on code and only fetch 5 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 5-10 claims.

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

Executing the Search

Finally, after providing all the aspects to the SearchBuilder, depending on the requirement, api execute() has to be used to perform the search and get the results. It will return a list of the type of objects that the search is performed on.