使用HSqlDB的SQL/JRT功能

使用HSqlDB的过程中，因为业务需求要执行动态SQL时，才突然发现HSqlDB不具有类似于SQL Server等数据库的Exec的功能。于是一番探索之后就有了本文。
SQL-92提出了支持动态SQL的标准（12 Support for dynamic execution of SQL language）。各种主流的关系型数据库也提供了执行方法，比如SQL Server的Exec和sp_executesql。

SQL-99提出了SQL/JRT的概念，这其实也可执行动态SQL。简单来说，SQL/JRT就是提供了在SQL中访问Java的static method的能力，它也被称为Java Procedure。从维基说明来看，Oracle、DB2和HSqlDb都实现了SQL/JRT。HSqlDb的文档的第八章中也详细介绍了Java Language Routines (SQL/JRT)，下面以HSqlDb为例，说明SQL/JRT的基本用法。

第一个例子：
SQL/JRT定义：

CREATE PROCEDURE get_customer(IN id INT, OUT firstname VARCHAR(50), OUT lastname VARCHAR(50))    READS SQL DATA   LANGUAGE JAVA   EXTERNAL NAME 'CLASSPATH:org.hsqldb.test.Test01.getCustomerProcedure'

可以看到，存储过程中实际上需要调用Test01#getCustomerProcedure方法，该方法定义如下：

public static void getCustomerProcedure(int id, String[] firstn, String[] lastn)   throws java.sql.SQLException {   firstn[0] = somevalue;  // parameter out value is assigned   lastn[0] = somevalue;   // parameter out value is assigned}

存储过程调用代码：

CallableStatement c = conn.prepareCall("call get_customer(?,?,?)"); c.setInt(1,5);c.execute();String firstname = c.getString(2);String lastname = c.getString(3);

大家不难看出，两者参数具有对应关系，但是比较特殊的是：OUT参数在Java method中被对应为数组，并且返回时必须放在数组的第一个元素。

第二个例子：
SQL/JRT定义：

CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname VARCHAR(50))   MODIFIES SQL DATA    LANGUAGE JAVA   DYNAMIC RESULT SETS 1   EXTERNAL NAME 'CLASSPATH:org.hsqldb.test.Test01.newCustomerProcedure'

可以看到，此存储过程中实际上需要调用Test01#newCustomerProcedure方法，该方法定义如下：

public static void newCustomerProcedure(String firstn, String lastn,ResultSet[] result) throws java.sql.SQLException {   result[0] = someresultset;  // dynamic result set is assigned}

存储过程调用代码：

CallableStatement c = conn.prepareCall("call new_customer(?,?)"); c.setString(1,"firstName");c.setString(2,"lastName");c.execute();if (c.getMoreResults()){ResultSet rs = c.getResultSet();}

相比上例，该例没有OUT参数，但是却有一个需要返回的记录集（DYNAMIC RESULT SETS 1），Java method处理方式与上例类似，将需要返回的结果赋给了参数数组的第一个元素。

SQL/JRT中调用的Java method必须注意以下事项：
1. 定义必须为static，而且返回值必须为void；
2. 存储过程与Java method必须参数对应。对于OUT参数，method中参数必须声明为数组，并且返回值必须作为数组第一个元素；
3. Java method如需进行JDBC操作，可使用外部Connection对象，也可使用SQL/JRT提供的jdbc:default:connection。
第三点大家可能不是很明白，还是看看下面的例子吧。
SQL/JRT定义：

CREATE PROCEDURE proc1(IN P1 INT, OUT P3 INT) SPECIFIC P2 LANGUAGE JAVA DETERMINISTIC MODIFIES SQL DATA  EXTERNAL NAME 'CLASSPATH:org.hsqldb.test.TestStoredProcedure.procTest2'");

Java method定义：

public static void procTest2(int p1, Integer[] p3) throws java.sql.SQLException {     Connection conn = DriverManager.getConnection("jdbc:default:connection");     java.sql.Statement stmt = conn.createStatement();     stmt.execute("INSERT INTO MYTABLE VALUES(" + p1 + ",'test1')");     java.sql.ResultSet rs = stmt.executeQuery("select * from MYTABLE");     java.sql.ResultSetMetaData meta = rs.getMetaData();     int cols  = meta.getColumnCount();     p3[0] = Integer.valueOf(cols);     rs.close();     stmt.close(); }//orpublic static void procTest2(Connection conn, int p1, Integer[] p3) throws java.sql.SQLException

两种定义方式效果是等价的。第一种不要调用conn.close()去试图关闭SQL/JRT维护的Connection。第二种SQL/JRT会自动忽略Java method的第一个Connecton参数。
下面列出两种数据类型的对应关系：

SQL/JRT类型Java类型SMALLINTshort or ShortINTint or IntegerBIGINTlong or LongNUMERIC  or DECIMALBigDecimalFLOAT  or DOUBLEdouble or DoubleCHAR or VARCHARStringDATEjava.sql.DateTIMEjava.sql.TimeTIMESTAMPjava.sql.TimestampBINARYByte[]BOOLEANboolean or BooleanARRAY of any typejava.sql.ArrayTABLEjava.sql.ResultSet

看到这里，大家应该明白怎么在HSqlDB中执行动态SQL了吧......