Hikari 数据库连接池配置主从结构

HikariCP是数据库连接池,而且是号称史上最快的,而且目前来看确实是这样的,SpringBoot2.0也已经采用HikariCP作为默认连接池配置.

HikariCP 默认配置
主要参数是在 com.zaxxer.hikari.HikariConfig 中初始化的,部分参数是在 com.zaxxer.hikari.pool.PoolBase 中初始化的。

name描述构造器默认值默认配置validate之后的值validate重置
autoCommit自动提交从池中返回的连接truetrue-
connectionTimeout等待来自池的连接的最大毫秒数SECONDS.toMillis(30) = 3000030000如果小于250毫秒,则被重置回30秒
idleTimeout连接允许在池中闲置的最长时间MINUTES.toMillis(10) = 600000600000如果idleTimeout+1秒>maxLifetime 且 maxLifetime>0,则会被重置为0(代表永远不会退出);如果idleTimeout!=0且小于10秒,则会被重置为10秒
maxLifetime池中连接最长生命周期MINUTES.toMillis(30) = 18000001800000如果不等于0且小于30秒则会被重置回30分钟
connectionTestQuery如果您的驱动程序支持JDBC4,我们强烈建议您不要设置此属性nullnull-
minimumIdle池中维护的最小空闲连接数-110minIdle<0或者minIdle>maxPoolSize,则被重置为maxPoolSize
maximumPoolSize池中最大连接数,包括闲置和使用中的连接-110如果maxPoolSize小于1,则会被重置。当minIdle<=0被重置为DEFAULT_POOL_SIZE则为10;如果minIdle>0则重置为minIdle的值
metricRegistry该属性允许您指定一个 Codahale / Dropwizard MetricRegistry 的实例,供池使用以记录各种指标nullnull-
healthCheckRegistry该属性允许您指定池使用的Codahale / Dropwizard HealthCheckRegistry的实例来报告当前健康信息nullnull-
poolName连接池的用户定义名称,主要出现在日志记录和JMX管理控制台中以识别池和池配置nullHikariPool-1-
initializationFailTimeout如果池无法成功初始化连接,则此属性控制池是否将 fail fast11-
isolateInternalQueries是否在其自己的事务中隔离内部池查询,例如连接活动测试falsefalse-
allowPoolSuspension控制池是否可以通过JMX暂停和恢复falsefalse-
readOnly从池中获取的连接是否默认处于只读模式falsefalse-
registerMbeans是否注册JMX管理Bean(MBeansfalsefalse-
catalog为支持 catalog 概念的数据库设置默认 catalogdriver defaultnull-
connectionInitSql该属性设置一个SQL语句,在将每个新连接创建后,将其添加到池中之前执行该语句。nullnull-
driverClassNameHikariCP将尝试通过仅基于jdbcUrl的DriverManager解析驱动程序,但对于一些较旧的驱动程序,还必须指定driverClassNamenullnull-
transactionIsolation控制从池返回的连接的默认事务隔离级别nullnull-
validationTimeout连接将被测试活动的最大时间量SECONDS.toMillis(5) = 50005000如果小于250毫秒,则会被重置回5秒
leakDetectionThreshold记录消息之前连接可能离开池的时间量,表示可能的连接泄漏00如果大于0且不是单元测试,则进一步判断:(leakDetectionThreshold < SECONDS.toMillis(2) or (leakDetectionThreshold > maxLifetime && maxLifetime > 0),会被重置为0 . 即如果要生效则必须>0,而且不能小于2秒,而且当maxLifetime > 0时不能大于maxLifetime
dataSource这个属性允许你直接设置数据源的实例被池包装,而不是让HikariCP通过反射来构造它nullnull-
schema该属性为支持模式概念的数据库设置默认模式driver defaultnull-
threadFactory此属性允许您设置将用于创建池使用的所有线程的java.util.concurrent.ThreadFactory的实例。nullnull-
scheduledExecutor此属性允许您设置将用于各种内部计划任务的java.util.concurrent.ScheduledExecutorService实例nullnull-

0.maven

  <dependency>
		    <groupId>com.zaxxer</groupId>
		    <artifactId>HikariCP</artifactId>
		    <version>3.2.0</version>
		</dependency>

1.mysqlContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd">
       
  
    
   <!--  hikari datasource  --> 
    <bean id="abstractDataSource" abstract="true" class="com.zaxxer.hikari.HikariDataSource"  destroy-method="close">
        <property name="connectionTimeout" value="30000" />
        <property name="idleTimeout" value="600000" />
        <property name="maxLifetime" value="1800000" />
        
    </bean>  

    <bean id="master" parent="abstractDataSource">
        <property name="driverClassName" value="${mysql.connection.driver_class}" />
        <property name="jdbcUrl" value="${mysql.connection.url}" />
        <property name="username" value="${mysql.connection.username}" />
        <property name="password" value="${mysql.connection.password}" />
        <property name="readOnly" value="false" />
        <property name="maximumPoolSize" value="3" />
    </bean>
    
    <bean id="slave" parent="abstractDataSource">
        <property name="driverClassName" value="${mysql.slave.connection.driver_class}" />
        <property name="jdbcUrl" value="${mysql.slave.connection.url}" />
        <property name="username" value="${mysql.slave.connection.username}" />
        <property name="password" value="${mysql.slave.connection.password}" />
        <property name="readOnly" value="true" />
        <property name="maximumPoolSize" value="20" />
    </bean>
    
    <bean id="dynamicDataSource" class="cn.xxx.common.persistence.datasource.DynamicDataSource">
        <property name="targetDataSources">
            <map>
                <entry value-ref="master" key="master"></entry>
                <entry value-ref="slave" key="slave"></entry>
            </map>
        </property>
    </bean>
    
     <bean id="dataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
        <property name="targetDataSource">
            <ref bean="dynamicDataSource"></ref>
        </property>
    </bean>

    <bean id="sessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation" value="classpath:mybatis-config.xml" />
    </bean>

    <!-- scan for mappers and let them be autowired -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="cn.xxx.common.persistence.mapper" />
        <property name="sqlSessionFactoryBeanName" value="sessionFactory"/>
    </bean>

    <bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>

</beans>


2.mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <settings>
        <!-- 这个配置使全局的映射器启用或禁用缓存 -->
        <setting name="cacheEnabled" value="false"/>
        <!-- 全局启用或禁用延迟加载。当禁用时,所有关联对象都会即时加载 -->
        <setting name="lazyLoadingEnabled" value="false"/>
        <!-- 当启用时,有延迟加载属性的对象在被调用时将会完全加载任意属性。否则,每种属性将会按需要加载 -->
        <setting name="aggressiveLazyLoading" value="false"/>
        <!-- 允许JDBC支持生成的键。需要适合的驱动。如果设置为true则这个设置强制生成的键被使用,尽管一些驱动拒绝兼容但仍然有效(比如Derby) -->
        <setting name="useGeneratedKeys" value="true"/>
        <!-- 允许或不允许多种结果集从一个单独的语句中返回(需要适合的驱动) -->
        <setting name="multipleResultSetsEnabled" value="true"/>
        <!-- 使用列标签代替列名。不同的驱动在这方便表现不同。参考驱动文档或充分测试两种方法来决定所使用的驱动 -->
        <setting name="useColumnLabel" value="true"/>
        <!-- 指定MyBatis如何自动映射列到字段/属性。PARTIAL只会自动映射简单,没有嵌套的结果。FULL会自动映射任意复杂的结果(嵌套的或其他情况) -->
        <setting name="autoMappingBehavior" value="PARTIAL"/>
        <!-- 配置默认的执行器。SIMPLE执行器没有什么特别之处。REUSE执行器重用预处理语句。BATCH执行器重用语句和批量更新 -->
        <setting name="defaultExecutorType" value="SIMPLE"/>
        <!-- 设置超时时间,它决定驱动等待一个数据库响应的时间 -->
        <setting name="defaultStatementTimeout" value="25000"/>
    </settings>

    <typeAliases>
        <package name="cn.xxx.common.persistence.model"/>
    </typeAliases>

    <plugins>
    	<plugin interceptor="cn.xxx.common.persistence.datasource.DynamicDataSourceInterceptor" />
        <plugin interceptor="com.xxx.orm.mybatis.interceptor.PersistenceInterceptor" />
    </plugins>
    
    <mappers>
        <package name="cn.xxx.common.persistence.mapper"/>
    </mappers>

</configuration>

3.DynamicDataSourceInterceptor


import java.util.Locale;
import java.util.Properties;

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.keygen.SelectKeyGenerator;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.transaction.support.TransactionSynchronizationManager;

@Intercepts({ @Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class }),
		@Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,
				RowBounds.class, ResultHandler.class }) })

public class DynamicDataSourceInterceptor implements Interceptor {

	private static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*|.*xxl_job.*|.*XXL_JOB.*";

	@Override
	public Object intercept(Invocation invocation) throws Throwable {

		boolean syschronizationActive = TransactionSynchronizationManager.isActualTransactionActive();
		Object[] objects = invocation.getArgs();
		MappedStatement mappedStatement = (MappedStatement) objects[0];
		String lookupKey = DynamicDataSourceHolder.DB_MASTER;

		if (syschronizationActive != true) {

			if (mappedStatement.getSqlCommandType().equals(SqlCommandType.SELECT)) {
				if (mappedStatement.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) {
					lookupKey = DynamicDataSourceHolder.DB_MASTER;
				} else {
					BoundSql boundSql = mappedStatement.getSqlSource().getBoundSql(objects[1]);
					String sql = boundSql.getSql().toLowerCase(Locale.CHINA).replaceAll("[\\t\\n\\r]", " ");
					if (sql.matches(REGEX)) {
						lookupKey = DynamicDataSourceHolder.DB_MASTER;
					} else {
						lookupKey = DynamicDataSourceHolder.DB_SLAVE;
					}
				}

			}
		} else {
			lookupKey = DynamicDataSourceHolder.DB_MASTER;
		}
		DynamicDataSourceHolder.setDbType(lookupKey);
		return invocation.proceed();
	}

	@Override
	public Object plugin(Object target) {
		if (target instanceof Executor)
			return Plugin.wrap(target, this);
		else
			return target;
	}

	@Override
	public void setProperties(Properties properties) {
	}

}

4.PersistenceInterceptor


import java.util.Collection;
import java.util.Date;
import java.util.Properties;
import org.apache.ibatis.binding.MapperMethod.ParamMap;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;

@Intercepts({@Signature(
    type = Executor.class,
    method = "update",
    args = {MappedStatement.class, Object.class}
)})
public class PersistenceInterceptor implements Interceptor {
    public PersistenceInterceptor() {
    }

    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        MappedStatement mappedStatement = (MappedStatement)args[0];
        Object object = args[1];
        NulEntity entity;
        if (mappedStatement.getSqlCommandType().equals(SqlCommandType.INSERT) && ThreadLocalHolder.get() != null) {
            if (object instanceof NulEntity) {
                entity = (NulEntity)object;
                entity.setCreator(ThreadLocalHolder.get().toString());
                entity.setCreatedAt(new Date());
            }

            if (object instanceof ParamMap) {
                ParamMap paramMap = (ParamMap)object;
                Object obj = paramMap.get("param1");
                if (obj instanceof Collection) {
                    Collection list = (Collection)obj;
                    Object[] objects = list.toArray();
                    if (objects[0] instanceof NulEntity) {
                        for(int i = 0; i < objects.length; ++i) {
                            NulEntity entity = (NulEntity)objects[i];
                            entity.setCreator(ThreadLocalHolder.get().toString());
                            entity.setCreatedAt(new Date());
                        }
                    }
                }
            }
        } else if (mappedStatement.getSqlCommandType().equals(SqlCommandType.UPDATE) && ThreadLocalHolder.get() != null && object instanceof NulEntity) {
            entity = (NulEntity)object;
            entity.setModifier(ThreadLocalHolder.get().toString());
            entity.setUpdatedAt(new Date());
            String key = CacheHelper.buildKey(entity.getClass().getName() + ":" + entity.getId());
            CacheHelper.evict(CacheType.FIVE_MINUTE_STORE.getValue(), key);
        }

        return invocation.proceed();
    }

    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    public void setProperties(Properties properties) {
    }
}

5.DynamicDataSourceHolder


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class DynamicDataSourceHolder {

	private static final Logger LOG = LoggerFactory.getLogger(DynamicDataSourceHolder.class);

	private static ThreadLocal<String> contextHolder = new ThreadLocal<String>();
	public static final String DB_MASTER = "master";
	public static final String DB_SLAVE = "slave";

	public static String getDbType() {
		String db = contextHolder.get();
		if (db == null)
			db = DB_MASTER;
		if (LOG.isInfoEnabled())
			LOG.info("当前所使用的数据源为:" + db);
		return db;
	}

	public static void setDbType(String datasource) {

		contextHolder.set(datasource);
	}

	public static void clearDbType() {
		contextHolder.remove();
	}

	public static boolean isMaster() {
		return DB_MASTER.equals(getDbType());
	}

}

6.DynamicDataSource

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {

	@Override
	protected Object determineCurrentLookupKey() {
		return DynamicDataSourceHolder.getDbType();
	}

}

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值