springboot配置多数据源mysql，presto，hive等
下面案例是配置多数据源，两个及以上，但是主数据源只能是一个，默认mybatis使用的是主数据源
下面配置mysql为主数据源，通过注解@Primary标注
yaml文件配置：
spring:
datasource:
mysqlMain: #mysql主数据源，可关联mybatis
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3306/aip?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false&rewriteBatchedStatements=true
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
hive: #hive数据源
url: jdbc:hive2://127.0.0.1:31000
type: com.alibaba.druid.pool.DruidDataSource
username: root
password: root
driver-class-name: org.apache.hive.jdbc.HiveDriver
presto: #presto数据源
url: jdbc:presto://127.0.0.1:30998/hive_dev/aip
type: com.alibaba.druid.pool.DruidDataSource
username: root
password: root
driver-class-name: com.facebook.presto.jdbc.PrestoDriver
commonconfig: #连接池统一配置，应用到所有的数据源
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
conf目录：
代码：
DataSourceCommonProperties：
package com.sangfor.api.config.druid;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
/**
* @author: xmz
*/
@ConfigurationProperties(prefix = DataSourceCommonProperties.DS, ignoreUnknownFields = false)
@Data
public class DataSourceCommonProperties {
final static String DS = "spring.datasource.commonconfig";
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;
}
DataSourceProperties：
package com.sangfor.api.config.druid;
import java.util.Map;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
/**
* @author: xmz
*/
@ConfigurationProperties(prefix = DataSourceProperties.DS, ignoreUnknownFields = false)
@Data
public class DataSourceProperties {
final static String DS = "spring.datasource";
private Map<String,String> mysqlMain;
private Map<String,String> hive;
private Map<String,String> presto;
private Map<String,String> commonConfig;
}
MysqlMainDruidConfig：
package com.sangfor.api.config.druid;
import java.sql.SQLException;
import javax.sql.DataSource;
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;
/**
* @author: xmz
*/
@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;
}
}
MysqlMainJdbcBaseDaoImpl：
package com.sangfor.api.config.druid;
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;
/**
* @author: xmz
*/
@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);
}
}
PrestoDruidConfig：
package com.sangfor.api.config.druid;
/**
* @author: xmz
*/
import com.alibaba.druid.pool.DruidDataSource;
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 javax.sql.DataSource;
import java.sql.SQLException;
import java.util.TimeZone;
@Configuration
@EnableConfigurationProperties({DataSourceProperties.class,DataSourceCommonProperties.class})//将配置类注入到bean容器，使ConfigurationProperties注解类生效
public class PrestoDruidConfig {
private static Logger logger = LoggerFactory.getLogger(PrestoDruidConfig.class);
@Autowired
private DataSourceProperties dataSourceProperties;
@Autowired
private DataSourceCommonProperties dataSourceCommonProperties;
@Bean("prestoDruidDataSource") //新建bean实例
@Qualifier("prestoDruidDataSource")//标识
public DataSource dataSource(){
TimeZone.setDefault(TimeZone.getTimeZone("+08:00"));
DruidDataSource datasource = new DruidDataSource();
//配置数据源属性
datasource.setUrl(dataSourceProperties.getPresto().get("url"));
datasource.setUsername(dataSourceProperties.getPresto().get("username"));
// datasource.setPassword(dataSourceProperties.getPresto().get("password"));
datasource.setDriverClassName(dataSourceProperties.getPresto().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;
}
}
PrestoJdbcBaseDaoImpl：
package com.sangfor.api.config.druid;
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;
import javax.sql.DataSource;
/**
* @author: xmz
*/
@Repository
public class PrestoJdbcBaseDaoImpl {
private JdbcTemplate jdbcTemplate;
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
@Autowired
public void setJdbcTemplate(@Qualifier("prestoDruidDataSource") DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
}
HiveDruidConfig：
package com.sangfor.api.config.druid;
/**
* @author: xmz
*/
import java.sql.SQLException;
import javax.sql.DataSource;
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;
@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;
}
}
HiveJdbcBaseDaoImpl：
package com.sangfor.api.config.druid;
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;
/**
* @author: xmz
*/
@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);
}
}
配置已经完成，可以正常使用，测试新建hive的db，正常使用
public void createDB(String dbName, String dbPath) {
String createDB = "CREATE DATABASE IF NOT EXISTS `"+dbName+"` location '"+dbPath + "'";
try {
hiveJdbcBaseDao.getJdbcTemplate().execute(createDB);
} catch (DataAccessException dae) {
throw new DataFilesException(DataFilesExceptionEnum.CREATE_HIVE_DB_FAIL,dae);
}
}
