文章目录
- PostgreSQL之pljava插件编译及安装(windows版)
- 前言
- 一、PL / Java是是什么？
- 二、使用步骤
- 1. 安装PostgreSQL
- 2. 安装jdk
- 3. maven 安装
- 3. 下载PL / Java并编译
- 4. 安装Microsoft Visual Studio Windows
- 4.1. 配置环境变量
- 总结
前言
在Oracle迁移至PostgreSQL 时，发现plsql中的SAVEPOINT技术点，在迁移中，无法十分方便的从循环中设置保存点，并回滚至保存点的操作，经过一番调查发现可以通过编辑并加载PostgreSQL的pljava 插件，通过pljava 插件实现Oracle plsql中的SAVEPOINT功能.
PostgreSQL官网文档参照：从Oracle PL/SQL 移植(其他要关注的事项)
提示：以下是本篇文章正文内容，下面案例可供参考
一、PL / Java是是什么？PL / Java是一个免费的附加模块，它将Java™存储过程，触发器和函数带到PostgreSQL™后端。
有关此项目的更多信息可以在 PL/Java Wiki和project information site找到。
二、使用步骤 1. 安装PostgreSQL不赘述，具体参照：runoob.com(Windows 上安装 PostgreSQL)
安装完成后可以在命令提示符(cmd)中输入pg_config安装完成后会有如下信息输出（实际输出因安装路径和版本而异)
BINDIR = D:/Program Files/PostgreSQL/13/bin DOCDIR = D:/Program Files/PostgreSQL/13/doc HTMLDIR = D:/Program Files/PostgreSQL/13/doc INCLUDEDIR = D:/Program Files/PostgreSQL/13/include PKGINCLUDEDIR = D:/Program Files/PostgreSQL/13/include INCLUDEDIR-SERVER = D:/Program Files/PostgreSQL/13/include/server LIBDIR = D:/Program Files/PostgreSQL/13/lib PKGLIBDIR = D:/Program Files/PostgreSQL/13/lib LOCALEDIR = D:/Program Files/PostgreSQL/13/share/locale MANDIR = D:/Program Files/PostgreSQL/13/man SHAREDIR = D:/Program Files/PostgreSQL/13/share SYSCONFDIR = D:/Program Files/PostgreSQL/13/etc PGXS = D:/Program Files/PostgreSQL/13/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = --enable-thread-safety --enable-nls --with-ldap --with-openssl --with-uuid --with-libxml --with-libxslt --with-icu --with-tcl --with-perl --with-python CC = 没有被记录 CPPFLAGS = 没有被记录 CFLAGS = 没有被记录 CFLAGS_SL = 没有被记录 LDFLAGS = 没有被记录 LDFLAGS_EX = 没有被记录 LDFLAGS_SL = 没有被记录 LIBS = 没有被记录 VERSION = PostgreSQL 13.62. 安装jdk
具体参照：runoob.com(Windows 10 配置Java 环境变量)
安装完成后可以在命令提示符(cmd)中输入java -version安装完成后会有如下信息输出（实际输出因安装路径和版本而异)
openjdk version "17" 2021-09-14 OpenJDK Runtime Environment (build 17+35-2724) OpenJDK 64-Bit Server VM (build 17+35-2724, mixed mode, sharing)3. maven 安装
具体参照：runoob.com(Maven 环境配置)
安装完成后可以在命令提示符(cmd)中输入mvn -v安装完成后会有如下信息输出（实际输出因安装路径和版本而异)
Apache Maven 3.8.5 (3599d3414f046de2324203b78ddcf9b5e4388aa0) Maven home: D:apache-maven-3.8.5 Java version: 17.0.3, vendor: Oracle Corporation, runtime: C:Program FilesJavajdk-17.0.3 Default locale: zh_CN, platform encoding: GBK OS name: "windows 11", version: "10.0", arch: "amd64", family: "windows"3. 下载PL / Java并编译
github链接：https://github.com/tada/pljava
下载完成后pljava根目录下直接执行命令提示符(cmd)mvn clean install 如果没配置编译环境必然会在pljava-so报错
[INFO] PostgreSQL PL/Java ................................. SUCCESS [01:53 min] [INFO] PL/Java API ........................................ SUCCESS [ 44.918 s] [INFO] PL/Java backend Java code .......................... SUCCESS [ 8.142 s] [INFO] PL/Java PGXS ....................................... SUCCESS [ 44.571 s] [INFO] PL/Java backend native code ........................ FAILURE [ 1.281 s] [INFO] PL/Java Ant tasks .................................. SKIPPED [INFO] PL/Java examples ................................... SKIPPED [INFO] PL/Java packaging .................................. SKIPPED [INFO] ------------------------------------------------------------------------ [INFO] BUILD FAILURE [INFO] ------------------------------------------------------------------------ [INFO] Total time: 03:32 min [INFO] Finished at: 2022-05-05T00:46:36+08:00 [INFO] ------------------------------------------------------------------------ [ERROR] Failed to execute goal org.postgresql:pljava-pgxs:2-SNAPSHOT:scripted-goal (build-shared-object) on project pljava-so: compile -> [Help 1] [ERROR] [ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch. [ERROR] Re-run Maven using the -X switch to enable full debug logging. [ERROR] [ERROR] For more information about the errors and possible solutions, please read the following articles: [ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException [ERROR] [ERROR] After correcting the problems, you can resume the build with the command [ERROR] mvn -rf :pljava-so
查看pljavapljava-so目录下的pom.xml
var configuration = [
{
name : "Linux",
//...
},
{
name : "Mac OS X",
//...
},
{
name : "Windows MinGW",
object_extension : ".o",
//...
},
{
name : "Windows MSVC",
object_extension : ".obj",
probe: function(os_name) {
return os_name.toLowerCase().contains("windows")
&& java.lang.System.getenv().containsKey("VCINSTALLDIR");
},
formatIncludes : function(includes_list) {
return includes_list.stream().map(function(s) {
return "/I" + s;
}).collect(java.util.stream.Collectors.toList());
},
formatDefines : function(defines_map) {
return defines_map.entrySet().stream().map(function(s) {
var define = "/D" + s.getKey();
if (s.getValue() != null)
define += "=" + s.getValue();
return define;
}).collect(java.util.stream.Collectors.toList());
},
compile : function(cc, files, output_dir, includes, defines, flags) {
includes.add(java_include.resolve("win32").toString());
includes.add(Paths.get(includedir_server, "port", "win32").toString());
includes.add(resolve(pljava_include,
Paths.get("fallback", "win32")).toString());
includes.add(Paths.get(includedir_server, "port", "win32_msvc").toString());
defines.put("Windows", null);
defines.put("WIN32", null);
defines.put("NDEBUG", null);
var compileProcess = utils.processBuilder(function(l) {
l.add("cl");
l.addAll(of("/c", "/nologo"));
if(isDebugEnabled)
l.addAll(of("/Zi", "/Od", "/RTC1", "/D_DEBUG", "/MDd"));
else
l.add("/MD");
l.addAll(pgxs.formatDefines(defines));
l.addAll(pgxs.formatIncludes(includes));
l.addAll(files);
});
compileProcess.directory(output_dir.toFile());
return runCommand(utils.forWindowsCRuntime(compileProcess));
},
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.addAll(files);
});
linkingProcess.directory(target_path.toFile());
return runCommand(utils.forWindowsCRuntime(linkingProcess));
}
}
];
发现有如下编译选择，此处windows 有两种编译方式MinGW和MSVC
MinGW参照: runoob.com(C 环境设置)
MSVC参照: microsoft.com(编译器选项)
| 变量 | 值 |
|---|---|
| INCLUDE | D:Program FilesMicrosoft Visual Studio2022ProfessionalVCToolsMSVC14.31.31103include D:Windows Kits10Include10.0.19041.0ucrt D:Windows Kits10Include10.0.19041.0um D:Windows Kits10Include10.0.19041.0winrt D:Windows Kits10Include10.0.19041.0shared D:Windows Kits10Include10.0.19041.0cppwinrt |
| LD_LIBRARY_PATH | D:Program FilesPostgreSQL13lib |
| LIB | D:Program FilesMicrosoft Visual Studio2022ProfessionalVCToolsMSVC14.31.31103libx64 D:Windows Kits10Lib10.0.19041.0umx64 D:Windows Kits10Lib10.0.19041.0ucrtx64 D:Windows Kits10Lib10.0.19041.0ucrt_enclavex64 |
| path | D:Program FilesMicrosoft Visual Studio2022ProfessionalVCToolsMSVC14.31.31103binHostx64x64 |
| VCIDEInstallDir | D:Program FilesMicrosoft Visual Studio2022ProfessionalCommon7IDEVC |
| VCINSTALLDIR | D:Program FilesMicrosoft Visual Studio2022ProfessionalVC |
| VSINSTALLDIR | D:Program FilesMicrosoft Visual Studio2022Professional |
| asl.log | Destination=file（不确定是否需要） |
未完待续,后续需整理…
- java.security : permission java.security.AllPermission;
- 管理员命令提示符(cmd)：F:pljavapljava-packaging>java -jar targetpljava-pg13.jar
- ALTER SYSTEM SET pljava.libjvm_location = 'C:Program FilesJavajdk-17binserverjvm.dll';
- CREATE EXTENSION pljava
- install_jar
--permission java.io.FilePermission "<< ALL FILES>>" "read";
select sqlj.install_jar('file:/D:/Program Files/PostgreSQL/13/lib/proj-0.0.1-SNAPSHOT.jar', 'myjar', true);
select sqlj.replace_jar('file:/D:/Program Files/PostgreSQL/13/lib/proj-0.0.1-SNAPSHOT.jar', 'myjar', true);
--select hello('world');
select sqlj.get_classpath('public');
select sqlj.set_classpath('public', 'myjar');
--select hello('world');
create temporary table if NOT EXISTS t_temp(
id int,
name text
);
select count(*) from pg_class where relname = 't_temp';
do
$$
declare
result refcursor;
--in_text integer:= 9;
test text;
begin
insert into t_temp(id, name) values(1, 'hello');
open result for select * from hello(16);
fetch result into test;
raise notice 'return: %', test;
end
$$;
-- 查询临时表t_temp 是否存在
select count(*) from pg_class where relname = 't_temp';
-- Language: postgresql
-- 创建临时表
select * from t_temp
--清除临时表
truncate TABLE t_temp;
- Hello
package com.example.proj;
import org.postgresql.pljava.annotation.Function;
import java.sql.*;
public class Hello {
@Function(onNullInput=Function.OnNullInput.RETURNS_NULL)
public static String hello(int loopIndex) throws SQLException {
try {
Connection connection = DriverManager.getConnection("jdbc:default:connection");
String sql = "select count(*) from pg_class where relname = 't_temp'";
try(PreparedStatement stmt = connection
.prepareStatement(sql)){
try(ResultSet rs = stmt.executeQuery()){
if(!rs.next()){
throw new SQLException("not found table t_temp");
}else {
int count = rs.getInt(1);
if(count == 0){
throw new SQLException("not found table t_temp");
}
}
}
}
String instSql = "insert into t_temp(id, name) values(?, ?);";
int lc = 1;
Savepoint savepoint = null;
for(int i = 0; i < loopIndex; i++){
if(lc==1){
savepoint = connection.setSavepoint("savepoint1");
}
try(PreparedStatement inst = connection
.prepareStatement(instSql)){
inst.setInt(1, lc);
inst.setString(2, "name" + i);
inst.executeUpdate();
}
if(lc==6){
connection.rollback(savepoint);
lc = 1;
}
if(i==15){
throw new SQLException("loopIndex is over 15");
}
lc=lc+1;
}
} catch (SQLException e) {
throw e;
}
return "execution succeed!";
}
}
4.0.0 com.example proj 0.0.1-SNAPSHOT com.example.proj.Hello in PL/Java Project that provides a com.example.proj.Hello function US-ASCII org.postgresql pljava-api 2-SNAPSHOT org.apache.maven.plugins maven-compiler-plugin 3.8.1 9 org.apache.maven.plugins maven-jar-plugin 2.6 true pljava.ddr true
总结
