开发 Derby 存储过程

开发 Derby 存储过程

Derby 存储过程简介使用
在 J2EE 开发中 JDBC(Java Database Connectivity) 是一种常见的数据库连接方式，它为 Java 环境下的应用程序提供了简便的数据库连接，查询和操作功能：

清单 1. JDBC 示例
                
Connection con = DriverManager.getConnection
           ( "jdbc:derby:testdb", "user","password");
              
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT col1, col2, col3 FROM Table1");
while (rs.next()) {
    int x = rs.getInt("col1");
    String s = rs.getString("col2");
    float f = rs.getFloat("col3");
}

由于 JDBC 基于 Java 语言的特性，它在 J2EE 工程开发中受到了极大的欢迎。
而通常在数据库的商业应用中，开发人员会大量使用到存储过程（Stored Procedure）这类技术。存储过程通过将一系列的数据库操作存储于数据库端极大地减少了网络开销，同时也增强了数据库操作的封装性和集成度。
传统的数据库存储过程有其自身特有的语法格式：

清单 2. 传统存储过程示例
                
CREATE PROCEDURE update_salary
(IN employee_number CHAR(6), IN rating INT)
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE EXIT HANDLER FOR not_found
SIGNAL SQLSTATE '02444';

CASE rating
WHEN 1 THEN
UPDATE employee
SET salary = salary * 1.10, bonus = 1000
WHERE empno = employee_number;
WHEN 2 THEN
UPDATE employee
SET salary = salary * 1.05, bonus = 500
WHERE empno = employee_number;
ELSE
UPDATE employee
SET salary = salary * 1.03, bonus = 0
WHERE empno = employee_number;
END CASE;
END @

与 JDBC 代码相比，传统的存储过程语法有相当大的区别。而在最新的 SQL 标准中，加入了Java/JDBC 风格的存储过程定义 SQL - Part 13: SQL Routines and Types Using the Java(TM) Programming Language (SQL/JRT)。对于 Derby 而言，它是一个完全实现于 Java 语言上的关系型数据库，因而 Derby 对数据库存储过程的定义也采用了 SQL 03 中的 Java Routine 标准。
________________________________________
回页首
开发和部署
存储过程的声明
Derby 中存储过程的声明和传统存储过程的声明类似，使用 CREATE PROCEDURE 语句：

清单 3. 存储过程声明示例
                
CREATE PROCEDURE TESTDB.TOTAL_REVENUE(IN THE_MONTH INTEGER, \
                  IN THE_YEAR INTEGER, OUT TOTAL DECIMAL(10,2))
PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA
EXTERNAL NAME 'testPackage.ProcedureClass.total_revenue'

其中 TESTDB 为数据库模式名，TOTAL_REVENUE 为存储过程在此模式中的名字。THE_MONTH，THE_YEAR，TOTAL则为过程的参数名。STYLE JAVA 指定了此过程实现符合 Java SQL Routines 的规则，这也是Derby目前唯一支持的格式。READS SQL DATA 则限定了此存储过程只能对数据库进行查询读取。最后的 JAVA EXTERNAL NAME 指定了此存储过程所使用的代码所在的 Java 类和具体的方法。
存储过程的定义
存储过程的实际定义使用的是纯粹的Java代码。Derby 对此的要求是存储过程的函数定义必须是一个公有类的共有静态方法。所以，在上节中声明的过程 TESTDB.TOTAL_REVENUE 需要被定义于一个包名为 testPackage 的类 ProcedureClass 中的公有静态方法 total_revenue中。以下给出一个样例：

清单 4. 存储过程定义示例
                
package testPackage;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.GregorianCalendar;

public class ProcedureClass {
    public static void total_revenue(int month, int year, BigDecimal[] total)
            throws SQLException {
        Calendar c = GregorianCalendar.getInstance();

        c.set(year, month, 1, 0, 0, 0);
        Timestamp start = new Timestamp(c.getTimeInMillis());

        c.add(Calendar.MONTH, 1);
        Timestamp end = new Timestamp(c.getTimeInMillis());

        Connection connection = DriverManager
                .getConnection("jdbc:default:connection");

        PreparedStatement p = connection
                .prepareStatement("SELECT SUM(amount) FROM REVENUE "
                        + "WHERE rev_time BETWEEN ? AND ?");
        p.setTimestamp(1, start);
        p.setTimestamp(2, end);

        System.out.println(start);
        System.out.println(end);
        ResultSet rs = p.executeQuery();
        if (rs.next()) {
            total[0] = rs.getBigDecimal(1);
        }

        rs.close();
        p.close();
        
        connection.close();
    }
}

在这个样例中我们将查询出给定月份中的收益总和。
我们可以发现 Derby 存储过程的定义和普通的 JDBC 代码没有太多区别，唯一特殊的一点是在存储过程中获取连接有其特殊方式。
在 derby 中，一个存储过程可以有两种获取连接的方式：嵌套方式和非嵌套方式。
在嵌套方式中，我们使用 DriverManager.getConnection("jdbc:default:connection")获取连接。这种方式所获得的连接实质上共享了调用存储过程的 SQL 语句所使用的连接。这样，在存储过程中的语句将自动和父 SQL 语句处于同一事务之中，有效地避免了额外的连接资源，同时也极大减少了多事务并发引起死锁的风险。
而非嵌套方式则直接打开新的数据库连接，在这种方式中存储过程将不受同一事务的管理，所以绝大多数情况下我们使用嵌套方式获取数据库连接。
另外我们也可以注意到，对于 OUT 型参数，相应的 Java 方法对应参数为数组类型。事实上，Derby 会以一个单元素数组作为实参传入到对应方法，而我们在方法实现中，只需要使用形如 total[0] = rs.getBigDecimal(1); 的语句为数组的唯一元素赋值就能确保 OUT 参数被返回调用方。
存储过程的部署
在传统的数据库存储过程中，过程的声明和定义都直接在数据库模式定义中完成。而 Derby 在模式定义中只能完成声明部分，定义部分需要在外部Java代码完成。因而我们需要额外地将先前定义的过程导入到数据库模式中。这涉及几个步骤：
1.    将定义了存储过程的class文件导出为jar包
2.    使用 sqlj.install_jar 过程将jar包导入到数据库。
3.    使用 SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY 将 jar 包加入 classpath。
对于上例：
1.    首先使用 jar 命令，或者 eclipse 导出工具将 ProcedureClass 导出为c:\test.jar。
2.    在 ij 控制台中调用 call sqlj.install_jar('c:\test.jar', 'testdb.totalRev', 0); 将 jar 包导入到 testdb 模式中，并命名为 totalRev。
3.    在 ij 控制台中调用 call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 'testdb.totalRev'); 将 jar 包加入到数据库 classpath 搜索路径中。
________________________________________
回页首
使用限制和约束
虽然 Derby 存储过程的撰写方式和 JDBC 过程几乎完全相同，但如果我们使用的连接方式为嵌套连接时我们还需要注意到 Derby 添加的额外约束：

表 1. Derby 存储过程的使用限制
限制    解释
不能改变连接的autocommit 属性    在嵌套连接中，连接的 autocommit 属性永远为 false。
不能修改调用方已经在使用的表    例如，调用方对表T进行了 select 操作，则不能继而调用一个使用 INSERT，UPDATE，DELETE 语句修改表 T 的过程，同样也不能在过程中 DROP 表 T。

________________________________________
回页首
事务控制
在上一节中提到在嵌套连接中，我们不能改变链接的 autocommit 属性。这是因为 Derby 会自动在存储过程进入前将嵌套连接的 autocommit 置为 false，而同时在过程返回时将 autocommit属性设置为原始值。
而除此之外 Derby 几乎支持 SQL 和 JDBC 规范所定义的各种事务功能。例如，用户可以在Derby 代码中设置事务隔离级，提交或者回滚事务。
然而有一点需要注意的是 Derby 在发生事务级 （transaction severity） 错误时会自动回滚当前事务。而由于事务的特性，一旦事务被回滚则将开始新的事务，因而在某些特定情况下会出现令人费解的现象。例如：

清单 5. 事务操作示例
                
        try {
            /*
             * insert into the person table
             */
            PreparedStatement insertToPerson = connection
                        .prepareStatement("INSERT INTO "
                           + "person(email, business_unit, country, "
                           + "first_access_time, last_access_time) "
                                + "VALUES (?, ?, ?, ?, ?)");
            insertToPerson.setString(1, u_email);
            insertToPerson.setString(2, u_bu);
            insertToPerson.setString(3, u_country);
            insertToPerson.setTimestamp(4, start_time);
            insertToPerson.setTimestamp(5, start_time);
            insertToPerson.executeUpdate();
            insertToPerson.close();
        }
        catch (SQLException e) {
            // some code to handle the exception
        }

        try {
            /*
             * insert into the log table
             */
            PreparedStatement insertToLog = connection
                    .prepareStatement("INSERT INTO log "
                            + "(user_email, user_ip, start_time) "
                            + "VALUES (?, ?, ?)");
            insertToLog.setString(1, u_email);
            insertToLog.setString(2, u_ip);
            insertToLog.setTimestamp(3, start_time);
            insertToLog.executeUpdate();
            insertToLog.close();
        }
        catch (SQLException e) {
            // some code to handle the exception
        }

该示例首先在 person 表中插入一行记录，继而在 log 表中插入另一条记录，多数情况中上述过程片段能够正常运行。然而，如果在向 person 表插入的执行过程中发生了死锁或者超时等待等事务级错误时，过程所使用的连接将会被自动回滚，从而插入 log 表的语句将会执行于一个新的事务过程中。而在此过程被执行完毕后，控制将会被返回给调用此过程的语句，而此时 Derby 会发现整个过程执行过程中有事务级错误发生，而再次将事务回滚，从而导致向 log 表插入的操作也被取消。
虽然这样的行为符合 SQL 标准的规范，但它却使开发人员感到困惑，且非常难以调试。因而Derby 建议我们不在过程内部对事务级错误进行处理，而永远将对事务级错误的处理交给调用过程的一方。
例如，使用如下的方式存储过程将只对有限的异常进行处理，而将其余错误交由调用方处理：

清单 6. 正确的处理方式示例
                
        try {
                preparedStatement.execute();
        }
        catch (SQLException se ) {
                String SQLState = se.getSQLState();
                if ( SQLState.equals( "23505" ) )  {             
                        correctDuplicateKey(); }
                else if ( SQLState.equals( "22003" ) ) {
                        correctArithmeticOverflow(); }
                else {
                        throw se;
                }
        }

________________________________________
回页首
小结
SQL03 规范中的 SQL/JRT 部分正式增加了 Java 存储过程的定义。而 Derby 则运用其构建于Java 语言的优势率先对此种定义给出了实现。Java 存储过程依然在发展和完善的过程中，因而目前还有较多的限制的不足之处，不过其简便易用的特点和 JDBC 样式的撰写方式无疑将大大加速 J2EE 项目中数据库端应用的开发过程。

转自：http://www.ibm.com/developerworks/cn/opensource/os-cn-derby-stored-p/