PostgreSQL PL / java简介

现代数据库允许以多种语言编写存储过程。 一种常见的实现语言是java.NB，本文讨论了PostgreSQL特定的Java实现。 其他数据库的详细信息会有所不同，但是概念是相同的。

PL / Java的安装

在Ubuntu系统上安装PL / Java很简单。 我将首先创建一个新模板template_java ，因此我仍然可以创建没有pl / java扩展名的数据库。

在命令行上，假设您是数据库超级用户，请输入

# apt-get install postgresql-9.1
# apt-get install postgresql-9.1-pljava-gcj

$ createdb template_java
$ psql -d template_java -c 'update db_database set datistemplate='t' where datnam='template_java''
$ psql -d template_java -f /usr/share/postgresql-9.1-pljava/install.sql

局限性

预包装的Ubuntu软件包使用Gnu GCJ Java实现，而不是标准的OpenJDK或Sun实现。 GCJ将Java源文件编译为本机目标代码，而不是字节码。 PL / Java的最新版本是“受信任的” –可以依靠它们保留在其沙箱中。 除其他外，这意味着您无法访问服务器上的文件系统。

如果必须打破信任关系，则可以使用第二种语言“ javaU”。 不受信任的函数只能创建一个数据库超级用户。

更重要的是，此实现是单线程的。 如果您需要与其他服务器通信，请记住这一点至关重要。

需要考虑的事情是是否要使用GCJ编译自己的常用库，并将它们作为共享库加载到PostgreSQL服务器中。 共享库位于/usr/lib/postgresql/9.1/lib中 ，稍后我可能要说更多。

快速验证

通过编写快速测试功能，我们可以轻松地检查安装。 使用template_java创建临时数据库，然后输入以下SQL：

CREATE FUNCTION getsysprop(VARCHAR) RETURNS VARCHAR
  AS 'java.lang.System.getProperty'
  LANGUAGE java;

SELECT getsysprop('user.home');

结果，您应该得到“ / var / lib / postgresql”。

安装我们自己的方法

这是一个不错的开始，但是如果我们不能调用自己的方法，那么我们并不会真正受益。 幸运的是，添加我们自己的并不难。

一个简单的PL / Java过程是

package sandbox;

public class PLJava {
    public static String hello(String name) {
        if (name == null) {
            return null;
        }

        return 'Hello, ' + name + '!';
    }
}

实现PL / Java过程的方法有两个简单的规则：

• 它们必须是公共静态的
• 如果任何参数为空 ，他们必须返回null

而已。

将Java类导入PostgreSQL服务器很简单。 假设包类在/tmp/sandbox.jar中，而我们启用Java的数据库是mydb 。 然后我们的命令是

--
-- 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:///tmp/sandbox.jar', 'sandbox', true);

--
-- set classpath to include new library.
--
-- parameters
--   schema    - schema (or database) name
--   classpath - colon-separated list of url_names.
--
select sqlj.set_classpath('mydb', 'sandbox');

-- -------------------
-- other procedures --
-- -------------------

--
-- reload java library
--
select sqlj.replace_jar('file:///tmp/sandbox.jar', 'sandbox', true);

--
-- remove java library
--
-- parameters:
--   url_name - how the library is referred to later
--   undeploy - should the deployment descriptor be used?
--
select sqlj.remove_jar('sandbox', true);

--
-- list classpath
--
select sqlj.get_classpath('mydb');

--

记住要设置类路径，这一点很重要。 库在卸载时会自动从类路径中删除，但安装后不会自动添加到类路径中。

我们还没有完全完成–我们仍然需要将新功能告诉系统。

--
-- create function
--
CREATE FUNCTION mydb.hello(varchar) RETURNS varchar
  AS 'sandbox.PLJava.hello'
  LANGUAGE java;

--
-- drop this function
--
DROP FUNCTION mydb.hello(varchar);

--

现在，我们可以以与其他任何存储过程相同的方式调用我们的java方法。

部署描述符

这里令人头疼–在安装库时必须显式创建函数，而在删除库时将其删除。 除了最简单的情况之外，这都是耗时且容易出错的。

幸运的是，有一个解决此问题的方法-部署描述符。 精确的格式由ISO / IEC 9075-13：2003定义，但是一个简单的示例就足够了。

SQLActions[] = {
  'BEGIN INSTALL
     CREATE FUNCTION javatest.hello(varchar)
       RETURNS varchar
       AS 'sandbox.PLJava.hello'
       LANGUAGE java;
   END INSTALL',
  'BEGIN REMOVE
     DROP FUNCTION javatest.hello(varchar);
   END REMOVE'
}

您必须在jar的MANIFEST.MF文件中告知部署人员有关部署描述符的信息。 一个示例Maven插件是

<plugin>
   <groupId>org.apache.maven.plugins</groupId>
   <artifactId>maven-jar-plugin</artifactId>
   <version>2.3.1</version>
   <configuration>
      <archive>
         <manifestSections>
            <manifestSection>
               <name>postgresql.ddr</name> <!-- filename -->
               <manifestEntries>
                  <SQLJDeploymentDescriptor>TRUE</SQLJDeploymentDescriptor>
               </manifestEntries>
            </manifestSection>
         </manifestSections>
      </archive>
   </configuration>
</plugin>

现在，数据库将在安装和删除我们的方法时知道它们。

内部查询

存储过程的“大赢家”之一是查询是在服务器本身上执行的，比通过编程接口运行查询要快得多。 我已经看到了一个过程，只需将查询到的循环从客户端移动到服务器，就可以通过Java花费30分钟以上将其缩短至不到一秒的时间。

内部连接的JDBC URL是“ jdbc：default：connection”。 您不能使用事务（因为您处于呼叫者的事务之内），但是只要您停留在单个呼叫中，就可以使用保存点。 我不知道您是否可以使用CallableStatements（还有其他存储过程）–您无法使用1.2版，但Ubuntu 11.10软件包使用1.4.2版。

标量值列表在Java世界中以迭代器的形式返回 ，在SQL世界中以SETOF的形式返回 。

public static Iterator<String> colors() {
        List<String> colors = Arrays.asList('red', 'green', 'blue');
        return colors.iterator();
    }

和

CREATE FUNCTION javatest.colors()
      RETURNS SETOF varchar
      AS 'sandbox.PLJava.colors'
      IMMUTABLE LANGUAGE java;

我添加了IMMUTABLE关键字，因为此函数将始终返回相同的值。 这允许数据库执行缓存和查询优化。

在开始之前，您不需要知道结果，甚至不需要知道结果的大小。 以下是被认为总是会终止的序列，但尚未得到证实。 （不幸的是，我忘记了序列的名称。）作为一个旁注，这不是一个完整的解决方案，因为它不检查溢出-正确的实现应对此进行检查或使用BigInteger。

public static Iterator seq(int start) {
        Iterator iter = null;
        try {
            iter = new SeqIterator(start);
        } catch (IllegalArgumentException e) {
            // should log error...
        }
        return iter;
    }

    public static class SeqIterator implements Iterator {
        private int next;
        private boolean done = false;
        
        public SeqIterator(int start) {
            if (start <= 0) {
                throw new IllegalArgumentException();
            }
            this.next = start;
        }

        @Override
        public boolean hasNext() {
            return !done;
        }

        @Override
        public Integer next() {
            int value = next;
            next = (next % 2 == 0) ? next / 2 : 3 * next + 1;
            done = (value == 1);
            return value;
        }

        @Override
        public void remove() {
            throw new UnsupportedOperationException();
        }
    }

CREATE FUNCTION javatest.seq(int)
      RETURNS SETOF int
      AS 'sandbox.PLJava.seq'
      IMMUTABLE LANGUAGE java;

在所有条件都相同的情况下，最好根据需要创建每个结果。 如果查询具有LIMIT子句，通常可以减少内存占用并避免不必要的工作。

单元组

在ResultSet中返回一个元组。

public static boolean singleWord(ResultSet receiver) throws SQLException {
        receiver.updateString('English', 'hello');
        receiver.updateString('Spanish', 'hola');
        return true;
    }

和

CREATE TYPE word AS (
      English varchar,
      Spanish varchar);

  CREATE FUNCTION javatest.single_word()
      RETURNS word
      AS 'sandbox.PLJava.singleWord'
      IMMUTABLE LANGUAGE java;

返回true表示有效结果，返回false表示无效结果。 可以用相同的方式将复杂类型传递给j​​ava方法-它是一个只读ResultSet ，只包含一行。

元组列表

返回复杂值列表需要一个实现两个接口之一的类。

org.postgresql.pljava.ResultSetProvider

当可以以编程方式或根据需要创建结果时，将使用ResultSetProvider 。

public static ResultSetProvider listWords() {
        return new WordProvider();
    }
 
    public static class WordProvider implements ResultSetProvider {
        private final Map<String,String> words = new HashMap<String,String>();
        private final Iterator<String> keys;
        
        public WordProvider() {
            words.put('one', 'uno');
            words.put('two', 'dos');
            words.put('three', 'tres');
            words.put('four', 'quatro');
            keys = words.keySet().iterator();
        }
        
        @Override
        public boolean assignRowValues(ResultSet receiver, int currentRow)
                throws SQLException {
            if (!keys.hasNext()) {
                return false;
            }
            String key = keys.next();
            receiver.updateString('English', key);
            receiver.updateString('Spanish', words.get(key));
            return true;
        }

        @Override
        public void close() throws SQLException {
        }
    }

和

CREATE FUNCTION javatest.list_words()
      RETURNS SETOF word
      AS 'sandbox.PLJava.listWords'
      IMMUTABLE LANGUAGE java;

org.postgresql.pljava.ResultSetHandle

当方法使用内部查询时，通常使用ResultSetHandle 。

public static ResultSetHandle listUsers() {
        return new UsersHandle();
    }

    public static class UsersHandle implements ResultSetHandle {
        private Statement stmt;

        @Override
        public ResultSet getResultSet() throws SQLException {
            stmt = DriverManager.getConnection('jdbc:default:connection').createStatement();
            return stmt.executeQuery('SELECT * FROM pg_user');
        }

        @Override
        public void close() throws SQLException {
            stmt.close();
        }      
    }

和

CREATE FUNCTION javatest.list_users()
      RETURNS SETOF pg_user
      AS 'sandbox.PLJava.listUsers'
      LANGUAGE java;

介面

我无法在标准maven存储库中获得pljava jar的最新副本。 我的解决方案是从PL / Java源tarball提取接口。 为了方便您在此处提供它们。

ResultSetProvider

// Copyright (c) 2004, 2005, 2006 TADA AB - Taby Sweden
 // Distributed under the terms shown in the file COPYRIGHT
 // found in the root folder of this project or at
 // http://eng.tada.se/osprojects/COPYRIGHT.html
 
package org.postgresql.pljava;

import java.sql.ResultSet;
import java.sql.SQLException;


 // An implementation of this interface is returned from functions and procedures
 // that are declared to return <code>SET OF</code> a complex type.    //Functions that
 // return <code>SET OF</code> a simple type should simply return an
 // {@link java.util.Iterator Iterator}.
 // @author Thomas Hallgren
 
public interface ResultSetProvider
{
 
  // This method is called once for each row that should be returned from
  // a procedure that returns a set of rows. The receiver
  // is a {@link org.postgresql.pljava.jdbc.SingleRowWriter SingleRowWriter}
  // writer instance that is used for capturing the data for the row.
  // @param receiver Receiver of values for the given row.
  // @param currentRow Row number. First call will have row number 0.
  // @return <code>true</code> if a new row was provided,   <code>false</