【译】PostgreSQL PL/Java – 实操（一）

作者: 码农大表哥3306 | 来源:发表于2022-04-12 19:58 被阅读0次

前言

此博客翻译自 percona 官网博客《PostgreSQL PL/Java – A How-To, Part 1》

最近，我们收到了一些关于 PL/Java 的问题，我发现很难在互联网上搜索到明确的说明。并不是说网上没有优质的信息，但其中大部分信息要么不完整，要么过时，要么令人困惑，我决定创建这个简短的“how-to”并展示如何安装 PL/Java ，以及如何通过几个示例运行 PL/Java。

安装

我将在这里展示如何从源代码安装 PL/Java，因为我的平台没有编译的二进制文件，如果你的平台有来自包管理器的二进制文件，你可以从那里安装，例如使用 YUM 或 APT。另外，请注意，为简单起见，我使用的 PL/Java 没有指定特定的可信用户，而是使用了 Postgres 数据库超级用户。我建议阅读有关用户和权限的文档，在这[1]。

我使用的软件版本如下：

PostgreSQL 12.7
PL/Java 1.6.2
OpenJDK 11
Apache Maven 3.6.3

我从“https://github.com/tada/pljava/releases”下载了源码，然后解包并用 maven 编译：

wget https://github.com/tada/pljava/archive/refs/tags/V1_6_2.tar.gz

tar -xf V1_6_2.tar.gz

cd pljava-1_6_2

mvn clean install

java -jar pljava-packaging/target/pljava-pg12.jar

我在这里假设你已经足够了解 maven 并且会使用 mvn 命令。 java -jar pljava-packaging/target/pljava-pg12.jar 会将所需的文件和包复制/安装到 Postgres 文件夹中。请注意，maven 使用了我的 Postgres 版本并使用该版本创建了 jar 文件：“pljava-pg12.jar”，因此请注意您已经拥有的版本，因为如果您有不同的 Postgres 版本，jar 文件将会不一样！

我现在可以将扩展安装到我将使用的数据库中。我在这个博客里使用的数据库叫 “demo”：

$ psql demo
psql (12.7)
Type "help" for help.

demo=# CREATE EXTENSION pljava;
WARNING: Java virtual machine not yet loaded
DETAIL: libjvm: cannot open shared object file: No such file or directory
HINT: SET pljava.libjvm_location TO the correct path to the jvm library (libjvm.so or jvm.dll, etc.)
ERROR: cannot use PL/Java before successfully completing its setup
HINT: Check the log for messages closely preceding this one, detailing what step of setup failed and what will be needed, probably setting one of the "pljava." configuration variables, to complete the setup. If there is not enough help in the log, try again with different settings for "log_min_messages" or "log_error_verbosity".

不完全是我所期待的，但我得到了一个很好的提示：“HINT: SET pljava.libjvm_location TO the correct path to the jvm library (libjvm.so or jvm.dll, etc.)“。好的，我必须找到我的系统正在使用的 libjvm 来配置 Postgres。我使用 SET 命令在线执行此操作：

demo=# SET pljava.libjvm_location TO '/usr/lib/jvm/java-11-openjdk-11.0.11.0.9-5.fc34.x86_64/lib/server/libjvm.so';
NOTICE: PL/Java loaded
DETAIL: versions:
PL/Java native code (1.6.2)
PL/Java common code (1.6.2)
Built for (PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.1.1 20210531 (Red Hat 11.1.1-3), 64-bit)
Loaded in (PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.1.1 20210531 (Red Hat 11.1.1-3), 64-bit)
OpenJDK Runtime Environment (11.0.11+9)
OpenJDK 64-Bit Server VM (11.0.11+9, mixed mode, sharing)
NOTICE: PL/Java successfully started after adjusting settings
HINT: The settings that worked should be saved (using ALTER DATABASE demo SET ... FROM CURRENT or in the "/v01/data/db/pg12/postgresql.conf" file). For a reminder of what has been set, try: SELECT name, setting FROM pg_settings WHERE name LIKE 'pljava.%' AND source = 'session'
NOTICE: PL/Java load successful after failed CREATE EXTENSION
DETAIL: PL/Java is now installed, but not as an extension.
HINT: To correct that, either COMMIT or ROLLBACK, make sure the working settings are saved, exit this session, and in a new session, either: 1. if committed, run "CREATE EXTENSION pljava FROM unpackaged", or 2. if rolled back, simply "CREATE EXTENSION pljava" again.
SET

还可以使用ALTER SYSTEM使其在我的所有数据库中持久化，因为它将给定的参数设置写入“postgresql.auto.conf”文件，Postgres 除了读取 “postgresql.conf”之外还读取 “postgresql.auto.conf” 文件：

demo=# ALTER SYSTEM SET pljava.libjvm_location TO '/usr/lib/jvm/java-11-openjdk-11.0.11.0.9-5.fc34.x86_64/lib/server/libjvm.so';
ALTER SYSTEM

现在我们已经安装了 PL/Java，我们可以检查系统 catalog 看它是否确实存在：

demo=# SELECT * FROM pg_language WHERE lanname LIKE 'java%';
oid    | lanname | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
-------+---------+----------+---------+--------------+---------------+-----------+--------------+-------------------
16428  | java    | 10       | t       | t            | 16424         | 0         | 16427        | {charly=U/charly}
16429  | javau   | 10       | t       | f            | 16425         | 0         | 16426        |
(2 rows)

并测试它是否可用：

demo=# CREATE FUNCTION getProperty(VARCHAR)
RETURNS VARCHAR
AS 'java.lang.System.getProperty'
LANGUAGE java;
CREATE FUNCTION
demo=# SELECT getProperty('java.version');
getproperty
-------------
11.0.11
(1 row)

可以看到它是可用的！是时候尝试一些有用的东西了。

使用 PL/Java 访问数据库对象

我发现，大多数示例都是如何从 Java 类中创建“hello world”或如何计算斐波那契数列，但没有展示如何访问数据库对象。好吧，这些例子没有错，但我想认为，那些在数据库中安装 PL/Java 的人想从 Java 函数内部访问数据库对象，这就是我们在这里要做的。

在这篇文章中，我将使用可在此处[2] 找到的示例数据库 “pagila” 进行测试。

对于第一个示例，我将创建一个带有静态方法的简单类，该类将像任何 Postgres 函数一样在外部访问。该函数将接收一个整数参数并使用它来搜索表“customer”、列“customer_id”，并将打印客户的 ID、全名、电子邮件和地址：

package com.percona.blog.pljava;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Customers {
    private static String m_url = "jdbc:default:connection";

    public static String getCustomerInfo(Integer id) throws SQLException {
        Connection conn = DriverManager.getConnection(m_url);
        String query = "SELECT c.customer_id, c.last_name ||', '|| c.first_name as full_name, "
                + " c.email, a.address, ci.city, a.district "
                + " FROM customer c"
                + "  JOIN address a on a.address_id = c.address_id "
                + " JOIN city ci on ci.city_id = a.city_id "
                + " WHERE customer_id = ?";

        PreparedStatement stmt = conn.prepareStatement(query);
        stmt.setInt(1, id);
        ResultSet rs = stmt.executeQuery();
        rs.next();
        String ret; 
        ret = "- ID: " + rs.getString("customer_id") ;
        ret += "\n- Name: " + rs.getString("full_name");
        ret += "\n- Email: " + rs.getString("email");
        ret += "\n- Address: " + rs.getString("address");
        ret += "\n- City: " + rs.getString("city");
        ret += "\n- District: " + rs.getString("district");
        ret += "\n--------------------------------------------------------------------------------";

        stmt.close();
        conn.close();

        return (ret);
    }
}

我使用以下命令手动编译并创建了“jar”文件：

javac com/percona/blog/pljava/Customers.java
jar -c -f /app/pg12/lib/demo.jar com/percona/blog/pljava/Customers.class

请注意，我在“/app/pg12/lib”文件夹中创建了 jar 文件，记住这一点，因为我们将在下一步中使用这些信息，在 Postgres 中加载 jar 文件：

demo=# SELECT sqlj.install_jar( 'file:///app/pg12/lib/demo.jar', 'demo', true );
 install_jar 
-------------
(1 row)

demo=# SELECT sqlj.set_classpath( 'public', 'demo' );
 set_classpath 
---------------
(1 row)

install_jar 函数具有签名“install_jar(<jar_url>, <jar_name>, <deploy>)”，该函数将一个 jar 文件从 URL 指定的位置加载到 SQLJ jar 存储库中。如果给定名称的 jar 已存在于存储库中，或者该 jar 在 URL 中不存在或数据库无法读取该 jar，则会出现错误：

demo=# SELECT sqlj.install_jar( 'file:///app/pg12/lib/<strong>demo2.jar</strong>', 'demo', true );
<strong>ERROR:  java.sql.SQLException: I/O exception reading jar file: /app/pg12/lib/demo2.jar (No such file or directory)

demo=# SELECT sqlj.install_jar( 'file:///app/pg12/lib/demo.jar', 'demo', true );
 install_jar 
------------- 
(1 row)

demo=# SELECT sqlj.install_jar( 'file:///app/pg12/lib/demo.jar', 'demo', true );
<strong>ERROR:  java.sql.SQLNonTransientException: A jar named 'demo' already exists
</strong>

函数 set_classpath 为给定模式定义类路径，在此示例中，模式为“public”。类路径由冒号分隔的 jar 名称或类名称列表组成。如果给定的模式不存在或者一个或多个 jar 名称引用了不存在的 jar，则这是一个错误。

下一步是创建 Postgres 函数：

demo=# CREATE FUNCTION getCustomerInfo( INT ) RETURNS CHAR AS 
    'com.percona.blog.pljava.Customers.getCustomerInfo( java.lang.Integer )'
LANGUAGE java;
CREATE FUNCTION

我们现在可以使用它：

demo=# SELECT getCustomerInfo(100);
                                 getcustomerinfo                                  
----------------------------------------------------------------------------------
 - ID: 100                                                                       +
 - Name: HAYES, ROBIN                                                            +
 - Email: ROBIN.HAYES@sakilacustomer.org                                         +
 - Address: 1913 Kamakura Place                                                  +
 - City: Jelets                                                                  +
 - District: Lipetsk                                                             +
 --------------------------------------------------------------------------------
(1 row)

亲爱的，我们的 Postgres 演示数据库中有我们的第一个 Java 函数。
现在，在我们的最后一个示例中，我将向此类添加另一个方法，现在列出来自给定客户的所有付款并计算其总额：

package com.percona.blog.pljava;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Customers {
    private static String m_url = "jdbc:default:connection";

    public static String getCustomerInfo(Integer id) throws SQLException {
        Connection conn = DriverManager.getConnection(m_url);
        String query = "SELECT c.customer_id, c.last_name ||', '|| c.first_name as full_name, "
                + " c.email, a.address, ci.city, a.district "
                + " FROM customer c"
                + "  JOIN address a on a.address_id = c.address_id "
                + " JOIN city ci on ci.city_id = a.city_id "
                + " WHERE customer_id = ?";

        PreparedStatement stmt = conn.prepareStatement(query);
        stmt.setInt(1, id);
        ResultSet rs = stmt.executeQuery();
        rs.next();
        String ret; 
        ret = "- ID: " + rs.getString("customer_id") ;
        ret += "\n- Name: " + rs.getString("full_name");
        ret += "\n- Email: " + rs.getString("email");
        ret += "\n- Address: " + rs.getString("address");
        ret += "\n- City: " + rs.getString("city");
        ret += "\n- District: " + rs.getString("district");
        ret += "\n--------------------------------------------------------------------------------";

        stmt.close();
        conn.close();

        return (ret);
    }

    public static String getCustomerTotal(Integer id) throws SQLException {
        Connection conn;
        PreparedStatement stmt;
        ResultSet rs;
        String result;
        double total;

        conn = DriverManager.getConnection(m_url);
        stmt = conn.prepareStatement(
                "SELECT c.customer_id, c.first_name, c.last_name FROM customer c WHERE c.customer_id = ?");
        stmt.setInt(1, id);
        rs = stmt.executeQuery();
        if (rs.next()) {
            result = "Customer ID  : " + rs.getInt("customer_id");
            result += "\nCustomer Name: " + rs.getString("last_name") + ", " + rs.getString("first_name");
            result += "\n--------------------------------------------------------------------------------------------------------";
        } else {
            return null;
        }

        stmt = conn.prepareStatement("SELECT p.payment_date, p.amount FROM payment p WHERE p.customer_id = ? ORDER BY 1");
        stmt.setInt(1, id);
        rs = stmt.executeQuery();
        total = 0;

        while (rs.next()) {
            result += "\nPayment date: " + rs.getString("payment_date") + ",    Value: " + rs.getString("amount");
            total += rs.getFloat("amount");
        }
        result += "\n--------------------------------------------------------------------------------------------------------";
        result += "\nTotal: " +String.format("%1$,.2f",  total);
        
        stmt.close();
        conn.close();
        return (result);
    }
}

相同的编译指令：

javac com/percona/blog/pljava/Customers.java 
jar -c -f /app/pg12/lib/demo.jar com/percona/blog/pljava/Customers.class

然后我们需要用新创建 jar文件替换之前加载的 jar 文件并在Postgres中创建函数：

demo=# SELECT sqlj.replace_jar( 'file:///app/pg12/lib/demo.jar', 'demo', true );
 replace_jar 
-------------
(1 row)

demo=# CREATE FUNCTION getCustomerTotal( INT ) RETURNS CHAR AS 
    'com.percona.blog.pljava.Customers.getCustomerTotal( java.lang.Integer )'
LANGUAGE java;
CREATE FUNCTION

结果是：

demo=# SELECT getCustomerInfo(100);
                                 getcustomerinfo                                  
----------------------------------------------------------------------------------
 - ID: 100                                                                       +
 - Name: HAYES, ROBIN                                                            +
 - Email: ROBIN.HAYES@sakilacustomer.org                                         +
 - Address: 1913 Kamakura Place                                                  +
 - City: Jelets                                                                  +
 - District: Lipetsk                                                             +
 --------------------------------------------------------------------------------
(1 row)

我们在这里用最后一个例子结束这一节。此时，我们可以访问对象，遍历结果集，并将结果作为单个对象（如 TEXT）返回。我将在本文的第二部分和第三部分讨论如何返回数组/结果集，如何在触发器中使用 PL/Java 函数，以及如何使用外部资源，敬请期待！

引用：
[1] https://tada.github.io/pljava/use/policy.html
[2] https://www.postgresql.org/ftp/projects/pgFoundry/dbsamples/pagila/

【译】PostgreSQL PL/Java – 实操（一）

前言

安装

使用 PL/Java 访问数据库对象

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

PostgreSQL

SQL极简教程 · MySQL · MyBatis · JPA 技术笔记教程总结

DB

PostgreSQL

【译】PostgreSQL PL/Java – 实操（一）

前言

安装

使用 PL/Java 访问数据库对象

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

PostgreSQL

SQL极简教程 · MySQL · MyBatis · JPA 技术笔记 教程 总结

DB

PostgreSQL

SQL极简教程 · MySQL · MyBatis · JPA 技术笔记教程总结