springboot项目多数据源配置，集成hive和mysql

小灰灰 2023-05-29 09:20 36阅读 0赞

最近做的这个项目，原本数据库是用的mysql，后来需求问题，经过讨论后打算把hive集成进来，就是有一批数据入hive。

经过昨晚的尝试加上上网查阅资料总算是集成好了。

集成好了后的效果就是mysql和hive互不影响可独立使用，而且也不会影响mybatis的 使用，既然集成应该都是要这种效果吧。

代码有点多，不要着急，咱们一步一步的来

**第一步pom文件**：**以下jar如果你已经有了就无需在添加，如果没有就添加吧，hive的版本号一定要与你的hive服务版本号一致**

<dependency>
               <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-configuration-processor</artifactId>
                <optional>true</optional>
            </dependency>
       
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.0.29</version>
            </dependency>
      
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-beans</artifactId>
            </dependency>
    
            
            <dependency>
                <groupId>org.apache.hadoop</groupId>
                <artifactId>hadoop-common</artifactId>
                <version>2.6.0</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.hadoop</groupId>
                <artifactId>hadoop-mapreduce-client-core</artifactId>
                <version>2.6.0</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.hadoop</groupId>
                <artifactId>hadoop-mapreduce-client-common</artifactId>
                <version>2.6.0</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.hadoop</groupId>
                <artifactId>hadoop-hdfs</artifactId>
                <version>2.6.0</version>
            </dependency>
    
            
            <dependency>
                <groupId>org.apache.hive</groupId>
                <artifactId>hive-jdbc</artifactId>
                <version>1.2.1</version>
                <exclusions>
                    <exclusion>
                        <groupId>org.eclipse.jetty.aggregate</groupId>
                        <artifactId>*</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>

第二步，yml的配置写法：配置的地方跟这个所对应，目的是把mysql和hive区分开，其他的配置不需要动。

spring:
      application:
        name: zhongtai
      datasource:
        mysqlMain: #mysql主数据源
          type: com.alibaba.druid.pool.DruidDataSource
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://连接地址:3306/库名?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true
          username: root
        hive: #hive数据源
          url: jdbc:hive2://连接地址:10000/库名
          type: com.alibaba.druid.pool.DruidDataSource
          username: root
          password: ******
          driver-class-name: org.apache.hive.jdbc.HiveDriver
        common-config: #连接池统一配置，应用到所有的数据源
          initialSize: 1
          minIdle: 1
          maxIdle: 5
          maxActive: 50
          maxWait: 10000
          timeBetweenEvictionRunsMillis: 10000
          minEvictableIdleTimeMillis: 300000
          validationQuery: select 'x'
          testWhileIdle: true
          testOnBorrow: false
          testOnReturn: false
          poolPreparedStatements: true
          maxOpenPreparedStatements: 20
          filters: stat

第三步，就是建管理配置文件的几个类，位置无所谓，但是为了归类不乱，最好放一块

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hpYW9kb25nZ2U1MjE_size_16_color_FFFFFF_t_70][]

1.HiveDruidConfig.java **hive数据源配置类**

package cn.**.**.system.config.druid.hive;
    import javax.sql.DataSource;
    
    import cn.cnic.zhongtai.system.config.DataSourceCommonProperties;
    import cn.cnic.zhongtai.system.config.DataSourceProperties;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.boot.context.properties.EnableConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    import com.alibaba.druid.pool.DruidDataSource;
    
    import java.sql.SQLException;
    
    /**
    * 
     * hive 数据源配置类
    * Created  by wdd on 2019/11/5
    *  
    **/
    @Configuration
    @EnableConfigurationProperties({DataSourceProperties.class, DataSourceCommonProperties.class})//将配置类注入到bean容器，使ConfigurationProperties注解类生效
    public class HiveDruidConfig {
    
        private static Logger logger = LoggerFactory.getLogger(HiveDruidConfig.class);
    
        @Autowired
        private DataSourceProperties dataSourceProperties;
    
        @Autowired
        private DataSourceCommonProperties dataSourceCommonProperties;
    
        @Bean("hiveDruidDataSource") //新建bean实例
        @Qualifier("hiveDruidDataSource")//标识
        public DataSource dataSource(){
            DruidDataSource datasource = new DruidDataSource();
    
            //配置数据源属性
            datasource.setUrl(dataSourceProperties.getHive().get("url"));
            datasource.setUsername(dataSourceProperties.getHive().get("username"));
            datasource.setPassword(dataSourceProperties.getHive().get("password"));
            datasource.setDriverClassName(dataSourceProperties.getHive().get("driver-class-name"));
    
            //配置统一属性
            datasource.setInitialSize(dataSourceCommonProperties.getInitialSize());
            datasource.setMinIdle(dataSourceCommonProperties.getMinIdle());
            datasource.setMaxActive(dataSourceCommonProperties.getMaxActive());
            datasource.setMaxWait(dataSourceCommonProperties.getMaxWait());
            datasource.setTimeBetweenEvictionRunsMillis(dataSourceCommonProperties.getTimeBetweenEvictionRunsMillis());
            datasource.setMinEvictableIdleTimeMillis(dataSourceCommonProperties.getMinEvictableIdleTimeMillis());
            datasource.setValidationQuery(dataSourceCommonProperties.getValidationQuery());
            datasource.setTestWhileIdle(dataSourceCommonProperties.isTestWhileIdle());
            datasource.setTestOnBorrow(dataSourceCommonProperties.isTestOnBorrow());
            datasource.setTestOnReturn(dataSourceCommonProperties.isTestOnReturn());
            datasource.setPoolPreparedStatements(dataSourceCommonProperties.isPoolPreparedStatements());
            try {
                datasource.setFilters(dataSourceCommonProperties.getFilters());
            } catch (SQLException e) {
                logger.error("Druid configuration initialization filter error.", e);
            }
            return datasource;
        }
    
    }

2.MysqlMainDruidConfig.java   **配置mysql主数据源，mysql连接默认主数据源**

package cn.**.**.system.config.druid.mysql;
    
    import java.sql.SQLException;
    
    import javax.sql.DataSource;
    
    import cn.cnic.zhongtai.system.config.DataSourceCommonProperties;
    import cn.cnic.zhongtai.system.config.DataSourceProperties;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.boot.context.properties.EnableConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    
    import com.alibaba.druid.pool.DruidDataSource;
    
    /**
     * -配置mysql主数据源，mysql连接默认主数据源
     * @author wdd
     * @Date 2019年11月04日
     *
     */
    @Configuration
    @EnableConfigurationProperties({DataSourceProperties.class, DataSourceCommonProperties.class})//将配置类注入到bean容器，使ConfigurationProperties注解类生效
    public class MysqlMainDruidConfig {
    
        private static Logger logger = LoggerFactory.getLogger(MysqlMainDruidConfig.class);
    
        @Autowired
        private DataSourceProperties dataSourceProperties;
    
        @Autowired
        private DataSourceCommonProperties dataSourceCommonProperties;
    
        @Primary //标明为主数据源，只能标识一个主数据源，mybatis连接默认主数据源
        @Bean("mysqlDruidDataSource") //新建bean实例
        @Qualifier("mysqlDruidDataSource")//标识
        public DataSource dataSource(){
            DruidDataSource datasource = new DruidDataSource();
    
            //配置数据源属性
            datasource.setUrl(dataSourceProperties.getMysqlMain().get("url"));
            datasource.setUsername(dataSourceProperties.getMysqlMain().get("username"));
            datasource.setPassword(dataSourceProperties.getMysqlMain().get("password"));
            datasource.setDriverClassName(dataSourceProperties.getMysqlMain().get("driver-class-name"));
    
            //配置统一属性
            datasource.setInitialSize(dataSourceCommonProperties.getInitialSize());
            datasource.setMinIdle(dataSourceCommonProperties.getMinIdle());
            datasource.setMaxActive(dataSourceCommonProperties.getMaxActive());
            datasource.setMaxWait(dataSourceCommonProperties.getMaxWait());
            datasource.setTimeBetweenEvictionRunsMillis(dataSourceCommonProperties.getTimeBetweenEvictionRunsMillis());
            datasource.setMinEvictableIdleTimeMillis(dataSourceCommonProperties.getMinEvictableIdleTimeMillis());
            datasource.setValidationQuery(dataSourceCommonProperties.getValidationQuery());
            datasource.setTestWhileIdle(dataSourceCommonProperties.isTestWhileIdle());
            datasource.setTestOnBorrow(dataSourceCommonProperties.isTestOnBorrow());
            datasource.setTestOnReturn(dataSourceCommonProperties.isTestOnReturn());
            datasource.setPoolPreparedStatements(dataSourceCommonProperties.isPoolPreparedStatements());
            try {
                datasource.setFilters(dataSourceCommonProperties.getFilters());
            } catch (SQLException e) {
                logger.error("Druid configuration initialization filter error.", e);
            }
            return datasource;
        }
    
    }

3.DataSourceCommonProperties.java   **扩展连接池，通用配置属性，可应用到所有数据源**

package cn.**.**.system.config;
    
    import org.springframework.boot.context.properties.ConfigurationProperties;
    
    /**
     * -扩展连接池，通用配置属性，可应用到所有数据源
     * @author wdd
     * @Date 2019年11月04日
     *
     */
    @ConfigurationProperties(prefix = DataSourceCommonProperties.DS, ignoreUnknownFields = false)
    public class DataSourceCommonProperties {
        final static String DS = "spring.datasource.common-config";
    
        private int initialSize = 10;
        private int minIdle;
        private int maxIdle;
        private int maxActive;
        private int maxWait;
        private int timeBetweenEvictionRunsMillis;
        private int minEvictableIdleTimeMillis;
        private String validationQuery;
        private boolean testWhileIdle;
        private boolean testOnBorrow;
        private boolean testOnReturn;
        private boolean poolPreparedStatements;
        private int maxOpenPreparedStatements;
        private String filters;
    
        private String mapperLocations;
        private String typeAliasPackage;
    
        这里是get,set方法。自己生成一下
    }

4.DataSourceProperties.java ** 统一属性控制类，获取配置文件属性**

package cn.**.**.system.config;
    import java.util.Map;
    
    import org.springframework.boot.context.properties.ConfigurationProperties;
    
    /**
     * -统一属性控制类，获取配置文件属性
     * @author wdd
     * @Date 2019年11月04日
     *
     */
    @ConfigurationProperties(prefix = DataSourceProperties.DS, ignoreUnknownFields = false)
    public class DataSourceProperties {
        final static String DS = "spring.datasource";
    
        private Map<String,String> mysqlMain;
    
        private Map<String,String> hive;
    
        private Map<String,String> commonConfig;
    
    
       get，set方法，自己生成
    }

5.HiveJdbcBaseDaoImpl.java ** 注入hive数据源**

package cn.**.**.system.config.jdbcConfig;
    import javax.sql.DataSource;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Repository;
    
    /**
     * -注入hive数据源
     * @author wdd
     * @Date 2019年11月04日
     *
     */
    @Repository
    public class HiveJdbcBaseDaoImpl {
    
        private JdbcTemplate jdbcTemplate;
    
        public JdbcTemplate getJdbcTemplate() {
            return jdbcTemplate;
        }
    
        @Autowired
        public void setJdbcTemplate(@Qualifier("hiveDruidDataSource") DataSource dataSource) {
            this.jdbcTemplate = new JdbcTemplate(dataSource);
        }
    
    }

6.MysqlMainJdbcBaseDaoImpl.java  **注入jdbctemplate**

package cn.**.**.system.config.jdbcConfig;
    import javax.sql.DataSource;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Repository;
    
    /**
     * -注入jdbctemplate
     * @author wdd
     * 2019年11月04日
     */
    @Repository
    public class MysqlMainJdbcBaseDaoImpl {
    
        private JdbcTemplate jdbcTemplate;
    
        public JdbcTemplate getJdbcTemplate() {
            return jdbcTemplate;
        }
    
        @Autowired
        public void setJdbcTemplate(@Qualifier("mysqlDruidDataSource") DataSource dataSource) {
            this.jdbcTemplate = new JdbcTemplate(dataSource);
        }
    
    }

到了这一步就算是创建完了，接下来就是测试一下吧，写一个测试类

用法呢就是直接注入即可使用

import cn.cnic.zhongtai.ZhongtaiApplication;
    import cn.cnic.zhongtai.system.config.jdbcConfig.HiveJdbcBaseDaoImpl;
    import cn.cnic.zhongtai.system.config.jdbcConfig.MysqlMainJdbcBaseDaoImpl;
    import cn.cnic.zhongtai.system.mapper.TestDao;
    import cn.cnic.zhongtai.system.model.GenTableColumn;
    import cn.cnic.zhongtai.system.service.GenTableColumnService;
    import cn.cnic.zhongtai.utils.SqlUtils;
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.test.context.junit4.SpringRunner;
    
    import java.util.List;
    import java.util.Map;
    
    /**
     * 使用 hive and mysql test
     */
    @RunWith(SpringRunner.class)
    @SpringBootTest(classes={ZhongtaiApplication.class})
    public class HiveAndMysqlTest {
    
       
        @Autowired
        private HiveJdbcBaseDaoImpl hiveJdbcBaseDaoImpl;
    
        @Autowired
        private MysqlMainJdbcBaseDaoImpl MysqlMainJdbcBaseDaoImpl;
    
    
        @Test
        public void testMysql() {
            String sql = "SELECT name from test.people limit 1";
            String info = hiveJdbcBaseDaoImpl.getJdbcTemplate().queryForObject(sql,String.class);
            System.out.println("hive中查出的数据是："+info);
    
            //校验该表是否在数据库中存在
            String sql1 = "SELECT  count(*) FROM person  ;";
            int count = MysqlMainJdbcBaseDaoImpl.getJdbcTemplate().queryForObject(sql1, Integer.class);
            System.out.println("mysql中查出的数据是："+count);
        }
     
    
    
    }

测试结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hpYW9kb25nZ2U1MjE_size_16_color_FFFFFF_t_70 1][]

就这样，写完了，这就是springboot多数据配置的两种hive和mysql。这应该够详细了。

如果有问题的话就多检查一下哪里设置错了，也欢迎留言讨论，如果对你有帮助就点个赞再走呗.

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hpYW9kb25nZ2U1MjE_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191105171632247.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hpYW9kb25nZ2U1MjE=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hpYW9kb25nZ2U1MjE_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191105173216984.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hpYW9kb25nZ2U1MjE=,size_16,color_FFFFFF,t_70