Java拼接动态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表中。