Main Tutorials

JDBC CallableStatement – PostgreSQL Stored Function

A JDBC CallableStatement example to show you how to call a stored function from PostgreSQL database.

P.S Tested with PostgreSQL 11 and Java 8

pom.xml

	<dependency>
		<groupId>org.postgresql</groupId>
		<artifactId>postgresql</artifactId>
		<version>42.2.5</version>
	</dependency>

1. Call Function

1.1 Create a stored function and calling it via JDBC.

FunctionReturnString.java

package com.mkyong.jdbc.callablestatement;

import java.sql.*;

public class FunctionReturnString {

    public static void main(String[] args) {

        String createFunction = "CREATE OR REPLACE FUNCTION hello(p1 TEXT) RETURNS TEXT "
                + " AS $$ "
                + " BEGIN "
                + " RETURN 'hello ' || p1; "
                + " END; "
                + " $$ "
                + " LANGUAGE plpgsql";

        String runFunction = "{ ? = call hello( ? ) }";

        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
             Statement statement = conn.createStatement();
             CallableStatement callableStatement = conn.prepareCall(runFunction)) {

            // create or replace stored function
            statement.execute(createFunction);

            //----------------------------------

            // output
            callableStatement.registerOutParameter(1, Types.VARCHAR);

            // input
            callableStatement.setString(2, "mkyong");

            // Run hello() function
            callableStatement.executeUpdate();

            // Get result
            String result = callableStatement.getString(1);

            System.out.println(result);

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

    }
}

Output


hello mkyong

1.2 SQL version.


CREATE OR REPLACE FUNCTION hello(p1 TEXT) RETURNS TEXT 
AS $$
BEGIN
    RETURN 'hello ' || p1;
END;
$$
LANGUAGE plpgsql;

-- run it 
select hello('mkyong');
-- output: hello mkyong

2. Function Returning SETOF

2.1 For Functions that return data as a SETOF, we should use the normal Statement or PreparedStatement, NOT CallableStatement

P.S The table pg_roles is a system table containing database roles

FunctionReturnResultSet.java

package com.mkyong.jdbc.callablestatement;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class FunctionReturnResultSet {

    public static void main(String[] args) {

        List<String> users = new ArrayList<>();

        String createFunction = "CREATE OR REPLACE FUNCTION getRoles() RETURNS SETOF pg_roles "
                + " AS 'select * from pg_roles' LANGUAGE sql;";

        String runFunction = "select * from getRoles();";

        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
             Statement statement = conn.createStatement()) {

            // create a function returns as SETOF
            statement.execute(createFunction);

            // run it
            ResultSet resultSet = statement.executeQuery(runFunction);
            while (resultSet.next()) {
                users.add(resultSet.getString("rolname"));
            }

            System.out.println("Database roles...");
            users.forEach(x -> System.out.println(x));

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

    }
}

Output


Database roles...
pg_signal_backend
pg_read_server_files
postgres
pg_write_server_files
pg_execute_server_program
pg_read_all_stats
pg_monitor
pg_read_all_settings
pg_stat_scan_tables

2.2 SQL version.


CREATE OR REPLACE FUNCTION getRoles() RETURNS SETOF pg_roles 
AS 'select * from pg_roles' LANGUAGE sql;

-- run it 
select * from getRoles();

3. Function Returning Cursor

3.1 JDBC + Ref Cursor example.

FunctionReturnRefCursor.java

package com.mkyong.jdbc.callablestatement;

import java.sql.*;

public class FunctionReturnRefCursor {

    public static void main(String[] args) {

        String createFunction = "CREATE OR REPLACE FUNCTION getUsers(mycurs OUT refcursor) "
                + " RETURNS refcursor "
                + " AS $$ "
                + " BEGIN "
                + "     OPEN mycurs FOR select * from pg_user; "
                + " END; "
                + " $$ "
                + " LANGUAGE plpgsql";

        String runFunction = "{? = call getUsers()}";

        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
             Statement statement = conn.createStatement();
             CallableStatement cs = conn.prepareCall(runFunction);
        ) {

            // We must be inside a transaction for cursors to work.
            conn.setAutoCommit(false);

            // create function
            statement.execute(createFunction);

            // register output
            cs.registerOutParameter(1, Types.REF_CURSOR);

            // run function
            cs.execute();

            // get refcursor and convert it to ResultSet
            ResultSet resultSet = (ResultSet) cs.getObject(1);
            while (resultSet.next()) {
                System.out.println(resultSet.getString("usename"));
                System.out.println(resultSet.getString("passwd"));
            }

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

    }
}

Output – This database contains a single user for testing 🙂


postgres
********

3.2 SQL version.


CREATE OR REPLACE FUNCTION getUsers(mycurs OUT refcursor) RETURNS refcursor 
	AS $$
	BEGIN 
		OPEN mycurs FOR select * from pg_user;
	END;
	$$
	LANGUAGE plpgsql;

Download Source Code

References

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments