ORM 技术能否方便实现数据库的移植
很多程序员尝试用 ORM 技术实现 SQL 在不同数据库间的移植，但 ORM 技术只适合 OLTP 场景下的简单 SQL，难以实现 OLAP 场景下较复杂 SQL 的移植。作为对比，集算器 SPL 直接支持大量函数，可描述更多运算，适合的场景更丰富。点击ORM 技术能否方便实现数据库的移植了解详情。
ORM是以面向对象的方法操作关系数据库的编程技术，ORM向上提供了通用查询接口（比如HQL），向下生成不同数据库的SQL。基于上层接口的通用性，就会有人考虑用ORM技术来实现数据库的移植，那么，ORM技术实现数据库移植可行吗？
Hibernate和Mybatis是两种最常见的ORM技术，我们分别来看一下。
因为SQL本身具有的一定的通用性，对于最简单的情况，Hibernate生成的SQL在所有数据库都可以正常工作，自然也可以实现数据库间的移植。比如实现条件查询的HQL：
from OrdersEntity where (amount between 2000 and 3000) and UPPER(client) like '%s%' |
生成的SQL大概是：
select ordersenti0_.OrderID as orderid1_1_, ordersenti0_.Client as client2_1_, ordersenti0_.SellerId as sellerid3_1_, ordersenti0_.Amount as amount4_1_, ordersenti0_.OrderDate as orderdat5_1_ from orders ordersenti0_ where (ordersenti0_.Amount between 2000 and 3000) and (upper(ordersenti0_.Client) like '%S%') |
这句SQL在MySQL或Oracle等数据库中都可以正常执行。
有些特殊数据类型和函数，即使在不同数据库下的规则不一样，Hiberante也能正确处理，以此保证移植成功，比如截取年份：
HQL：select client,year(orderDate),sum(amount),count(orderId) from OrdersEntity group by client,year(orderDate) having sum(amount)>2000
MySQL：select ordersenti0_.Client as col_0_0_, year(ordersenti0_.OrderDate) as col_1_0_, sum(ordersenti0_.Amount) as col_2_0_, count(ordersenti0_.OrderID) as col_3_0_ from orders ordersenti0_ group by ordersenti0_.Client , year(ordersenti0_.OrderDate) having sum(ordersenti0_.Amount)>2000
Oracle：select ordersenti0_.Client as col_0_0_, extract(year from ordersenti0_.OrderDate) as col_1_0_, sum(ordersenti0_.Amount) as col_2_0_, count(ordersenti0_.OrderID) as col_3_0_ from system.orders ordersenti0_ group by ordersenti0_.Client , extract(year from ordersenti0_.OrderDate) having sum(ordersenti0_.Amount)>2000 |
Hibernate能够正确地根据数据库选择相应的函数，其中，为MySQL生成的SQL使用year函数，Oracle则使用extract函数。
Hibernate甚至能把一些类上的运算翻译成正确的SQL来执行，比如取前3条记录：
HQL+JAVA：session.createQuery("select orderId,client,amount,orderDate from OrdersEntity").setMaxResults(3);
MySQL：select ordersenti0_.OrderID as col_0_0_, ordersenti0_.Client as col_1_0_, ordersenti0_.Amount as col_2_0_, ordersenti0_.OrderDate as col_3_0_ from orders ordersenti0_ limit ?
Oracle: select * from (select ordersenti0_.OrderID as col_0_0_, ordersenti0_.Client as col_1_0_, ordersenti0_.Amount as col_2_0_, ordersenti0_.OrderDate as col_3_0_ from test.orders ordersenti0_) where rownum <= ? |
但是更复杂一些的情况，Hibernate就无能为力了。
HQL不直接支持的函数
Hibernate HQL支持少量函数，比如求绝对值、求字符串长度、取年月日（见前面例子）等，但比起SQL来说还是太少，尤其是项目开发中常用的日期函数和字符串函数，前者比如日期增减、求年中第几天、求季度数等，后者比如替换、left截取、求ASCII码等。为了实现类似的HQL函数，就必须在HQL中嵌入SQL，这会导致HQL不可移植。
举例来说，数据库为MySQL，我们希望HQL使用原本并不支持的自定义函数udf_datasub以实现日期增减。HQL形如：
select udf_dateadd (orderDate,3) from OrdersEntity |
为实现上述功能，需要继承org.hibernate.dialect.MySQLDialect父类，并编写自定义JAVA类。在自定义JAVA类中，注册HQL函数名udf_dateadd，并引用MySQL函数date_add，关键代码如下：
registerFunction("udf_dateadd", new SQLFunctionTemplate( DateType.INSTANCE,"date_add(?1,INTERVAL ?2 DAY)") ); |
在执行HQL时，Hibernate生成的SQL（MySQL）：
select date_add(ordersenti0_.OrderDate,INTERVAL 3 DAY) as col_0_0_ from orders ordersenti0_ |
如果将数据库切换为Oracle，Hibernate生成的SQL还是上面这样，而Oracle没有date_add函数，执行这句SQL就会报错，无法顺利移植。这时候，需要修改上面的自定义JAVA类，关键代码改成：
registerFunction("udf_dateadd", new SQLFunctionTemplate( DateType.INSTANCE,"?1 + ?2)") ); |
显然，这样就无法透明移植了。
类似地，week\quarter\replace\left\char（以MySQL为例）等函数也无法透明地使用HQL实现移植，混合使用SQL函数已是Hibernate开发的常态。
HQL不能描述的运算
还有很多运算都是SQL能描述但HQL不能描述的，比如FROM中的子查询、有序计算、窗口函数。在这种SQL中如果涉及了需要移植的语法部件，就无法用HQL来实现移植了。
HQL支持在SELECT中返回单字段的子查询，但不支持FROM中的子查询。比如，HQL无法生成下面的SQL，硬写这样的HQL会直接报错：
select orderId, m from (select orderId, month(orderDate) m from OrdersEntity) t1 |
这种情况下，使用HQL来移植也就无从谈起了，只能直接使用SQL。
如果想在HQL中变相实现子查询的功能，还有些麻烦的办法。办法一，在数据库中建立视图，将子查询转为普通的表查询（Hibernate不区分视图和表）。当需要移植数据库时，只需在新数据库中重建视图。办法二，在JAVA中建立虚拟视图类（使用@Subselect注解），这种方法虽然增强了可移植性，但开发调试阶段的代码量很大，往往比建立视图更麻烦。
上面两种方法都很麻烦，通常业务数据库也不会轻易加视图，所以很多人会用直接使用SQL解决眼前的问题： org.hibernate.Session.createSQLQuery(SQL)。但这种SQL语句中如果含有待移植的函数，则不会在更换数据库时自动变换，比如从MySQL的year函数到Oracle的extract函数。
HQL也不支持rownum，含有这种关键字的SQL也无法用HQL写出来，例如
WITH t AS (SELECT sales, month(orderDate) m,value,rownum num FROM tbl ORDER BY month(orderDate) ASC) SELECT sales,m,v1,v2, (v1-v2)/v2 loopRate FROM ( SELECT t1.sales,t1.m,t1.value v1,t2.value v2 FROM t t1 LEFT JOIN t t2 ON t1.num=t2.num+1 WHERE t1.sales=t2.sales) t |
这句SQL中，除了多处使用HQL不支持的子查询外，关键在于要用rownum伪序号字段来实现有序计算，而HQL不支持，也就无法写出这种计算，仍然只能写成无法移植的SQL。
上面的环比运算，还可以用窗口函数来简化：
SELECT sales,m,v1,v2, (v1-v2)/v2 loopRate FROM ( SELECT sales, month(orderDate) m,value v1, LAG(value,1) OVER(PARTITION BY sales ORDER BY month(orderDate)) v2 FROM tbl) t |
但HQL不支持窗口函数，这时候仍然要直接写成SQL，失去移植能力
Hibernate被称为“全自动ORM”，因为理论上它支持输入（HQL）到输出（Object）全程的可移植性，但实际开发时就会发现，很多函数和运算仍然要依赖SQL，除非是小型或简单项目，否则移植过程并不方便。
Mybatis被称为“半自动ORM”，因为只有输出部分才是可移植的JAVA代码，输入部分还是不可移植的SQL。很多人意识到了这个问题，也希望弥补Mybatis的缺憾，所以各种第三方扩展包也应运而生，比如Mybatis-plus、Mybatis-ext。这类扩展包通常向上提供函数式编程接口，向下生成SQL，以此实现输入端的可移植性。但遗憾的是，此类扩展包通常连关联查询这种最简单的情况都不支持，对SQL的依赖性比Hibernate大的多，可移植性也远远比不上Hibernate。至于Hibernate和Mybatis之外的ORM技术，那就更不成熟了，可移植性也更差。
看起来，ORM实现数据库的移植还很初级，OLTP场景下的SQL相对简单，基本能过关，但对于OLAP场景下，就会常常出现一些复杂的SQL了，再使用ORM来移植就难以胜任了。
那么，还有什么办法呢？
还可以用集算器 SPL。SPL设计了一套标准的SQL查询语法，该语法内置大量函数（还在持续追加中），可描述更多常用运算。SPL中有一个sqltranslate函数，可以把这种标准SQL翻译成不同数据库的SQL，实现数据库的迁移。
比如这样的标准SQL：
select client,year(orderDate),sum(amount),count(orderId) from OrdersEntity group by client,year(orderDate) having sum(amount)>2000 |
用.sqltranlate("MYSQL")翻译后就得到：
select client,year(orderDate),sum(amount),count(orderId) from OrdersEntity group by client,year(orderDate) having sum(amount)>2000 |
而如果使用.sqltranslate("ORACLE")将返回：
select client,EXTRACT(YEAR FROM orderDate),sum(amount),count(orderId) from OrdersEntity group by client,EXTRACT(YEAR FROM orderDate) having sum(amount)>2000 |
可以看到，标准函数能够正确地根据数据库选择相应的函数。
标准SQL直接支持大量函数
除了一些简单函数外，标准SQL还支持项目开发中大量的常用函数，比如时间函数中的日期增减、求年中第几天、求季度数等，字符串函数中的替换、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 |
标准SQL可描述更多运算
不同数据库SQL的区别主要在函数上，运算语句上的区别就小多了，换而言之，不同的数据库在运算语句上的兼容性较好。标准SQL采取的策略是只翻译函数，不翻译（原样照抄）语句，从而使标准SQL可描述更多的运算。比如，对于下面的子查询无论翻译成哪种数据库SQL都不会变，也都可以正常执行。
select orderId, m from (select orderId, month(orderDate) m from Orders) t1 |
因为SPL不翻译运算语句，所以有些特殊的运算无法移植，比如低版本的MySQL不支持窗口函数，之后才开始支持，所以下面的环比运算只能在高版本MySQL和Oracle/SQLSVR等数据库之间移植。
SELECT sales,m,v1,v2, (v1-v2)/v2 loopRate FROM ( SELECT sales, month(orderDate) m,value v1, LAG(value,1) OVER(PARTITION BY sales ORDER BY month(orderDate)) v2 FROM tbl) t |
有些数据库支持rownum，比如Oracle/DB2，另一些数据库不支持rownum伪列，比如MySQL/SQLSVR，显然，包含rownum的标准SQL只能在Oracle和DB2之间移植，无法移植到MySQL/SQLSVR。
SPL可胜任移植较复杂的OLAP语句，详情可参考《如何写出能适应不同数据库的 SQL 查询》
