投稿
  1. 首页
  2. java

PostgreSQL之pljava插件编译及安装

code • 2022-5-6 • java • 阅读 87

PostgreSQL之pljava插件编译及安装,第1张

PostgreSQL之pljava插件编译及安装(windows版)

文章目录
  • 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.6
2. 安装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 Files\Java\jdk-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

查看pljava\pljava-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(编译器选项)

4. 安装Microsoft Visual Studio Windows 4.1. 配置环境变量
变量值
INCLUDED:\Program Files\Microsoft Visual Studio\2022\Professional\VC\Tools\MSVC\14.31.31103\include
D:\Windows Kits\10\Include\10.0.19041.0\ucrt
D:\Windows Kits\10\Include\10.0.19041.0\um
D:\Windows Kits\10\Include\10.0.19041.0\winrt
D:\Windows Kits\10\Include\10.0.19041.0\shared
D:\Windows Kits\10\Include\10.0.19041.0\cppwinrt
LD_LIBRARY_PATHD:\Program Files\PostgreSQL\lib
LIBD:\Program Files\Microsoft Visual Studio\2022\Professional\VC\Tools\MSVC\14.31.31103\lib\x64
D:\Windows Kits\10\Lib\10.0.19041.0\um\x64
D:\Windows Kits\10\Lib\10.0.19041.0\ucrt\x64
D:\Windows Kits\10\Lib\10.0.19041.0\ucrt_enclave\x64
pathD:\Program Files\Microsoft Visual Studio22\Professional\VC\Tools\MSVC.31.31103\bin\Hostx64\x64
VCIDEInstallDirD:\Program Files\Microsoft Visual Studio22\Professional\Common7\IDE\VC\
VCINSTALLDIRD:\Program Files\Microsoft Visual Studio22\Professional\VC\
VSINSTALLDIRD:\Program Files\Microsoft Visual Studio22\Professional\
asl.logDestination=file（不确定是否需要）

未完待续,后续需整理…

  • java.security : permission java.security.AllPermission;
  • 管理员命令提示符(cmd)：F:\pljava\pljava-packaging>java -jar target\pljava-pg13.jar
  • ALTER SYSTEM SET pljava.libjvm_location = 'C:\Program Files\Java\jdk-17\bin\server\jvm.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!";
    }
}

<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.0modelVersion>

    

    <groupId>com.examplegroupId>
    <artifactId>projartifactId>
    <version>0.0.1-SNAPSHOTversion>

    

    <name>com.example.proj.Hello in PL/Javaname>
    <description>Project that provides a com.example.proj.Hello functiondescription>

    

    <properties>
        <project.build.sourceEncoding>US-ASCIIproject.build.sourceEncoding>
    properties>


    

    <dependencies>
        <dependency>
            <groupId>org.postgresqlgroupId>
            <artifactId>pljava-apiartifactId>
            <version>2-SNAPSHOTversion>
        dependency>

    dependencies>

    

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.pluginsgroupId>
                <artifactId>maven-compiler-pluginartifactId>
                <version>3.8.1version>
                <configuration>
                    <release>9release>
                configuration>
            plugin>
            <plugin>
                <groupId>org.apache.maven.pluginsgroupId>
                <artifactId>maven-jar-pluginartifactId>
                <version>2.6version>
                <configuration>
                    <archive>
                        <manifest>
                            
                            <addDefaultImplementationEntries>
                                true
                            addDefaultImplementationEntries>
                        manifest>

                        <manifestSections>
                            
                            <manifestSection>
                                <name>pljava.ddrname>
                                <manifestEntries>
                                    <SQLJDeploymentDescriptor>
                                        true
                                    SQLJDeploymentDescriptor>
                                manifestEntries>
                            manifestSection>
                        manifestSections>
                    archive>
                configuration>
            plugin>
        plugins>
    build>
project>
总结

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/langs/799535.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
code code 管理员组
0 0
上一篇 2022-05-06
下一篇 2022-05-06

发表评论

登录后才能评论

评论列表（0条）

保存