How to fetch the data In JAVA from refcursor resultset in postgres stored procedure

115 Views Asked by At

I am using a Postgres database. And I have one stored procedure which returns a refcursor as below

INOUT p_refcur refcursor DEFAULT NULL::refcursor

I am calling the SP in pgAdmin tool as below and getting proper result :

BEGIN;
CALL my_spg('some_val', 'another_val', 'refcursorresult');
FETCH ALL IN "refcursorresult";
COMMIT;

Now I am trying to call the above SP from my Java code and trying to obtain the result from the refcursor , but I am not able to fetch. I looked around many forums but I did not find any single way on how to obtain the value from the refcursor in postgres.

My sample minimum relevant java code :

public List<ResponseDTO> callMySP(String someVal, String anotherVal) {      
    
    Connection conn = DatabaseConnection.connect();
    CallableStatement cstmt = null;     
    List<ResponseDTO> respList= new ArrayList<>();
    
    try {           
        if(conn != null) {
            conn.setAutoCommit(false);
        }           
        
        cstmt = conn.prepareCall("call public.my_sp (?,?,'refcursorresult');");
        cstmt.setString(1, someVal);
        cstmt.setString(2, anotherVal);         
        cstmt.registerOutParameter(1, Types.OTHER);
        
        Boolean result = cstmt.execute();
        
        if(result) {
            ResultSet rs = (ResultSet) cstmt.getObject(1);
            if(null != rs) {    
               
                // Here I am confused on how to obtain the refcursor value from RS.             
                while(rs.next()) {
                    
                       // my logic here
                       respList.add();                      
                }
            }
        } else {
            LOGGER.info("Result Set is null");
        }
        
        conn.commit();          

    } catch (SQLException sqlEx) {          
        LOGGER.error("", sqlEx);            
    } catch (Exception e) {
        LOGGER.error("", e);            
    } finally {
        try {
            cstmt.close();
            conn.close();
        } catch (SQLException e) {
            LOGGER.error("Error in connection close.", e);
        }
    }

    return respList;
}

Can some one please help me out. I am stuck here.

0

There are 0 best solutions below