实战 Java 存储过程的编写及在 DB2 上的部署【来源于网络】

cnq

　　在一个设计良好的数据库应用中，存储过程（Stored Procedure）几乎是必不可少的。存储过程可以有效的节约查询的网络开销，并且降低应用部署的难度。Java 存储过程是 DB2 存储过程的一个重要分类，既拥有 Java 语言的灵活性又拥有一般存储过程的优点，同时可以让大量的 Java 开发人员只需经过简单的学习，就可以编写服务器端的存储过程应用。
      DB2 对 Java 存储过程一直有着良好的支持，DB2 各个版本均自带有 JDK 并且已经配置妥当，用户无需复杂的操作即可直接开始部署 Java 存储过程。并且 DB2 已经提供了整套存储过程部署的工具以方便数据库管理人员。
    在 DB2 中Java 存储过程是一个相对独立的模块，它运行于 JVM 之上而不是直接运行于数据库之中，与用 SQL 写成的存储过程相比，Java 存储过程的编写方法更类似于传统的 Java 应用。下图简单的表示了 DB2 中的执行 Java 存储过程的体系结构：
　　
图 1. DB2 中的执行 Java 存储过程的体系结构

　　当然，与一般的 Java 程序相比，Java 存储过程在其设计、编写过程中，还是有许多不同之处。本文之后的章节以一个 Java 程序员的角度，从一个简单的例子出发，逐步介绍了如何编写一个 Java 存储过程，并在 DB2 中进行部署及调用该存储过程。
　　Java 存储过程的编写
　　在本章节中，我们将通过把一个程序修改为 DB2 中的一个存储过程，来介绍一般存储过程的编写方式。首先我们设计一个简单的数据库查询程序：
　　
清单 1 一个简单的数据库调用函数

public ResultSet hello(int intp) throws ClassNotFoundException, SQLException { Class.forName("com.ibm.db2.jcc.DB2Driver"); Connection conn = DriverManager.getConnection( "jdbc:db2://localhost:50000/test", "user", "password"); String sql = "SELECT * from t1 where c1 = ?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, intp); ResultSet rs = ps.executeQuery(sql); return rs; }

　　下面一步步将其改写为一个 DB2 标准的 Java 存储过程：
　　步骤 1：将函数修改为 public static 函数
　　就像普通 Java 程序中的 main 函数一样，作为入口点的存储过程函数，必须是 public 和 static 的。当数据库用户调用这个存储过程时，数据库会把用户传入的参数进行适当的处理，并传给这个 Java 方法。Java 方法执行完毕之后，将结果返回给数据库。
　　步骤 2：修改函数返回值的方式
　　我们知道，数据库中的存储过程是没有函数返回值的概念的，所有的返回值都是通过参数来传递的。在 Java 中并没有 C 语言中的指针的概念，因此想要把返回值放在参数中，就必须要使用数组。从数组中提取返回值的操作由 DB2 完成，我们只需要把需要返回的参数声明为一个数组。具体方法可以参考下面的例子。
经过前两步的修改，我们的程序变为：
　　
清单 2 初步修改的函数

public static void hello(int inp, ResultSet[] rs) throws ClassNotFoundException, SQLException { Class.forName("com.ibm.db2.jcc.DB2Driver"); Connection conn = DriverManager.getConnection( "jdbc:db2://localhost:50000/test", "user", "password"); String sql = "SELECT * from t1 where c1 = ?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, intp); rs[0] = ps.executeQuery(sql); }

　　步骤 3：修改函数中的 connection
　　这一步是可选的，要依据数据库的需要的具体逻辑来定。一般来说，存储过程中的数据库连接不应该硬编码到程序里，而是获得当前默认的数据库连接——也就是该存储过程安装到的那个数据库。经过这一步的修改，我们的程序变为：
　　
清单 3 可用作存储过程的函数

public static void hello(int inp, ResultSet[] rs) throws ClassNotFoundException, SQLException { Class.forName("com.ibm.db2.jcc.DB2Driver"); Connection conn = DriverManager .getConnection("jdbc:default:connection"); String sql = "SELECT * from t1 where c1 = ?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, intp); rs[0] = ps.executeQuery(sql); }

　　经过这 3 步的修改，我们的 Java 程序已经变成了一个标准的 Java 存储过程程序了。
　　步骤 4：为存储过程加上更多的返回值（可选）
　　存储过程可以有多个返回值，并且也可以返回多个结果集，前面我们的示例程序过于简单，还没有覆盖这部分的内容。当然这一步并非必需的，对于许多简单的应用来说，这样的存储过程已经足够了。让我们给这个函数加上更多的返回值，修改后的结果如下：
　　
清单 4 加入了更多返回值的函数

public static void hello(int inp, int[] outp, int[] inoutp, ResultSet[] rs, ResultSet[] rs2) throws ClassNotFoundException, SQLException { Class.forName("com.ibm.db2.jcc.DB2Driver"); Connection conn = DriverManager .getConnection("jdbc:default:connection"); String sql = "SELECT * from t1 where c1 = ?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, intp); rs[0] = ps.executeQuery(sql); outp[0] = inp * 2; inoutp[0] = inoutp[0] + 1; Statement s2 = conn.createStatement(); rs2[0] = s2.executeQuery("SELECT * from t1"); }

　　相应的存储过程中参数的定义为 (IN name VARCHAR(10), IN inp INTEGER, OUT outp INTEGER, INOUT inoutp INTEGER)。
　　该存储过程返回两个结果集，并且包含一个传出参数和一个可同时传入传出的参数。在 Java 中读取他们的方法在后面章节有介绍。把函数声明和存储过程的参数对照如下表：
　　
表 1 存储过程参数和 Java 函数声明对照表


存储过程参数
示例
Java 参数
示例


IN
IN in INTEGER
不使用数组
int in


OUT
OUT out INTEGER
使用数组
int[] out


INOUT
INOUT inout INTEGER
使用数组
int[] inout


返回结果集
IN in INTEGER
无需定义
ResultSet[] rs
　　读者可对照此表，修改自己需要的存储过程定义。
　　在 DB2 中部署 Java 存储过程
　　Java 存储过程必须要部署到 DB2 数据库服务器实例的文件系统中，才能被定位、加载和调用执行。完成 Java 存储过程的编写后，使用 JDK 提供的编译器将源程序编译为 Java class 文件，之后便可以将其部署在 DB2 实例上。对于编译后产生多个 Java class 文件的复杂程序，可以使用 JDK 提供的打包工具将其打包到 JAR 文件中以便于部署。打包 JAR 文件的方法与一般 Java 应用相同，并无特殊要求，这里就不再赘述了。需要注意，打包的时候要把我们的工程所依赖的类全部打包进去，以防止将来调用时出现找不到类的异常。部署的过程分成两步：

• 安装 Java class 文件或 JAR 文件到 DB2 数据库服务器实例上；
  
• 注册 Java 存储过程。
  

　　安装 Java class 文件或 JAR 文件到 DB2 数据库服务器实例上
　　安装 Java class 文件的默认路径是目标 DB2 实例安装路径下的 function 文件夹。通常情况下，它在 Windows 操作系统中位于 SQLLIB\FUNCTION，而 UNIX 操作系统中位于 sqllib/function。确保将 JDK 编译器生成的完整文件结构拷贝到该文件夹中。例如在 Windows 下，com.ibm.sp.TestSP 类生成的 Java class 文件 TestSP.class 应被复制到 SQLLIB\function\com\ibm\sp 下。如果安装 Java class 文件到其他路径，确保被包括在 DB2 实例运行 Java 存储过程的 JVM 的启动变量 CLASSPATH 中，详细情况请参考文献 1。
　　安装 JAR 文件到目标 DB2 实例下需要在一个 DB2 客户端执行 DB2 内置的存储过程。DB2 提供了一组存储过程进行 JAR 文件的管理，包括安装，替换和卸载。下表列出了它们的名称和用法：
　　
表 2 DB2 管理 JAR 文件的存储过程


名称
功能
用法


sqlj.install_jar
将 JAR 文件安装到 DB2 服务器端
CALL sqlj.install_jar( jar-url, jar-id )


sqlj.replace_jar
用一个新的 JAR 文件副本替换 DB2 服务器端存在的 JAR 文件
CALL sqlj.replace_jar( jar-url, jar-id )


sqlj.remove_jar
从 DB2 服务器端删除 JAR 文件
CALL sqlj.remove_jar( jar-id )


sqlj.refresh_classes
让 DB2 刷新 JAR 文件中包括的所有 class 文件以获得更新
CALL sqlj.refresh_classes
　　注意：

• jar-url 表示 JAR 文件能够被定位并加载路径的 URL 表示，DB2 只支持’ file: ’一种模式；
  
• jar-id 是一个最长为 128 个字符的字符串，用来在 DB2 实例上唯一地标识一个 JAR 文件。
  

　　如果更新了 class 文件或者 JAR 文件，需要调用 sqlj.refresh_classes 存储过程进行刷新，否则 DB2 实例仍然使用旧副本中的 Java class 文件。
　　例如，将 TestSP.class 打包到 TestSP.JAR 文件中之后，可以使用 CALL sqlj.install_jar( ' file:///<path>/TestSP.JAR ', 'TESTSP' ) 将其安装在 DB2 实例上；使用 CALL sqlj.replace_jar( ' file:///<path>/TestSP_NEW.JAR ', ' TESTSP ') 和 CALL sqlj.refresh_classes 将 DB2 实例上的 TestSP.JAR 更新为 TestSP_NEW.JAR；使用 CALL sqlj.remove_jar(' TESTSP ') 卸载这个 JAR 文件。
　　注册 Java 存储过程
　　完成安装后，使用 CREATE PROCEDURE 语句注册存储过程。CREATE PROCEDURE 语句的参数格式和所有选项信息请参考文献 2，下面是其中几个重要的选项：

• DYNAMIC RESULT SETS：指定存储过程返回几个结果集。存储过程也可能不返回结果集，此时该选项值为 0。
  
• LANGUAGE：对于 Java 存储过程使用 JAVA。
  
• EXTERNAL NAME：指定存储过程的 class 文件或 JAR 文件的位置以及文件内的方法。文件的默认位置是目标 DB2 实例的 function 文件夹，也可以指定文件实际位置的完整路径。
  
  
  
  • 对于 class 文件，格式为 class_id.method_id，例如 com.ibm.sp.TestSP.hello
    
  • 对于 JAR 文件，格式为 jar-id:class_id.method_id，例如 TESTSP:com.ibm.sp.TestSP.hello
    
  
  
  
• FENCED / NOT FENCED：指定存储过程是否被认为可以在数据库管理器操作环境的进程或地址空间中“安全地”运行。对于 Java 存储过程一般应指定为 FENCED，只有在确定代码能够安全执行的情况下，才应该使用 NOT FENCED 选项。
  
• threadsafe / NOT THREADSAFE：指定 FENCED 存储过程的执行是否是线程安全的。仅对 FENCED 存储过程起作用，因为 NOT FENCED 过程默认被指定为 THREADSAFE。
  
• PARAMETER STYLE：对于 Java 存储过程，只能是 PARAMETER STYLE JAVA 或 PARAMETER STYLE DB2GENERAL。
  

　　清单 5 显示一个 CREATE PROCEDURE 语句示例，其中使用了上述选项：
　　
清单 5. CREATE PROCEDURE 语句示例

create procedure db2admin.test_sp_call (in inp integer, out outp integer, inout inoutp) dynamic result sets 2 language java external name 'testsp:com.ibm.sp.testsp.hello fenced threadsafe parameter style java;

　　注意: 存储过程的定义务必要与 Java 函数的定义保持一致，否则存储过程会创建失败。
　　在 Java 中调用存储过程
　　在 JDBC 中已经提供了调用存储过程的方法，利用 JDBC 就可以方便的调用存储过程。下面代码片段展示了如何 JDBC 中如何调用一个存储过程：
　　
清单 6 通过 JDBC 调用存储过程

String callsp = "call TESPSP (?,?,?)"; CallableStatement callStmt = conn.prepareCall(callsp); callStmt.setInt(1, 1); callStmt.registerOutParameter(2, Types.INTEGER); callStmt.setInt(3, 1); callStmt.registerOutParameter(3, Types.INTEGER); callStmt.executeUpdate();

　　该存储过程第一个参数为输入参数，第二个参数为输出参数。注意到我们在 CallableStatement 对象中注册参数的时候，对着两种类型的参数用了两个不同的方法去注册。第三个参数为输入输出参数，则需要同时用两个方法去注册。
　　在存储过程执行成功后，即可通过 JDBC 提供的方法对结果进行访问。下面的代码片段展示了如何取回该存储过程的两个结果集，以及输出的参数：
　　
清单 7 通过 JDBC 查询存储过程返回结果

ResultSet rs = callStmt.getResultSet(); if (callStmt.getMoreResults()){ ResultSet rs2 = callStmt.getResultSet(); } int out = callStmt.getInt(2);

　　当存储过程有返回多个结果集时，应该用 getMoreResults() 方法遍历所有的结果集。对 OUT 类型的参数，获得方法是直接用 getXXX() 方法从 CallableStatement 对象中获得。
　　存储过程开发的注意事项
　　Java 存储过程与本地运行的 Java 程序运行环境有很大的不同，因此在开发的过程中，有许多独特的问题需要注意。本章节中，笔者会结合以往的一些开发经验和心得，来介绍在 Java 存储过程开发中一些常见问题的解决方案。
　　线程安全
　　前面的章节已经介绍过，在部署 Java 存储过程的时候，一个参数是 THREADSAFE，这个参数表明该存储过程是否可以安全的被其他例程所调用。默认情况下 Java 存储过程是使用 THREADSAFE 参数的。因此在我们开发中，要注意通常所说的线程安全性问题。一般来讲，在 Java 程序中就不应该随意使用静态变量，对于各种 I/O 操作也要更加小心。关于线程安全性的问题，在许多 Java 编程的参考书中都有详细介绍，在此我们就不展开论述了。
　　宿主环境的差异
　　许多数据库开发人员常常忽视的一个问题，我们在本地进行 Java 存储过程开发，但是这个存储过程最终是要部署到数据库服务器上执行的。执行存储过程的 JVM 是宿主机的 JVM，他上面的配置环境可能会有一些不同。JVM 本身并不是 DB2 的组件之一，对于 JVM 参数的调整，并不一定能够通过调节 DB2 的参数来实现。
例如最常见的多语言问题，在宿主机上返回的时间格式，小数点格式等等，都可能与我们预想的不同。根据笔者的经验，在 JAVA 存储过程的编写过程中，最好能在日志中记录运行时的环境变量，当发生问题时能够比较方便的进行调试。我们可以用如下 Java 代码来获得当前的环境变量：Properties sysProps = System.getProperties()。在出现问题时，这些信息常常是非常有用的。
　　数据库上存储过程的执行用户
　　我们这里所讲的执行用户，并非是哪个 DB2 的用户去调用了这个存储过程，而是这个存储过程是在服务器的哪个用户空间之中运行的。举例来说，我们有一个用户 user1 调用了存储过程 TEST_SP_CALL，该存储过程并非是在服务器端 user1 这个用户空间内来执行的（服务器上甚至可能不存在这个用户），真正执行这个存储过程的用户是数据库内建的账号：比如在 Windows 系统上，默认是 db2admin；在 AIX 系统上，默认则是 db2fenc1。
　　假如存储过程只是进行数据库方面的操作，这个差异并不会造成问题。但是假如在存储过程中还涉及文件存取等涉及系统权限的操作，就一定要注意数据库管理账号是否有相应的权限。同时如果我们希望修改存储过程执行时的某些环境变量，也要在相应的用户环境里面去修改。
　　JDK 的版本
　　虽然 DB2 在发布的时候都会带有 JDK，但是用户也可以手工修改来指定他们自己的 JDK 环境。JDK 只能向下兼容，也就是较低版本的 JDK 不能加载更高版本编译的 class 文件。当开发人员需要编写需要部署到许多不同的数据库上的存储过程时，将 Java 的编译级别设为能兼容较低版本的 JDK 是一个比较稳妥的方法。以 Eclipse 集成开发工具为例，修改 Java 的编译兼容级别的方法如下：
　　在工程上点右键选择 Properties 菜单，之后选择 Java Compiler 选项页。
　　
图 2. Eclipse 中设置 Java 编译选项

　　此图中笔者将工程的兼容性设置设置为了 JDK1.4，即在 1.4 以上的 JDK 上都可运行。
　　假如出现了 Java class 版本高于 JDK 版本造成不能加载的情况，可以通过查看 db2diag.log 里面的错误信息来发现。
　　例子：当用户将 DB2 的 JDK 配置为 1.4，并试图加载一个编译 Java 1.5 环境的存储过程时，可在 db2diag.log 里面看到如下出错信息。
　　
清单 8 db2diag.log

2011-03-24-11.06.26.273703-240 I12591220A1084     LEVEL: Warning PID     : 12903                TID  : 12          PROC : db2fmp (Java) 0 INSTANCE: ccintapp             NODE : 000 EDUID   : 12                   EDUNAME: db2fmp (Java) 0 FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10 DATA #1 : String, 659 bytes java.lang.UnsupportedClassVersionError: com/ibm/sp/TestSP (Unsupported major.minor version 49.0) at java.lang.ClassLoader.defineClass0(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader.java:539) at java.lang.ClassLoader.defineClass(ClassLoader.java:448) at com.ibm.db2.jcc.uw.classloader.b.a(b.java:236) at com.ibm.db2.jcc.uw.classloader.a.loadClass(a.java:85) at java.lang.ClassLoader.loadClass(ClassLoader.java:235) at com.ibm.db2.jcc.uw.classloader.PowerClassLoader.a(PowerClassLoader.java:71) at com.ibm.db2.jcc.uw.classloader.a.loadClass(a.java:85) at java.lang.ClassLoader.loadClass(ClassLoader.java:235) DATA #2 : Hexdump, 4 bytes 0xFFFFFFFF664FE4AC : 0000 0000

　　JDK 会抛出一个 UnsupportedClassVersionError 错误，在错误说明中出现的 49.0 是 Java 1.5 的内部版本号。
　　总结和结论
　　在一个设计良好的数据库应用中，存储过程（Stored Procedure）的使用几乎是必不可少的。使用存储过程可以有效的节约查询的网络开销，并且降低客户端应用部署的难度。对于许多初级的数据库开发人员来说，编写一个拥有复杂逻辑的存储过程是一件非常头疼的事情。Java 存储过程由于其高级语言的灵活性，是刚刚进入数据库领域的开发人员的非常好的选择。对于一个熟练的 Java 开发人员，只需经过简单的学习，就可以编写出完善的服务器端的存储过程应用。本文以一个 Java 程序员的角度，一步步的介绍如何编写出一个完整的数据库存储过程，并将其部署在 DB2 上以及通过 JDBC 的调用的完整步骤。最后本文介绍了一些编写存储过程经常会遇到的问题的解决方案。