title: Spring batch JdbcPaging Item Reader with multiple table joins
body: <p>Need to implement paginating for an spring batch application. The Query has multiple join, Guide for an effective Pagining query, with existing table model can this join be effectively written to pagination where "ED_EMP_LOGIN_STATE" is the filter where other tables are select. running this query is still slow and look likes paginzation with rownnum condition does not show any impact on execution plan.</p>
  
  <p>How this query can be modified to pass in spring batch paginzation reader</p>
  
  <pre><code>&lt;bean id="empItemReader" class="org.springframework.batch.item.database.JdbcPagingItemReader" scope="step"&gt;
      &lt;property name="dataSource" ref="empDataSource" /&gt;
      &lt;property name="rowMapper"&gt;
          &lt;bean class="org.sample.model.EmployeeMapper" /&gt;
      &lt;/property&gt;
      &lt;property name="queryProvider"&gt;
          &lt;bean class="org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean"&gt;
              &lt;property name="dataSource" ref="empDataSource" /&gt;
              &lt;property name="sortKeys"&gt;
                  &lt;map&gt;
                      &lt;entry key="ID" value="ASCENDING"/&gt;
                  &lt;/map&gt;
              &lt;/property&gt; 
              &lt;!-- Intentionally put sort key second in the query list as a test --&gt;
              &lt;property name="selectClause" &gt;
                  &lt;value&gt;
                      &lt;![CDATA[
                              emp_task_sub_details.DEP_NAME AS DEP_NAME 
                              ,emp_task_sub_details.EmpName AS EmpName 
                              ,emp_task_sub_details.EmpFirstName AS EmpFirstName
                              ,emp_task_sub_details.EmpIDCard AS EmpIDCard
                              ,emp_task_sub_details.EmpBuilding AS EmpBuilding
                              ,emp_task_sub_details.EmpLocation EmpLocation ENV_CAT
                              ,emp_task_sub_details.NAME AS NAME
                              ,ed_parent_join.task_name AS TASK_NAME
                              ,ed_parent_join.start_time AS TASK_NAME_START_TIME
                              ,ed_parent_join.finish_time AS TASK_NAME_END_TIME
                              ,emp_task_sub_details.STATUS AS STATUS
                              ,emp_task_sub_details.STATE_ID AS ID
                      ]]&gt;
                  &lt;/value&gt;
              &lt;/property&gt;
              &lt;property name="fromClause"&gt;
                  &lt;value&gt;
                      &lt;![CDATA[   
                          ED_EMP_LOGIN_STATE ed_parent_join
                          JOIN (
                                  select
                                  ed_dep.name as DEP_NAME
                                  ,empName_code_prop.string as EmpName
                                  ,empfirst_code_prop.string as EmpFirstName
                                  ,id_code_prop.string as EmpIDCard
                                  ,build_code_prop.string as EmpBuilding
                                  ,loc_code_prop.string as EmpLocation
                                  ,ed_emp.name as NAME
                                  ,ed_emp_lg_st.id as STATE_ID
                                  ,ed_emp_tas_wk.STATUS AS STATUS
                                  from
                                  ED_DEPARTMENT ed_dep
                                  ,ED_EMPLOYEE ed_emp
                                  ,ed_emp_tas_wkK ed_emp_tsk
                                  ,ED_EMP_LOGIN ed_emp_lg 
                                  ,ED_EMP_LOGIN_STATE ed_emp_lg_st 
                                  ,ED_EMP_LOGIN_WORK ed_emp_tas_wk
                                  ,ED_TASK_PROPS empName_code_prop
                                  ,ED_TASK_PROPS empfirst_code_prop
                                  ,ED_TASK_PROPS id_code_prop
                                  ,ED_TASK_PROPS build_code_prop
                                  ,ED_TASK_PROPS loc_code_prop
                                  ,ED_ACTIVITY ed_act
                                  ,ED_LOCATION ed_loc
                                  where
                                  ed_emp.id = ed_dep.id
                                  and ed_emp.tsk_id = ed_emp_tsk.id
                                  and ed_emp_lg.tsk_id = ed_emp_tsk.id
                                  and ed_emp_lg_st.parent_emp_id = ed_emp_lg.id
                                  and ed_emp_tas_wk.state_id = ed_emp_lg_st.id
                                  and ed_emp_lg.prop_id = empName_code_prop.parent_prop_id
                                  and empName_code_prop.name = 'EmpName'
                                  and ed_emp_lg.prop_id = empfirst_code_prop.parent_prop_id
                                  and empfirst_code_prop.name = 'EmpFirstName'
                                  and ed_emp_lg.prop_id = id_code_prop.parent_prop_id
                                  and id_code_prop.name = 'EmpIDCard'
                                  and ed_emp_lg.prop_id = build_code_prop.parent_prop_id
                                  and build_code_prop.name = 'EmpBuilding'
                                  and ed_emp_tas_wk.JOB_ID = ed_act.id
                                  and ed_act.environment_name = ed_loc.name 
                                  and ed_loc.prop_id = loc_code_prop.parent_prop_id
                                  and loc_code_prop.name = 'EmpLocation')  emp_task_sub_details 
                                  ON emp_task_sub_details.STATE_ID = ed_parent_join.id        
                      ]]&gt;
                  &lt;/value&gt;
              &lt;/property&gt;
              &lt;property name="whereClause"&gt;
                  &lt;value&gt;
                      &lt;![CDATA[   
                          ed_parent_join.start_time &gt;= :startTime
                          and ed_parent_join.finish_time &lt; :finishTime        
                      ]]&gt;
                  &lt;/value&gt;
              &lt;/property&gt;
          &lt;/bean&gt;
      &lt;/property&gt;
      &lt;property name="pageSize" value="${loadPageCommitSize}" /&gt;
      &lt;property name="parameterValues"&gt;
          &lt;map&gt;
              &lt;entry key="startTime" value="#{jobParameters[StartTime]}" /&gt;
              &lt;entry key="finishTime" value="#{jobParameters[FinishTime]}" /&gt;
          &lt;/map&gt;
      &lt;/property&gt;
  &lt;/bean&gt;
  </code></pre>