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();
}
}
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/188717.html
