JDBC CallableStatement – PostgreSQL存储功能

最新推荐文章于 2022-10-09 11:51:28 发布

cyan20115

最新推荐文章于 2022-10-09 11:51:28 发布

阅读量581

点赞数

文章标签：数据库 java git

本文提供了一个JDBC示例，详细解释了如何从PostgreSQL数据库中调用存储的函数，包括返回SETOF和游标的处理，并提供了源代码下载链接。

摘要由CSDN通过智能技术生成

JDBC CallableStatement示例，向您展示如何从PostgreSQL数据库中调用存储的函数。

PS已通过PostgreSQL 11和Java 8测试

pom.xml

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

1.通话功能

1.1创建一个存储的函数并通过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();
        }

    }
}

输出量

hello mkyong

1.2 SQL版本。

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.函数返回SETOF

2.1对于将数据作为SETOF返回的函数，我们应该使用普通的Statement或PreparedStatement ，而不是CallableStatement

PS表pg_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();
        }

    }
}

输出量

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版本。

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

-- run it 
select * from getRoles();

3.函数返回游标

3.1 JDBC +引用光标示例。

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();
        }

    }
}

输出–该数据库包含一个用于测试的用户🙂

postgres
********

3.2 SQL版本。

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