【新闻翻译】Java JDBC 执行 DDL 和 SQLJ 存储过程

Java JDBC 执行 DDL 和 SQLJ 存储过程


序言
Java JDBC（Java Data Base Connectivity，Java 数据库连接）是一种用于执行 SQL 语句的 Java API，可以为多种关系数据库提供统一访问接口，它由一组用 Java 语言编写的类和接口组成。JDBC 为数据库应用开发人员、数据库前台工具开发人员提供了一个标准的 API，据此可以构建更高级的工具和接口，使数据库开发人员能够用纯 Java API 编写数据库应用程序。
DDL（Data Definition Language）是指数据定义语句用于定义和管理 SQL 模式、基本表、视图、索引和存储过程等数据库中的对象。
SQLJ 由一系列定义了 SQL 与 Java 之间相互作用的子句和程序扩充组成。SQLJ 是在 Java 编程语言中静态嵌入式 SQL。本文研究的 SQLJ 存储过程特指 DB2 数据库提供的内嵌 SQLJ 存储过程，例如 SQLJ.DB2_INSTALL_JAR 存储过程，它用于创建一个新定义的 JAR 文件到特定的数据库。
本文在系统分析利用数据库脚本文件执行 DDL 和 SQLJ 存储过程缺点的基础上，提出并详细介绍了利用 Java JDBC 执行 DDL 和 SQLJ 存储过程的方法。
回页首
方法提出的背景
在 Java 与数据库交互编程过程中，经常遇到这样的场景：需要执行大量的 DDL 语句和 SQLJ 存储过程，并且这些语句之间有着较强的依赖关系。下面是一个这样的例子，SQL 语句中既有多条 DDL 又有对 DB2 内嵌 SQLJ 存储过程的调用。

清单 1. DDL 和 SQLJ 存储过程语句示例

-- -- connect to the &database
connect to &database user &user using &password;
-- -- install Stored Procedure
DROP PROCEDURE DB2TOOL.CALLDB2ADVIS;
CALL SQLJ.REMOVE_JAR ('DB2TOOL.CALLDB2ADVIS');
CALL SQLJ.REFRESH_CLASSES();
CALL SQLJ.INSTALL_JAR('file:/home/luwsp.jar', 'DB2TOOL.CALLDB2ADVIS');------------- ①
CALL SQLJ.REFRESH_CLASSES();
-- -- create Stored Procedure
CREATE PROCEDURE DB2TOOL.CALLDB2ADVIS ( INOUT major_version INTEGER,
         INOUT minor_version INTEGER,
         IN requested_locale VARCHAR(33),
         IN xml_input BLOB(32M),
         IN xml_filter BLOB(4K),
         OUT xml_output BLOB(4K),
         OUT xml_message BLOB(64K) )
DYNAMIC RESULT SETS 3
NOT DETERMINISTIC
LANGUAGE Java
EXTERNAL NAME 'DB2TOOL.CALLDB2ADVIS:com.ibm.datatools.ia.luw.CALLDB2ADVIS.cALLDB2ADVIS'
FENCED
THREADSAFE
PARAMETER STYLE JAVA;      --------------------------------------------------------- ②
-- -- grant the execution privilege to public    
GRANT EXECUTE ON PROCEDURE DB2TOOL.CALLDB2ADVIS TO PUBLIC WITH GRANT OPTION;
connect reset;
terminate;

在上面的 SQL 语句中，语句之间的依赖性很强。例如，如果语句①不能正确执行，直接影响到语句②的执行，因为它们之前存在着引用关系。针对这种情况的 Java 数据库交互编程，通常采用将这些语句封装成一个数据库 SQL 脚本文件去执行，主要执行过程如下：
准备数据库脚本文件的执行环境，主要是对一些环境变量的设置；
运行数据库脚本文件，把输出结果定向到特定的文件；
Java 程序系统的分析数据库脚本文件的输出结果，得到每一条 SQL 的运行状况；
清除数据库脚本文件的执行环境，还原到初始状态。
这种运行 DDL 和 SQLJ 存储过程的方法，存在着以下几个缺点：
依赖数据库环境。需要在过程的开始阶段，对脚本文件的执行环境进行初始化，在脚本文件运行结束后，必须对环境进行清除；
难以对执行过程进行控制。例如在清单 1 中的 SQL 语句执行过程中，如果语句①执行失败，脚本文件不会终止运行并把错误信息发送给 Java 程序，而会继续执行下一条 SQL 语句，这时可以确定语句②必定执行失败，但是脚本文件还是强制执行语句②；
脚本文件的输出结果难以处理。由于 SQL 语句在不同的数据库环境下，输出结果的格式信息有所变化，这就极大的影响了 Java 程序读取输出结果的准确性，难以精确的定位到出现问题的 SQL 语句；
本文针对执行 DDL 和 SQLJ 存储过程数据库脚本文件引出的这些缺点，提出了利用 Java JDBC 执行 DDL 和 SQLJ 存储过程的方法。方法的一些简单示例代码如下。

清单 2. 简单的示例代码

Driver dbDriver=(Driver)Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
        String url="jdbc:db2://"+host+":"+port+"/"+database+"";
        Properties p = new Properties();
        p.put("user", username);
        p.put("password",password);
        conn= dbDriver.connect(url,p);
    try{
        stat=conn.createStatement();
        stat.executeUpdate(“DROP PROCEDURE DB2TOOL.CALLDB2ADVIS”);
        stmt = conn.prepareCall(“CALL SQLJ.REFRESH_CLASSES()”);
        stmt.execute();                    
    }
    catch(SQLException e){
        System.out.println(e.getMessage());
    }
        stat.close();
        stmt.close();
        conn.close();

通过上面的示例代码，可以了解到利用 Java JDBC 执行 DDL 和 SQLJ 存储过程的基本步骤：
建立数据库连接；
根据需要创建合适的 Java JDBC Statement 对象；
根据 SQL 语句选择合适的执行方法。
在实际的 Java 与数据库交互编程的环境中，总结分析这种方法具有下列优点：
容易与 Java 程序进行交互。可以获取每一条 SQL 语句的执行情况，通知 Java 程序选择运行对应的处理逻辑；
不依赖数据库环境。完全脱离了数据库环境的限制，能对本地或远程的数据库进行高效的数据操作。
能精确的获取每条 SQL 语句的执行状态。如果某一条 SQL 语句运行失败，Java 程序能及时的捕获到对应的异常信息。
由于 Java JDBC 是通过 Statement 对象来执行 SQL 语句的，所以它是执行 DDL 和 SQLJ 存储过程的入口，下面将详细介绍 JDBC 包含的几种 Statement 对象。
回页首
执行 DDL 和 SQLJ 存储过程的入口：Java JDBC Statement
Java JDBC Statement 对象用于将 SQL 语句发送到数据库中。实际上有三种 Statement 对象，它们都作为在给定连接上执行 SQL 语句的包容器：Statement、PreparedStatement 和 CallableStatement。它们都专用于发送特定类型的 SQL 语句。三种 Statement 对象的关系如图 1 所示。

图 1. 三种 Statement 对象关系

Statement 对象用于执行不带参数的静态 SQL 语句，提供了执行语句和获取结果的基本方法。它的 execute(String sql) 和 executeUpdate(String sql) 方法支持执行 DDL 语句。示例代码如下。

清单 3. Statement 对象的示例代码

Statement stat=conn.createStatement();
stat.executeUpdate(“DROP PROCEDURE DB2TOOL.CALLDB2ADVIS”);
stat.execute(“DROP FUNCTION DB2TOOL.DEMO_LIC”);
stat.close();

PreparedStatement 对象用于执行带或不带 IN 参数的预编译 SQL 语句，它从 Statement 继承而来，添加了处理输入参数的方法，有防止 SQL 注入的功能，还有较好的执行效率。它的 execute() 和 executeUpdate() 方法支持执行 DDL 语句。使用示例代码如下。

清单 4. PreparedStatement 对象的示例代码

PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
SET SALARY = ? WHERE ID = ?");
    pstmt.setBigDecimal(1, 153833.00);
    pstmt.setInt(2, 110592);
    pstmt.executeUpdate();
    pstmt.close();

CallableStatement 对象用于执行对数据库已有存储过程的调用，它从 PreparedStatement 继承而来，添加了处理输出参数的方法。它的 execute() 和 executeUpdate() 方法支持执行 DDL 语句。使用示例代码如下。

清单 5. CallableStatement 对象的示例代码

CallableStatement cstm = connection.prepareCall("CALL SQLJ.REMOVE_JAR(?)");
    cstmt.setString(1, “test”);
    cstmt.execute();
    cstmt.close();

回页首
Java JDBC 执行 DDL 和 SQLJ 存储过程：实例演示
上一部分详细介绍了 Java JDBC 的三种 Statement 对象，了解了它们之间的关系和特定的操作对象，为利用 Java JDBC 执行 DDL 和 SQLJ 存储过程奠定了理论基础。下面的两个程序实例，将充分利用这三种 Statement 对象，展示这种方法的实现过程及其灵活性。
实例 1 演示利用 Java JDBC 执行 DDL 的方法。需要执行的 DDL 语句如下：

清单 6. DDL 语句示例

DROP FUNCTION DB2TOOL.DEMO_LIC;
CREATE FUNCTION DB2TOOL.DEMO_LIC() RETURNS VARCHAR(8) LANGUAGE SQL CONTAINS SQL
    NO EXTERNAL ACTION DETERMINISTIC RETURN VARCHAR('DEMO_V10');
GRANT EXECUTE ON FUNCTION DB2TOOL.DEMO_LIC TO PUBLIC WITH GRANT OPTION;


清单 7. JDBC 执行 DDL 代码示例

Driver dbDriver=(Driver)Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
    String url="jdbc:db2://"+host+":"+port+"/"+database+"";
    Properties p = new Properties();
    p.put("user", username);
    p.put("password",password);
    conn= dbDriver.connect(url,p);
    try{
        Statement stat=conn.createStatement();
        stat.executeUpdate(“DROP FUNCTION DB2TOOL.DEMO_LIC”);
        PreparedStatement pstmt = conn.prepareStatement(
            "CREATE FUNCTION DB2TOOL.DEMO_LIC() RETURNS VARCHAR(8) LANGUAGE SQL
            CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC RETURN VARCHAR(?)");
        pstmt.setString(1,”DEMO_V10”);
        pstmt.execute();
        stat.execute(“GRANT EXECUTE ON FUNCTION DB2TOOL.DEMO_LIC TO
            PUBLIC WITH GRANT OPTION”);
    } catch(SQLException e){
        System.out.println(e.getMessage());
    }
    stat.close();
    pstmt.close();
    conn.close();

实例 2 演示利用 Java JDBC 执行 SQLJ 存储过程的方法。需要执行的 SQLJ 语句如下：

清单 8. SQLJ 存储过程语句示例

CALL SQLJ.REMOVE_JAR ('DB2TOOL.CALLDB2ADVIS');
CALL SQLJ.REFRESH_CLASSES();
CALL SQLJ.INSTALL_JAR('file:/home/luwsp.jar', 'DB2TOOL.CALLDB2ADVIS');
CALL SQLJ.REFRESH_CLASSES();

由于 SQLJ.INSTALL_JAR 存储过程仅支持在本地数据库创建一个新定义的 JAR 文件，所以在下面的 JDBC 执行代码中使用 SQLJ.DB2_INSTALL_JAR 代替它，扩大它的使用范围。

清单 9. JDBC 执行 SQLJ 存储过程代码示例

Driver dbDriver=(Driver)Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
String url="jdbc:db2://"+host+":"+port+"/"+database+"";
Properties p = new Properties();
p.put("user", username);
p.put("password",password);
conn= dbDriver.connect(url,p);
CallableStatement cstmt = null;    
try{
    cstmt = conn.prepareCall(“CALL SQLJ.REMOVE_JAR (?)”);
    cstmt.setString(1,”DB2TOOL.CALLDB2ADVIS”);
    cstmt.execute();    
    cstmt = conn.prepareCall(“CALL SQLJ.REFRESH_CLASSES()”);
    cstmt.execute();
    File aFile = new File(“/home/luwsp.jar”);
    FileInputStream inputStream = new FileInputStream(aFile);
    cstmt = conn.prepareCall(“Call SQLJ.DB2_INSTALL_JAR(?,?,?)”);
    cstmt.setBinaryStream(1, inputStream, (int)aFile.length());
    cstmt.setString(2,”DB2TOOL.CALLDB2ADVIS”);
    cstmt.setInt(3, 0);
    cstmt.execute();                
    cstmt = conn.prepareCall(“CALL SQLJ.REFRESH_CLASSES()”);
    cstmt.execute();
} catch(SQLException e){
    System.out.println(e.getMessage());
}
cstmt.close();    
conn.close();

上面的两个实例详细的展示了利用 Java JDBC 执行 DDL 和 SQLJ 存储过程的方法，在执行的过程中可以确定每一条 SQL 语句的执行状态，例如三种 Statement 对应的 execute(String sql) 和 execute() 方法能返回 boolean 类型的值，executeUpdate(String sql) 和 executeUpdate() 方法能返回 int 类型的值，可以根据这些返回值精确的确定每条 SQL 的执行状态，另外也可以通过捕获 SQLException 获得执行情况。这两个实例充分体现了本文提出的方法具有灵活性、易于控制执行过程、易于获得 SQL 执行状态等优点。
回页首
总结
本文主要介绍了利用 Java JDBC 执行 DDL 和 SQLJ 存储过程的方法，描述了方法提出的背景，详细学习了 Java JDBC 中的三种 Statement 对象，最后通过两个实例展现了方法实现的过程，进一步证明了使用这种方法，可以使 Java 程序和 DDL,SQLJ 的交互操作变得非常灵活，提高 Java 编程的效率。

参考资料
学习
通过 developerWorks Java technology 专区，学习关于 Java technology 的更多知识。在这里可以找到技术文档、how-to 文章、培训、下载、产品信息等等。

作者：李兆伟 IBM软件工程师