前言
此博客翻譯自 percona 官網(wǎng)博客 《PostgreSQL PL/Java – A How-To, Part 1》
最近，我們收到了一些關(guān)于 PL/Java 的問題忽刽，我發(fā)現(xiàn)很難在互聯(lián)網(wǎng)上搜索到明確的說明。并不是說網(wǎng)上沒有優(yōu)質(zhì)的信息酌住，但其中大部分信息要么不完整吹艇，要么過時，要么令人困惑总寻，我決定創(chuàng)建這個簡短的“how-to”并展示如何安裝 PL/Java 谤草，以及如何通過幾個示例運(yùn)行 PL/Java衙傀。
安裝
我將在這里展示如何從源代碼安裝 PL/Java抬吟，因?yàn)槲业钠脚_沒有編譯的二進(jìn)制文件，如果你的平臺有來自包管理器的二進(jìn)制文件统抬，你可以從那里安裝火本，例如使用 YUM 或 APT危队。另外，請注意钙畔，為簡單起見茫陆，我使用的 PL/Java 沒有指定特定的可信用戶，而是使用了 Postgres 數(shù)據(jù)庫超級用戶擎析。我建議閱讀有關(guān)用戶和權(quán)限的文檔簿盅，在這[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
我在這里假設(shè)你已經(jīng)足夠了解 maven 并且會使用 mvn 命令桨醋。 java -jar pljava-packaging/target/pljava-pg12.jar 會將所需的文件和包復(fù)制/安裝到 Postgres 文件夾中。 請注意现斋，maven 使用了我的 Postgres 版本并使用該版本創(chuàng)建了 jar 文件：“pljava-pg12.jar”喜最，因此請注意您已經(jīng)擁有的版本，因?yàn)槿绻胁煌?Postgres 版本庄蹋，jar 文件將會不一樣瞬内！
我現(xiàn)在可以將擴(kuò)展安裝到我將使用的數(shù)據(jù)庫中。我在這個博客里使用的數(shù)據(jù)庫叫 “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.)“虫蝶。好的，我必須找到我的系統(tǒng)正在使用的 libjvm 來配置 Postgres蔗包。我使用 SET 命令在線執(zhí)行此操作：
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使其在我的所有數(shù)據(jù)庫中持久化秉扑，因?yàn)樗鼘⒔o定的參數(shù)設(shè)置寫入“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
現(xiàn)在我們已經(jīng)安裝了 PL/Java调限，我們可以檢查系統(tǒng) catalog 看它是否確實(shí)存在：
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 訪問數(shù)據(jù)庫對象
我發(fā)現(xiàn)耻矮，大多數(shù)示例都是如何從 Java 類中創(chuàng)建“hello world”或如何計(jì)算斐波那契數(shù)列，但沒有展示如何訪問數(shù)據(jù)庫對象忆谓。好吧裆装，這些例子沒有錯，但我想認(rèn)為倡缠，那些在數(shù)據(jù)庫中安裝 PL/Java 的人想從 Java 函數(shù)內(nèi)部訪問數(shù)據(jù)庫對象哨免，這就是我們在這里要做的。
在這篇文章中昙沦，我將使用可在此處[2] 找到的示例數(shù)據(jù)庫 “pagila” 進(jìn)行測試琢唾。
對于第一個示例，我將創(chuàng)建一個帶有靜態(tài)方法的簡單類盾饮，該類將像任何 Postgres 函數(shù)一樣在外部訪問采桃。該函數(shù)將接收一個整數(shù)參數(shù)并使用它來搜索表“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);
}
}
我使用以下命令手動編譯并創(chuàng)建了“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”文件夾中創(chuàng)建了 jar 文件衔蹲，記住這一點(diǎn)肢娘，因?yàn)槲覀儗⒃谙乱徊街惺褂眠@些信息，在 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 函數(shù)具有簽名“install_jar(<jar_url>, <jar_name>, <deploy>)”踪危，該函數(shù)將一個 jar 文件從 URL 指定的位置加載到 SQLJ jar 存儲庫中蔬浙。如果給定名稱的 jar 已存在于存儲庫中，或者該 jar 在 URL 中不存在或數(shù)據(jù)庫無法讀取該 jar贞远，則會出現(xiàn)錯誤：
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>
函數(shù) set_classpath 為給定模式定義類路徑畴博，在此示例中，模式為“public”蓝仲。類路徑由冒號分隔的 jar 名稱或類名稱列表組成俱病。如果給定的模式不存在或者一個或多個 jar 名稱引用了不存在的 jar，則這是一個錯誤袱结。
下一步是創(chuàng)建 Postgres 函數(shù)：
demo=# CREATE FUNCTION getCustomerInfo( INT ) RETURNS CHAR AS
'com.percona.blog.pljava.Customers.getCustomerInfo( java.lang.Integer )'
LANGUAGE java;
CREATE FUNCTION
我們現(xiàn)在可以使用它：
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 演示數(shù)據(jù)庫中有我們的第一個 Java 函數(shù)。
現(xiàn)在垢夹，在我們的最后一個示例中溢吻，我將向此類添加另一個方法，現(xiàn)在列出來自給定客戶的所有付款并計(jì)算其總額：
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
然后我們需要用新創(chuàng)建 jar文件替換之前加載的 jar 文件并在Postgres中創(chuàng)建函數(shù)：
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
結(jié)果是：
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)
我們在這里用最后一個例子結(jié)束這一節(jié)果元。 此時促王，我們可以訪問對象，遍歷結(jié)果集而晒，并將結(jié)果作為單個對象（如 TEXT）返回蝇狼。 我將在本文的第二部分和第三部分討論如何返回數(shù)組/結(jié)果集，如何在觸發(fā)器中使用 PL/Java 函數(shù)倡怎，以及如何使用外部資源迅耘，敬請期待！
引用：
[1] https://tada.github.io/pljava/use/policy.html
[2] https://www.postgresql.org/ftp/projects/pgFoundry/dbsamples/pagila/
