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 |
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:
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();
Sub-Query 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();