准备
不支持Windows
下载地址 https://github.com/tada/pljava
准备好maven3.x
准备好java, 不清楚兼容的最低版本, 最低看到看有人用8, 我用的17
安装步骤
- 解压pljava, cd, mvn clean install -Dpgsql.pgconfig=/home/ubuntu/_/pg14/bin/pg_config -e -X
在pljava目录下执行
java -Dpgconfig=/home/ubuntu/_/pg14/bin/pg_config \-jar pljava-packaging/target/pljava-pg14.jar
在postgresql.conf中添加jvm启动参数, policy配置用来处理jna调用java时的权限不足
PLJAVA.VMOPTIONS='-Xms64M -Xmx128M -Djava.security.policy=/home/ubuntu/_/java.policy --add-opens=java.base/java.lang=ALL-UNNAMED --add-opens=java.base/jdk.internal=ALL-UNNAMED' -- permission java.security.AllPermission; 关掉校验使用超级用户设置jvm动态库
-- 库级参数 alter database 你的库 set pljava.libjvm_location='/home/ubuntu/_/jdk-17/lib/server/libjvm.so' -- 会话级参数 set pljava.libjvm_location to '/home/ubuntu/_/jdk-17/lib/server/libjvm.so';创建扩展 create extension pljava, 设置步骤4的参数会, 应会自动创建
使用
- 部署jar
第一个参数: 磁盘绝对路径, 第二个参数: 别名, 第三个参数, 是否加载, 稍后会用到别名
别名不能有符号-, 可以有下划线
select sqlj.install_jar('file:///test.jar', 'test', true);
设置classpath时, 只能使用别名, 多个jar使用:分开
-- 设置classpath select sqlj.set_classpath('你的schema', 'test别名'); -- 获取classpath select sqlj.get_classpath('你的schema');卸载jar
select sqlj.remove_jar('test', true);更新jar
select sqlj.replace_jar('你的路径','别名',true)查看已部署的jar
select * from sqlj.jar_repository;
使用方法
create function hello(varchar) returns varchar
AS 'com.pljava.Test.方法名'
LANGUAGE java;
如果普通用户没权限使用pljava
grant usage on language java to someone;
grant usage on schema sqlj to someone;
java方法引用是一个public static 函数
更多示例参考 https://github.com/tada/pljava/tree/master/pljava-examples/src/main/java/org/postgresql/pljava/example
输出
postgres=# select info();
info
-----------------------------------------------------------------------
pid: 96757 +
+
JavaVM Name: OpenJDK 64-Bit Server VM +
JavaVM Version: 17-loom+7-342 +
JavaVM Vendor: Oracle Corporation +
JavaVM Info: mixed mode, sharing +
+
total memory: 66 MB +
free memory: 44.04 MB +
max memory: 128 MB +
+
user threads: +
1 main main runnable +
+
system threads: +
1 system Reference Handler runnable +
2 system Finalizer waiting +
3 system Attach Listener runnable +
4 system Notification Thread runnable +
+
current thread: Thread[main,5,main] +
current connection: org.postgresql.pljava.jdbc.SPIConnection@31ff1390+
path: /home/ubuntu/_/pg14data
(1 row)
java
package org.pljava;
import cn.hutool.core.io.FileUtil;
import cn.hutool.system.SystemUtil;
import com.jfinal.template.Engine;
import com.jsoniter.JsonIterator;
import com.jsoniter.any.Any;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Map;
public class Test {
private static final Engine engine = Engine.create("test").setDevMode(true).setBaseTemplatePath("/234356sdfsdf");
/*
create or replace function "init_render"(dev boolean,path varchar) returns int
AS 'org.pljava.Test.init'
LANGUAGE java;
-- select init_render(true,'/home/ubuntu/_')
-- select render('index.html',convert_to('{"title":"标题","obj":{"list":[123,456,789]}}','UTF-8'));
* */
public static void init(boolean devMode, String basePath) {
engine.setDevMode(devMode).setBaseTemplatePath(basePath);
}
/*
* java里如果转String, 是UTF16LE, 到了pg端还得转一次UTF8
* 索性直接用bytes
* */
public static byte[] render(String file, byte[] json) {
Any any = JsonIterator.deserialize(json);
Map<String, Any> map = any.asMap();
ByteArrayOutputStream bos = new ByteArrayOutputStream();
engine.getTemplate(file).render(map, bos);
return bos.toByteArray();
}
/*
create function "connAddr"() returns int
AS 'org.pljava.Test.connAddr'
LANGUAGE java;
-- fixed 1926673338
* */
/*
public static int connAddr() throws SQLException {
Connection connection = DriverManager.getConnection("jdbc:default:connection");
return System.identityHashCode(connection);
}
*/
/*
create function "info"() returns varchar
AS 'org.pljava.Test.info'
LANGUAGE java;
-- class org.postgresql.pljava.jdbc.SPIConnection
* */
public static String info() throws SQLException {
StringBuilder sb = new StringBuilder();
sb.append("pid: ").append(SystemUtil.getCurrentPID()).append("\n\n");
sb.append(SystemUtil.getJvmInfo()).append("\n");
sb.append("total memory: ").append(FileUtil.readableFileSize(SystemUtil.getTotalMemory())).append("\n");
sb.append("free memory: ").append(FileUtil.readableFileSize(SystemUtil.getFreeMemory())).append("\n");
sb.append("max memory: ").append(FileUtil.readableFileSize(SystemUtil.getMaxMemory())).append("\n\n");
ArrayList<Thread> userThreads = new ArrayList<>();
ArrayList<Thread> systemThreads = new ArrayList<>();
Thread.getAllStackTraces().keySet().stream().sorted((a, b) -> Math.toIntExact(a.getId() - b.getId()))
.filter(thread -> !(thread.getName().contains("Coroutines") || thread.getName().contains("Ctrl-Break") || thread.getThreadGroup().getName().equals("InnocuousThreadGroup"))).forEach(thread -> {
if (thread.getThreadGroup().getName().equals("system"))
systemThreads.add(thread);
else userThreads.add(thread);
});
sb.append("user threads: \n");
int i = 0;
for (Thread thread : userThreads) {
sb.append(i + 1).append(" ").append(thread.getThreadGroup().getName()).append(" ").append(thread.getName()).append(" ").append(thread.getState().toString().toLowerCase()).append("\n");
i++;
}
sb.append("\n");
i = 0;
sb.append("system threads: \n");
for (Thread thread : systemThreads) {
sb.append(i + 1).append(" ").append(thread.getThreadGroup().getName()).append(" ").append(thread.getName()).append(" ").append(thread.getState().toString().toLowerCase()).append("\n");
i++;
}
sb.append("\n");
sb.append("current thread: ").append(Thread.currentThread());
sb.append("\n");
Connection connection = DriverManager.getConnection("jdbc:default:connection");
sb.append("current connection: ").append(connection);
sb.append("\n");
sb.append("path: ").append(new File("").getAbsolutePath());
return sb.toString();
}
/*
jvm 运行参数 加上 --add-opens=java.base/java.lang=ALL-UNNAMED
* */
public static void main(String[] args) {
Any any = JsonIterator.deserialize("{\"title\":\"标题\",\"obj\":{\"list\":[123,456,789]}}");
/* System.out.println(any.get("obj").get("list").get(-1));
System.out.println(any.get("obj").get("list").get(0));*/
System.out.println(any.toInt("obj", "list", 0));
Map<String, Any> map = any.asMap();
System.out.println(engine.getTemplateByString("#for(it:obj.get(\"list\"))\n" +
"#(it)\n" +
"#end\n" +
"#(obj.toInt('list',0))\n" +
"\n").renderToString(map));
var s = """
sdfsd
sdf
sdf
""";
}
}
pom
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>test</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>17</maven.compiler.source>
<maven.compiler.target>17</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>com.jfinal</groupId>
<artifactId>enjoy</artifactId>
<version>4.9.16</version>
</dependency>
<dependency>
<groupId>com.jsoniter</groupId>
<artifactId>jsoniter</artifactId>
<version>0.9.23</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.4</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<!-- 指定maven编译的jdk版本,如果不指定,maven3默认用jdk 1.5 maven2默认用jdk1.3 -->
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.1</version>
<configuration>
<source>17</source>
<target>17</target>
<compilerArgs>
</compilerArgs>
</configuration>
</plugin>
</plugins>
</build>
</project>
注意事项
一个connection 对应一个jvm(这里实际上已经不能叫做jvm了,
jna同化了java代码, pg客户端进程和java代码是一体的)pg运行的java代码中如果创建了线程, 当客户端(这里指linux terminal)退出登录, pg客户端进程会一直运行, 此时的它虽然仍是postgres进程, 但和pg server, 没有一毛钱关系了, 它是一个独立的进程, pg_stat_activity是查不到它的
在pg中启动一个java server, 并不实用, 应该把java当作pg的工具类扩展,
在Windows上构建
pg官方构建出的Windows程序, pgconfig拿不到相关编译参数, 只能自己试错, 缺什么补什么
首先要装vs, 然后装这么些组件, 我是挑着装的, 没有全部安装, 自己创建一个没有空格的目录安装
装完之后它可能会给你环境变量塞几条, 删掉他们, 找到, 默认他会给Windows开始菜单里加一个x86的terminal快捷方式, 也用不到, 我要用x64的. 然后 找到**C:\\MicrosoftVisualStudio\2022\Community\VC\Tools\MSVC\14.30.30705\bin\Hostx64\x64**
配置到环境变量, 这是编译器和链接器
然后再pgso这里改成写死的, 用msvcvar os_name = java.lang.System.getProperty("os.name"); var implementation = null; var extension = null; implementation = configuration[3]; extension = implementation.object_extension; info("Using compiling/linking rules for " + implementation.name);然后在mslink这里加上需要的库
link : function(cc, flags, files, target_path) { var linkingProcess = utils.processBuilder(function(l) { l.add("link"); l.addAll(of("/MANIFEST", "/NOLOGO", "/DLL", "/SUBSYSTEM:CONSOLE", "/INCREMENTAL:NO")); l.add("/OUT:" + library_name + ".dll"); if(isDebugEnabled) l.add("/DEBUG"); // From compiler-msvc profile l.add(Paths.get(pkglibdir, "postgres.lib").toString()); l.add('C:/_/MicrosoftVisualStudio/2022/Community/VC/Tools/MSVC/14.30.30705/lib/x64/ucrt.lib'); l.add('C:/_/MicrosoftVisualStudio/2022/Community/VC/Tools/MSVC/14.30.30705/lib/x64/vcruntime.lib'); l.add('C:/_/MicrosoftVisualStudio/2022/Community/VC/Tools/MSVC/14.30.30705/lib/x64/kernel32.Lib'); l.add('C:/_/MicrosoftVisualStudio/2022/Community/VC/Tools/MSVC/14.30.30705/lib/x64/oldnames.lib'); l.add('C:/_/MicrosoftVisualStudio/2022/Community/VC/Tools/MSVC/14.30.30705/lib/x64/msvcrt.lib'); l.add('C:/_/MicrosoftVisualStudio/2022/Community/VC/Tools/MSVC/14.30.30705/bin/Hostx64/x64/uuid.lib'); l.addAll(files); }); linkingProcess.directory(target_path.toFile()); return runCommand(utils.forWindowsCRuntime(linkingProcess)); }然后
这里改成GBK org.postgresql.pljava.pgxs.PGXSUtils#defaultCharsetDecodeStrict 这里改成直接用cl.exe org.postgresql.pljava.pgxs.ScriptingMojo#getPgConfigProperty if(property.equals("--cc")) return "cl.exe"; 这里打印一下编译链接的命令 org.postgresql.pljava.pgxs.PGXSUtils#runCommand log.info(processBuilder.command().stream().collect(Collectors.joining(" ")));写这篇文章时, pg14还太新了, 需要修改一下pg_type_d.h ```plsql
define PG_NODE_TREEOID 194
define PG_NDISTINCTOID 3361
define PG_DEPENDENCIESOID 3402
define PG_MCV_LISTOID 5017
define PG_DDL_COMMANDOID 32
// 从pg13或更低, 复制以下五行
define PGNODETREEOID 194
define PGNDISTINCTOID 3361
define PGDEPENDENCIESOID 3402
define PGMCVLISTOID 5017
define PGDDLCOMMANDOID 32
<a name="LaC6t"></a>
#### 运行成功
```plsql
mvn clean install -Dnar.cores=1 -Dpgsql.pgconfig=C:\_\green\pg14\bin\pg_config.exe -e -X
java -Dpgconfig=C:\_\green\pg14\bin\pg_config.exe -jar pljava-packaging/target/pljava-pg14.jar
set pljava.libjvm_location to 'C:/_/green/jdk/java17loom/bin/server/jvm.dll';
[2021-11-21 04:52:43] [00000] PL/Java loaded
[2021-11-21 04:52:43] [00000] PL/Java successfully started after adjusting settings
[2021-11-21 04:52:43] [00000] PL/Java load successful after failed CREATE EXTENSION
[2021-11-21 04:52:43] [01000] [JEP 411] migration advisory: there will be a Java version (after Java 17) that will be unable to run PL/Java 1.6.3 with policy enforcement
PLJava参数
PLJAVA.VMOPTIONS='-Xms64M -Xmx128M -Dfile.encoding=utf8 -Djava.security.policy=C:/_/green/pg13/etc/java.policy --add-opens=java.base/java.lang=ALL-UNNAMED --add-opens=java.base/jdk.internal=ALL-UNNAMED'
若有收获，就点个赞吧
0 人点赞
