Query Construction Logic

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 
EMP_ID, EMP_NAME, EMP_AGE
FROM
EMPLOYEES

If you require dynamic filters and sorts, the query must be changed –as follows:

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 -

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 -

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:

• Forces application to fire two queries for every data fetch
• The query construct is not optimized for huge data sets.
• Usage of Oracle proprietary ROWNUM which will not work with other Databases

Approach 2: Get the row number as part of a single query

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:

• Query construct is complex
• The query construct is not optimized for huge data sets and can create performance issues.

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:

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:

<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:

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

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

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.