CODEWITHSUNDEEP
javasql-serverservicedaodto
Sonny
Sonny

Reputation: 321

Cannot get my Query to execute in my DAO. Always returns null

I had a small issue updating a query to link with my DAO.

Here is my Query:

String PROJECT_ID_QUERY_BY_RAB_WWID = "SELECT PR.ID FROM PR "
       + "LEFT OUTER JOIN PROJECT ON PR.PROJECT_ID = PROJECT.ID "
       + "LEFT OUTER JOIN TW_V_WWID ON PR.ID = TW_V_WWID.PR_ID "
       + "LEFT OUTER JOIN PR_STATUS_TYPE ON PR_STATUS_TYPE.ID = PR.STATUS_TYPE "
       + "LEFT OUTER JOIN TW_V_SITE_NAME_S ON PR.ID = TW_V_SITE_NAME_S.PR_ID "
       + "WHERE PROJECT.NAME = 'Site Lead' " 
       + "and TW_V_WWID.S_VALUE = (SELECT PERSON_RELATION.S_EXTERNAL_ID FROM TW_V_ROLES_APPROVED_BY " 
       + "left outer join person_relation on tw_v_roles_approved_by.id = person_relation.id "
       + "WHERE TW_V_ROLES_APPROVED_BY.PR_ID IN ?) and "
       + "(TW_V_SITE_NAME_S.S_VALUE IN (SELECT TW_V_SITE_NAME.S_VALUE FROM TW_V_SITE_NAME "
       + "left outer join tw_v_site_name_s on tw_v_site_name_s.pr_id = tw_v_site_name.pr_id "
       + "where tw_v_site_name.pr_id in ?) or tw_v_site_name_s.s_value = 'ALL SITES') "
       + "and PR_STATUS_TYPE.NAME = 'Approved Site Lead Permissions'";

As you can see there are two ?. These two ? are actually going to be the same.

This is my DAO class to call this Query:

public Integer getProjectID(Integer UA_PrID)
            throws SQLException, IOException {

        Integer prId = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(QueryConstants.PROJECT_ID_QUERY_BY_RAB_WWID);
            ps.setLong(1, UA_PrID);
            ps.setLong(2, UA_PrID);
            rs = ps.executeQuery();
            while (rs.next()) {
                prId = rs.getInt("ID");
            }
        } finally {
            DaoUtil.cleanUp( ps, rs);
        }
        return prId;
    }

As you can see my PS statements are the same.

I make this call in my Service class to the DAO method:

Integer prId = userAccessVerificationDAO.getProjectID(wsdlDtoList.get(0).getPrId());

where wsdlDtoList.get(0).getPrId() is an integet value in my DTO class.

Everytime I try and debug this code I get to the DAO file and step over and I notice it skips this line everytime in the DAO:

rs = ps.executeQuery();

I know I am missing something small here. A second set of eyes on this would be great. Please let me know if more information is required in order to solve this minor issue. I thank you all in advance for you input and help.

Here is the larger portion of the service class:

Integer prId = userAccessVerificationDAO.getProjectID(wsdlDtoList.get(0).getPrId());

        if (prId == null) {
            verificationResponse = new VerificationResponseDto();
            verificationResponse.setErrorType(ErrorConstants.VERIFICATION_ERROR_TYPE);
            verificationResponse.setErrorCode(ErrorConstants.FAILURE_ERROR_CODE);
            verificationResponse.setErrorMessage(ErrorConstants.NO_PR_ID_ERROR);
            logger.error(ErrorConstants.NO_PR_ID_ERROR);
            verificationResponseDtoList.add(verificationResponse);
            return verificationResponseDtoList;

Basically it returns null everytime. I manually ran this query in sqldeveloper and it does return a valid prID.

Thanks

Upvotes: 1

Views: 673

Answers (1)

Utkarsh
Utkarsh

Reputation: 589

The reason behind the executeQuery getting skipped is because of exception occurring in previous line of code.

In your method, conn object is not initialized(this may be the issue). Also, add a catch block to log the error.

Upvotes: 1

Related Questions