Postgresql хранимые процедуры java

PL/Java: stored procedures, triggers, and functions for PostgreSQL™

PL/Java is a free open-source extension for PostgreSQL™ that allows stored procedures, triggers, and functions to be written in the Java™ language and executed in the backend. More about the features and benefits of PL/Java can be read on the wiki.

About this site

This site includes reference information on PL/Java, covering how to build it, install it, and use it. There is also a wiki with more information and examples, though in some cases dated. While information from the wiki is gradually being migrated to this site and brought up to date, you should still check the wiki for information you do not find here.

The following sections offer very brief summaries.

Use of PL/Java, in a nutshell

Backend functions and triggers are written in Java using a directly-connected, efficient version of the standard Java JDBC API that PL/Java transparently provides, with enhanced capabilities found in the PL/Java API.

PL/Java source files can use Java annotations from the org.postgresql.pljava.annotation package to identify the methods and types that should be seen in PostgreSQL, as in this example code. For a step-by-step example, there is always Hello, world.

When the sources are compiled, the Java compiler will also write an SQLJ deployment descriptor containing the SQL statments that must be executed when installing and uninstalling the compiled Java code in the PostgreSQL backend.

When the compiled Java code and the deployment descriptor file are stored together in a JAR file, PL/Java’s install_jar function will both load the code into PostgreSQL and execute the necessary SQL commands in the deployment descriptor, making the new types/functions/triggers available for use.

Installation, in a nutshell

PL/Java can be downloaded, then built using Maven. The build produces a native code library (file with name ending in .so, .dll, etc., depending on the plaform) and a JAR file. PostgreSQL must be configured to know where these are, in addition to the native library for the Java runtime itself. The installation guide has details.

Installation from a prebuilt package

There may be a prebuilt distribution available for your platform. You can check for that on the wiki prebuilt packages page, which can be kept up to date with known available packages.

Moving PL/Java forward

The Contribution Guide describes how to contribute to PL/Java’s development. While only the PL/Java API module must be understood to use PL/Java, contributing may require getting familiar with PL/Java’s other modules. Most have JavaDoc available, which will be found under each module’s Project Reports menu.

Источник

Calling Stored Functions and Procedures

PostgreSQL® supports two types of stored objects, functions that can return a result value and — starting from v11 — procedures that can perform transaction control. Both types of stored objects are invoked using CallableStatement and the standard JDBC escape call syntax . The escapeSyntaxCallMode connection property controls how the driver transforms the call syntax to invoke functions or procedures.

The default mode, select , supports backwards compatibility for existing applications and supports function invocation only. This is required to invoke a function returning void.

For new applications, use escapeSyntaxCallMode=callIfNoReturn to map CallableStatements with return values to stored functions and CallableStatements without return values to stored procedures.

Example 6.1. Calling a built-in stored function

This example shows how to call the PostgreSQL® built-in function, upper , which simply converts the supplied string argument to uppercase.

CallableStatement upperFunc = conn.prepareCall(""); upperFunc.registerOutParameter(1, Types.VARCHAR); upperFunc.setString(2, "lowercase to uppercase"); upperFunc.execute(); String upperCased = upperFunc.getString(1); upperFunc.close(); 

Obtaining a ResultSet from a stored function

PostgreSQL’s™ stored functions can return results in two different ways. The function may return either a refcursor value or a SETOF some datatype. Depending on which of these return methods are used determines how the function should be called.

From a Function Returning SETOF type

Functions that return data as a set should not be called via the CallableStatement interface, but instead should use the normal Statement or PreparedStatement interfaces.

Example 6.2. Getting SETOF type values from a function

Statement stmt = conn.createStatement(); stmt.execute("CREATE OR REPLACE FUNCTION setoffunc() RETURNS SETOF int AS " +  "' SELECT 1 UNION SELECT 2;' LANGUAGE sql"); ResultSet rs = stmt.executeQuery("SELECT * FROM setoffunc()"); while (rs.next())   // do something  > rs.close(); stmt.close(); 

From a Function Returning a refcursor

When calling a function that returns a refcursor you must cast the return type of getObject to a ResultSet`

NOTE

One notable limitation of the current support for a ResultSet created from a refcursor is that even though it is a cursor backed ResultSet , all data will be retrieved and cached on the client. The Statement fetch size parameter described in the section called Getting results based on a cursor is ignored. This limitation is a deficiency of the JDBC driver, not the server, and it is technically possible to remove it, we just haven’t found the time.

Example 6.3. Getting refcursor Value From a Function

// Setup function to call.  Statement stmt = conn.createStatement(); stmt.execute("CREATE OR REPLACE FUNCTION refcursorfunc() RETURNS refcursor AS '" +  " DECLARE " +  " mycurs refcursor; " +  " BEGIN " +  " OPEN mycurs FOR SELECT 1 UNION SELECT 2; " +  " RETURN mycurs; " +  " END;' language plpgsql"); stmt.close();  // We must be inside a transaction for cursors to work.  conn.setAutoCommit(false);  // Function call.  CallableStatement func = conn.prepareCall(""); func.registerOutParameter(1, Types.OTHER); func.execute(); ResultSet results = (ResultSet) func.getObject(1); while (results.next())   // do something with the results.  > results.close(); func.close(); 

It is also possible to treat the refcursor return value as a cursor name directly. To do this, use the getString of ResultSet . With the underlying cursor name, you are free to directly use cursor commands on it, such as FETCH and MOVE .

Example 6.4. Treating refcursor as a cursor name

conn.setAutoCommit(false); CallableStatement func = conn.prepareCall(""); func.registerOutParameter(1, Types.OTHER); func.execute(); String cursorName = func.getString(1); func.close(); 

Example 6.5. Calling a stored procedure

This example shows how to call a PostgreSQL® procedure that uses transaction control.

// set up a connection  String url = "jdbc:postgresql://localhost/test"; Properties props = new Properties(); . other properties.  // Ensure EscapeSyntaxCallmode property set to support procedures if no return value  props.setProperty("escapeSyntaxCallMode", "callIfNoReturn"); Connection con = DriverManager.getConnection(url, props);  // Setup procedure to call.  Statement stmt = con.createStatement(); stmt.execute("CREATE TEMP TABLE temp_val ( some_val bigint )"); stmt.execute("CREATE OR REPLACE PROCEDURE commitproc(a INOUT bigint) AS '" +  " BEGIN " +  " INSERT INTO temp_val values(a); " +  " COMMIT; " +  " END;' LANGUAGE plpgsql"); stmt.close();  // As of v11, we must be outside a transaction for procedures with transactions to work.  con.setAutoCommit(true);  // Procedure call with transaction  CallableStatement proc = con.prepareCall(""); proc.setInt(1, 100); proc.execute(); proc.close(); 

Источник

Writing PostgreSQL functions with Java using PL/Java

The PostgreSQL RDBMS offers a great variety for writing server-side functions and with PostgreSQL 11 now also stored procedures. PostgreSQL comes with a language named PL/pqSQL which enhances plain SQL with control flow predicates (similar to Oracle’s PL/SQL). Besides this SQL-like language, PostgreSQL also offers built-in support for using Perl 5, Python and TCL as possible languages to implement functions and stored procedures. As a Java developer, I was always curious if there is a way to use Java within PostgreSQL. While searching for a solution, I found PL/Java which is an open-source extension for PostgreSQL that allows stored procedures, triggers and functions to be written in Java.

In this blog, I’ll show you how to install the third-party extension and how to write and deploy Java code as a PostgreSQL function. I’ll be using PL/Java with version 1.5.2, OpenJDK 8, Debian 9, PostgreSQL 9.6 and Maven 3.

Installing PL/Java via apt-get (for Debian &Ubtunu)

There are pre-built packages for Debian and Ubuntu once you enable the PostgreSQL Debian repository (follow the Quickstart section in the provided link). If you are running on Debian or Ubuntu and have the PostgreSQL repository enabled, you can simply install the PL/Java extension alongside the PostgreSQL server and skip the following example to build the extension manually:

Источник