springboot中实现多种数据库，如何增加适配另一种数据库

适配新增数据库代码改造主要是 Mapper 文件的改造。

1 多种模式

支持多数据库的代码主要有以下三种类型：

1.1 单mapper单SQL模式

只有一套mapper文件，每个功能只有一个sql（多库共用），通过if else以及databaseId区分 mysql,oracle等。示例如下：

<select id="selectSample">
select a,b,c from bw_sample
     <where>
         <!-- if实现特定数据库差异化sql -->
         <if test="_databaseId='mysql'">
             and d = concat(#{d}, "xxx")
         </if>
         <if test="_databaseId='oracle'">
             and d  = #{d}||"%"
         </if>
     </where>
</select>

1.2 单mapper多SQL模式

只有一套mapper文件，每个SQL都是两份（mysql,oracle），通过databaseId区分。示例如下：

<!-- 批量插入 -->
 <insert id="insertBatchSample" parameterType="java.util.List">
   INSERT INTO bw_sample
   (ID,REVISION,CREATED_BY)
   VALUES
   <foreach collection="list" item="item" index="index" separator=",">
     (#{item.id},#{item.revision},#{item.createdBy})
   </foreach>
 </insert>
 
 <insert id="insertBatchSample" parameterType="java.util.List" databaseId="oracle">
   INSERT INTO bw_sample
   (ID,REVISION,CREATED_BY)
   <foreach collection="list" item="item" index="index" separator="union">
     select #{item.id,jdbcType=BIGINT},#{item.revision,jdbcType=VARCHAR},#{item.createdBy,jdbcType=VARCHAR} from dual
   </foreach>
 </insert>

1.3 多mapper目录模式

mysql和oracle各有一套mapper目录，所有sql都是两份。示例如下：

在使用MyBatis或类似的ORM（对象关系映射）框架时，通常会有一种机制来确定使用哪个数据库配置文件，以下讲述两种方法：

一、在配置中添加配置：

datasource.driver-class-name-type = mysql #可以是Oracle/postgre

通过配置类定义数据源区分包扫描

@ConditionalOnProperty(name = "datasource.driver-class-name-type", havingValue = "mysql")
@Configuration
@MapperScan(basePackages = "com.example.mapper", sqlSessionFactoryRef = "sqlSessionFactory")
public class MySql_DataSourceConfig {

    // 也可以根据datasource.driver-class-name-type设置不同的Mapper文件路径

    @Bean(name = "dataSource")
    @Primary
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        datasource.setLogAbandoned(logAbandoned);
        datasource.setRemoveAbandoned(removeAbandoned);
        datasource.setRemoveAbandonedTimeout(removeAbandonedTimeout);
        datasource.setFilters(filters);
        datasource.setConnectionProperties(connectionProperties);
        return datasource;
    }

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setTypeAliasesPackage("com.example.mysql");
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mysql/*.xml"));

        return sessionFactory.getObject();
    }
    
}

二、也可以使用mybatis-plus的配置指定包扫描

mybatis-plus:
  mapper-locations: classpath*:mapper/${datasource.driver-class-name-type}/*.xml
  type-aliases-package: com.example.mysql

2 原则

尽量修改现有 oracle 的 sql 语句（尽量减少对 mysql 语句的影响，未来持续迭代也要减少对 mysql 的影响），以支持未来更多的数据库。建议如下：

2.1 使用标准SQL语法

尽可能使用标准SQL语法，避免使用特定数据库的专有语法。这可以提高SQL语句的通用性。

2.2 数据类型标准化

统一数据类型，尽量使用标准SQL的数据类型，以便在不同数据库之间切换时更容易适应。

2.3 分页查询

使用标准SQL的分页查询方法，而不是特定数据库的分页语法。

2.4 存储过程和函数

尽量避免使用特定数据库的存储过程和函数，改用标准SQL或Java逻辑处理。

3 改造

3.1 单mapper单SQL模式

兼容 PG 示例，注意连字符 MySQL使用 concat ，Oracle 使用 || ，PG使用 || 或 concat

<select id="selectSample">
     select a,b,c from bw_sample
     <where>
         <!-- if实现特定数据库差异化sql -->
         <if test="_databaseId='mysql'">
             and d = concat(#{d}, "%")
         </if>
         <if test="_databaseId='oracle'">
             and d  = #{d}||"%"
         </if>
         <if test="_databaseId='postgresql'">
             and d  = #{d}||'%'
         </if>
     </where>
 </select>

或者

<select id="selectSample">
     select a,b,c from bw_sample
     <where>
         <!-- if实现特定数据库差异化sql -->
         <if test="_databaseId='mysql'">
             and d = concat(#{d}, "%")
         </if>
         <if test="_databaseId='oracle'">
             and d  = #{d}||"%"
         </if>
         <if test="_databaseId='postgresql'">
             and d = concat(#{d}, '%')
         </if>
     </where>
 </select>

3.2 单mapper多SQL模式

兼容 PG 示例

<!-- 批量插入 --> 
<insert id="insertBatchSample" parameterType="java.util.List">
   INSERT INTO bw_sample
   (ID,REVISION,CREATED_BY)
   VALUES
   <foreach collection="list" item="item" index="index" separator=",">
     (#{item.id,jdbcType=BIGINT}, #{item.revision,jdbcType=VARCHAR}, #{item.createdBy,jdbcType=VARCHAR})
   </foreach>
 </insert>

 <insert id="insertBatchSample" parameterType="java.util.List" databaseId="oracle">
   INSERT INTO bw_sample
   (ID,REVISION,CREATED_BY)
   <foreach collection="list" item="item" index="index" separator="union">     select #{item.id,jdbcType=BIGINT},#{item.revision,jdbcType=VARCHAR},#{item.createdBy,jdbcType=VARCHAR} from dual
   </foreach>
 </insert>

 <insert id="insertBatchSample" parameterType="java.util.List" databaseId="postgresql">
     INSERT INTO bw_sample
     (ID, REVISION, CREATED_BY)
     VALUES
     <foreach collection="list" item="item" index="index" separator=",">
      (#{item.id,jdbcType=BIGINT}, #{item.revision,jdbcType=VARCHAR}, #{item.createdBy,jdbcType=VARCHAR})
     </foreach>
 </insert>

3.3 多mapper目录模式

兼容 PG 示例

3.4 分页查询

兼容 PG 示例，一般项目中都使用了分页组件，不涉及此类改造

<select id="findUsersByPage" parameterType="map" resultType="User">
     SELECT * FROM user WHERE name LIKE #{name}||'%' ORDER BY id LIMIT #{limit} OFFSET #{offset} 
</select>
 <select id="findUsersByPage" parameterType="map" resultType="User" databaseId="oracle">
     SELECT * FROM (
         SELECT ROWNUM rn, t.* FROM (
             SELECT * FROM user WHERE name LIKE #{name}||"%"
         ) t
         WHERE ROWNUM <= #{end}
     ) WHERE rn >= #{start}
 </select>

 <select id="findUsersByPage" parameterType="map" resultType="User" databaseId="postgresql">
     SELECT * FROM user WHERE name LIKE #{name}||'%' ORDER BY id LIMIT #{limit} OFFSET #{offset} 
</select>

3.5 日期函数

兼容 PG 示例，TO_DATE 函数 Oracle 和 PG 数据库均支持，但结果不同

TO_DATE('2023-10-01 14:30:00', 'yyyy-MM-dd HH24:MI:SS')： Oracle（2023-10-01 14:30:00.000）、PG（2023-10-01）

<select id="queryByDateTime" parameterType="string" resultType="com.example.model.YourModel">
     <choose>
         <when test="_databaseId == 'mysql'">
             SELECT * FROM bw_sample WHERE date_column = DATE_FORMAT(#{myTime},'%Y-%m-%d %k:%i:%s')
         </when>
         <when test="_databaseId == 'oracle'">
             SELECT * FROM bw_sample WHERE date_column = TO_DATE(#{myTime}, 'YYYY-MM-DD HH24:MI:SS')
         </when>
         <when test="_databaseId == 'postgresql'">
             SELECT * FROM bw_sample WHERE date_column = TO_TIMESTAMP(#{myTime}, 'YYYY-MM-DD HH24:MI:SS')
         </when>
     </choose>
 </select>

3.6 字符串或字符类型转换为数值类型

兼容 PG 示例，代码片段

<choose>
   <when test="_databaseId == 'oracle'">
     order by TO_NUMBER(XH) ASC
   </when>
   <when test="_databaseId == 'postgresql'">
     order by XH::int ASC
   </when>
   <otherwise>
     order by CAST(XH AS SIGNED) ASC
   </otherwise>
 </choose>

或者

<choose>
   <when test="_databaseId == 'oracle'">
     order by TO_NUMBER(XH) ASC
   </when>
   <when test="_databaseId == 'postgresql'">
     order by XH::numeric ASC
   </when>
   <otherwise>
     order by CAST(XH AS SIGNED) ASC
   </otherwise>
 </choose>

3.7 字符串截取

兼容 PG 示例，注意 MySQL 同时支持 SUBSTR 或者 SUBSTRING，Oracle 仅支持 SUBSTR ，PG 仅支持 SUBSTRING

<select id="queryByDateTime" parameterType="string" resultType="com.example.model.YourModel">
     <choose>
         <when test="_databaseId == 'mysql'">
              SELECT SUBSTR('2024-08-28 20:20:20', 1, 10) AS myDate FROM DUAL
         </when>
         <when test="_databaseId == 'oracle'">
              SELECT SUBSTR('2024-08-28 20:20:20', 1, 10) AS myDate FROM DUAL
         </when>
         <when test="_databaseId == 'postgresql'">
              SELECT SUBSTRING('2024-08-28 20:20:20', 1, 10) AS myDate
         </when>
     </choose>
 </select>

或者

<select id="queryByDateTime" parameterType="string" resultType="com.example.model.YourModel">
     <choose>
         <when test="_databaseId == 'mysql'">
              SELECT SUBSTRING('2024-08-28 20:20:20', 1, 10) AS myDate FROM DUAL
         </when>
         <when test="_databaseId == 'oracle'">
              SELECT SUBSTR('2024-08-28 20:20:20', 1, 10) AS myDate FROM DUAL
         </when>
         <when test="_databaseId == 'postgresql'">
              SELECT SUBSTRING('2024-08-28 20:20:20', 1, 10) AS myDate
         </when>
     </choose>
 </select>

3.8 为NULL时替换默认值

兼容 PG 示例，注意 MySQL 使用 IFNULL，Oracle 仅支持 NVL，PG 仅支持 COALESCE

<select id="queryByDateTime" parameterType="string" resultType="com.example.model.YourModel">
     <choose>
         <when test="_databaseId == 'mysql'">
              IFNULL(bw_qdfp_mx.je, 0)
         </when>
         <when test="_databaseId == 'oracle'">
              NVL(bw_qdfp_mx.je, 0)
         </when>
         <when test="_databaseId == 'postgresql'">
             COALESCE(bw_qdfp_mx.je, 0)
         </when>
     </choose>
 </select>

3.9 四舍五入到指定的小数位数

兼容 PG 示例，注意 MySQL 使用 TRUNCATE ，Oracle 使用 ROUND ，PG 使用 ROUND 

<select id="queryByDateTime" parameterType="string" resultType="com.example.model.YourModel">
    <choose>
        <when test="_databaseId == 'mysql'">
            TRUNCATE (
            SUM(
            CASE
            WHEN bw_qdfp_mx.zzstsgl = '03' AND bw_qdfp_mx.ZZS_SL = '0.000000'
            THEN IFNULL(bw_qdfp_mx.je, 0)
            ELSE 0
            END
            ),
            6
            ) JE_FREE
        </when>
        <when test="_databaseId == 'oracle'">
            ROUND (
            SUM(
            CASE
            WHEN bw_qdfp_mx.ZZSTSGL = '03' AND bw_qdfp_mx.ZZS_SL = '0.000000'
            THEN NVL(bw_qdfp_mx.je, 0)
            ELSE 0
            END
            ),
            6
            ) JE_FREE
 
        </when>
        <when test="_databaseId == 'postgresql'">
            ROUND(
            SUM(
            CASE
            WHEN bw_qdfp_mx.ZZSTSGL = '03' AND bw_qdfp_mx.ZZS_SL = '0.000000'
            THEN COALESCE(bw_qdfp_mx.je, 0)
            ELSE 0
            END
            ),
            6
            ) AS JE_FREE
        </when>
    </choose>
</select>

3.10 隐式转换

隐式转换可能会对查询性能产生影响，特别是在涉及大量数据时。尽量避免在频繁使用的查询中依赖隐式转换。

-- 创建cast需要有pg_cast系统表的权限
CREATE CAST (numeric AS varchar) WITH INOUT AS IMPLICIT;
CREATE CAST (varchar AS numeric) WITH INOUT AS IMPLICIT;
 
-- 如果存在就不创建
DO $$
BEGIN
   -- 检查从 numeric 到 varchar 的转换是否已经存在
   IF NOT EXISTS (
       SELECT 1
       FROM pg_cast
       WHERE castsource = 'numeric'::regtype
       AND casttarget = 'varchar'::regtype
       AND castcontext = 'i' -- 'i' 代表隐式转换
   ) THEN
       -- 如果不存在，则创建转换
       EXECUTE 'CREATE CAST (numeric AS varchar) WITH INOUT AS IMPLICIT';
   END IF;

   -- 检查从 varchar 到 numeric 的转换是否已经存在
   IF NOT EXISTS (
       SELECT 1
       FROM pg_cast
       WHERE castsource = 'varchar'::regtype
       AND casttarget = 'numeric'::regtype
       AND castcontext = 'i' -- 'i' 代表隐式转换
   ) THEN
       -- 如果不存在，则创建转换
       EXECUTE 'CREATE CAST (varchar AS numeric) WITH INOUT AS IMPLICIT';
   END IF;
END $$;

在 PG 中，如果存在多个匹配的隐式转换，系统可能会因为不确定选择哪个转换而报错。为了避免这种情况，可以采取以下几种策略：

1 显式转换

-- 显式转换
SELECT name, CAST(price AS varchar) || 'USD' AS price_with_currency FROM MY_DUAL;

2 删除多余的隐式转换

-- 删除多余的隐式转换
DROP CAST (numeric AS varchar);

3 使用 COERCION 策略

-- 通常情况下，删除多余转换或使用显式转换更为简单和明确。

IMPLICIT：最高优先级，可以在任何需要的地方自动应用。
ASSIGNMENT：次高优先级，仅在赋值时自动应用。
NONE：最低优先级，永远不会自动应用，只能通过显式转换使用。

4 错误示例

-- 创建两个隐式转换
CREATE CAST (numeric AS varchar) WITH INOUT AS IMPLICIT;
CREATE CAST (numeric AS text) WITH INOUT AS IMPLICIT;

-- 插入一些数据
INSERT INTO MY_DUAL(name, price) VALUES ('Product A', 19.99);
INSERT INTO MY_DUAL(name, price) VALUES ('Product B', 29.99);
 
-- 可能会报错，因为存在多个匹配的隐式转换
SELECT name, price || ' USD' AS price_with_currency FROM MY_DUAL;
 
-- 方法一：显式转换
-- 显式转换为 varchar
SELECT name, CAST(price AS varchar) || ' USD' AS price_with_currency FROM MY_DUAL;
 
-- 方法二：删除多余的隐式转换
-- 删除从 numeric 到 text 的隐式转换
DROP CAST (numeric AS text);
-- 现在只有从 numeric 到 varchar 的隐式转换
SELECT name, price || ' USD' AS price_with_currency FROM MY_DUAL;