跨库移植 SQL

背景

应用程序可能要基于不同数据库工作，各种数据库的 SQL 语法大体一致，但仍有些差别，结果就要改造这些 SQL，而这事通常只能手工调整，工作量大还容易出错。

完全自动改造 SQL 几乎是无法做到的，毕竟各种数据库很可能功能就不一样。

不过，梳理一下会发现，大部分问题都是由于 SQL 函数写法不同造成的。

特别是日期和字符串相关的函数，业界没有标准，各个数据库各行其是。比如将字符串 "2020-02-05" 转换成日期，不同数据库有不同的写法。

ORACLE：

select TO_DATE('2020-02-05', 'YYYY-MM-DD') from USER

SQL Server：

select CONVERT(varchar(100), '2020-02-05', 23) from USER

MySQL：

select DATE_FORMAT('2020-02-05','%Y-%m-%d') from USER

如果希望应用在不同的数据库之间切换，就需要改写 SQL 语句。

SPL 方案

SPL 针对这个场景提供了 SQL 转换功能，可以将某种标准 SQL 转换成不同数据库对应的语句，从而完成数据库切换时 SQL 无缝移植。

sql.sqltranslate(dbtype) 函数前面的 sql 是需要翻译的 SQL 语句，参数 dbtype 是数据库类型。函数要在 SPL 的简单 SQL 中定义过，未定义的不会被翻译。已定义的函数列表和数据库类型可查阅 sqltranslate 函数帮助：Function-sqltranslate ()

IDE 内使用

我们先在 SPL 的 IDE 内尝试一下，将

转换成不同数据库对应的语法。

可以看到 ADDDAYS 这个函数被翻译成各个数据库不同的语法，实现了 SQL 在不同数据库之间移植。

我们再看一些例子。

月份加 10

SELECT EID, NAME, BIRTHDAY, ADDMONTHS(BIRTHDAY,10) DAY10 FROM EMP

通过 sqltranslate 翻译成不同数据库的语法：

ORACLE:

SELECT EID, NAME, BIRTHDAY, BIRTHDAY+NUMTOYMINTERVAL(10,'MONTH') DAY10 FROM EMP

SQLSVR:

SELECT EID, NAME, BIRTHDAY, DATEADD(MM,10,BIRTHDAY) DAY10 FROM EMP

DB2:

SELECT EID, NAME, BIRTHDAY, BIRTHDAY+10 MONTHS DAY10 FROM EMP

MYSQL:

SELECT EID, NAME, BIRTHDAY, BIRTHDAY+INTERVAL 10 MONTH DAY10 FROM EMP

POSTGRES:

SELECT EID, NAME, BIRTHDAY, BIRTHDAY+interval '10 months' DAY10 FROM EMP

TERADATA:

SELECT EID, NAME, BIRTHDAY, ADD_MONTHS(BIRTHDAY, 10) DAY10 FROM EMP

ADDMONTHS 函数在不同数据库的实现方式差异很大，SQLServer 有 DATEADD 函数，而 MySQL 和 PG 则直接加，Oracle 则采用两者相结合的方式实现。

求季度

SELECT EID,AREA,QUARTER(ORDERDATE) QUA, AMOUNT FROM ORDERS

转换后：
ORACLE:

SELECT EID,AREA,FLOOR((EXTRACT(MONTH FROM ORDERDATE)+2)/3) QUA, AMOUNT FROM ORDERS 

SQLSVR:

SELECT EID,AREA,DATEPART(QQ,ORDERDATE) QUA, AMOUNT FROM ORDERS

POSTGRES:

SELECT EID,AREA,EXTRACT(QUARTER FROM ORDERDATE) QUA, AMOUNT FROM ORDERS

TERADATA:

SELECT EID,AREA,TD_QUARTER_OF_YEAR(ORDERDATE) QUA, AMOUNT FROM ORDERS

求季度的函数，不同数据库虽然都有函数实现，但函数名称和参数的定义又有很大差异。

类型转换

SELECT EID, NAME, DATETOCHAR(BIRTHDAY) FROM EMP

转换后：

ORACLE:

SELECT EID, NAME, TO_CHAR(BIRTHDAY,'YYYY-MM-DD HH:MI:SS') FROM EMP

SQLSVR:

SELECT EID, NAME, CONVERT(CHAR,BIRTHDAY,120) FROM EMP

DB2:

SELECT EID, NAME, TO_CHAR(BIRTHDAY,'YYYY-MM-DD HH:MI:SS') FROM EMP

MYSQL:

SELECT EID, NAME, DATE_FORMAT(BIRTHDAY, '%Y-%m-%d %H:%i:%S) FROM EMP

POSTGRES:

SELECT EID, NAME, TO_CHAR(BIRTHDAY,'YYYY-MM-DD HH:MI:SS') FROM EMP

TERADATA:

SELECT EID, NAME, TO_CHAR(BIRTHDAY,'YYYY-MM-DD HH:MI:SS') FROM EMP

类型转换函数各个数据库的函数名称和格式化形式有较大差异。

这些五花八门的差异都可以用 SPL 的 sqltranslate 来转换。

函数定义与扩展

SPL 支持的数据库类型和函数定义在发布包 esproc-bin.jar 中的字典文件 /com/scudata/dm/sql/function.xml 中。

<?xml version="1.0" encoding="utf-8"?>
<STANDARD>
    <FUNCTIONS type="FixParam">
        <FUNCTION name="ADDDAYS" paramcount="2" value="">
            <INFO dbtype="ORACLE" value="?1+NUMTODSINTERVAL(?2,'DAY')"></INFO>
            <INFO dbtype="SQLSVR" value="DATEADD(DD,?2,?1)"></INFO>
            <INFO dbtype="DB2" value="?1+?2 DAYS"></INFO>
            <INFO dbtype="MYSQL" value="?1+INTERVAL ?2 DAY"></INFO>
            <INFO dbtype="HSQL" value="DATEADD('dd', ?2, ?1)"></INFO>
            <INFO dbtype="TERADATA" value="?1+CAST(?2 AS INTERVAL DAY)"></INFO>
            <INFO dbtype="POSTGRES" value="?1+interval '?2 days'"></INFO>
            <INFO dbtype="ESPROC" value="elapse(?1,?2)"></INFO>
        </FUNCTION>
    </FUNCTIONS>
</STANDARD>

FUNCTIONS 节点代表一个函数组，type 是函数组类型，FixParam 表示参数个数固定的函数组。FUNCTION 节点代表一个简单 SQL 函数，name 是函数名，paramcount 是参数个数，value 是翻译本函数时的默认值，空串时表示无需翻译。INFO 节点代表一种数据库，dbtype 是数据库名称，空串时表示是 SPL 中的简单 SQL，value 是翻译到本数据库时的对应值。value 中的? 或?1 代表函数的第 1 个参数值，?2 代表函数的第 2 个参数值，依此类推。当 INFO 中的 value 值为空串时，则使用父节点 FUNCTION 的 value 值。

在翻译时，如果 FUNCTION 节点下没有指定数据库的 INFO 节点定义，则此函数保持原样，不会被翻译。

SPL 在 funtion.xml 中定义了很多函数，但并不是所有。实际使用中可能碰到新的，可以自行增加。

比如我们要增加函数来计算两个日期的相差天数，我们就可以增加 FUNCTION 节点，定义 DATEDIFF 函数名，然后在 INFO 节点分别配置不同数据库的写法。

<FUNCTION name="DATEDIFF" paramcount="2" value="">
    <INFO dbtype="ORACLE" value="?1-?2"></INFO>
    <INFO dbtype="SQLSVR" value="DATEDIFF(day,?1,?2)"></INFO>
    <INFO dbtype="MYSQL" value="DATEDIFF(?1,?2)"></INFO>
    <INFO dbtype="POSTGRES" value="?1-?2"></INFO>
    <INFO dbtype="ESPROC" value="interval(?2,?1)"></INFO>
</FUNCTION>

类似地，如果还要增加对其他数据库的支持，直接增加 INFO 节点信息，把新数据库配置上就可以。比如这里要增加对 SQLite 的支持，来完成日期相差天数的翻译。

<FUNCTION name="DATEDIFF" paramcount="2" value="">
    <INFO dbtype="ORACLE" value="?1-?2"></INFO>
    <INFO dbtype="SQLSVR" value="DATEDIFF(day,?1,?2)"></INFO>
    <INFO dbtype="MYSQL" value="DATEDIFF(?1,?2)"></INFO>
    <INFO dbtype="POSTGRES" value="?1-?2"></INFO>
    <INFO dbtype="ESPROC" value="interval(?2,?1)"></INFO>
    <INFO dbtype="SQLite" value="JULIANDAY(?1) - JULIANDAY(?2)"></INFO>
</FUNCTION>

不固定个数参数情况

我们前面看到的都是函数参数个数固定的例子，但还有一些事先无法固定参数个数的情况，比如字符串连接，case when，以及取多个参数中的第一个非空值等。
SPL 对这种动态参数个数的情况也提供支持，将 FUNCTIONS 节点的 type 值配置成 AnyParam，也就是任意个数参数。

 <FUNCTIONS type="AnyParam">
    <FUNCTION classname="com.scudata.dm.sql.simple.Case" name="case">
        <INFO dbtype="ESPROC" classname="com.scudata.dm.sql.simple.Case"></INFO>
    </FUNCTION>
    <FUNCTION classname="com.scudata.dm.sql.simple.Coalesce" name="coalesce">
        <INFO dbtype="ESPROC" classname="com.scudata.dm.sql.simple.Coalesce"></INFO>
    </FUNCTION>
    <FUNCTION classname="com.scudata.dm.sql.simple.Concat" name="concat">
        <INFO dbtype="ESPROC" classname="com.scudata.dm.sql.simple.Concat"></INFO>
    </FUNCTION>
 </FUNCTIONS>

我们要为每个数据库的对应函数编写相应的 Java 类。比如我们要为字符串函数 CONCAT 增加对 Oracle 的支持，我们就可以编写这样的代码：

编译后添加到 esproc-bin.jar 的 /com/scudata/dm/sql/oracle 路径下。

然后在 funtion.xml 中配置 oracle 对应的翻译类。

Jar 包修改后重启 IDE，我们尝试一下。

可以看到连接 3 个参数，转换成 Oracle 语法时，变成了以双竖线的拼接方式，而目标数据库是 ESPROC 时则采用小写的 concat 来实现。

至此，我们已经学会了如何使用翻译函数，如何配置，以及如何新增函数和数据库，包括参数个数不定的情况。

与应用结合

接下来，我们来学习如何与应用相结合。

SPL 与应用集成非常简单，只需要将 [安装目录]\esProc\lib 下的：esproc-bin-xxxx.jar 和 icu4j-60.3.jar 两个 jar 包引入到应用中，然后复制 [安装目录]\esProc\config 下的 raqsoftConfig.xml 到应用的类路径下即可。

raqsoftConfig.xml 是 SPL 的核心配置文件，名称不可更改，后续的数据源和网关配置都需要用到。

单库情况

我们先看应用只有单一数据库的情况。

使用方式 1- 仅用 SQL 翻译

在应用中使用 SPL 的 SQL 翻译功能，最简单的方式就是用 sqltranslate 把 SQL 翻译成目标数据库的语法后执行。
SPL 中翻译 SQL 的 API 是 com.scudata.dm.sql.SQLUtil.translate 函数，直接使用它就可以实现 SQL 语法的翻译。

不过，需要说明的是，SPL 官方并不推荐直接使用 API，而是建议使用 SPL 的 JDBC 接口，但仅仅为了个字符串转换动作而写好几行代码连接 JDBC 确实有点麻烦，所以我们直接使用了 API。

另外，我们希望把 SQL 移植做到尽量透明，除了首次改写，以后再换数据库无需再更改代码重编译，只要维护配置文件即可。因此，我们把数据库类型维护在配置文件中。

比如，我们增加数据库类型配置文件 dbconfig.properties，里面配置数据库类型，如 MYSQL。

dbconfig.properties 内容：

database.type=MYSQL

然后封装一个翻译方法，调用 SPL 的 API 完成 SQL 翻译。

public static String translateSQL(String sql) {
    String dbType = null;
    try (InputStream input = SQLTranslator.class.getClassLoader().getResourceAsStream("dbconfig.properties")) {
        Properties prop = new Properties();
        if (input == null) {
            System.out.println("Sorry, unable to find dbconfig.properties");
            return null;
        }
        prop.load(input);
        dbType = prop.getProperty("database.type");
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return SQLUtil.translate(sql, dbType);
}

主程序调用，传入 SQL 并调用 SQL 翻译，后面的代码与原来完全一致，包括设置参数、执行 SQL、获取结果集等等。事实上，主程序代码仅仅增加了一句 sql = translateSQL(sql) 。

public static void main(String[] args) {
    ……

    String sql = “SELECT name, birthday, adddays(birthday,10) day10 “
        + “ FROM emp where dept=? and salary>?” ;
        sql = translateSQL(sql);
        
        pstmt.setString(1, "Sales"); 
        pstmt.setDouble(2, 50000);

        ……

}

使用方式 2- 透明化并执行 SQL

前面的方法在调用时需要多做一步翻译，如果执行 SQL 的地方比较多，原程序的改动也会比较大。而且还使用了官方不推荐的接口，未来可能有不兼容的风险。

为了克服这些缺点，我们还可以采用更透明的方法，即把 SQL 翻译以及执行 SQL 获取结果集的动作也在 SPL 内完成。

SPL 提供了标准 JDBC 支持，只要将数据库驱动和 URL 都改成 SPL 的，其它代码可以完全不动，既不需要封装方法，也不需要显式翻译。

这段代码中并没有翻译的过程，那是怎么实现 SQL 翻译的呢？看起来似乎有点神奇。

关键点在于 SPL 的 JDBC 网关。我们事先配置一个 SPL 脚本，JDBC 中执行所有 SQL 语句都会先交给这个脚本处理执行。也就是说，SQL 的翻译和执行都是在脚本中完成的。

要使用 JDBC 网关，需要在 raqsoftConfig.xml 中的 JDBC 节点配置 SPL 脚本，比如这里配置的 gateway.splx。

 <JDBC>
    <load>Runtime,Server</load>
    <gateway>gateway.splx</gateway>
 </JDBC>

网关脚本需要两个参数，一个 sql 参数用于接收 SQL 语句，另一个 args 参数则用于接收 SQL 语句中的参数，也就是 JDBC 给 SQL 传递的参数。

下面有个“最后一个参数是动态参数” 的选项 要勾选，这样才能接收到 SQL 语句的多个参数。

我们来看看脚本内容。

	A	B
1	if !ifv(dbName)	>call(“initGlobalVars.splx”)
2	=sql=trim(sql).sqltranslate(dbType)
3	=argsN=args.len()	=(“sql “argsN.(“args(”/~/”)”)).concat@c()
4	=connect(dbName)
5	if pos@hc(sql,“select”)	return A4.query@x(${B3})
6	else	=A4.execute(${B3})
7		>A4.close()

A1 中判断 dbName 变量是否存在，如果不存在则在 B1 调用初始化脚本 initGlobalVars.splx：

	A
1	>env(dbType,file(“dbconfig.properties”).property(“database.type”))
2	>env(dbName,file(“dbconfig.properties”).property(“database.name”))

这个脚本读取配置文件中的数据源名称和数据库类型，用 ENV 函数放置在全局变量 dbType 和 dbName 中。

其中，配置文件 dbconfig.properties 内容：

database.type=MYSQL
database.name=MYDATASOURCE

A2 进行 SQL 翻译，这个方法大家已经不陌生了。

A3 计算参数个数。B3 将参数拼成一个串，比如两个参数的时候 B3 的结果是这样的。

A4 进行数据源连接，这个数据源是在 raqsoftConfig.xml 中配置的，增加 DB 节点配置相应数据源连接信息即可，多个数据源可以依次配置。

 <DB name="MYDATASOURCE">
    <property name="url" value="jdbc:mysql://127.0.0.1:3306/mydb?useCursorFetch=true"></property>
    <property name="driver" value="com.mysql.jdbc.Driver"></property>
    <property name="type" value="10"></property>
    <property name="user" value="root"></property>
    <property name="password" value="root"></property>
    <property name="batchSize" value="0"></property>
    <property name="autoConnect" value="false"></property>
    <property name="useSchema" value="false"></property>
    <property name="addTilde" value="false"></property>
    <property name="caseSentence" value="false"></property>
 </DB>

A5 判断是否是 select 语句，我们要实现所有 SQL 的翻译和执行，而 DQL 和 DML 语句的执行方式不同，返回值也不同，所以要分别处理。

如果是 select 语句，B5 使用 db.query 函数进行查询并获得结果，@x 代表查询后关闭数据库连接。这里使用了 SPL 宏，宏替换的语句是这样。

A6 对于非 select 语句，需要使用 db.execute 函数执行 SQL 语句。

整体脚本并不是很复杂，而且以后修改脚本也不需要重启应用，因为 SPL 是解释执行的，支持热切换。

通过这个网关脚本，也可以执行 update 这类 DML 语句。

我们在程序中执行 update 语句看一下，可以看到同样会被翻译成对应的数据库语句，并且更新成功。这意味着所有 SQL 都可以无缝移植。

多库情况

有些应用可能会涉及多个数据库，这种情况应该如何处理呢？

使用方式 1- 仅用 SQL 翻译

还是先看仅翻译的用法。

仍然要在配置文件中维护数据源名称和数据源类型。在 dbsconfig.properties 中添加如下内容：

database.oracleds.type=ORACLE
database.mysqlds.type=MYSQL
database.pgds.type=POSTGRESQL

等号前面的中间部分是数据源名称，如 oracleds，等号后面是数据源类型，如 ORACLE。由于存在多个数据库，我们需要根据数据源名称查找类型。

编写翻译方法，根据数据源名称查找类型，加载配置文件获得属性信息，进行 SQL 翻译：

翻译方法与前面类似，这里不再赘述。

主程序使用时传递 SQL 语句和数据源名称，这里是 mysqlDS，还可以是其他不同数据源，然后翻译 SQL，接下来设置参数、执行 SQL、获取结果集等与原程序完全一致。

使用方式 2-SPL 脚本翻译并执行 SQL

仅翻译的优缺点我们前面谈论过，下面我们看一下用 SPL 网关翻译并执行 SQL。

public static void main(String[] args) {
    String driver = "com.esproc.jdbc.InternalDriver";
    String url = "jdbc:esproc:local://";
    try {
        Class.forName(driver);
        String mysqlDsName = "mysqlds";
        Connection mysqlConn = DriverManager.getConnection(url);
        String setDS = "setds "+ mysqlDsName;
        PreparedStatement setst = mysqlConn.prepareStatement(setDS);
        setst.execute();
        String sql = "SELECT orderid, employeeid, adddays(orderdate,10) day10,amount "
            + "FROM orders WHERE employeeid > ? AND amount > ?";
        PreparedStatement st = mysqlConn.prepareStatement(sql);
        st.setObject(1, "506");
        st.setObject(2, 9900);
        ResultSet rs = st.executeQuery();
        while (rs.next()) {
            String employeeid = rs.getString("employeeid");
            System.out.print(employeeid + ",");
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    }

这里为不同数据源分别建立 Connection，并增加一步设置数据源名称，数据源解析在网关脚本中处理，其余执行 SQL 部分则与原来程序完全一致。

网关脚本的参数与前面的单库网关脚本完全一致，sql 参数用于接收 SQL 语句，args 参数用于接收 SQL 参数。

网关脚本 gateway.splx 内容：

	A	B
1	if !ifv(dbs)	>call(“initGlobalVarsMulti.splx”)
2	=sql=trim(sql)
3	if pos@hc(sql,“setds”)	>env@j(dsName,lower(trim(mid(sql,7))))
4		>env@j(dbType,dbs.select(name==“database.”+dsName+“.type”).value)
5		return
6	=sql=sql.sqltranslate(dbType)
7	=argsN=args.len()	=(“sql “argsN.(“args(”/~/”)”)).concat@c()
8	=connect(dsName)
9	if pos@hc(sql,“select”)	return A8.query@x(${B7})
10	else	>A8.execute(${B7})
11		>A8.close()

处理多库的网关脚本增加了数据源名称设置过程。

B1 调用的 initGlobalVarsMulti.splx 初始化脚本读取配置文件：

	A
1	>env(dbs,file(“dbsconfig.properties”).property())

结果如下：

A3 接收程序传递的设置数据源参数，也就是：setds mysqlds，如果是以 setds 开头，则在 B3 将数据源名称放置在任务变量 dsName 中，任务变量的作用域是同一个 Connection，接下来所有该数据源下的 SQL 语句可以直接运行，B4 类似，根据数据源列表 dbs 查找数据库类型并存入 dbType 任务变量中。

A6 开始的脚本与单库一样，此处不再赘述。

这个网关脚本仍然能处理所有 SQL 语句，全部能够无缝移植。

以上就是我们要学习 SPL 的 SQL 移植全部内容，有了 SPL 数据库切换不需要再更改代码，做到无缝移植。

当然，SPL 的能力还远不止于此，SPL 还支持并行执行 SQL 取数、完成跨库查询、支持数据库与其他非数据库混合计算，甚至可以借助 SPL 的计算能力对 SQL 进行性能优化，这些内容我们会在后面的专题逐渐介绍。

SPL 是开源软件，源码地址

免费下载

原文地址：https://blog.csdn.net/smilejingwei/article/details/144283806

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！