Java下拼接执行动态SQL语句

       在实际业务中经常需要拼接动态SQL来完成复杂数据计算，网上各类技术论坛都有讨论，比如下面这些问题：

        http://bbs.csdn.net/topics/390876591

        http://bbs.csdn.net/topics/390981627

        https://www.linkedin.com/groups/SQL-Query-Help-needed-137774.S.5948812806903119877?trk=groups_items_see_more-0-b-ttl

        http://bbs.csdn.net/topics/390073758

        http://bbs.csdn.net/topics/390611005

             

       拼接动态SQL的一般做法有

      1、使用动态语句

很多数据库都提供了处理动态SQL的语法，如Oracle的EXECUTE IMMEDIATE语句、MSSQL的EXEC和SP_EXECUTESQL、Mysql的预处理语句等。这些功能让我们在数据库端来处理动态查询提供了极大遍历，但这种方式只适用于相对简单地动态查询，复杂的情况经常会采用下面的方式。

2、使用存储过程

对于复杂的情况，一般会在存储过程中来拼接动态SQL。使用存储过程完成相对灵活，但编码复杂度过高，有时运行效率较低。

3、使用其他（如JAVA）程序

       使用外部的其他高级语言（如JAVA）拼接后再交由数据库执行也是一种选择，其灵活性较高，但由于JAVA缺乏对集合计算的支持，完成这些准备工作并不轻松。

 

如果需要执行动态SQL的主控程序是JAVA的，那么可以使用集算器来协助完成动态SQL类计算，集算器是动态解释执行的脚本，可以方便地拼出动态SQL执行。集算器提供了JDBC接口，可以置于Java应用程序与数据库之间，让应用程序继续象访问数据库一样执行集算器脚本，应用结构几乎不用改变。

下面通过例子来说明如何使用集算器完成动态SQL类计算，并集成进JAVA程序。

拼接动态SQL

       在集算器中完成动态SQL拼接，并将拼接后的SQL再交由数据库执行，以查询出目标结果。集算器在完成时并不涉及目标计算，只拼接动态SQL。如下面的需求：

 

参数source和target代表两个结构相同但数据不同的表，但表结构未知。要求以主键为标准用source更新target，比如table1和table2的主键都是A和B，数据如下：

 

       用table2更新table1时，MERGE语句应当如下：

       MERGE INTO table1 as t

       USING table2 as s

       ON t.A=s.A and t.B=s.B

       WHEN MATCHED

       THEN UPDATE SET t.C=s.C,t.D=s.D

       WHEN NOT MATCHED

       THEN INSERT VALUES(s.A,s.B,s.C,s.D)

 

 

        实现脚本：

 

        A1,A2: 从系统表中读出表source的主键存入变量pks，计算结果为集合["A","B"]。各种数据库获得主键的方法不同，这里以MSSQL为例。

 

        A3,A4：读出source的完整字段，columns的计算结果为["A","B","C","D"]。

 

        A5：动态生成MERGE语句。pks.(…)是循环函数，可对集合（包括结果集）的成员依次计算，计算中可用~引用循环变量，用#引用循环计数。

 

        A6：执行MERGE语句。

 

由于表结构未知，用存储过程或JAVA获得表结构再动态拼出SQL非常麻烦。使用集合类计算支持良好的集算器来做，代码简单，脚本通用，易于维护。

       集算脚本的计算结果可以作为报表数据源供报表使用，还可以在JAVA程序中通过JDBC的方式读取并使用，JAVA读取调用集算脚本代码如下：

           Class.forName("com.esproc.jdbc.InternalDriver");

                    con= DriverManager.getConnection("jdbc:esproc:local://");

                    //调用集算器脚本（类似存储过程），其中p1是集算脚本的文件名

                    st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call p1()");

st.setObject(1,"table1");

                    st.setObject(2," table2");

 

                        //执行脚本

                    st.execute();

                     ……

调用集算器脚本和访问数据库的方法完全一样，熟悉JDBC的程序员可以很快掌握。

关于集算器JDBC的部署和调用的更详细信息可参考集算器集成应用之被JAVA调用。

动态表间连接

       相对静态的表间连接，动态表间连接事先并不知道要使用的表。如下面的数据查询：

       A表

 

        B表

 

        C表

 

        现需要根据A表的TableName获取B表或C表对应ID的Num值。

 

        目标结果：

 

        实现脚本：

 

        A1：执行SQL从A表取数；

        A2：先按TableName分组，循环分组拼接动态查询语句，最后把查询结果按照ID排序。

 

       通过集算器的集合计算能力（分组后仍然保存着分组成员供后续使用），让动态SQL的拼接工作简单化。

特殊格式数据更新

       除了动态数据查询，有时还需要进行动态更新，更新的数据经常来源于第三方程序，其格式也多种多样，如JSON格式、XML等。在特殊的业务背景下，有时需要将这些较特殊格式（相对传统的二维表来说）的数据更新到（关系）数据库中。这就需要借助第三方程序完成，而像JAVA等高级语言存在缺少类库、硬编码困难等问题。这时可以采用集算器来完成，下面来看一个集算器解析JSON格式文件入库的例子，源数据如下：

 

        要求：将上述内容中指定节点，主要是imei的Service列表更新到数据库2张表groups和Services中。

 

这里的JSON串由于包含多层且很多层都是动态的（如LIST和SERVICES下的节点数量和名称都不固定），这为解析带来了很大难度；而且其中属性名部分还包含空格（如MOVISTAR SPAIN）和点号（如Requires.Network）这也大大增加了解析难度，使用JAVA非常难写。

 

        实现脚本：

 

        A1：读入JSON格式文件，结果为带有层次的结果集；

 

        A2-A3：创建存储更新内容的两个空序表；

 

        A4-D10：循环A1，动态解析内容并将解析结果输出到A2、A3目标结果序表中；

 

        A11-A12：执行更新，将A2、A3序表更新到groups和services表中。