Mybatis-Plus入门系列(20) -兼容多种数据库

太过爱你忘了你带给我的痛 2023-10-09 15:42 43阅读 0赞

> **有道无术，术尚可求，有术无道，止于术。**

#### 文章目录 ####

*   *  前言
     *  方案分析
     *   *  1. 分页
         *  2. XML自定义SQL
     *  案例演示
     *   *  1. 配置
         *  2. 简单分页查询
         *  3. 带方言的分页查询
     *  参考

### 前言 ###

在我们实际开发软件产品过程中，数据库的类型可能不是确定的，也有客户会有要求必须用什么数据库，比如很多政府机构要求必须使用**国产数据库**，所以我们在开发时，需要适配多种数据库。

`MySQL`、`Oracle`、`PostgreSQL`、达梦等数据库在进行增删改查时，都是基于美国国家标准局制定的`SQL`标准，比如`SQL-92`、`SQL-99`。

但是每个数据库厂商实际的`SQL`会有较小差异，也就是数据库方言，大家最熟知的就是`MySQL`分页使用`limit`，`Oracle`分页使用`rownum`。

`MyBatis-Plus`支持各种标准 `SQL` 的数据库，接下来我们实际演示如何使用`MyBatis-Plus`适配各种数据库。  
![在这里插入图片描述][fac5faf179ab4be69bc37e22918efc45.png]

### 方案分析 ###

#### 1. 分页 ####

很多数据库分页`SQL`使用方式都不大相同，`MyBatis-Plus`内置分页插件`PaginationInnerInterceptor`已支持多种数据库，官网说明：  
![在这里插入图片描述][d33851be8ea348088c9d4851a5f42f30.png]

在使用**内置分页插件**时，可以设置数据库的类型：

@Configuration
    @MapperScan("com.pearl.pay.mapper") //持久层扫描
    @EnableTransactionManagement //启用事务管理
    public class MybatisPlusConfig {
        
        @Bean
        @ConditionalOnMissingBean(MybatisPlusInterceptor.class)
        public MybatisPlusInterceptor mybatisPlusInterceptor() {
        
            MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
            PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
            paginationInnerInterceptor.setDbType(DbType.MYSQL);
            interceptor.addInnerInterceptor(paginationInnerInterceptor);
            return interceptor;
        }
    }

**内置分页插件**在执行`SQL`时，会根据当前数据库类型获取**分页方言**。  
![在这里插入图片描述][63192ee1ebf148878dcb1a5be39efe7c.png]  
在**分页方言工厂类**`DialectFactory`中，可以看到具体获取方言逻辑，`mysql`、`mariadb`、`clickhouse`、`oceanbase`等数据库都是使用`mysql`方言。  
![在这里插入图片描述][483ded15425d4af9bd55fcf42cbf2694.png]  
`oracle`、`达梦数据库`用的是`oracle`方言：  
![在这里插入图片描述][12950db2c8594e29b96d1a59b2ea130a.png]  
`MYSQL` 数据库分页语句使用`LIMIT`组装：  
![在这里插入图片描述][dd0392a3422f4bdd860bcad7cf3867b0.png]  
`ORACLE` 数据库分页语句使用`ROWNUM、ROW_ID`组装：  
![在这里插入图片描述][a6767acac1634764baa30a0e02cfb8cb.png]  
**综上：** **在分页时，适配多种数据库只需要在分页插件中设置数据库类型即可。**

#### 2. XML自定义SQL ####

调用`MP`的`API`进行增删改查时，比如调用`xxMpper.selectList()`时，因为`MP`在构建`SQL`时，都是使用的基础标准，所以一般不存在兼容问题。但是我们自己在`XML`文件中编写`SQL`，就需要注意各种数据库匹配兼容问题了。

`Mybatis`本身已经做了多数据库支持，只需要告诉框架用的是什么数据库，可以根据不同的数据库厂商执行不同的语句。

`Mybatis`中的`DatabaseIdProvider`（数据库厂商标识提供者）接口声明了获取厂商标识的方法，标识可用于以后为每种数据库类型构建不同的查询，该机制支持多个厂商或版本。

public interface DatabaseIdProvider {
        
    
      default void setProperties(Properties p) {
        
        // NOP
      }
      // 根据数据源获取数据库厂商标识
      String getDatabaseId(DataSource dataSource) throws SQLException;
    }

`Mybatis`也提供了`VendorDatabaseIdProvider`实现类：

public class VendorDatabaseIdProvider implements DatabaseIdProvider {
        
      
      // 支持的数据库厂商（需要自己定义），比如： Oracle=》oracle
      private Properties properties;
      // 获取数据库厂商标识ID（databaseId）,eg：oracle
      @Override
      public String getDatabaseId(DataSource dataSource) {
        
        if (dataSource == null) {
        
          throw new NullPointerException("dataSource cannot be null");
        }
        try {
        
          return getDatabaseName(dataSource);
        } catch (Exception e) {
        
          LogHolder.log.error("Could not get a databaseId from dataSource", e);
        }
        return null;
      }
    
      @Override
      public void setProperties(Properties p) {
        
        this.properties = p;
      }
      // 根据产品名称，获取对应的databaseId。
      private String getDatabaseName(DataSource dataSource) throws SQLException {
        
        String productName = getDatabaseProductName(dataSource);
        if (this.properties != null) {
        
          for (Map.Entry<Object, Object> property : properties.entrySet()) {
        
            if (productName.contains((String) property.getKey())) {
        
              return (String) property.getValue();
            }
          }
          // no match, return null
          return null;
        }
        return productName;
      }
      // 从数据源中获取数据库产品名称，比如： Oracle
      private String getDatabaseProductName(DataSource dataSource) throws SQLException {
        
        try (Connection con = dataSource.getConnection()) {
        
          DatabaseMetaData metaData = con.getMetaData();
          return metaData.getDatabaseProductName();
        }
      }
    }

在`Mybatis`的`XML`中编写`SQL`时，有个`databaseId`属性，可以指定当前语句块属于哪个数据库类型，比如：

<mapper namespace="org.pearl.mybatis.demo.dao.UserMapper">
        <select id="selectOneById" resultType="org.pearl.mybatis.demo.pojo.entity.User" databaseId="mysql">
        select * from user where user_id = #{id}
      </select>
    </mapper>

**综上：我们只需要配置`DatabaseIdProvider`中支持哪些数据库，然后在`XML`中针对每种数据库方言编写查询语句，并添加`databaseId`属性，`Mybatis`会在启动时获取数据源使用的哪个类型数据库，然后执行配置了当前数据库对应的语句**

### 案例演示 ###

#### 1. 配置 ####

搭建工程，集成`MP`、`Oracle`、`Mysql`很简单，这里就不赘述了。

在配置文件中，添加对应的`Oracle`、`Mysql`连接地址：

spring:
      datasource:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: oracle.jdbc.OracleDriver
        url: jdbc:oracle:thin:@127.0.0.1:1521:ORCL
        username: root
        password: root
        #driver-class-name: com.mysql.cj.jdbc.Driver
        #url: jdbc:mysql://127.0.0.1:3306/d_account?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
        #username: root
        #password: root

在`MP`中添加配置类：

@Configuration
    @MapperScan("com.pearl.pay.mapper") //持久层扫描
    @EnableTransactionManagement //启用事务管理
    public class MybatisPlusConfig {
        
         @Bean
        public MybatisPlusInterceptor mybatisPlusInterceptor(DataSource dataSource,DatabaseIdProvider databaseIdProvider) throws SQLException {
        
            // MP插件
            MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
            PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
            // 获取当前数据源对应的数据库类型，添加分页插件
            String databaseId = databaseIdProvider.getDatabaseId(dataSource);
            DbType dbType = DbType.getDbType(databaseId);
            paginationInnerInterceptor.setDbType(dbType);
            interceptor.addInnerInterceptor(paginationInnerInterceptor);
            return interceptor;
        }
    
        @Bean
        public DatabaseIdProvider databaseIdProvider() {
        
            // 数据库厂商提供者
            DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
            Properties p = new Properties();
            p.setProperty("Oracle", "oracle");
            p.setProperty("Mysql", "mysql");
            databaseIdProvider.setProperties(p);
            return databaseIdProvider;
        }
    }

#### 2. 简单分页查询 ####

因为`MyBatis-Plus`内置分页插件已经做了适配，简单的（没有数据库方言）分页查询不用自己写代码适配。

首先添加一个分页查询：

IPage<User> test(Page<User> page);

<select id="test" resultType="com.pearl.entity.User">
            select * from user
        </select>

数据源配置的是`Oracle`，没有配置`databaseId`，测试`SQL`语句打印如下：

SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( select * from user) TMP WHERE ROWNUM <=10) WHERE ROW_ID > 0

数据源配置为`Mysql`，测试`SQL`语句打印如下：

select * from user LIMIT 10

#### 3. 带方言的分页查询 ####

**需求**： **查询时间节点小于当前时间的数据。**

`Oracle`当前时间使用的是`sysdate`函数，`Mysql`使用的是`now()`函数，这个时候就需要手动去兼容了。

编写两个重名的查询语句，针对不同的数据库厂商编写`SQL`，并配置对应的`databaseId`

<select id="test" resultType="com.pearl.entity.User" databaseId="mysql">
            select * from user t <![CDATA[ where t.create_time <= now()]]>
        </select>
        
        <select id="test" resultType="com.pearl.entity.User" databaseId="oracle">
            select * from user t  <![CDATA[ where t.create_time  <=  sysdate ]]>
        </select>

也可以使用`if`语句，判断当前数据库类型，添加不同语句（推荐）。

<select id="test" resultType="com.pearl.entity.User">
            select * from user t
            <where>
                <if test="_databaseId == 'mysql'">
                    <![CDATA[ AND t.create_time <= now()]]>
                </if>
                <if test="_databaseId == 'oracle'">
                    <![CDATA[ AND t.create_time  <=  sysdate ]]>
                </if>
            </where>
        </select>

切换数据库，执行如下：

select * from user t where t.create_time <= now() LIMIT 10
    SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( select * from user t where t.create_time <= sysdate ) TMP WHERE ROWNUM <=10) WHERE ROW_ID > 0

### 参考 ###

接下来我们简单了解下`Oracle` 和`Mysql`的一些区别，便于开发。

**数据类型对照：**

<table> 
 <thead> 
  <tr> 
   <th>数据库</th> 
   <th>对比项</th> 
   <th>类型</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MySQL</td> 
   <td>数据类型</td> 
   <td>INTEGER、SMALLINT、TINYINT、MEDIUMINT、BIGINT</td> 
  </tr> 
  <tr> 
   <td>Oracle</td> 
   <td>数据类型</td> 
   <td>number</td> 
  </tr> 
  <tr> 
   <td>MySQL</td> 
   <td>日期和时间</td> 
   <td>date、timestamp、timestamp</td> 
  </tr> 
  <tr> 
   <td>Oracle</td> 
   <td>日期和时间</td> 
   <td>date、timestamp</td> 
  </tr> 
  <tr> 
   <td>MySQL</td> 
   <td>字符类型</td> 
   <td>char、varchar</td> 
  </tr> 
  <tr> 
   <td>Oracle</td> 
   <td>字符类型</td> 
   <td>char、varchar、varchar2、nvarchar、nvarchar2</td> 
  </tr> 
  <tr> 
   <td>MySQL</td> 
   <td>大字段</td> 
   <td>LONGTEXT</td> 
  </tr> 
  <tr> 
   <td>Oracle</td> 
   <td>大字段</td> 
   <td>clob</td> 
  </tr> 
 </tbody> 
</table>

**常用函数对照：**

<table> 
 <thead> 
  <tr> 
   <th>数据库</th> 
   <th>对比项</th> 
   <th>函数</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MySQL</td> 
   <td>获取字符串长度</td> 
   <td>char_length(str)</td> 
  </tr> 
  <tr> 
   <td>Oracle</td> 
   <td>获取字符串长度</td> 
   <td>length(str)</td> 
  </tr> 
  <tr> 
   <td>MySQL</td> 
   <td>生成随机序列</td> 
   <td>UUID()</td> 
  </tr> 
  <tr> 
   <td>Oracle</td> 
   <td>生成随机序列</td> 
   <td>sys_guid()</td> 
  </tr> 
  <tr> 
   <td>MySQL</td> 
   <td>时间转换为字符串</td> 
   <td>date_format(NOW(),‘%Y-%m-%d’)</td> 
  </tr> 
  <tr> 
   <td>Oracle</td> 
   <td>时间转换为字符串</td> 
   <td>to_char(sysdate, ‘YYYY-MM-DD’)</td> 
  </tr> 
  <tr> 
   <td>MySQL</td> 
   <td>字符串型时间转换为时间</td> 
   <td>str_to_date(‘2019-01-01’,‘%Y-%m-%d’)</td> 
  </tr> 
  <tr> 
   <td>Oracle</td> 
   <td>字符串型时间转换为时间</td> 
   <td>to_date(‘2019-01-01’, ‘YYYY-MM-DD’)</td> 
  </tr> 
  <tr> 
   <td>MySQL</td> 
   <td>包含时分秒的函数转换</td> 
   <td>date_format(NOW(),‘%Y-%m-%d %H:%i:%s’)</td> 
  </tr> 
  <tr> 
   <td>Oracle</td> 
   <td>包含时分秒的函数转换</td> 
   <td>str_to_date(‘2019-01-01’,‘%Y-%m-%d %H:%i:%s’)</td> 
  </tr> 
  <tr> 
   <td>MySQL</td> 
   <td>当前时间</td> 
   <td>now()</td> 
  </tr> 
  <tr> 
   <td>Oracle</td> 
   <td>当前时间</td> 
   <td>sysdate</td> 
  </tr> 
 </tbody> 
</table>

**其他：**

<table> 
 <thead> 
  <tr> 
   <th>数据库</th> 
   <th>对比项</th> 
   <th>支持</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MySQL</td> 
   <td>引号</td> 
   <td>双引号和单引号</td> 
  </tr> 
  <tr> 
   <td>Oracle</td> 
   <td>引号</td> 
   <td>只能识别单引号</td> 
  </tr> 
  <tr> 
   <td>MySQL</td> 
   <td>字符串连接符</td> 
   <td>concat()函数</td> 
  </tr> 
  <tr> 
   <td>Oracle</td> 
   <td>字符串连接符</td> 
   <td>可用双竖线连接字符串</td> 
  </tr> 
  <tr> 
   <td>MySQL</td> 
   <td>分页</td> 
   <td>limit</td> 
  </tr> 
  <tr> 
   <td>Oracle</td> 
   <td>分页</td> 
   <td>rownum</td> 
  </tr> 
 </tbody> 
</table>

[fac5faf179ab4be69bc37e22918efc45.png]: https://img-blog.csdnimg.cn/fac5faf179ab4be69bc37e22918efc45.png
[d33851be8ea348088c9d4851a5f42f30.png]: https://img-blog.csdnimg.cn/d33851be8ea348088c9d4851a5f42f30.png
[63192ee1ebf148878dcb1a5be39efe7c.png]: https://img-blog.csdnimg.cn/63192ee1ebf148878dcb1a5be39efe7c.png
[483ded15425d4af9bd55fcf42cbf2694.png]: https://img-blog.csdnimg.cn/483ded15425d4af9bd55fcf42cbf2694.png
[12950db2c8594e29b96d1a59b2ea130a.png]: https://img-blog.csdnimg.cn/12950db2c8594e29b96d1a59b2ea130a.png
[dd0392a3422f4bdd860bcad7cf3867b0.png]: https://img-blog.csdnimg.cn/dd0392a3422f4bdd860bcad7cf3867b0.png
[a6767acac1634764baa30a0e02cfb8cb.png]: https://img-blog.csdnimg.cn/a6767acac1634764baa30a0e02cfb8cb.png