兼容不同数据库

HOS基础平台需要兼容多种数据库，不同数据库在语法操作上会存在一些差异。

HOS提供了全局参数_databaseId以处理不同数据库情况， 用户可通过 <if></if>标签对_databaseId参数判断实现对不同数据库sql的具体处理。 对应MySQL、人大金仓、Oracle、南大通用、南大通用8C、达梦、高斯数据库，参数值分别为kingbase、mysql、oracle、gbasedbt、gbase8c、dm、openGauss。

使用用例如下：

<select id="listResourcesForUser"  resultMap="ResourceMenuVOResultMap">
        SELECT
        DISTINCT(tr.id),
            <if test="_databaseId == 'kingbase'">
                tr.ancestors as ancestors
            </if>
            <if test="_databaseId == 'mysql'">
            	tr.ancestors
            </if>
            <if test="_databaseId == 'gbase8c'">
                tr.ancestors as ancestors
            </if>
            <if test="_databaseId == 'openGauss'">
                tr.ancestors as ancestors
            </if>
            <if test="_databaseId == 'oracle'">
            	to_char(tr.ancestors) as ancestors
            </if>
            <if test="_databaseId == 'dm'">
                tr.ancestors as ancestors
            </if>
        FROM
        t_sys_resource tr
        LEFT JOIN t_sys_page_preset page ON tr.id = page.resource_id
        WHERE tr.is_deleted = 0 AND tr.is_actived = 1
</select>

以上方sql为例，对应不同的数据最终sql分别为：

Mysql:

<select id="listResourcesForUser"  resultMap="ResourceMenuVOResultMap">
        SELECT
        DISTINCT(tr.id),
            	tr.ancestors
        FROM
        t_sys_resource tr
        LEFT JOIN t_sys_page_preset page ON tr.id = page.resource_id
        WHERE tr.is_deleted = 0 AND tr.is_actived = 1
</select>

人大金仓：

<select id="listResourcesForUser"  resultMap="ResourceMenuVOResultMap">
        SELECT
        DISTINCT(tr.id),
                tr.ancestors as ancestors
        FROM
        t_sys_resource tr
        LEFT JOIN t_sys_page_preset page ON tr.id = page.resource_id
        WHERE tr.is_deleted = 0 AND tr.is_actived = 1
</select>

oracle:

<select id="listResourcesForUser"  resultMap="ResourceMenuVOResultMap">
        SELECT
        DISTINCT(tr.id),
            	to_char(tr.ancestors) as ancestors
        FROM
        t_sys_resource tr
        LEFT JOIN t_sys_page_preset page ON tr.id = page.resource_id
        WHERE tr.is_deleted = 0 AND tr.is_actived = 1
</select>

达梦:

<select id="listResourcesForUser"  resultMap="ResourceMenuVOResultMap">
        SELECT
        DISTINCT(tr.id),
            	to_char(tr.ancestors) as ancestors
        FROM
        t_sys_resource tr
        LEFT JOIN t_sys_page_preset page ON tr.id = page.resource_id
        WHERE tr.is_deleted = 0 AND tr.is_actived = 1
</select>

• 若遇到不同数据库采用同一处理的场景，还可以使用or拼接_databaseId判断，例如：

<select id="listResourcesForUser"  resultMap="ResourceMenuVOResultMap">
        SELECT
        DISTINCT(tr.id),
            <if test="_databaseId == 'mysql' or _databaseId == 'kingbase' or _databaseId == 'dm'">
            	tr.ancestors
            </if>
            <if test="_databaseId == 'oracle'">
            	to_char(tr.ancestors) as ancestors
            </if>
        FROM
        t_sys_resource tr
        LEFT JOIN t_sys_page_preset page ON tr.id = page.resource_id
        WHERE tr.is_deleted = 0 AND tr.is_actived = 1
</select>

上方示例下，mysql和人大金仓、达梦最终结果相同，结果为：

<select id="listResourcesForUser"  resultMap="ResourceMenuVOResultMap">
        SELECT
        DISTINCT(tr.id),
            	tr.ancestors
        FROM
        t_sys_resource tr
        LEFT JOIN t_sys_page_preset page ON tr.id = page.resource_id
        WHERE tr.is_deleted = 0 AND tr.is_actived = 1
</select>

oracle结果为:

<select id="listResourcesForUser"  resultMap="ResourceMenuVOResultMap">
        SELECT
        DISTINCT(tr.id),
            	to_char(tr.ancestors) as ancestors
        FROM
        t_sys_resource tr
        LEFT JOIN t_sys_page_preset page ON tr.id = page.resource_id
        WHERE tr.is_deleted = 0 AND tr.is_actived = 1
</select>