java pulsar 配置 pl/java

转载

编程小匠人 2023-11-09 01:15:28

文章标签 java pulsar 配置数据库开发 java postgresql PostgreSQL 文章分类 Java 后端开发

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 <args> -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和MSVCMinGW参照: runoob.com(C 环境设置)MSVC参照: microsoft.com(编译器选项)

4. 安装Microsoft Visual Studio Windows

4.1. 配置环境变量

变量	值
INCLUDE	D:\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_PATH	D:\Program Files\PostgreSQL\13\lib
LIB	D:\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
path	D:\Program Files\Microsoft Visual Studio\2022\Professional\VC\Tools\MSVC\14.31.31103\bin\Hostx64\x64
VCIDEInstallDir	D:\Program Files\Microsoft Visual Studio\2022\Professional\Common7\IDE\VC\
VCINSTALLDIR	D:\Program Files\Microsoft Visual Studio\2022\Professional\VC\
VSINSTALLDIR	D:\Program Files\Microsoft Visual Studio\2022\Professional\
asl.log	Destination=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

--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;

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

    <!-- Above is all boilerplate. Next: your project's "Maven coordinates" -->

    <groupId>com.example</groupId>
    <artifactId>proj</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <!-- Coordinates are nice, but so are names and descriptions for humans. -->

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

    <!--
      Many Maven plugins care what character set encoding your files are in.
      For this example I've chosen the most restrictive (US-ASCII). Change if
      your files use a different encoding, but be sure not to lie. You should
      be sure the encoding named here IS the way your source files are coded.
    -->

    <properties>
        <project.build.sourceEncoding>US-ASCII</project.build.sourceEncoding>
    </properties>


    <!-- Here's where you say your project depends on a pljava-api version. -->

    <dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>pljava-api</artifactId>
            <version>2-SNAPSHOT</version>
        </dependency>
<!--        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>pljava</artifactId>
            <version>2-SNAPSHOT</version>
        </dependency>-->
    </dependencies>

    <!-- The rest here is pretty much boilerplate. -->

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <release>9</release>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <archive>
                        <manifest>
                            <!-- This identifies and version-stamps the jar.
                               Not essential, but easy and useful. -->
                            <addDefaultImplementationEntries>
                                true
                            </addDefaultImplementationEntries>
                        </manifest>

                        <manifestSections>
                            <!-- This identifies a file in the jar named
                               pljava.ddr as an SQLJDeploymentDescriptor. -->
                            <manifestSection>
                                <name>pljava.ddr</name>
                                <manifestEntries>
                                    <SQLJDeploymentDescriptor>
                                        true
                                    </SQLJDeploymentDescriptor>
                                </manifestEntries>
                            </manifestSection>
                        </manifestSections>
                    </archive>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

总结

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：java使用弹不出操作窗口 java点击按钮弹窗

下一篇：后端 java 埋点实现前端埋点sdk

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯