pl/java简介
pl/java是一个postgreSQL数据库插件,与pl/sql、pl/perl、pl/python类似,安装该插件之后,函数和触发器可以用java语言实现然后加载进数据库。pljava开发工作于2003年开始,2005年1月第一个正式版本发布。
pl/java下载
git clone https://github.com/tada/pljava
pl/java构建条件
1、C程序编译链接工具,gcc g++ (笔者版本为4.8.5,推荐4.3.0以上版本)
2、jdk(笔者版本为openjdk1.8,1.7以下版本可能会某些依赖下载失败)
3、安装postgre数据库,pg_config路径加入环境变量
4、pljava由maven构建,需要安装maven(不低于3.0.4版本)
note:建议不要用yum安装maven, yum安装的maven版本一般比较低,编译过程可能会由于依赖库版本问题导致编译失败,建议手动下载maven 3.6以上的版本.
构建 pl/java
1、下载pl/java源码到指定目录
[qin@pcmk2 pljava-master]$ pwd
/home/qin/pljava-master
2、进入源码根目录执行命令:mvn clean install
[qin@pcmk2 pljava-master]$ ls
COPYRIGHT docs freenode.ver pljava pljava-ant pljava-api pljava-examples pljava-packaging pljava-so pom.xml README.md src target
[qin@pcmk2 pljava-master]$ mvn clean install
note: 第一次构建,有很多依赖库需要下载,构建过程可能会需要几个小时,请耐心等待,二次构建就会变快
3、构建成功后显示如下:
```bash
[INFO] Executed tasks
[INFO]
[INFO] --- maven-install-plugin:2.5.2:install (default-install) @ pljava-packaging ---
[INFO] Installing /home/qin/pljava-master/pljava-packaging/pom.xml to /home/qin/.m2/repository/org/postgresql/pljava-packaging/1.6.0-SNAPSHOT/pljava-packaging-1.6.0-SNAPSHOT.pom
[INFO] ------------------------------------------------------------------------
[INFO] Reactor Summary for PostgreSQL PL/Java 1.6.0-SNAPSHOT:
[INFO]
[INFO] PostgreSQL PL/Java ................................. SUCCESS [ 2.224 s]
[INFO] PL/Java API ........................................ SUCCESS [ 14.573 s]
[INFO] PL/Java backend Java code .......................... SUCCESS [ 5.684 s]
[INFO] PL/Java backend native code ........................ SUCCESS [ 22.446 s]
[INFO] PL/Java Ant tasks .................................. SUCCESS [ 0.587 s]
[INFO] PL/Java examples ................................... SUCCESS [12:23 min]
[INFO] PL/Java packaging .................................. SUCCESS [15:00 min]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 28:11 min
[INFO] Finished at: 2020-05-02T18:48:34+08:00
[INFO] ------------------------------------------------------------------------
构建成功之后pljava-packaging/target子目录下的jar文件pljava-pg9.6-amd64-Linux-gpp.jar,包含有安装pljava所需的所有文件,java -jar命令可以把jar包所包含的文件提取出来并自动放入postgresql安装目录extension子目录下。
安装pl/java进入postgreSQL 9.6
1、build完成之后,执行以下命令,可以安装Pl/java extension进入postgresql安装目录
cd ~/pljava-master/pljava-packaging/target
java -jar pljava-pg9.6-amd64-Linux-gpp.jar
[qin@pcmk2 target]$ java -jar pljava-pg9.6-amd64-Linux-gpp.jar
/home/qin/pg96/lib/postgresql/libpljava-so-1.6.0-SNAPSHOT.so as bytes
/home/qin/pg96/share/postgresql/pljava/pljava-1.6.0-SNAPSHOT.jar as bytes
/home/qin/pg96/share/postgresql/pljava/pljava-api-1.6.0-SNAPSHOT.jar as bytes
/home/qin/pg96/share/postgresql/pljava/pljava-examples-1.6.0-SNAPSHOT.jar as bytes
/home/qin/pg96/share/postgresql/extension/pljava.control as lines (ASCII)
/home/qin/pg96/share/postgresql/pljava/pljava--1.6.0-SNAPSHOT.sql as lines (UTF8)
/home/qin/pg96/share/postgresql/pljava/pljava--unpackaged--1.6.0-SNAPSHOT.sql as lines (UTF8)
/home/qin/pg96/share/postgresql/pljava/pljava--1.5.5--1.6.0-SNAPSHOT.sql as lines (UTF8)
/home/qin/pg96/share/postgresql/pljava/pljava--1.5.4--1.6.0-SNAPSHOT.sql as lines (UTF8)
/home/qin/pg96/share/postgresql/pljava/pljava--1.5.3--1.6.0-SNAPSHOT.sql as lines (UTF8)
/home/qin/pg96/share/postgresql/pljava/pljava--1.5.2--1.6.0-SNAPSHOT.sql as lines (UTF8)
/home/qin/pg96/share/postgresql/pljava/pljava--1.5.1--1.6.0-SNAPSHOT.sql as lines (UTF8)
/home/qin/pg96/share/postgresql/pljava/pljava--1.5.1-BETA3--1.6.0-SNAPSHOT.sql as lines (UTF8)
/home/qin/pg96/share/postgresql/pljava/pljava--1.5.1-BETA2--1.6.0-SNAPSHOT.sql as lines (UTF8)
/home/qin/pg96/share/postgresql/pljava/pljava--1.5.1-BETA1--1.6.0-SNAPSHOT.sql as lines (UTF8)
/home/qin/pg96/share/postgresql/pljava/pljava--1.5.0--1.6.0-SNAPSHOT.sql as lines (UTF8)
/home/qin/pg96/share/postgresql/pljava/pljava--1.5.0-BETA3--1.6.0-SNAPSHOT.sql as lines (UTF8)
/home/qin/pg96/share/postgresql/pljava/pljava--1.5.0-BETA2--1.6.0-SNAPSHOT.sql as lines (UTF8)
/home/qin/pg96/share/postgresql/pljava/pljava--1.5.0-BETA1--1.6.0-SNAPSHOT.sql as lines (UTF8)
[qin@pcmk2 target]$ pwd
/home/qin/pljava-master/pljava-packaging/target
2、执行完成后,进入postgresql安装目录下extension子目录,可以发现pljava库文件已经生成
3、在create extension之前还必须设置一个数据库环境变量pljava.libjvm_location,该变量告诉pljava使用的java库文件位置
可以在postgresql.conf中填加:
pljava.libjvm_location=’/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.252.b09-2.el7_8.x86_64/jre/lib/amd64/server/libjvm.so’
该环境变量为会话级,也可以:
SET pljava.libjvm_location TO ‘/usr/lib/jvm/java-1.8.0/lib/…’;
4、psql登录数据库执行create exteinsion:
postgres=# CREATE EXTENSION pljava;
CREATE EXTENSION
5、插件创建成功
postgres=# \dL
List of languages
Name | Owner | Trusted | Description
---------+----------+---------+----------------------------------------------------------------------------------------------
java | postgres | t | Trusted/sandboxed language for routines and types in Java; http://tada.github.io/pljava/
javau | postgres | f | Untrusted/unsandboxed language for routines and types in Java; http://tada.github.io/pljava/
plpgsql | postgres | t | PL/pgSQL procedural language
(3 rows)
测试plsql 调用java程序
1、用idea写一个PL/java 代码
package com.yaogx.test;
public class Hello {
public static String hello(Strring url,String data) {
// 可以通过HTTPClient 远程请求 url。
return "Hello, " + data+ "!";
}
}
实现PL / Java代码的方法有两个简单的规则:
- 它们必须是公共静态的
- 如果任何参数为空 ,他们必须返回null而已。
2、编写manifest.txt
Manifest-Version: 1.0
Main-Class: Hello
Specification-Title: "Hello"
Specification-Version: "1.0"
Created-By: 1.8.0_252
Build-Date: 05/02/2020 21:00
4、加载包含测试程序的Jar包至数据库
pl/java扩展安装之后,sqlj模式下有
install_jar、replace_jar、remove_jar 三个函数用于加载、替换、移除jar包到数据库中
sqlj.replace_jar(<jar_url>, <jar_name>, true)函数包函三个参数
jar_url :jar文件的URL.
jar_name:数据库中映射jar包的逻辑名.
4.1 安装 程序的Jar包
--
-- load java library
--
-- parameters:
-- url_path - where the library is located
-- url_name - how the library is referred to later
-- deploy - should the deployment descriptor be used?
--
select sqlj.install_jar('file:///opt/pglib/cms-pljava-lib.jar', 'cmspljava', true);
4.2 映射逻辑名 cmspljava 到public模式
-- set classpath to include new library.
--
-- parameters
-- schema - schema (or database) name
-- classpath - colon-separated list of url_names.
--
select sqlj.set_classpath('public','cmspljava');
4.3 替换程序jar
-- reload java library
--
select sqlj.replace_jar('file:///opt/pglib/cms-pljava-lib.jar','cmspljava', true);
4.4 移除 程序 jar
-- remove java library
--
-- parameters:
-- url_name - how the library is referred to later
-- undeploy - should the deployment descriptor be used?
--
select sqlj.remove_jar('cmspljava', true);
4.5 查询映射是否成功
-- list classpath
--
select sqlj.get_classpath('public');
记住要设置类路径,这一点很重要。 库在卸载时会自动从类路径中删除,但安装后不会自动添加到类路径中。
我们还没有完全完成–我们仍然需要将新功能告诉系统。
创建触发器
该触发器会在 新增,删除,修改行字段时,触发该触发器。
-- 2.触发器
CREATE TRIGGER test001Trigger AFTER INSERT OR UPDATE OR DELETE ON "public"."test001"
FOR EACH ROW
EXECUTE PROCEDURE "public"."test001_trigger_fun"();
创建触发器函数
-- 2.触发器函数
CREATE OR REPLACE FUNCTION "public"."test001_trigger_fun"()
RETURNS "pg_catalog"."trigger" AS $BODY$
DECLARE
V_SBSTR varchar(4000);
V_RESULT "varchar";
rw RECORD;
BEGIN
rw := new;
raise notice '变动数据拼接 is: %', rw; -- 输出测试
V_SBSTR := 'test001&serialno='||new.serialno || '&payintv='||new.payintv || '&operator='||new.operator
||'&birthday='||new.birthday|| '&makedate='||new.makedate|| '&maketime='||new.maketime;
raise notice '变动数据拼接 is: %', V_SBSTR; -- 输出测试
V_RESULT := postrequest('http://yaog', V_SBSTR);
raise notice '后端返回结果 V_RESULT is: %', V_RESULT; -- 输出测试
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
创建存储过程
-- 2.存储过程
CREATE OR REPLACE FUNCTION "public"."postrequest"(varchar, varchar)
RETURNS varchar AS
'com.yaogx.test.hello'
LANGUAGE 'java';
测试plsql函数功能
postgres=# select postrequest('http://xuecheng.com/api/hello','Lining');
postrequest
----------------
Hello,data!
(1 row)
postgres=#
根据表名自动生成触发器及触发函数
public ApiResponse queryPGColumnByTableName(String tableName) {
List<String> list = dataBaseOperationsDao.queryPGColumnByTableName(tableName);
StringBuilder sb = new StringBuilder();
sb.append("\nCREATE OR REPLACE FUNCTION ").append(tableName).append("_trigger_fun () RETURNS TRIGGER AS $$ \n" +
" DECLARE \n" +
" V_SBSTR VARCHAR (4000) ;\n" +
" V_RESULT varchar ;\n" +
" V_COLUMN varchar(4000) ;\n")
.append(" V_OPERATION varchar;\n")
.append(" V_TABLE_NAME varchar :=")
.append("\'").append(tableName).append("=\';\n")
.append(" BEGIN \n V_SBSTR := '';\n")
.append(" V_OPERATION := TG_OP;\n")
.append(" \tIF (TG_OP = 'DELETE') THEN \n");
int i = list.size();
for (String str : list) {
i--;
// 删除数据时拼接语句
sb.append(" IF OLD.").append(str).append(" IS NULL THEN \n")
.append(" V_COLUMN := ").append("\'&").append(str).append("=*\';\n")
.append(" ELSE \n V_COLUMN := ").append("\'&").append(str).append("=\'|| OLD.")
.append(str).append(";\n").append(" END IF;\n");
if (i > 0) {
sb.append(" V_SBSTR := V_SBSTR || V_COLUMN;\n");
} else {
sb.append(" V_SBSTR := V_TABLE_NAME || V_OPERATION || V_SBSTR || V_COLUMN;\n")
.append(" ELSIF (TG_OP = 'UPDATE' OR TG_OP = 'INSERT') THEN\n");
}
}
int j = list.size();
for (String str : list) {
j--;
// 新增,修改数据时拼接语句
sb.append("IF NEW.").append(str).append(" IS NULL THEN \n")
.append(" V_COLUMN := ").append("\'&").append(str).append("=*\';\n")
.append("ELSE \n V_COLUMN := ").append("\'&").append(str).append("=\'|| NEW.")
.append(str).append(";\n").append("END IF;\n");
if (j > 0) {
sb.append(" V_SBSTR := V_SBSTR || V_COLUMN;\n");
} else {
sb.append(" V_SBSTR := V_TABLE_NAME || V_OPERATION || V_SBSTR || V_COLUMN;\n");
}
}
sb.append("END IF;\n").append("raise notice '变动数据拼接 is: %',V_SBSTR ; -- 输出测试\n" +
"V_RESULT := postrequest('http://yaog', V_SBSTR); \n" +
"raise notice '后端返回结果 V_RESULT is: %'," +
" V_RESULT ; -- 输出测试\n" +
"RETURN NULL ;\n" +
"END ; \n" +
"$$ \n" +
"LANGUAGE plpgsql; \n\n");
/**
* 生成触发器
* CREATE TRIGGER ladimission_trigger AFTER INSERT OR UPDATE OR DELETE ON "public"."ladimission"
* FOR EACH ROW
* EXECUTE PROCEDURE "public"."ladimission_trigger_fun"();
*/
sb.append("\n-- XXX.触发器 \n");
sb.append("CREATE TRIGGER ").append(tableName).append("_trigger AFTER INSERT OR UPDATE OR DELETE ON \"public\".\"")
.append(tableName).append("\" \n");
sb.append("FOR EACH ROW \n")
.append("EXECUTE PROCEDURE \"public\".\"").append(tableName).append("_trigger_fun\"(); \n");
System.out.println(sb.toString());
ApiResponse apiResponse = new ApiResponse();
apiResponse.setStatus(ApiResponse.SUCCESS);
apiResponse.setData(sb.toString());
return apiResponse;
}
postgresql数据库,查询表中的字段 Mapper.xml 实现
<!--根据表名查询PG库指定表的列名-->
<select id="queryPGColumnByTableName" resultType="java.lang.String" parameterType="java.lang.String">
select COLUMN_NAME from information_schema.columns
where table_schema='public' and table_name=#{tableName}
order by ordinal_position
</select>
参考博客:https://blog.csdn.net/sxqinjh/article/details/105880430