网站 抄袭,外贸网站搭建推广,2022年电商平台排行榜,国内十大网站建设品牌背景
应用程序可能要基于不同数据库工作#xff0c;各种数据库的 SQL 语法大体一致#xff0c;但仍有些差别#xff0c;结果就要改造这些 SQL#xff0c;而这事通常只能手工调整#xff0c;工作量大还容易出错。
完全自动改造 SQL 几乎是无法做到的#xff0c;毕竟各种…背景
应用程序可能要基于不同数据库工作各种数据库的 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, BIRTHDAYNUMTOYMINTERVAL(10,MONTH) DAY10 FROM EMP
SQLSVR:
SELECT EID, NAME, BIRTHDAY, DATEADD(MM,10,BIRTHDAY) DAY10 FROM EMP
DB2:
SELECT EID, NAME, BIRTHDAY, BIRTHDAY10 MONTHS DAY10 FROM EMP
MYSQL:
SELECT EID, NAME, BIRTHDAY, BIRTHDAYINTERVAL 10 MONTH DAY10 FROM EMP
POSTGRES:
SELECT EID, NAME, BIRTHDAY, BIRTHDAYinterval 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 version1.0 encodingutf-8?
STANDARDFUNCTIONS typeFixParamFUNCTION nameADDDAYS paramcount2 valueINFO dbtypeORACLE value?1NUMTODSINTERVAL(?2,DAY)/INFOINFO dbtypeSQLSVR valueDATEADD(DD,?2,?1)/INFOINFO dbtypeDB2 value?1?2 DAYS/INFOINFO dbtypeMYSQL value?1INTERVAL ?2 DAY/INFOINFO dbtypeHSQL valueDATEADD(dd, ?2, ?1)/INFOINFO dbtypeTERADATA value?1CAST(?2 AS INTERVAL DAY)/INFOINFO dbtypePOSTGRES value?1interval ?2 days/INFOINFO dbtypeESPROC valueelapse(?1,?2)/INFO/FUNCTION/FUNCTIONS
/STANDARD FUNCTIONS 节点代表一个函数组type 是函数组类型FixParam 表示参数个数固定的函数组。FUNCTION 节点代表一个简单 SQL 函数name 是函数名paramcount 是参数个数value 是翻译本函数时的默认值空串时表示无需翻译。INFO 节点代表一种数据库dbtype 是数据库名称空串时表示是 SPL 中的简单 SQLvalue 是翻译到本数据库时的对应值。value 中的? 或?1 代表函数的第 1 个参数值?2 代表函数的第 2 个参数值依此类推。当 INFO 中的 value 值为空串时则使用父节点 FUNCTION 的 value 值。
在翻译时如果 FUNCTION 节点下没有指定数据库的 INFO 节点定义则此函数保持原样不会被翻译。
SPL 在 funtion.xml 中定义了很多函数但并不是所有。实际使用中可能碰到新的可以自行增加。
比如我们要增加函数来计算两个日期的相差天数我们就可以增加 FUNCTION 节点定义 DATEDIFF 函数名然后在 INFO 节点分别配置不同数据库的写法。
FUNCTION nameDATEDIFF paramcount2 valueINFO dbtypeORACLE value?1-?2/INFOINFO dbtypeSQLSVR valueDATEDIFF(day,?1,?2)/INFOINFO dbtypeMYSQL valueDATEDIFF(?1,?2)/INFOINFO dbtypePOSTGRES value?1-?2/INFOINFO dbtypeESPROC valueinterval(?2,?1)/INFO
/FUNCTION 类似地如果还要增加对其他数据库的支持直接增加 INFO 节点信息把新数据库配置上就可以。比如这里要增加对 SQLite 的支持来完成日期相差天数的翻译。
FUNCTION nameDATEDIFF paramcount2 valueINFO dbtypeORACLE value?1-?2/INFOINFO dbtypeSQLSVR valueDATEDIFF(day,?1,?2)/INFOINFO dbtypeMYSQL valueDATEDIFF(?1,?2)/INFOINFO dbtypePOSTGRES value?1-?2/INFOINFO dbtypeESPROC valueinterval(?2,?1)/INFOINFO dbtypeSQLite valueJULIANDAY(?1) - JULIANDAY(?2)/INFO
/FUNCTION 不固定个数参数情况
我们前面看到的都是函数参数个数固定的例子但还有一些事先无法固定参数个数的情况比如字符串连接case when以及取多个参数中的第一个非空值等。 SPL 对这种动态参数个数的情况也提供支持将 FUNCTIONS 节点的 type 值配置成 AnyParam也就是任意个数参数。 FUNCTIONS typeAnyParamFUNCTION classnamecom.scudata.dm.sql.simple.Case namecaseINFO dbtypeESPROC classnamecom.scudata.dm.sql.simple.Case/INFO/FUNCTIONFUNCTION classnamecom.scudata.dm.sql.simple.Coalesce namecoalesceINFO dbtypeESPROC classnamecom.scudata.dm.sql.simple.Coalesce/INFO/FUNCTIONFUNCTION classnamecom.scudata.dm.sql.simple.Concat nameconcatINFO dbtypeESPROC classnamecom.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.typeMYSQL
然后封装一个翻译方法调用 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。 JDBCloadRuntime,Server/loadgatewaygateway.splx/gateway/JDBC
网关脚本需要两个参数一个 sql 参数用于接收 SQL 语句另一个 args 参数则用于接收 SQL 语句中的参数也就是 JDBC 给 SQL 传递的参数。 下面有个“最后一个参数是动态参数” 的选项 要勾选这样才能接收到 SQL 语句的多个参数。
我们来看看脚本内容。
AB1if !ifv(dbName)call(“initGlobalVars.splx”)2sqltrim(sql).sqltranslate(dbType)3argsNargs.len()(“sql “argsN.(“args(”/~/”)”)).concatc()4connect(dbName)5if poshc(sql,“select”)return A4.queryx(${B3})6elseA4.execute(${B3})7A4.close()
A1 中判断 dbName 变量是否存在如果不存在则在 B1 调用初始化脚本 initGlobalVars.splx
A1env(dbType,file(“dbconfig.properties”).property(“database.type”))2env(dbName,file(“dbconfig.properties”).property(“database.name”))
这个脚本读取配置文件中的数据源名称和数据库类型用 ENV 函数放置在全局变量 dbType 和 dbName 中。
其中配置文件 dbconfig.properties 内容
database.typeMYSQL
database.nameMYDATASOURCE
A2 进行 SQL 翻译这个方法大家已经不陌生了。
A3 计算参数个数。B3 将参数拼成一个串比如两个参数的时候 B3 的结果是这样的。
A4 进行数据源连接这个数据源是在 raqsoftConfig.xml 中配置的增加 DB 节点配置相应数据源连接信息即可多个数据源可以依次配置。 DB nameMYDATASOURCEproperty nameurl valuejdbc:mysql://127.0.0.1:3306/mydb?useCursorFetchtrue/propertyproperty namedriver valuecom.mysql.jdbc.Driver/propertyproperty nametype value10/propertyproperty nameuser valueroot/propertyproperty namepassword valueroot/propertyproperty namebatchSize value0/propertyproperty nameautoConnect valuefalse/propertyproperty nameuseSchema valuefalse/propertyproperty nameaddTilde valuefalse/propertyproperty namecaseSentence valuefalse/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.typeORACLE
database.mysqlds.typeMYSQL
database.pgds.typePOSTGRESQL
等号前面的中间部分是数据源名称如 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 内容
AB1if !ifv(dbs)call(“initGlobalVarsMulti.splx”)2sqltrim(sql)3if poshc(sql,“setds”)envj(dsName,lower(trim(mid(sql,7))))4envj(dbType,dbs.select(name“database.”dsName“.type”).value)5return6sqlsql.sqltranslate(dbType)7argsNargs.len()(“sql “argsN.(“args(”/~/”)”)).concatc()8connect(dsName)9if poshc(sql,“select”)return A8.queryx(${B7})10elseA8.execute(${B7})11A8.close()
处理多库的网关脚本增加了数据源名称设置过程。
B1 调用的 initGlobalVarsMulti.splx 初始化脚本读取配置文件
A1env(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 是开源软件源码地址
免费下载
