数据库连接池 spring多数据源

连接池的原理主要由三部分组成
第一、连接池的建立。一般在系统初始化时，连接池会根据系统配置建立，并在池中创建了几个连接对象，以便使用时能从连接池中获取。连接池中的连接不能随意创建和关闭，这样避免了连接随意建立和关闭造成的系统开销。Java中提供了很多容器类可以方便的构建连接池，例如Vector、Stack等。
第二、连接池的管理。连接池管理策略是连接池机制的核心，连接池内连接的分配和释放对系统的性能有很大的影响。其管理策略是：
当客户请求数据库连接时，首先查看连接池中是否有空闲连接，如果存在空闲连接，则将连接分配给客户使用；
如果没有空闲连接，则查看当前所开的连接数是否已经达到最大连接数，如果没达到就重新创建一个连接给请求的客户；
如果达到就按设定的最大等待时间进行等待，如果超出最大等待时间，则抛出异常给客户。
当客户释放数据库连接时，先判断该连接的引用次数是否超过了规定值，如果超过就从连接池中删除该连接，否则保留为其他客户服务。
该策略保证了数据库连接的有效复用，避免频繁的建立、释放连接所带来的系统资源开销。
第三、连接池的关闭。当应用程序退出时，关闭连接池中所有的连接，释放连接池相关的资源，该过程正好与创建相反。

 

 

多数据源

pom.xml

<!-- company jdbc properties -->
<company.jdbc.driverClassName.db>com.mysql.jdbc.Driver</company.jdbc.driverClassName.db>
<company.jdbc.url.db><![CDATA[jdbc:mysql://211.160.73.239:33306/jtqy02?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&failOverReadOnly=false&tinyInt1isBit=false&zeroDateTimeBehavior=convertToNull]]></company.jdbc.url.db>
<company.jdbc.username.db>jtqytest02</company.jdbc.username.db>
<company.jdbc.password.db>3kL@pr7o3pD2</company.jdbc.password.db>
<!-- company druid properties -->
<company.druid.initial-size>5</company.druid.initial-size>
<company.druid.min-idle>5</company.druid.min-idle>
<company.druid.maxActive>200</company.druid.maxActive>
<company.druid.maxWait>60000</company.druid.maxWait>
<company.druid.timeBetweenEvictionRunsMillis>60000</company.druid.timeBetweenEvictionRunsMillis>
<company.druid.minEvictableIdleTimeMillis>300000</company.druid.minEvictableIdleTimeMillis>
<company.druid.maxPoolPreparedStatementPerConnectionSize>20</company.druid.maxPoolPreparedStatementPerConnectionSize>
application.yml

# 工商大数据
companydb:
  name: companyDb
  driver-class-name: @company.jdbc.driverClassName.db@
  url: @company.jdbc.url.db@
  username: @company.jdbc.username.db@
  password: @company.jdbc.password.db@
  # 初始化大小，最小，最大
  initialSize: @master.druid.initial-size@
  minIdle: @master.druid.min-idle@
  maxActive: @master.druid.maxActive@
  maxWait: @master.druid.maxWait@
  timeBetweenEvictionRunsMillis: @master.druid.timeBetweenEvictionRunsMillis@
  minEvictableIdleTimeMillis: @master.druid.minEvictableIdleTimeMillis@
  validationQuery: SELECT 1 FROM DUAL
  testWhileIdle: true
  testOnBorrow: false
  testOnReturn: false
  poolPreparedStatements: true
  maxPoolPreparedStatementPerConnectionSize: @master.druid.maxPoolPreparedStatementPerConnectionSize@
  filters: stat,wall,log4j
  web-stat-filter:
    enabled: false
  stat-view-servlet:
    enabled: false
实例化连接Bean

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
@Configuration
@ConditionalOnProperty("spring.datasource.companydb.name")
public class CompanyDbConfig {
    @Value("${mybatis.configuration.map-underscore-to-camel-case}")
    private boolean mapUnderscoreToCamelCase;
    @Value("${mybatis.mapper-locations}")
    private String mapperLocations;
    /**
     * datasource配置
     *
     * @return
     */
    @Bean(name = "companyDb")
    @ConfigurationProperties(prefix = "spring.datasource.companydb")
    public DataSource setDataSource() {
        return DruidDataSourceBuilder.create().build();
    }
    /**
     * SqlSessionFactory配置
     *
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean(name = "sqlSessionFactoryCompany")
    public SqlSessionFactory setSqlSessionFactory(@Qualifier("companyDb") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        // 设置数据源
        bean.setDataSource(dataSource);
        // 设置驼峰转换
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(mapUnderscoreToCamelCase);
        bean.setConfiguration(configuration);
        // 设置mapper文件位置
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
        return bean.getObject();
    }
    /**
     * SqlSessionTemplate配置
     *
     * @param sqlSessionFactory
     * @return
     */
    @Bean(name = "sqlSessionCompany")
    public SqlSessionTemplate setSqlSessionTemplate(@Qualifier("sqlSessionFactoryCompany") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
    @Bean(name = "jdbcTemplateCompany")
    public JdbcTemplate JdbcTemplateSlave(@Qualifier("companyDb") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}
使用例子

jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
    if (rowNum > 0) {
        throw new ServiceNoException("无异常处理");
    }
    ResultSetMetaData meta = rs.getMetaData();
    int count = meta.getColumnCount();
    for (int i = 1; i <= count; i++) {
        FieldInfo fieldInfo = new FieldInfo();
        fieldInfo.setColumnName(meta.getColumnName(i));
        fieldInfo.setFieldName(meta.getColumnLabel(i));
        fieldInfo.setFieldClass(meta.getColumnClassName(i));
        if (fieldsTitle.length >= i) {
            fieldInfo.setFieldComment(fieldsTitle[i - 1]);
        } else {
            fieldInfo.setFieldComment(meta.getColumnLabel(i));
        }
        list.add(fieldInfo);
    }
    return null;

});

List<Map<String, Object>> resultMap = jdbcTemplateCompany.queryForList(qSql);

 注解方式实现多数据源

import org.springframework.util.Assert;
public class DynamicDataSourceHolder {
    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<String>();
    public static void setDataSourceType(String dataSourceType) {
        Assert.notNull(dataSourceType, "DataSourceType cannot be null");
        CONTEXT_HOLDER.set(dataSourceType);
    }
    public static String getDataSourceType() {
        return (String) CONTEXT_HOLDER.get();
    }
    public static void clearDataSourceType() {
        CONTEXT_HOLDER.remove();
    }
}

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;
 
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceHolder.getDataSourceType();
    }
    @Override
    public Connection getConnection() throws SQLException {
        return determineTargetDataSource().getConnection();
    }
    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return determineTargetDataSource().getConnection(username, password);
    }
    @Override
    public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
        super.setDefaultTargetDataSource(defaultTargetDataSource);
    }
    @Override
    public void setTargetDataSources(Map<Object, Object> targetDataSources) {
        super.setTargetDataSources(targetDataSources);
    }
}

import lombok.extern.slf4j.Slf4j;
import org.aopalliance.intercept.MethodInterceptor;
import org.aopalliance.intercept.MethodInvocation;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.Ordered;

import java.lang.reflect.Method;

@Slf4j
public class ADBAop implements MethodInterceptor, Ordered {

   private String defalutDataSource = "one";

   public String getDefalutDataSource() {
      return defalutDataSource;
   }

   public void setDefalutDataSource(String defalutDataSource) {
      this.defalutDataSource = defalutDataSource;
   }

   //   @Pointcut("execution( * com.leimingtech.service.module.goods.service.GoodsClassService.*(..))")
   public void readMethod() {

   }
   public void before(ProceedingJoinPoint point) {
      Object target = point.getTarget();
      String method = point.getSignature().getName();

      Class<?>[] classz = target.getClass().getInterfaces();
      Class<?>[] parameterTypes = ((MethodSignature) point.getSignature())
            .getMethod().getParameterTypes();
      try {
         Method m = target.getClass().getMethod(method, parameterTypes);
         if (m != null && m.isAnnotationPresent(DataSource.class)) {
            DataSource data = m
                  .getAnnotation(DataSource.class);
            DynamicDataSourceHolder.setDataSourceType(data.value());
         } else {
            DynamicDataSourceHolder.setDataSourceType(defalutDataSource);
         }

      } catch (Exception e) {
         log.error("错误信息：",e);
      }
      try {
         point.proceed();
      } catch (Throwable e) {

         log.error("错误信息",e);
      }
   }

   /*@After(value ="execution(* com.thinkgem.jeesite.test.service.TestService.*(..))")
   public void after(){
      System.out.println("after-------------");
   }
    @AfterThrowing("execution(* com.thinkgem.jeesite.test.service.TestService.*(..))")
   public void doAfterThrow(){
      System.out.println("throwing---------------");
   } */
   @Override
   public int getOrder() {
      return 1;
   }
   @Override
   public Object invoke(MethodInvocation point) throws Throwable {
      Method m = point.getMethod();
      if (m != null && m.isAnnotationPresent(DataSource.class)) {
         DataSource data = m
               .getAnnotation(DataSource.class);
         DynamicDataSourceHolder.setDataSourceType(data.value());
      } else {
         DynamicDataSourceHolder.setDataSourceType("one");
      }
      return point.proceed();
   }
}