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
$ git clone https://github.com/mkyong/java-jdbc.git
References
About Author
Comments
Subscribe
0 Comments