springboot hive mysql 多数据源切换

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"?><projectxmlns="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:6633spring: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!!@@1122db2:driver-class-name: org.apache.hive.jdbc.HiveDriver
      jdbc-url: jdbc:hive2://spark03:10000/lihaozhe
      username: root
      password:mybatis:mapper-locations: classpath:mapper/*.xmltype-aliases-package: com.lihaozhe.pojo
  lazy-loading-enabled:trueaggressive-lazy-loading:falseconfiguration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case:true

pojo 类

packagecom.lihaozhe.pojo;importlombok.*;@Setter@Getter@ToString@NoArgsConstructor@AllArgsConstructorpublicclassPerson{/**
   * 身份证
   */privateString idCard;/**
   * 姓名
   */privateString realName;/**
   * 手机号
   */privateString mobile;/**
   * 分区码
   */privateString provinceCode;}

mapper 接口

packagecom.lihaozhe.mapper;importcom.lihaozhe.pojo.Person;importorg.apache.ibatis.annotations.Mapper;importjava.util.List;/**
 * @author 李昊哲
 * @version 1.0.0  2023/4/28 下午2:00
 */@MapperpublicinterfacePersonMapper{/**
     * 查询Person列表
     *
     * @return Person列表
     */List<Person>selectList();intinsertList(List<Person> persons);}

mybatis 映射配置文件

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.lihaozhe.mapper.PersonMapper"><selectid="selectList"resultType="person">
        select id_card,real_name,mobile,province_code from person
    </select><insertid="insertList">
        insert into person values
        <foreachcollection="persons"index="index"item="person"separator=",">
            (#{person.idCard},#{person.realName},#{person.mobile},#{person.provinceCode})
        </foreach></insert></mapper>

多数据配置

多数据源切换工具类 DataSourceUtil

packagecom.lihaozhe.config;/**
 * @author 李昊哲
 * @version 1.0.0  2023/4/28 下午2:15
 */publicclassDataSourceUtil{/**
     * 默认数据源
     */publicstaticfinalString DEFAULT_DS ="db1";/**
     *  数据源属于一个公共的资源
     *  采用ThreadLocal可以保证在多线程情况下线程隔离
     */privatestaticfinalThreadLocal<String> contextHolder =newThreadLocal<>();/**
     * 设置数据源名
     * @param dbType
     */publicstaticvoidsetDB(String dbType){
        contextHolder.set(dbType);}/**
     * 获取数据源名
     * @return
     */publicstaticStringgetDB(){return(contextHolder.get());}/**
     * 清除数据源名
     */publicstaticvoidclearDB(){
        contextHolder.remove();}}

多数据源动态配置类 DynamicDataSource

packagecom.lihaozhe.config;importorg.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;/**
 * @author 李昊哲
 * @version 1.0.0  2023/4/28 下午2:16
 */publicclassDynamicDataSourceextendsAbstractRoutingDataSource{@OverrideprotectedObjectdetermineCurrentLookupKey(){returnDataSourceUtil.getDB();}}

多数据源配置类 DataSourceConfig

packagecom.lihaozhe.config;importorg.springframework.boot.context.properties.ConfigurationProperties;importorg.springframework.boot.jdbc.DataSourceBuilder;importorg.springframework.context.annotation.Bean;importorg.springframework.context.annotation.Configuration;importorg.springframework.context.annotation.Primary;importorg.springframework.jdbc.datasource.DataSourceTransactionManager;importorg.springframework.transaction.PlatformTransactionManager;importjavax.sql.DataSource;importjava.util.HashMap;importjava.util.Map;/**
 * @author 李昊哲
 * @version 1.0.0  2023/4/28 下午2:17
 */@ConfigurationpublicclassDataSourceConfig{/**
     * 数据源1
     * spring.datasource.db1：application.properteis中对应属性的前缀
     * @return
     */@Bean(name ="db1")@ConfigurationProperties(prefix ="spring.datasource.db1")publicDataSourcedataSourceOne(){returnDataSourceBuilder.create().build();}/**
     * 数据源2
     * spring.datasource.db2：application.properteis中对应属性的前缀
     * @return
     */@Bean(name ="db2")@ConfigurationProperties(prefix ="spring.datasource.db2")publicDataSourcedataSourceTwo(){returnDataSourceBuilder.create().build();}/**
     * 动态数据源: 通过AOP在不同数据源之间动态切换
     * @return
     */@Primary@Bean(name ="dynamicDataSource")publicDataSourcedynamicDataSource(){DynamicDataSource dynamicDataSource =newDynamicDataSource();// 默认数据源
        dynamicDataSource.setDefaultTargetDataSource(dataSourceOne());// 配置多数据源Map<Object,Object> dsMap =newHashMap<>();
        dsMap.put("db1",dataSourceOne());
        dsMap.put("db2",dataSourceTwo());

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

业务层代码

业务层接口 PersonService

packagecom.lihaozhe.service;importcom.lihaozhe.pojo.Person;importjava.util.List;/**
 * @author 李昊哲
 * @version 1.0.0  2023/4/28 下午2:04
 */publicinterfacePersonService{List<Person>queryList();intmigrate();}

业务层接口实现 PersonServiceImpl

packagecom.lihaozhe.service.impl;importcom.lihaozhe.config.DataSourceUtil;importcom.lihaozhe.mapper.PersonMapper;importcom.lihaozhe.pojo.Person;importcom.lihaozhe.service.PersonService;importorg.springframework.stereotype.Service;importjava.util.List;/**
 * @author 李昊哲
 * @version 1.0.0  2023/4/28 下午2:05
 */@ServicepublicclassPersonServiceImplimplementsPersonService{privatefinalPersonMapper personMapper;publicPersonServiceImpl(PersonMapper personMapper){this.personMapper = personMapper;}@OverridepublicList<Person>queryList(){DataSourceUtil.setDB("db2");return personMapper.selectList();}@Overridepublicintmigrate(){// 数据源使用 hiveDataSourceUtil.setDB("db2");// 从hive中查询数据List<Person> personList = personMapper.selectList();// 数据源使用 mysqlDataSourceUtil.setDB("db1");return personMapper.insertList(personList);}}

API 接口 PersonController

packagecom.lihaozhe.controller;importcom.lihaozhe.pojo.Person;importcom.lihaozhe.service.PersonService;importorg.springframework.web.bind.annotation.GetMapping;importorg.springframework.web.bind.annotation.RequestMapping;importorg.springframework.web.bind.annotation.RestController;importjava.util.List;/**
 * @author 李昊哲
 * @version 1.0.0  2023/4/28 下午2:06
 */@RestController@RequestMapping("/person")publicclassPersonController{privatefinalPersonService personService;publicPersonController(PersonService personService){this.personService = personService;}@GetMapping("/queryList")publicList<Person>queryList(){return personService.queryList();}@GetMapping("/migrate")publicintmigrate(){return personService.migrate();}}