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 查询》