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