怎样让报表能够适应各种数据库

报表通常都是通过 SQL 来从数据库中取数和计算再呈现的,有些情况下,报表可能始终对接一个数据库,但也有不少情况时,报表需要在不同情况下对接不同的数据库,比如:

  • 行业开发商做的通用行业报表,要去匹配不同客户不同的数据库

  • 报表上线后企业数据库发生迁移、变更等

  • 报表需要同时从生产数据库以及历史数据仓库中取数,生产库和历史库的类型不同

  • 报表开发用的测试数据库与生产数据库不同

如果要对接多种数据库,就涉及到 SQL 是否通用的问题,SQL 通用,报表移植起来才方便,报表用的 SQL 大都是形如 SELECT…(个别有 WITH)的查询语句,这种 SQL 大体是通用的,报表移植到各类数据库一般都没什么大问题,但数据库也会有方言,有自己独有的函数,用方言的地方,SQL 就不通用了,举一个最简单的例子:

比如将字符串 “2020-02-05” 转换成日期

不同数据库就有各自的方言,写法就不同

ORACLE:select TO_DATE(‘2020-02-05’, ‘YYYY-MM-DD’) from USER;

SQLServer:select CONVERT(varchar(100), ‘2020-02-05’, 23) from USER;

MYSQL:select DATE_FORMAT(‘2020-02-05’,‘%Y-%m-%d’) from USER

SQL 这时候就不通用了

遇到这样的情况,报表中的 SQL 就只能是见谁说谁的方言,得改写成很多个不同的 SQL 才能匹配不同的数据库了,SQL 语句量少的情况下,改改也无所谓,复杂且多的时候,改起来就费劲了

转换函数智能翻译

使用润乾报表就可以解决这种烦恼

针对这样的问题,润乾报表在它独有的 SPL(SPL 是一款流行的开源计算工具平台)脚本数据集中提供了方案

SPL 里支持写一种“简单 SQL”,这个简单 SQL 里有各种常用的函数,可以在 SPL 脚本里先写出这种 SQL 语句,然后再用 SPL 的 sqltranslate 功能把简单 SQL 翻译成其它数据库的 SQL 语句,让这种简单 SQL 能适用于各种有方言的地方,比如下面这个脚本

A
1 select ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE between date(‘2015-03-18’) and date(‘2015-07-18’) and AMOUNT>1000
2 =A1.sqltranslate(“ORACLE”)
3 =connect(“oracle”).query@x(A2)

A1: SPL 的简单 SQL 语句
A2: 将 A1 中的 SQL 翻译成 ORACLE 格式
A3 :连接 oracle 并执行 翻译后的 SQL

A2 的结果:

select ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT

from ORDERS where ORDERDATE

between TO\_DATE('2015-03-18','YYYY-MM-DD') and TO\_DATE('2015-07-18','YYYY-MM-DD') and AMOUNT>1000

可以看到 sqltranslate 函数,自动把简单 SQL 的日期函数转换成了 ORACLE 的函数

如果想转换成 MYSQL 的,那只需要在 A2 单元格中,把 ORACLE 改成 MYSQL 就可以了,然后翻译完的结果就变成 MYSQL 的函数了

select ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT

from ORDERS where ORDERDATE

between DATE\_FORMAT('2015-03-18','%Y-%m-%d') and DATE\_FORMAT('2015-07-18','%Y-%m-%d') and AMOUNT>1000

整个过程很简单,

1 写出简单 SQL 的语句,2 根据目标数据库类型翻译 3 到目标数据库执行

原本切换数据源带来的繁杂的改 SQL 的工作,润乾的脚本数据集中直接用一个函数搞定了

SPL 的简单 SQL 中预定义了大量的常用函数,可以保障语句顺利准确翻译,具体函数可以参考:http://d.raqsoft.com.cn:6999/esproc/func/jiandansql.html

这些函数中,不仅有简单函数,还有项目开发中大量的常用函数,比如时间函数中的日期增减、求年中第几天、求季度数等,字符串函数中的替换、left 截取、求 ASCII 码等

日期增减函数示例,简单 SQL:

select ADDDAYS(OrderDate,3) from orders orders

翻译成 MySQL SQL:

select OrderDate+INTERVAL 3 DAY from orders orders

翻译成 Oracle SQL:

select OrderDate+NUMTODSINTERVAL(3,‘DAY’) from orders orders

这些都可以让数据库的移植更加方便

万一遇到一些特殊情况,没有预定义函数而翻译不了,SPL 还支持自定义函数,在简单 SQL 的字典文件 function.xml 中,增加一个节点,定义各种数据库中此函数应被翻译成什么语法即可,具体例子可以参考
SPL:跨数据库移植 SQL

另外简单 SQL 也不仅仅是用来翻译转换各种不同数据库语句的,它本身也很有业务意义它可以对着文件工作,可以赋予文件类数据 SQL 查询的能力,用 SQL 去查询 EXCEL,CSV,TXT,而且还能进行混合计算,感兴趣的同学可以看一下这篇帖子:

在文件上跑 SQL 的 Java 开源包 esProc SPL

SPL 脚本好处更多

当然 SPL 的“简单 SQL”+sqltranslate 函数也不是万能的,它擅长的是常见关系数据库的单句 SQL 的翻译,如果超出这个范围很多,那它也不一定能胜任了,比如成百上千行的 SQL、存储过程,或者 JAVA 写的自定义数据集等,这些如果要切换数据源,就搞不定了

不过润乾的 SPL 脚本也不仅仅是只有简单 SQL+sqltranslate 函数切换数据源这一点功能,它本身就是计算工具,对这种复杂逻辑计算场景,复杂数据源准备,切换等情况更加擅长

比存储过程好移植

报表中用到存储过程是很常见的现象,因为 SQL 做不了逻辑复杂的,多步过程式的复杂计算,只能用存储过程,存储过程本身就不太好移植,而且使用中还会用到数据库独有的函数、语法、特性,就会造成和当前数据库高度绑定情况,更是无法移植到其他数据库中,都得改很多或者干脆重写

如果用 SPL 代替存储过程来做计算则没有这些麻烦

SPL 做计算,只需要 SQL 做最简单的字段查询,计算过程则全部由 SPL 脚本来写,数据源有不同或者要迁移的时候,只需要重新连一下新数据源就可以,不需要改动其中的计算逻辑部分,一个脚本就可以应对不同的来源,能做到随便移植

另外,用存储过程当报表的数据集,其实还会有很多其他麻烦,比如代码难写,修改维护麻烦,影响数据库管理和安全等,用 SPL 代替存储过程,这些烦难就一股脑全解决了,有这些困扰的同学可以看看这篇帖子

怎样减少报表开发中的存储过程?

比 JAVA 好移植

报表中用 JAVA 的时候也挺多,逻辑复杂的情况有时会用 JAVA 来写,非关系型数据的连接接或者多源混算情况也得用 JAVA 来写

JAVA 自身的移植性很好,但 JAVA 对接数据源做数据处理和计算时的移植性却很差,因为面对不同的数据源,代码差异很大,如果计算中还用了数据源自有的计算类库,那就更不好移植,因为别的数据库用不了,都得重写,而且 JAVA 本身还会和应用高耦合,调试修改都得和应用一起重新编译,难度就更大了

同样的,用 SPL 代替 JAVA 则不会有这些烦恼,用一个例子来看下,比如报表要对 JSON 和 ORACLE 混算,大家可以想一下用 JAVA 写该怎么写,而 SPL 5 行简单的代码就搞定了

A
1 =json(file(“/data/EO.json”).read()) JSON 数据
2 =A1.conj(Orders)
3 =A2.select(Amount>1000 &&Amount<=3000 && like@c(Client,“s”)) 条件过滤
4 =db.query@x(“select ID,Name,Area from Client”) 数据库数据
5 =join(A3:o,Client;A4:c,ID) 关联计算

如果这时候要把 JSON 或者 ORACLE 换成其他数据源,可以再想一下 JAVA 得改多少代码才能是适配,而 SPL 只需要改下数据源所在的行就可以, 其他的都不用改(就算改,也顶多是重写 5 行),直接就迁移过去了

而且,SPL 代替 JAVA 给报表准备数据,除了移植方便,也还有很多其他方面的优势,有总写总改 JAVA 数据源烦恼的同学,可以看看这个帖子

报表中总是得写 java 程序怎么办

最后,SPL 可以代替存储过程和 JAVA 还有一点最根本的原因,那就是它本身是一款专业的计算工具,它在很多计算复杂、性能低下的场景中,都远远比存储过程和 JAVA 要写起来更简单,算起来更快,就像上面那个小例子一样,5 行代码就能搞定

想了解 SPL 脚本为什么写的快,算的快的,可以参考下面两篇帖子,篇幅有限这里就不展开写了

SQL 嵌套 N 层太长太难写怎么办?

SQL(及存储过程)跑得太慢怎么办?

总结

润乾报表的 SPL,不仅用一个 sqltranslate 函数就能轻松解决关系数据库的数据源切换问题,让报表适应更多的数据库,而且 SPL 脚本本身也可以代替复杂 SQL、存储过程,JAVA 等来给报表做数据准备,解决更复杂场景下的数据计算和切换的难题,还写的快算的快,有需要的,可以去试一试了

以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾