Looking at the keys provided, question does arise – What does all this mean to me when I have to write my SQL query? Let us take a sample query and look at typical needs / asks -
Assume there is a table EMPLOYEES having the columns EMP_ID, EMP_NAME, EMP_AGE, EMP_DOJ. A typical query for showcasing the Employee Id, Name and Age will be as –follows: SELECT
| Code Block | ||
|---|---|---|
| ||
SELECT EMP_ID, EMP_NAME, EMP_AGE |
...
FROM |
...
EMPLOYEES |
If you require dynamic filters and sorts, the query must be changed –as follows: SELECT
| Code Block | ||
|---|---|---|
| ||
SELECT EMP_ID, EMP_NAME, EMP_AGE |
...
FROM |
...
EMPLOYEES |
...
WHERE |
...
<COL 1> like '%filter%' –- and so on |
...
ORDER BY |
...
<COL1> ASC, <COL 2> DESC–- and so on |
For enabling pagination, the following two inputs are necessary– the start row number and end row number. For the end UI to handle the pagination properly, the output should also include the total number of records in the result after applying filters.
There are multiple approaches to –enable pagination:
Approach 1: Fire separate queries for getting the total number of records and for the actual slice of data corresponding to the start and end row number. An example would be -
| Code Block | ||
|---|---|---|
| ||
Query for Total number of records |
...
SELECT |
...
COUNT |
...
as TOTAL_COUNT |
...
FROM |
...
EMPLOYEES |
...
WHERE |
...
<COL 1> like '%filter%' –- and so on |
...
ORDER BY |
...
<COL1> ASC, <COL 2> DESC –- and so on |
And the actual query with Paging is done like -
| Code Block | ||
|---|---|---|
| ||
Query with paging |
...
SELECT |
...
EMP_ID, EMP_NAME, EMP_AGE |
...
FROM |
...
EMPLOYEES |
...
WHERE |
...
<COL 1> like '%filter%' –- and so on |
...
AND ROWNUM < endRowOfPage and ROWNUM > startRowOfPage |
...
ORDER BY |
...
<COL1> ASC, <COL 2> DESC –- and so on |
While this construct will work, it gives rise to multiple inconsistencies, such as:
...
Approach 2: Get the row number as part of a single query
| Code Block | ||
|---|---|---|
| ||
Query with paging |
...
SELECT * FROM ( SELECT PAGINATED_TBL.*, ROWNUM RNUM FROM ( |
...
SELECT |
...
EMP_ID, EMP_NAME, EMP_AGE |
...
FROM |
...
EMPLOYEES |
...
WHERE |
...
<COL 1> like '%filter%' –- and so on |
...
ORDER BY |
...
<COL1> ASC, <COL 2> DESC –- and so on |
...
) PAGINATED_TBL WHERE ROWNUM < endRowOfPage ) WHERE RNUM > startRowOfPage |
While this construct will work, it gives rise to different set of problems, such as:
...
Approach 3: Using Analytical functions to get the Row number as well as the target result set in a single go. There are multiple analytical functions provided that work with the result set in memory rather than involve additional physical reads or computation resources that make for best result. The following sample query construct in this approach is provided:
| Code Block | ||
|---|---|---|
| ||
Query with paging using analytical functions |
...
SELECT * FROM ( |
...
SELECT |
...
EMP_ID, EMP_NAME, EMP_AGE |
...
,COUNT |
...
OVER () AS TOTAL_ROWS |
...
, ROW_NUMBER () OVER ( |
...
<COL1> ASC, <COL 2> DESC –- and so on |
...
) ROWNM |
...
FROM |
...
EMPLOYEES |
...
WHERE |
...
<COL 1> like '%filter%' –- and so on |
...
) WHERE ROWNM < endRowOfPage AND ROWNM > startRowOfPage ORDER BY ROWNM |
This approach brings about an interesting option. It enables the total count of records like any other column for usage at no additional query cost.
The third approach is used while framing queries with support for dynamic filters, sort and paging. This means that a typical query can be created as a template as –follows:
| Code Block | ||
|---|---|---|
| ||
<Pagination Begin section>----------------------> Id: NEW_PAGINATION_BEGINS |
...
<Select query with columns>---------------> As defined by you |
...
<Pagination row count section>---------------> Id: TOTALCOUNT_FOR_PAGINATION |
...
<Dynamic Sorts section>----------------------> Id: NEW_DEFAULT_ORDERBY_CLAUSE |
...
<Default From and Where clause for the query> ---> as defined by you |
...
<Dynamic Filters section>----------------------> Id: FILTER_CLAUSE |
...
<Pagination End section>----------------------> Id: NEW_PAGINATION_ENDS |
The following code segment is a sample query configuration using these Maps–IDs:
| Code Block | ||
|---|---|---|
| ||
<select id="FEATURE_1_SELECT_MODULE_1" resultMap="FEATURE_1_RESULT_MAP"> |
...
<include refid="NEW_PAGINATION_BEGINS" /> |
...
SELECT |
...
TRAN_REF_NO, |
...
DEBIT_ACC_NO, |
...
BENE_ACC_NO, |
...
<include refid="TOTALROWS_FOR_PAGINATION"/> |
...
<include refid="NEW_DEFAULT_ORDERBY_CLAUSE"/> |
...
FROM (SELECT TRAN_REF_NO, DEBIT_ACC_NO, BENE_ACC_NO |
...
FROM CT_TRAN_SUMMARY_DATA) |
...
WHERE 1=1 |
...
<include refid="FILTER_CLAUSE" /> |
...
<include refid="NEW_PAGINATION_ENDS" /> |
...
</select> |
This way of organization brings about the following pointers:
- The result map configured for this Query model should ensure that it provides a mapping for the column TOTAL_COUNT. The following code segment is a sample result mapping for this column. The TOTAL_COUNT mapping is highlighted in yellow for easy identification.
| Code Block | ||
|---|---|---|
| ||
<resultMap id="FEATURE_1_RESULT_MAP" class="java.util.HashMap"> |
...
<result property="TRAN_REF_NO" nullValue="" column="TRAN_REF_NO" javaType="java.lang.String" jdbcType="VARCHAR2"/> |
...
<result property="DEBIT_ACC_NO" nullValue="" column="DEBIT_ACC_NO" javaType="java.lang.String" jdbcType="VARCHAR2"/> |
...
<result property="BENE_ACC_NO" nullValue="" column="BENE_ACC_NO" javaType="java.lang.String" jdbcType="VARCHAR2"/> |
...
<result property="TOTAL_COUNT" nullValue="" column="TOTAL_COUNT" javaType="java.lang.Integer" jdbcType="VARCHAR"/> |
...
</resultMap> |
- It is advised that the Pagination section is always added to ensure that whenever the App is configured pointing to this query model, it automatically gets activated. If the query model has nothing to do with an App, then these SQL template fragments are not necessary.
| Info |
|---|
TOTAL_COUNT is the field that is expected in the first row of the result Map that is provided back to the invoker as part of the App rendering logic. This is critical for the UI components to get the actual number of records as any paging calculation is based on this count. |
| Info |
|---|
There is a small difference between the TOTALROWS_FOR_PAGINATION and TOTALCOUNT_FOR_PAGINATION. The difference is in the column name under which the record count is returned. TOTALCOUNT_FOR_PAGINATION returns it under the column TOTAL_COUNT while TOTALROWS_FOR_PAGINATION returns it under the column TOTAL_ROWS. Accordingly you have to adjust the result map for this column. |
Now that we have these basics clear, let us look at how to use the framework for simple CRUD operations, advanced batch operations, Stored Procedure based invocations.