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