SPL 实践系列：跨库移植 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 内尝试一下，将

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

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

可以看到 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 的支持，我们就可以编写这样的代码：

public class Concat implements IFunction
{public String getFormula(String[] params){StringBuffer sb = new StringBuffer();for(int i = 0, len = params.length; i < len; i++) {if(params[i].isEmpty()) {throw new RQException("Concat function parameter cannot be empty");}if(i > 0) {sb.append(" || ");}sb.append(params[i]);}return sb.toString();}
}

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

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

 <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><INFO dbtype="ORACLE" classname="com.scudata.dm.sql.oracle.Concat"></INFO>**</FUNCTION></FUNCTIONS>

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 语法的翻译。

String sql = "select name, birthday, adddays(birthday,10) day10 from emp";
sql = com.scudata.dm.sql.SQLUtil.translate(sql, "MYSQL");

不过，需要说明的是，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 的，其它代码可以完全不动，既不需要封装方法，也不需要显式翻译。

public static void main(String[] args) {String driver = "com.esproc.jdbc.InternalDriver";String url = "jdbc:esproc:local://";try {Class.forName(driver);Connection conn = DriverManager.getConnection(url);String sql = "SELECT orderid, employeeid, adddays(orderdate,10) day10,amount "+ "FROM orders WHERE employeeid > ? AND amount > ?";PreparedStatement st = conn.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);}
}

这段代码中并没有翻译的过程，那是怎么实现 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 语句。

public static void main(String[] args) {String driver = "com.esproc.jdbc.InternalDriver";String url = "jdbc:esproc:local://";try {Class.forName(driver);Connection conn = DriverManager.getConnection(url);String sql = "update orders set customername = ? where orderid = ? ";PreparedStatement st = conn.prepareStatement(sql);st.setObject(1,"PTCAG001");st.setObject(2,"1");st.executeUpdate();} catch (Exception e) {throw new RuntimeException(e);}
}

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

多库情况

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

使用方式 1- 仅用 SQL 翻译

还是先看仅翻译的用法。

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

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

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

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

public static String translateSQL(String sql, String dataSourceName) {try (InputStream input = SQLTranslator.class.getClassLoader().getResourceAsStream("dbsconfig.properties")) {Properties prop = new Properties();if (input == null) {System.out.println("Sorry, unable to find dbsconfig.properties");return null;}prop.load(input);String dbType = prop.getProperty("database." + dataSourceName.toLowerCase() + ".type");if (dbType == null) {throw new RuntimeException("Data source " + dataSourceName + " not configured in the configuration file.");}return SQLUtil.translate(sql, dbType);} catch (Exception ex) {ex.printStackTrace();return null;}
}

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

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

public static void main(String[] args) {String sql = "SELECT orderid, employeeid, adddays(orderdate,10) day10,amount "+ "FROM orders WHERE employeeid > ? AND amount > ?";String dataSourceName = "mysqlds"; String translatedSQL = translateSQL(sql, dataSourceName);System.out.println("Translated SQL: " + translatedSQL);……
}

使用方式 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 是开源软件，可以从 github 获取源码：https://github.com/SPLWare/esProc