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

Question

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.

Answer 1

Connection conn = DatabaseConnection.connect();
conn.setAutoCommit(false);
...
<code>
...
connection.commit();

example:

private void useCallableStatement() {
    Connection connection = null;
    try {
        connection = dataSourcePostgrest.getConnection();
        connection.setAutoCommit(false);
        CallableStatement prepareCall = connection.prepareCall("{call get_employee_by_id(?,?)}");

        prepareCall.setInt(1, 15);
        prepareCall.registerOutParameter(2, Types.OTHER);
        prepareCall.execute();

        ResultSet rs = (ResultSet) prepareCall.getObject(2);

        while (rs.next()) {
            System.out.println(rs.getString("emp_id") + " " + rs.getString("emp_name"));
        }

        connection.commit();
    } catch (Exception e) {
        if (connection != null) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        e.printStackTrace();
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}