spring 项目升级到spring cloud记录 数据源配置

 

1. 用的阿里的数据源  增加pom

            <dependency>
               <groupId>com.alibaba</groupId>
               <artifactId>druid</artifactId>
               <version>1.1.6</version>
           </dependency>

    

2. application.yml配置

3. spring:
     datasource:
       type: com.alibaba.druid.pool.DruidDataSource
       url: jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.167)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.168)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.169)(PORT = 1521))(LOAD_BALANCE = ON)(FAILOVER =ON))(CONNECT_DATA = (SERVER = DEDICATED)(INSTANCE_ROLE = ANY)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC))(SERVICE_NAME = devdb)))
       username: mis
       password: mis
       driver: oracle.jdbc.OracleDriver
       # 初始化大小，最小，最大
       initialSize: 5
       minIdle: 5
       maxActive: 20
       # 配置获取连接等待超时的时间
       maxWait: 600000
       # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
       timeBetweenEvictionRunsMillis: 60000
       # 配置一个连接在池中最小生存的时间，单位是毫秒
       minEvictableIdleTimeMillis: 300000
       validationQuery: SELECT 1 FROM DUAL
       #建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
       testWhileIdle: true
       #申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
       testOnBorrow: false
       #归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
       testOnReturn: false
       # 是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。
       poolPreparedStatements: true
       #要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
       maxPoolPreparedStatementPerConnectionSize: 20

    

4. 配置类

5. package com.guige.core.conf;
   
   import com.alibaba.druid.filter.Filter;
   import com.alibaba.druid.filter.stat.StatFilter;
   import com.alibaba.druid.pool.DruidDataSource;
   import com.alibaba.druid.support.http.StatViewServlet;
   import com.alibaba.druid.support.http.WebStatFilter;
   import com.alibaba.druid.wall.WallConfig;
   import com.alibaba.druid.wall.WallFilter;
   import com.guige.core.ext.base.dao.JDBCDao;
   import org.springframework.beans.factory.annotation.Value;
   import org.springframework.boot.context.properties.ConfigurationProperties;
   import org.springframework.boot.web.servlet.FilterRegistrationBean;
   import org.springframework.boot.web.servlet.ServletRegistrationBean;
   import org.springframework.context.annotation.Bean;
   import org.springframework.context.annotation.Configuration;
   import org.springframework.context.annotation.Primary;
   import org.springframework.core.io.ClassPathResource;
   import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
   import org.springframework.core.io.support.ResourcePatternResolver;
   import org.springframework.jdbc.core.JdbcTemplate;
   import org.springframework.jdbc.datasource.DataSourceTransactionManager;
   import org.springframework.jdbc.support.lob.DefaultLobHandler;
   import org.springframework.orm.ibatis.SqlMapClientFactoryBean;
   import org.springframework.stereotype.Component;
   import org.springframework.transaction.PlatformTransactionManager;
   import org.springframework.transaction.annotation.EnableTransactionManagement;
   import org.springframework.transaction.annotation.TransactionManagementConfigurer;
   
   import javax.sql.DataSource;
   import java.sql.SQLException;
   import java.util.ArrayList;
   import java.util.List;
   import java.util.Properties;
   
   @Component
   @Configuration
   @EnableTransactionManagement
   public class DataSourceConfig implements TransactionManagementConfigurer {
       @Value("${spring.datasource.url:#{null}}")
       private String dbUrl;
       @Value("${spring.datasource.username: #{null}}")
       private String username;
       @Value("${spring.datasource.password:#{null}}")
       private String password;
       @Value("${spring.datasource.driverClassName:#{null}}")
       private String driverClassName;
       @Value("${spring.datasource.initialSize:#{null}}")
       private Integer initialSize;
       @Value("${spring.datasource.minIdle:#{null}}")
       private Integer minIdle;
       @Value("${spring.datasource.maxActive:#{null}}")
       private Integer maxActive;
       @Value("${spring.datasource.maxWait:#{null}}")
       private Integer maxWait;
       @Value("${spring.datasource.timeBetweenEvictionRunsMillis:#{null}}")
       private Integer timeBetweenEvictionRunsMillis;
       @Value("${spring.datasource.minEvictableIdleTimeMillis:#{null}}")
       private Integer minEvictableIdleTimeMillis;
       @Value("${spring.datasource.validationQuery:#{null}}")
       private String validationQuery;
       @Value("${spring.datasource.testWhileIdle:#{null}}")
       private Boolean testWhileIdle;
       @Value("${spring.datasource.testOnBorrow:#{null}}")
       private Boolean testOnBorrow;
       @Value("${spring.datasource.testOnReturn:#{null}}")
       private Boolean testOnReturn;
       @Value("${spring.datasource.poolPreparedStatements:#{null}}")
       private Boolean poolPreparedStatements;
       @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize:#{null}}")
       private Integer maxPoolPreparedStatementPerConnectionSize;
       @Value("${spring.datasource.filters:#{null}}")
       private String filters;
       @Value("{spring.datasource.connectionProperties:#{null}}")
       private String connectionProperties;
       @Bean
       @Primary
       public DataSource dataSource(){
           DruidDataSource datasource = new DruidDataSource();
   
           datasource.setUrl(this.dbUrl);
           datasource.setUsername(username);
           datasource.setPassword(password);
           datasource.setDriverClassName(driverClassName);
           //configuration
           if(initialSize != null) {
               datasource.setInitialSize(initialSize);
           }
           if(minIdle != null) {
               datasource.setMinIdle(minIdle);
           }
           if(maxActive != null) {
               datasource.setMaxActive(maxActive);
           }
           if(maxWait != null) {
               datasource.setMaxWait(maxWait);
           }
           if(timeBetweenEvictionRunsMillis != null) {
               datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
           }
           if(minEvictableIdleTimeMillis != null) {
               datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
           }
           if(validationQuery!=null) {
               datasource.setValidationQuery(validationQuery);
           }
           if(testWhileIdle != null) {
               datasource.setTestWhileIdle(testWhileIdle);
           }
           if(testOnBorrow != null) {
               datasource.setTestOnBorrow(testOnBorrow);
           }
           if(testOnReturn != null) {
               datasource.setTestOnReturn(testOnReturn);
           }
           if(poolPreparedStatements != null) {
               datasource.setPoolPreparedStatements(poolPreparedStatements);
           }
           if(maxPoolPreparedStatementPerConnectionSize != null) {
               datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
           }
   
           if(connectionProperties != null) {
               datasource.setConnectionProperties(connectionProperties);
           }
   
           List<Filter> filters = new ArrayList<>();
           filters.add(statFilter());
           filters.add(wallFilter());
           datasource.setProxyFilters(filters);
   
           return datasource;
       }
   
   
   
       @Override
       public PlatformTransactionManager annotationDrivenTransactionManager() {
           return new DataSourceTransactionManager(dataSource());
       }
       //阿里druid界面设置
       @Bean
       public FilterRegistrationBean webStatFilterRegistrationBean() {
           FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
           filterRegistrationBean.setFilter(new WebStatFilter());
           filterRegistrationBean.addUrlPatterns("/*");
           filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
           return filterRegistrationBean;
       }
       //阿里druid界面设置
       @Bean
       public ServletRegistrationBean statViewServlet() {
   
           ServletRegistrationBean reg = new ServletRegistrationBean();
           reg.setServlet(new StatViewServlet());
           reg.addUrlMappings("/druid/*");
           reg.addInitParameter("loginUsername", "111");
           reg.addInitParameter("loginPassword", "111#123");
           return reg;
       }
       @Bean
       public WallFilter wallFilter(){
           WallFilter wallFilter = new WallFilter();
           wallFilter.setDbType("oracle");
           WallConfig wallConfig = new WallConfig();
           wallConfig.setFunctionCheck(false);
           wallConfig.setSelectUnionCheck(false);
           wallConfig.setMultiStatementAllow(true);
           wallFilter.setConfig(wallConfig);
           return wallFilter;
       }
       @Bean
       public StatFilter statFilter(){
           StatFilter statFilter = new StatFilter();
           statFilter.setLogSlowSql(true); //slowSqlMillis用来配置SQL慢的标准，执行时间超过slowSqlMillis的就是慢。
           statFilter.setMergeSql(true); //SQL合并配置
           statFilter.setSlowSqlMillis(1000);//slowSqlMillis的缺省值为3000，也就是3秒。
           return statFilter;
       }
   
   
   }