Apache Doris——SpringBoot集成Doris

概述

由于 Doris 高度兼容 Mysql 协议，两者在 SQL 语法方面有着比较强的一致性，另外 Mysql 客户端也是 Doris 官方选择的客户端。因此，如需对 Mysql 进行数据分析，使用 Doris 的迁移成本较低。另外加上 Doris 各种优秀的计算能力，对原 Mysql 开发人员来说，基于 Doris 进行 Mysql 数据分析是一个良好的选择。

一、SpringBoot集成Doris

1.1 创建表

mysql> CREATE TABLE `doris_test` (
    ->   `id` int NULL COMMENT "id",
    ->   `name` varchar(40) NULL COMMENT "名称"
    -> ) ENGINE=OLAP
    -> DUPLICATE KEY(`id`)
    -> COMMENT "OLAP"
    -> DISTRIBUTED BY HASH(`id`) BUCKETS 1
    -> PROPERTIES (
    -> "replication_num" = "1",
    -> "in_memory" = "false",
    -> "storage_format" = "V2"
    -> );
Query OK, 0 rows affected (0.06 sec)

1.2 引入Maven

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.6.1</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>1.8</java.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!--如果要用传统的xml或properties配置，则需要添加此依赖-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-configuration-processor</artifactId>
    </dependency>

    <dependency>
        <groupId>tk.mybatis</groupId>
        <artifactId>mapper-spring-boot-starter</artifactId>
        <version>2.1.5</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-aop</artifactId>
    </dependency>

    <!-- swagger -->
    <dependency>
        <groupId>io.springfox</groupId>
        <artifactId>springfox-swagger-ui</artifactId>
        <version>2.9.2</version>
    </dependency>

    <dependency>
        <groupId>io.springfox</groupId>
        <artifactId>springfox-swagger2</artifactId>
        <version>2.9.2</version>
    </dependency>

 
    <!-- fastjson -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.70</version>
    </dependency>


    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>

</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>

        <plugin>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-maven-plugin</artifactId>
            <version>1.3.6</version>
            <configuration>
                <configurationFile>
                    ${basedir}/src/main/resources/generator/generatorConfig.xml
                </configurationFile>
                <overwrite>true</overwrite>
                <verbose>true</verbose>
            </configuration>
            <dependencies>
                <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <version>5.1.41</version>
                </dependency>
                <dependency>
                    <groupId>tk.mybatis</groupId>
                    <artifactId>mapper</artifactId>
                    <version>4.1.5</version>
                </dependency>
            </dependencies>
        </plugin>
    </plugins>
</build>

1.3 通过接口Spring boot mybatis的方式访问数据

• application.yml

server:
  port: 8080
spring:
  #数据库连接配置
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://192.168.1.161:9030/wudl_db?characterEncoding=utf-8&useSSL=false
    username: root
    password:

#mybatis的相关配置
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.wudl.doris.domain.DorisTest

• Controller

@RestController
public class DorisController {

    @Autowired
    DorisService dorisService;

    @GetMapping("/dorislist")
    public List<DorisTest>  getListDoris()
    {
        return  dorisService.listDoris();
    }
}

• Service

public interface DorisService {

    /**
     * 查询所有的表数据
     * @return
     */
   public List<DorisTest> listDoris();

}

@Service
public class DorisServiceImpl implements DorisService {

    @Autowired
    DorisMapper dorisMapper;
    @Override
    public List<DorisTest> listDoris() {
        return dorisMapper.listDoris();
    }
}

• 实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class DorisTest {

    private int id;
    private String name;

}

• Mapper接口

public interface DorisMapper {

    /**
     * 查询所有的doris 数据
     * @return
     */
   public  List<DorisTest> listDoris();

}

• Mapper.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.wudl.doris.mapper.DorisMapper">

    <select id="listDoris" resultType="com.wudl.doris.domain.DorisTest">
        select id,name from doris_test
    </select>
</mapper>

二、SpringBoot 配置MySql和Doris多数据源

SpringBoot配置Mysql和Doris数据源， 其实跟配置Mysql多个数据源是一个样的。

2.1 application.yml配置

server:
  port: 8080
 
#数据库连接配置
spring:
  datasource:
    mysql: # mysql配置
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: 数据库地址, 这里会根据版本不同名字不一样（有的是url）
      username: 账号
      password: 密码
    doris: # doris配置
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: 数据库地址
      username: 账号
      password: 密码

2.2 Mysql和Doris 配置类

• Mysql配置类

@Configuration
//basePackages 这里是mapper所在包路径， 根据自己项目调整
@MapperScan(basePackages = "com.mo.xue.doristest.mapper.mysql", sqlSessionFactoryRef = "mysqlSqlSessionFactory")
public class MysqlConfig {
    //这里是mapper.xml路径， 根据自己的项目调整
    private static final String MAPPER_LOCATION = "classpath*:mapper/mysql/*.xml";
    //这里是数据库表对应的entity实体类所在包路径， 根据自己的项目调整 
    private static final String TYPE_ALIASES_PACKAGE = "com.mo.xue.doristest.bean.mysql.*"; 
 
    @Primary //这个注解的意思是默认使用当前数据源
    @Bean(name="mysqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.mysql")
    public DataSource mysqlDataSource() {
        return DataSourceBuilder.create().build();
    }
 
    @Primary
    @Bean("mysqlSqlSessionFactory")
    public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // mapper的xml形式文件位置必须要配置，不然将报错：no statement （这种错误也可能是mapper的xml中，namespace与项目的路径不一致导致）
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        bean.setTypeAliasesPackage(TYPE_ALIASES_PACKAGE);
        return bean.getObject();
    }
    
    /**
     * 配置事务管理
     */
    @Bean(name = "mysqlTransactionManager")
    @Primary
    public DataSourceTransactionManager mysqlTransactionManager(@Qualifier("mysqlDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
 
    @Primary
    @Bean("mysqlSqlSessionTemplate")
    public SqlSessionTemplate mysqlSqlSessionTemplate(@Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

• Doris配置类

@Configuration
@MapperScan(basePackages  = "com.mo.xue.doristest.mapper.doris" , sqlSessionFactoryRef = "dorisSqlSessionFactory")
public class DorisConfig {
    private static final String MAPPER_LOCATION = "classpath*:mapper/doris/*.xml";
    private static final String TYPE_ALIASES_PACKAGE = "com.mo.xue.doristest.bean.doris.*";
 
    @Bean("dorisDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.doris")
    public DataSource getDb1DataSource(){
        return DataSourceBuilder.create().build();
    }
 
    @Bean("dorisSqlSessionFactory")
    public SqlSessionFactory dorisSqlSessionFactory(@Qualifier("dorisDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        bean.setTypeAliasesPackage(TYPE_ALIASES_PACKAGE);
        return bean.getObject();
    }
    
    
    //Doris是否需要配置事务
    /*@Bean(name = "dorisTransactionManager")
    public DataSourceTransactionManager dorisTransactionManager(@Qualifier("dorisDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }*/

 
    @Bean("dorisSqlSessionTemplate")
    public SqlSessionTemplate dorisSqlSessionTemplate(@Qualifier("dorisSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

参考：
https://blog.csdn.net/wudonglianga/article/details/119513855

https://blog.csdn.net/Stranger_Orz/article/details/122081492

https://cloud.tencent.com/document/practice/1387/81207