阿里云ODPS接入MybatisPlus动态数据源插件

背景

项目需要查询阿里云 ODPS（MaxCompute）数据源，原有方式使用 JDBC 直连或 SDK 方式查询。

解决方案

配置 ODPS 数据源

在 application-qa.yml 或 application-prod.yml 中配置：

spring:
  datasource:
    dynamic:
      datasource:
        master:
          url: jdbc:mysql://localhost:3306/your_db
          username: xxx
          password: xxx
          driver-class-name: com.mysql.cj.jdbc.Driver
        odps:
          url: jdbc:odps:https://service.cn-hangzhou.maxcompute.aliyun.com/api?project=${aliyun.maxcompute.project}&useProjectTimeZone=true
          username: ${aliyun.maxcompute.keyId}
          password: ${aliyun.maxcompute.keySecret}
          driver-class-name: com.aliyun.odps.jdbc.OdpsDriver
          hikari:
            minimum-idle: 0
            maximum-pool-size: 10

添加 ODPS JDBC 依赖

在 pom.xml 中添加：

<dependency>
    <groupId>com.aliyun.odps</groupId>
    <artifactId>odps-jdbc</artifactId>
    <version>3.10.1</version>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.6.0</version>
</dependency>

自定义方言

为了支持 ODPS 数据源，需要自定义一个方言类 ODPSDialect 实现 IDialect 接口。

public class DynamicDialect implements IDialect {

    private static final Map<String, IDialect> DIALECT_MAP = ImmutableMap.of(
            "master", new MySqlDialect(),
            "odps", new MySqlDialect()
    );

    @Override
    public DialectModel buildPaginationSql(String originalSql, long offset, long limit) {
        String ds = DynamicDataSourceContextHolder.peek();
        IDialect iDialect = DIALECT_MAP.get(ds);
        if (iDialect == null) {
            throw new BusinessException("数据源需要手动设置方言");
        }
        return iDialect.buildPaginationSql(originalSql, offset, limit);
    }

}

手动设置插件的方言处理：

    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor interceptor = new PaginationInterceptor();
        interceptor.setDialectClazz(DynamicDialect.class.getName());
        return interceptor;
    }

Mapper 改造

@DS("odps")
public interface ODPSMapper {

    List<Map<String, Object>> listOrder(@Param("ds") String ds, @Param("date") String date);
}

创建对应的 XML 文件

在 src/main/resources/mapper/odps/ 下创建 ODPSMapper.xml：

LocalDate 转 Timestamp 异常

问题：Cannot transform ODPS-SDK Java class java.time.LocalDate to java.sql.Timestamp

官方文档引用：

Currently, 16 ODPS data types are supported. Please see the following table for supported ODPS data types and corresponding JDBC interfaces.

When the getObject() method is called, what is obtained is the Java type directly corresponding to each ODPS type without any conversion operation. Please see the following table for he correspondence between ODPS types and Java types.

NOTE: Possible timezone issue

DATETIME in MaxCompute is actually defined as EPOCH in milliseconds, which is UTC, and so is TIMESTAMP in JDBC. This driver fill the DATETIME value directly into JDBC TIMESTAMP and do no parse or format action. When application that using JDBC display a DATETIME as a human-readable string format, it is the application itself did the format using application defined or OS defined timezone. It is suggested to keep your application/OS timezone setting same to MaxCompute to avoid inconsistent datetime parse/format.

附录

相关文件清单

数据源类型支持

版本信息

• Spring Boot: 2.1.5.RELEASE
• MyBatis-Plus: 3.x
• dynamic-datasource: 3.6.0
• odps-jdbc: 3.10.1