Recently, I am working on spring boot project. This is basically a backend project. I have repository class in where I have used a complex query that take header id and give single row output.
Repository interface:
package com.springboot.project.leavemonitoringapp.repository;
import com.springboot.project.leavemonitoringapp.model.LeaveApproval;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
public interface LeaveApprovalRepository extends JpaRepository<LeaveApproval, Long> {
@Query(value = "SELECT ROWNUM SRL_NUM,n" +
" REPORT_HEADER_ID,n" +
" NTF_RESPONDER,n" +
" CASEn" +
" WHEN APPROVER_NAME IS NULL THEN NTF_RESPONDERn" +
" ELSE APPROVER_NAMEn" +
" ENDn" +
" APPROVER_NAME,n" +
" APPROVER_ACTION,n" +
" NTF_NOTE NOTE,n" +
" ACTION_DATEn" +
" FROM --n" +
" ( --n" +
" SELECT SRL_NUM,n" +
" REPORT_HEADER_ID,n" +
" ACTION_DATE,n" +
" NTF_RESPONDER,n" +
" APPROVER_NAME,n" +
" APPROVER_ACTION,n" +
" NTF_NOTEn" +
" FROM --n" +
" ( SELECT X.SRL_NUM SRL_NUM,n" +
" X.EXPENSE_REPORT_HDR_ID REPORT_HEADER_ID,n" +
" X.NTF_RESPONDER,n" +
" CASEn" +
" WHEN PPF.FULL_NAME IS NULLn" +
" THENn" +
" (SELECT DISPLAY_NAMEn" +
" FROM APPS.WF_ROLESn" +
" WHERE NAME = X.NTF_RESPONDER)n" +
" ELSEn" +
" PPF.FULL_NAMEn" +
" ENDn" +
" AS APPROVER_NAME,n" +
" INITCAP (X.NTF_ACTION) AS APPROVER_ACTION,n" +
" REPLACE (REPLACE (X.NTF_NOTE, CHR (13), ' '),n" +
" CHR (10),n" +
" '')n" +
" AS NTF_NOTE,n" +
" TO_CHAR (X.CREATION_DATE, 'DD-MON-RR HH24:MI')n" +
" AS ACTION_DATEn" +
" FROM --xxssgil_er_approval_hist xn" +
" XXSSGIL.XXSSGIL_APPROVAL_HISTORYS_VV X,n" +
" APPS.PER_ALL_PEOPLE_F PPFn" +
" WHERE X.EMPLOYEE_ID = PPF.PERSON_ID(+)n" +
" AND TRUNC (X.CREATION_DATE) BETWEEN PPF.EFFECTIVE_START_DATE(+)n" +
" AND PPF.EFFECTIVE_END_DATE(+)n" +
" AND X.EXPENSE_REPORT_HDR_ID = :L_INVOICE_IDn" +
" ORDER BY X.SRL_NUM)n" +
" UNION ALLn" +
" (SELECT SRL_NUM,n" +
" REPORT_HEADER_ID,n" +
" ACTION_DATE,n" +
" NTF_RESPONDER,n" +
" APPROVER_NAME,n" +
" APPROVER_ACTION,n" +
" NTF_NOTEn" +
" FROM (SELECT 0 AS SRL_NUM,n" +
" H.REPORT_HEADER_ID,n" +
" WUR.USER_NAME AS NTF_RESPONDER,n" +
" (SELECT PAPF.FULL_NAMEn" +
" FROM APPS.PER_ALL_PEOPLE_F PAPF,n" +
" APPS.PER_ALL_ASSIGNMENTS_F PAAFn" +
" WHERE 1 = 1n" +
" AND PAPF.BUSINESS_GROUP_ID = '84'n" +
" AND PAPF.EMPLOYEE_NUMBER = WUR.USER_NAME --'3023'n" +
" AND PAPF.PERSON_ID = PAAF.PERSON_IDn" +
" AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATEn" +
" AND PAPF.EFFECTIVE_END_DATEn" +
" AND TRUNC (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATEn" +
" AND PAAF.EFFECTIVE_END_DATE)n" +
" AS APPROVER_NAME,n" +
" 'Pending' AS APPROVER_ACTION,n" +
" '' AS NTF_NOTE,n" +
" '' AS ACTION_DATEn" +
" FROM APPS.XXSSGIL_APPROVAL_LV_HDR H,n" +
" APPS.XXSSGIL_APPROVAL_LV_LINE L,n" +
" APPS.WF_USER_ROLES WUR,n" +
" APPS.WF_NOTIFICATIONS Nn" +
" WHERE 1 = 1n" +
" AND H.REPORT_HEADER_ID = :L_INVOICE_IDn" +
" AND WUR.ROLE_NAME = N.TO_USERn" +
" AND H.WF_ITEM_KEY = N.ITEM_KEYn" +
" AND L.REPORT_HEADER_ID = H.REPORT_HEADER_IDn" +
" AND N.STATUS = 'OPEN'n" +
" AND H.STATUS_FLG = 'In-Process')))n" +
" order by SRL_NUM descn" +
" FETCH FIRST 1 ROW ONLY",nativeQuery = true)
List<LeaveApproval> findLeaveApprovalRecord(@Param("L_INVOICE_ID") String headerId); //here L_INVOICE_ID is replace by report header id
}
Service class:
@Service
public class AttendanceLeaveService {
@Autowired
private AttendanceLeaveRepository attendanceLeaveRepository;
@Autowired
private LeaveApprovalRepository leaveApprovalRepository;
@Autowired
private LeaveIssueRepository leaveIssueRepository;
@Autowired
private PFLoanRepository pfLoanRepository;
public List<AttendanceLeave> getAttendanceLeaveRecord(String status){
return attendanceLeaveRepository.findAttendanceLeaveRecord("");
}
public List<LeaveApproval> getLeaveApprovalRecord(String headerId){
return leaveApprovalRepository.findLeaveApprovalRecord(headerId);
}
public PFLoan getPFInfo(String empId){
return pfLoanRepository.findPFInfo("",empId);
}
public Map<String,List<LeaveApproval>> getLeaveIssue(){
List<String> headerIdList = List.of("17524","17527","17529");
Map<String,List<LeaveApproval>> unsolvedLeaves = new HashMap<>();
for (String headerId : headerIdList){
List<LeaveApproval> leaveApproval = getLeaveApprovalRecord(headerId);
unsolvedLeaves.put(headerId,leaveApproval);
}
return unsolvedLeaves;
}
Here, there is getLeaveIssue() in where there is a list of header id and a for loop that take a header id and pass it by below line
List<LeaveApproval> leaveApproval = getLeaveApprovalRecord(headerId);
This function is ok for each header id with passing single value by a list(List.of(“17524”)). But it not working properly with passing multiple values(List.of(“17524″,”17527″,”17529”)). To find out I have debug a lot and found that inaccurate output.
like below
{
"17524": [
{
"serialNumber": 3,
"headerId": "17524",
"responderName": "4939",
"approverName": "Mr. ABDUS SALAM (GM-Distribution)",
"approverAction": "Approved",
"note": null,
"actionDate": "24-APR-24 11:29"
}
],
"17527": [
{
"serialNumber": 2,
"headerId": "17527",
"responderName": "5284",
"approverName": "Mr. MD. MOTIUR RAHMAN (COO-Operations)",
"approverAction": "Approved",
"note": null,
"actionDate": "24-APR-24 15:58"
}
],
"17529": [
{
"serialNumber": 3,
"headerId": "17524",
"responderName": "4939",
"approverName": "Mr. ABDUS SALAM (GM-Distribution)",
"approverAction": "Approved",
"note": null,
"actionDate": "24-APR-24 11:29"
}
]
}
Here we can see the value with 1st header id is repeated for last output.
Why it happen?
How to fix?