springboot hive mysql 多数据源切换

最新推荐文章于 2024-02-03 13:59:14 发布

李昊哲小课

最新推荐文章于 2024-02-03 13:59:14 发布

阅读量6.9w

点赞数

文章标签： spring boot 后端大数据 hive mysql

本文链接：https://blog.csdn.net/qq_24330181/article/details/130426057

版权

springboot hive mysql 多数据源切换

本次实验重在多数据源切换性能不在考虑其中

开发环境：

hive 3.1.3
mysql 8.0.33
jdk 1.8
maven 3.9.1
idea 2023.1
springboot 2.7.11
HikariCP 连接池

实验效果：从 hive 中迁移数据到 MySQL

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>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.11</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.lihaozhe</groupId>
    <artifactId>hm</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>hm</name>
    <description>hm</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.3.0</version>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </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>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>3.1.3</version>
            <exclusions>
                <exclusion>
                    <groupId>org.eclipse.jetty</groupId>
                    <artifactId>jetty-runner</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.eclipse.jetty.aggregate</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.hive</groupId>
                    <artifactId>hive-shims</artifactId>
                </exclusion>
                <exclusion>
                    <artifactId>jasper-compiler</artifactId>
                    <groupId>tomcat</groupId>
                </exclusion>
                <exclusion>
                    <artifactId>jasper-runtime</artifactId>
                    <groupId>tomcat</groupId>
                </exclusion>
                <exclusion>
                    <artifactId>servlet-api</artifactId>
                    <groupId>javax.servlet</groupId>
                </exclusion>
                <exclusion>
                    <artifactId>log4j-slf4j-impl</artifactId>
                    <groupId>org.apache.logging.log4j</groupId>
                </exclusion>
                <exclusion>
                    <artifactId>slf4j-log4j12</artifactId>
                    <groupId>org.slf4j</groupId>
                </exclusion>
                <exclusion>
                    <groupId>tomcat</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>ch.qos.logback</groupId>
                    <artifactId>logback-classic</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.eclipse.jetty.orbit</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.eclipse.jetty.aggregate</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>javax.servlet</groupId>
                    <artifactId>servlet-api</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.mortbay.jetty</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

springboot 配置文件 application.yml

server:
  port: 6633
spring:
  datasource:
#    driver-class-name: com.mysql.cj.jdbc.Driver
#    url: jdbc:mysql:///quiz_t6?useUnicode=true&characterEncoding=UTF8&useSSL=false&useServerPrepStmts=false&rewriteBatchedStatements=true&cachePrepStmts=true&allowMultiQueries=true&serverTimeZone=Aisa/Shanghai
#    username: root
#    password: lihaozhe
#    driver-class-name: org.apache.hive.jdbc.HiveDriver
#    url: jdbc:hive2://spark03:10000/lihaozhe
#    username: root
#    password:
    db1:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://spark03:3306/lihaozhe
      username: root
      password: Lihaozhe!!@@1122
    db2:
      driver-class-name: org.apache.hive.jdbc.HiveDriver
      jdbc-url: jdbc:hive2://spark03:10000/lihaozhe
      username: root
      password:

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.lihaozhe.pojo
  lazy-loading-enabled: true
  aggressive-lazy-loading: false
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true

pojo 类

package com.lihaozhe.pojo;

import lombok.*;

@Setter
@Getter
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Person {
  /**
   * 身份证
   */
  private String idCard;
  /**
   * 姓名
   */
  private String realName;
  /**
   * 手机号
   */
  private String mobile;
  /**
   * 分区码
   */
  private String provinceCode;
}

mapper 接口

package com.lihaozhe.mapper;

import com.lihaozhe.pojo.Person;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

/**
 * @author 李昊哲
 * @version 1.0.0  2023/4/28 下午2:00
 */
@Mapper
public interface PersonMapper {
    /**
     * 查询Person列表
     *
     * @return Person列表
     */
    List<Person> selectList();

    int insertList(List<Person> persons);
}

mybatis 映射配置文件

<?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.lihaozhe.mapper.PersonMapper">
    <select id="selectList" resultType="person">
        select id_card,real_name,mobile,province_code from person
    </select>
    <insert id="insertList">
        insert into person values
        <foreach collection="persons" index="index" item="person" separator=",">
            (#{person.idCard},#{person.realName},#{person.mobile},#{person.provinceCode})
        </foreach>
    </insert>
</mapper>

多数据配置

多数据源切换工具类 DataSourceUtil

package com.lihaozhe.config;

/**
 * @author 李昊哲
 * @version 1.0.0  2023/4/28 下午2:15
 */
public class DataSourceUtil {
    /**
     * 默认数据源
     */
    public static final String DEFAULT_DS = "db1";
    /**
     *  数据源属于一个公共的资源
     *  采用ThreadLocal可以保证在多线程情况下线程隔离
     */
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    /**
     * 设置数据源名
     * @param dbType
     */
    public static void setDB(String dbType) {
        contextHolder.set(dbType);
    }

    /**
     * 获取数据源名
     * @return
     */
    public static String getDB() {
        return (contextHolder.get());
    }

    /**
     * 清除数据源名
     */
    public static void clearDB() {
        contextHolder.remove();
    }
}

多数据源动态配置类 DynamicDataSource

package com.lihaozhe.config;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * @author 李昊哲
 * @version 1.0.0  2023/4/28 下午2:16
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceUtil.getDB();
    }
}

多数据源配置类 DataSourceConfig

package com.lihaozhe.config;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * @author 李昊哲
 * @version 1.0.0  2023/4/28 下午2:17
 */
@Configuration
public class DataSourceConfig {
    /**
     * 数据源1
     * spring.datasource.db1：application.properteis中对应属性的前缀
     * @return
     */
    @Bean(name = "db1")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource dataSourceOne() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 数据源2
     * spring.datasource.db2：application.properteis中对应属性的前缀
     * @return
     */
    @Bean(name = "db2")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource dataSourceTwo() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 动态数据源: 通过AOP在不同数据源之间动态切换
     * @return
     */
    @Primary
    @Bean(name = "dynamicDataSource")
    public DataSource dynamicDataSource() {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        // 默认数据源
        dynamicDataSource.setDefaultTargetDataSource(dataSourceOne());
        // 配置多数据源
        Map<Object, Object> dsMap = new HashMap<>();
        dsMap.put("db1", dataSourceOne());
        dsMap.put("db2", dataSourceTwo());

        dynamicDataSource.setTargetDataSources(dsMap);
        return dynamicDataSource;
    }

    /**
     * 配置多数据源后IOC中存在多个数据源了，事务管理器需要重新配置，不然器不知道选择哪个数据源
     * 事务管理器此时管理的数据源将是动态数据源dynamicDataSource
     * 配置@Transactional注解
     * @return
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }
}

业务层代码

业务层接口 PersonService

package com.lihaozhe.service;

import com.lihaozhe.pojo.Person;

import java.util.List;

/**
 * @author 李昊哲
 * @version 1.0.0  2023/4/28 下午2:04
 */
public interface PersonService {
     List<Person> queryList();

     int migrate();
}

业务层接口实现 PersonServiceImpl

package com.lihaozhe.service.impl;

import com.lihaozhe.config.DataSourceUtil;
import com.lihaozhe.mapper.PersonMapper;
import com.lihaozhe.pojo.Person;
import com.lihaozhe.service.PersonService;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author 李昊哲
 * @version 1.0.0  2023/4/28 下午2:05
 */
@Service
public class PersonServiceImpl implements PersonService {
    private final PersonMapper personMapper;

    public PersonServiceImpl(PersonMapper personMapper) {
        this.personMapper = personMapper;
    }

    @Override
    public List<Person> queryList() {
        DataSourceUtil.setDB("db2");
        return personMapper.selectList();
    }

    @Override
    public int migrate() {
        // 数据源使用 hive
        DataSourceUtil.setDB("db2");
        // 从hive中查询数据
        List<Person> personList = personMapper.selectList();
        // 数据源使用 mysql
        DataSourceUtil.setDB("db1");
        return personMapper.insertList(personList);
    }
}

API 接口 PersonController

package com.lihaozhe.controller;

import com.lihaozhe.pojo.Person;
import com.lihaozhe.service.PersonService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * @author 李昊哲
 * @version 1.0.0  2023/4/28 下午2:06
 */
@RestController
@RequestMapping("/person")
public class PersonController {
    private final PersonService personService;

    public PersonController(PersonService personService) {
        this.personService = personService;
    }
    @GetMapping("/queryList")
    public List<Person> queryList(){
        return personService.queryList();
    }

    @GetMapping("/migrate")
    public int migrate(){
        return personService.migrate();
    }
}