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 执行状态等优点。