MyBatis-Plus实现MySQL和Starrocks多数据源整合

项目中有需求要同时使用MySQL和Starrocks，正好整理一下，文末有完整代码包

先了解一下Starrocks

对于Starrocks的一些文档，看官网来的更直接一些

官方文档：docs.starrocks.io/zh/docs/int…

阿里的文档：help.aliyun.com/zh/emr/emr-… （有些实际操作能借鉴）

实际使用及链接Starrocks库

实际使用可以当作mysql来使用，能使用baomidou.com/ MyBatis-Plus，只要支持mysql的orm框架都可以接入使用。

数据库连接可以直接使用DBeaver dbeaver.io/download/

建表语句也是可以参照通用的sql标准，就是表的属性上有所不同，需要注意，（表属性这里需要学习一下），下面是一个建表的示例。 表属性部分

0. ENGINE = OLAP DUPLICATE KEY (conn_id) COMMENT "OLAP"：

• 表明该表使用 OLAP 引擎。设置了重复键为conn_id，这意味着conn_id列的值在表中可以重复出现，并且可能用于某些特定的查询和操作。注释为 “OLAP” 用于对表的用途进行说明。

2. DISTRIBUTED BY HASH (user) BUCKETS 10：

• 表数据按照user列的值进行哈希分区，将数据分布到 10 个桶（buckets）中。这样可以提高数据的并行处理能力和查询性能。

3. PROPERTIES (部分：

• "replication_num" = "2"：指定表的副本数为 2，这意味着数据会在集群中存储两份，以提高数据的可用性和容错性。
• "in_memory" = "false"：表示表数据不是全部存储在内存中。
• "storage_format" = "DEFAULT"：使用默认的存储格式。
• "enable_persistent_index" = "false"：不启用持久化索引。
• "compression" = "LZ4"：使用 LZ4 压缩算法对数据进行压缩，以减少存储空间占用和提高数据读写性能。

starrocks建表语句

/* 创建表。*/
CREATE TABLE
    `test_emr_query_logs` (
      `conn_id` varchar(10) NULL COMMENT "链接id",
      `database` varchar(100) NULL COMMENT "数据库",
      `start_time` bigint (20) NULL COMMENT "开始时间",
      `end_time` bigint (20) NULL COMMENT "结束时间",
      `event_time` bigint (20) NULL COMMENT "事件事件",
      `is_query` boolean NULL COMMENT "是否查询",
      `latency` int (11) NULL COMMENT "延迟",
      `query_id` varchar(40) NULL COMMENT "查询id",
      `remote_ip` varchar(15) NULL COMMENT "远程ip",
      `state` varchar(20) NULL COMMENT "状态",
      `user` varchar(20) NULL COMMENT "用户"
    ) ENGINE = OLAP DUPLICATE KEY (`conn_id`) COMMENT "OLAP" DISTRIBUTED BY HASH (`user`) BUCKETS 10 PROPERTIES (
      "replication_num" = "2",
      "in_memory" = "false",
      "storage_format" = "DEFAULT",
      "enable_persistent_index" = "false",
      "compression" = "LZ4"
    );
/* 插入数据。*/
insert into
    ais_offline_data_co.test_emr_query_logs (
      `conn_id`,
      `database`,
      `start_time`,
      `end_time`,
      `event_time`,
      `is_query`,
      `latency`,
      `query_id`,
      `remote_ip`,
      `state`,
      `user`
    )
values
    (
      '54656',
      'tpc_h_sf1',
      1691635106990,
      1691635107405,
      1691635107405000000,
      1,
      415,
      'fbec0dd7-3726-11ee-a3ef-720338511ec3',
      '10.0.**.**',
      'FINISHED',
      'admin'
    ),
    (
      '54658',
      'tpc_h_sf1',
      1691635107632,
      1691635107860,
      1691635107860000000,
      1,
      228,
      'fc4e0301-3726-11ee-a3ef-720338511ec3',
      '10.0.**.**',
      'FINISHED',
      'admin'
    ),
    (
      '54659',
      'tpc_h_sf1',
      1691635108757,
      1691635108930,
      1691635108930000000,
      1,
      173,
      'fcf9ac5s8-3726-11ee-a3ef-720338511ec3',
      '10.0.**.**',
      'FINISHED',
      'admin'
    ),
    (
      '54661',
      'tpc_h_sf1',
      1691635108994,
      1691635109137,
      1691635109137000000,
      1,
      143,
      'fd1dd62e-3726-11ee-a3ef-720338511ec3',
      '10.0.**.**',
      'FINISHED',
      'admin'
    ),
    (
      '54663',
      'tpc_h_sf1',
      1691635109445,
      1691635109533,
      1691635109533000000,
      1,
      88,
      'fd62a765-3726-11ee-a3ef-720338511ec3',
      '10.0.**.**',
      'FINISHED',
      'admin'
    ),
    (
      '54664',
      'tpc_h_sf1',
      1691635109724,
      1691635109907,
      1691635109907000000,
      1,
      183,
      'fd8d39d9-3726-11ee-a3ef-720338511ec3',
      '10.0.**.**',
      'FINISHED',
      'admin'
    );
/* 查看表信息。*/
select * from test_emr_query_logs;

MySQL建表语句

-- test.`user` definition 创建一个test库
​
CREATE TABLE `user` (
  `id` bigint DEFAULT NULL COMMENT 'id',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '名称',
  `age` int DEFAULT NULL COMMENT '年龄',
  `email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邮件'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
​
​
INSERT INTO `user` (id,name,age,email) VALUES
     (1,'yuanmomo',18,'u8475845@qq.com'),
     (2,'eefsfsdf',23,'dfjsdshj@qq.com');
​

代码中直接使用

对应的实体、mapper啥的都放在了代码包中。

项目实际中可能会使用到多数据源，需要额外接入dynamic-datasource-spring-boot-starter多数据源来使用。

<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <scope>runtime</scope>
</dependency>
​
<!-- mybatis-plus -->
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
  <version>3.5.4</version>
</dependency>
​
<!-- 多数据源 -->
<!-- https://mvnrepository.com/artifact/com.baomidou/dynamic-datasource-spring-boot-starter -->
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
  <version>3.5.2</version>
</dependency>

springboot配置相关（数据库连接需要改为自己的）

# 应用服务 WEB 访问端口
server:
  port: 8083
​
logging:
  level:
    com.baomidou.example.mapper: debug
​
spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    # 动态数据源文档 https://www.kancloud.cn/tracy5546/dynamic-datasource/content
    dynamic:
      # 设置默认的数据源或者数据源组,默认值即为 master
      primary: master
      datasource:
        # 主数据源
        master:
          type: ${spring.datasource.type}
          driverClassName: com.mysql.cj.jdbc.Driver
          # jdbc 所有参数配置参考 https://lionli.blog.csdn.net/article/details/122018562
          url: jdbc:mysql://120.44.184.244:3336/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
          username: root
          password: 13meng...
        # 数据源
        starrocks:
          lazy: true
          type: ${spring.datasource.type}
          driverClassName: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://aliyunos.test.com:9876/ais_offline_data_co?useSSL=false&serverTimezone=UTC
          username: ais_o33ffline
          password: lRpkgwIOdfd43ssl6OYZP
​
mybatis-plus:
  mapper-locations: classpath:/mapper/*.xml
  type-aliases-package: com.nio.data.service.entity
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #开启sql日志

切换不同的数据库源 使用@DS注解，框架拦截@DS注解方法调用

项目结构

controller包

MySQLUserController.java

package com.yuanmomo.demo.datasource.controller;
​
import com.yuanmomo.demo.datasource.entity.User;
import com.yuanmomo.demo.datasource.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
​
import java.util.List;
​
@RestController
@RequestMapping("/user")
public class MySQLUserController {
​
​
    @Autowired
    private UserService userService;
​
    @GetMapping("/list")
    public List<User> list() {
        return userService.list();
    }
​
    @PostMapping("/add")
    public boolean add(@RequestBody User user) {
        return userService.save(user);
    }
​
    @PutMapping("/update")
    public boolean update(@RequestBody User user) {
        return userService.updateById(user);
    }
​
    @DeleteMapping("/delete/{id}")
    public boolean delete(@PathVariable Long id) {
        return userService.removeById(id);
    }
​
}
​

StarrocksTestEmrQueryLogsController.java

package com.yuanmomo.demo.datasource.controller;
​
​
import com.yuanmomo.demo.datasource.entity.TestEmrQueryLogs;
import com.yuanmomo.demo.datasource.service.TestEmrQueryLogsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
​
import java.util.List;
​
@RestController
@RequestMapping("/testEmrQueryLogs")
public class StarrocksTestEmrQueryLogsController {
​
​
    @Autowired
    private TestEmrQueryLogsService testEmrQueryLogsService;
​
    @GetMapping("/list")
    public List<TestEmrQueryLogs> list() {
        return testEmrQueryLogsService.list();
    }
​
    @PostMapping("/add")
    public boolean add(@RequestBody TestEmrQueryLogs testEmrQueryLogs) {
        return testEmrQueryLogsService.save(testEmrQueryLogs);
    }
​
    @PutMapping("/update")
    public boolean update(@RequestBody TestEmrQueryLogs testEmrQueryLogs) {
        return testEmrQueryLogsService.updateById(testEmrQueryLogs);
    }
​
    @DeleteMapping("/delete/{id}")
    public boolean delete(@PathVariable Long id) {
        return testEmrQueryLogsService.removeById(id);
    }
}
​

entity包

TestEmrQueryLogs.java

package com.yuanmomo.demo.datasource.entity;
​
​
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
​
@Data
@TableName("test_emr_query_logs")
public class TestEmrQueryLogs {
    /**
     * 链接 ID，最大长度为 10 的字符串
     */
    private String connId;
​
    /**
     * 数据库名称，最大长度为 100 的字符串
     */
    //private String database;
​
    /**
     * 开始时间，长整型
     */
    private Long startTime;
​
    /**
     * 结束时间，长整型
     */
    private Long endTime;
​
    /**
     * 事件时间，长整型
     */
    private Long eventTime;
​
    /**
     * 是否查询，布尔类型
     */
    private Boolean isQuery;
​
    /**
     * 延迟时间，整型
     */
    private Integer latency;
​
    /**
     * 查询 ID，最大长度为 40 的字符串
     */
    private String queryId;
​
    /**
     * 远程 IP 地址，最大长度为 15 的字符串
     */
    private String remoteIp;
​
    /**
     * 状态，最大长度为 20 的字符串
     */
    private String state;
​
    /**
     * 用户名称，最大长度为 20 的字符串
     */
    private String user;
}

User.java

package com.yuanmomo.demo.datasource.entity;


import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName("user")
public class User {
    /**
     * id
     */
    @TableId
    private Long id;

    /**
     * 姓名
     */
    private String name;

    /**
     * 年龄
     */
    private Integer age;

    /**
     * 邮件
     */
    private String email;
}

mapper包

TestEmrQueryLogsMapper.java

package com.yuanmomo.demo.datasource.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yuanmomo.demo.datasource.entity.TestEmrQueryLogs;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface TestEmrQueryLogsMapper extends BaseMapper<TestEmrQueryLogs> {
}

UserMapper.java

package com.yuanmomo.demo.datasource.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yuanmomo.demo.datasource.entity.User;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserMapper extends BaseMapper<User> {
}

service包

TestEmrQueryLogsService.java

package com.yuanmomo.demo.datasource.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.yuanmomo.demo.datasource.entity.TestEmrQueryLogs;

public interface TestEmrQueryLogsService extends IService<TestEmrQueryLogs> {
}

UserService.java

package com.yuanmomo.demo.datasource.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.yuanmomo.demo.datasource.entity.User;

public interface UserService extends IService<User> {
}

service.impl包

TestEmrQueryLogsServiceImpl.java

package com.yuanmomo.demo.datasource.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.yuanmomo.demo.datasource.entity.TestEmrQueryLogs;
import com.yuanmomo.demo.datasource.mapper.TestEmrQueryLogsMapper;
import com.yuanmomo.demo.datasource.service.TestEmrQueryLogsService;
import org.springframework.stereotype.Service;

@Service
@DS("starrocks")
public class TestEmrQueryLogsServiceImpl extends ServiceImpl<TestEmrQueryLogsMapper, TestEmrQueryLogs> implements TestEmrQueryLogsService {
}

UserServiceImpl.java

package com.yuanmomo.demo.datasource.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.yuanmomo.demo.datasource.entity.User;
import com.yuanmomo.demo.datasource.mapper.UserMapper;
import com.yuanmomo.demo.datasource.service.UserService;
import org.springframework.stereotype.Service;

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

}

resources.mapper包

TestEmrQueryLogsMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yuanmomo.demo.datasource.mapper.TestEmrQueryLogsMapper">

  <!-- 定义 resultMap -->
  <resultMap id="testEmrQueryLogsResultMap" type="com.yuanmomo.demo.datasource.entity.TestEmrQueryLogs">
    <id property="connId" column="conn_id"/>
    <!--        <result property="database" column="database"/>-->
    <result property="startTime" column="start_time"/>
    <result property="endTime" column="end_time"/>
    <result property="eventTime" column="event_time"/>
    <result property="isQuery" column="is_query"/>
    <result property="latency" column="latency"/>
    <result property="queryId" column="query_id"/>
    <result property="remoteIp" column="remote_ip"/>
    <result property="state" column="state"/>
    <result property="user" column="user"/>
  </resultMap>

</mapper>

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yuanmomo.demo.datasource.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.yuanmomo.demo.datasource.entity.User">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <result column="email" property="email"/>
    </resultMap>

    <sql id="Base_Column_List">
        id, name, age, email
    </sql>

    <select id="selectAll" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List"/>
        FROM user
    </select>
</mapper>

配置文件

# 应用服务 WEB 访问端口
server:
  port: 8083

logging:
  level:
    com.baomidou.example.mapper: debug

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    # 动态数据源文档 https://www.kancloud.cn/tracy5546/dynamic-datasource/content
    dynamic:
      # 设置默认的数据源或者数据源组,默认值即为 master
      primary: master
      datasource:
        # 主数据源
        master:
          type: ${spring.datasource.type}
          driverClassName: com.mysql.cj.jdbc.Driver
          # jdbc 所有参数配置参考 https://lionli.blog.csdn.net/article/details/122018562
          url: jdbc:mysql://120.461.184.244:3336/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
          username: root
          password: mysqlya85555s75987213meng..3.!#
        # 数据源
        starrocks:
          lazy: true
          type: ${spring.datasource.type}
          driverClassName: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://fe-cae-255ea02cfdc92579.starrocks.aliyuncs.com:9030/sr_db?useSSL=false&serverTimezone=UTC
          username: admin
          password: 87520.1413MenGgeesag@%

mybatis-plus:
  mapper-locations: classpath:/mapper/*.xml
  type-aliases-package: com.yuanmomo.demo.datasource.entity
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #开启sql日志

pom.xml内容

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.yuanmomo.demo</groupId>
  <artifactId>mybatis-plus-dynamic-datasource</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>mybatis-plus-dynamic-datasource</name>
  <description>mybatis-plus-dynamic-datasource</description>
  <properties>
    <java.version>17</java.version>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <spring-boot.version>2.7.6</spring-boot.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <scope>runtime</scope>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.5.4</version>
    </dependency>

    <!-- 多数据源 -->
    <!-- https://mvnrepository.com/artifact/com.baomidou/dynamic-datasource-spring-boot-starter -->
    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
      <version>3.5.2</version>
    </dependency>
  </dependencies>
  <dependencyManagement>
    <dependencies>
      <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-dependencies</artifactId>
        <version>${spring-boot.version}</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
    </dependencies>
  </dependencyManagement>

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.8.1</version>
        <configuration>
          <source>1.8</source>
          <target>1.8</target>
          <encoding>UTF-8</encoding>
        </configuration>
      </plugin>
      <plugin>
        <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>${spring-boot.version}</version>
                <configuration>
                    <mainClass>com.yuanmomo.demo.datasource.MybatisPlusDynamicDatasourceApplication</mainClass>
                    <skip>true</skip>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

项目启动，能看到加载了数据源