Correct way of calling reference cursors in PostgreSQL 11 and above using JDBC driver

493 Views Asked by At

I need some suggestion on how to get the data through PostgreSQL JDBC driver from stored procedures using reference cursors.

Since PostgreSQL 11, it supports stored procedures with create procedure command instead of create function.

I have a scenario in which I want to fetch data from a stored procedure using a reference cursor.

My stored procedure SQL looks like as shown below

CREATE OR REPLACE PROCEDURE public.pr_sampleuser(
    p_firstuser character varying,
    INOUT p_qusers refcursor)
LANGUAGE 'plpgsql'
AS $BODY$

BEGIN
   OPEN p_qusers FOR
   SELECT first_name,last_name,address
   FROM public.test_user
   WHERE UPPER(first_name) = UPPER(p_firstuser);
END; 
$BODY$;

When we want to fetch the data using the JDBC driver, the first thing we need to add to the connection string is escapeSyntaxCallMode=call.

Following is the code-snippet that I am using to fetch the data,

try {
    Properties props = new Properties();
    props.setProperty("escapeSyntaxCallMode", "call");
    Connection conn = DriverManager.getConnection(url,props);
    String storedProc = "{call public.pr_sampleuser(?,?)}";
    CallableStatement cs = conn.prepareCall(storedProc);
    cs.setString(1,"Umesh");
    cs.setObject(2,null);
    cs.registerOutParameter(2,Types.REF_CURSOR);
    conn.setAutoCommit(false);

    // run StoredProcedure
    cs.execute();

    // get refcursor and convert it to ResultSet
    ResultSet resultSet = (ResultSet) cs.getObject(2);
    while (resultSet.next()) {
        String firstName = resultSet.getString("first_name");
        String lastname = resultSet.getString("last_name");
        String address = resultSet.getString("address");

        System.out.println(firstName);
        System.out.println(lastname);
        System.out.println(address);
    }
} catch (SQLException e) {
    System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
    e.printStackTrace();
} catch (Exception e) {
    e.printStackTrace();
}

In this I am passing the second parameter as null using

cs.setObject(2,null);

I wanted to check if this is the correct way to fetch or if there is any better way to get the data.

0

There are 0 best solutions below