Spring JDBC主从数据库访问配置




1. 将读操作和写操作分离到不同的数据库上,避免主服务器出现性能瓶颈;

2. 主服务器进行写操作时,不影响查询应用服务器的查询性能,降低阻塞,提高并发;

3. 数据拥有多个容灾副本,提高数据安全性,同时当主服务器故障时,可立即切换到其他服务器,提高系统可用性;







    <bean id="masterDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="${database.master.jdbcUrl}"></property>
        <property name="user" value="${database.master.user}" />
        <property name="password" value="${database.master.password}" />
        <property name="maxPoolSize" value="${database.master.maxPoolSize}"></property>  
        <property name="minPoolSize" value="${database.master.minPoolSize}"></property>
        <property name="maxIdleTime" value="${database.master.maxIdleTime}"></property>
        <property name="idleConnectionTestPeriod" value="${database.master.idleConnectionTestPeriod}"></property>
    <bean id="slaveDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="${database.slave.jdbcUrl}"></property>
        <property name="user" value="${database.slave.user}" />
        <property name="password" value="${database.slave.password}" />
        <property name="maxPoolSize" value="${database.slave.maxPoolSize}"></property>  
        <property name="minPoolSize" value="${database.slave.minPoolSize}"></property>
        <property name="maxIdleTime" value="${database.slave.maxIdleTime}"></property>
        <property name="idleConnectionTestPeriod" value="${database.slave.idleConnectionTestPeriod}"></property>




<-- 此类继承了AbstractRoutingDataSource 类,且 AbstractRoutingDataSource类 为Spring jdbc中提供的类,需要重写其中的determineCurrentLookupKey()方法,获取当前切换到的数据库源名称-->
    <bean id="dataSource" class="com.imzhitu.admin.common.dataSourceMasterSlave.DynamicDataSource">
        <property name="targetDataSources"><-- 将数据源置入到类中,通过之后的aop拦截到的数据库名称,匹配到指定的数据源进而链接 -->
            <map key-type="java.lang.String">
                <entry key="master" value-ref="masterDataSource"/>
                <entry key="slave" value-ref="slaveDataSource"/>
        <property name="defaultTargetDataSource" ref="masterDataSource"/>


package com.imzhitu.admin.common.dataSourceMasterSlave;

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

 * 动态数据源,动态获取数据源的实现
public class DynamicDataSource extends AbstractRoutingDataSource{
     * 用户返回当且切换到的数据库
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceHolder.getDataSource();//DynamicDataSourceHolder有获取和设置当前数据库的方法get & put



package com.imzhitu.admin.common.dataSourceMasterSlave;

 * 动态数据源holder
public class DynamicDataSourceHolder {
    public static final ThreadLocal<String> holder = new ThreadLocal<String>();
    public static void putDataSource(String name) {
    public static String getDataSource() {
        return holder.get();


AbstractRoutingDataSource.java <简化版>
 * Copyright 2002-2012 the original author or authors.
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *      http://www.apache.org/licenses/LICENSE-2.0
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * See the License for the specific language governing permissions and
 * limitations under the License.

package org.springframework.jdbc.datasource.lookup;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.jdbc.datasource.AbstractDataSource;
import org.springframework.util.Assert;

 * Abstract {@link javax.sql.DataSource} implementation that routes {@link #getConnection()}
 * calls to one of various target DataSources based on a lookup key. The latter is usually
 * (but not necessarily) determined through some thread-bound transaction context.
 * @author Juergen Hoeller
 * @since 2.0.1
 * @see #setTargetDataSources
 * @see #setDefaultTargetDataSource
 * @see #determineCurrentLookupKey()
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {

    private Map<Object, Object> targetDataSources;//所有数据源,在xml中有相应的配置

    private Object defaultTargetDataSource;//默认数据源,在xml中有相应的配置private Map<Object, DataSource> resolvedDataSources;//将targetDataSources值传入其中,做值的传递

    private DataSource resolvedDefaultDataSource;//同targetDataSources

     * Specify the map of target DataSources, with the lookup key as key.
     * The mapped value can either be a corresponding {@link javax.sql.DataSource}
     * instance or a data source name String (to be resolved via a
     * {@link #setDataSourceLookup DataSourceLookup}).
     * <p>The key can be of arbitrary type; this class implements the
     * generic lookup process only. The concrete key representation will
     * be handled by {@link #resolveSpecifiedLookupKey(Object)} and
     * {@link #determineCurrentLookupKey()}.
    public void setTargetDataSources(Map<Object, Object> targetDataSources) {
        this.targetDataSources = targetDataSources;

     * Specify the default target DataSource, if any.
     * <p>The mapped value can either be a corresponding {@link javax.sql.DataSource}
     * instance or a data source name String (to be resolved via a
     * {@link #setDataSourceLookup DataSourceLookup}).
     * <p>This DataSource will be used as target if none of the keyed
     * {@link #setTargetDataSources targetDataSources} match the
     * {@link #determineCurrentLookupKey()} current lookup key.
    public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
        this.defaultTargetDataSource = defaultTargetDataSource;

     * Specify whether to apply a lenient fallback to the default DataSource
     * if no specific DataSource could be found for the current lookup key.
     * <p>Default is "true", accepting lookup keys without a corresponding entry
     * in the target DataSource map - simply falling back to the default DataSource
     * in that case.
     * <p>Switch this flag to "false" if you would prefer the fallback to only apply
     * if the lookup key was {@code null}. Lookup keys without a DataSource
     * entry will then lead to an IllegalStateException.
     * @see #setTargetDataSources
     * @see #setDefaultTargetDataSource
     * @see #determineCurrentLookupKey()
    public void setLenientFallback(boolean lenientFallback) {
        this.lenientFallback = lenientFallback;

     * Set the DataSourceLookup implementation to use for resolving data source
     * name Strings in the {@link #setTargetDataSources targetDataSources} map.
     * <p>Default is a {@link JndiDataSourceLookup}, allowing the JNDI names
     * of application server DataSources to be specified directly.
    public void setDataSourceLookup(DataSourceLookup dataSourceLookup) {
        this.dataSourceLookup = (dataSourceLookup != null ? dataSourceLookup : new JndiDataSourceLookup());

    public void afterPropertiesSet() {
        if (this.targetDataSources == null) {
            throw new IllegalArgumentException("Property 'targetDataSources' is required");
     //*** 将targetDataSources的值传递给resolvedDataSources
this.resolvedDataSources = new HashMap<Object, DataSource>(this.targetDataSources.size()); for (Map.Entry<Object, Object> entry : this.targetDataSources.entrySet()) { Object lookupKey = resolveSpecifiedLookupKey(entry.getKey()); DataSource dataSource = resolveSpecifiedDataSource(entry.getValue()); this.resolvedDataSources.put(lookupKey, dataSource); }if (this.defaultTargetDataSource != null) { this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource); }
     //*** }
/** * Resolve the given lookup key object, as specified in the * {@link #setTargetDataSources targetDataSources} map, into * the actual lookup key to be used for matching with the * {@link #determineCurrentLookupKey() current lookup key}. * <p>The default implementation simply returns the given key as-is. * @param lookupKey the lookup key object as specified by the user * @return the lookup key as needed for matching */ protected Object resolveSpecifiedLookupKey(Object lookupKey) { return lookupKey; } /** * Resolve the specified data source object into a DataSource instance. * <p>The default implementation handles DataSource instances and data source * names (to be resolved via a {@link #setDataSourceLookup DataSourceLookup}). * @param dataSource the data source value object as specified in the * {@link #setTargetDataSources targetDataSources} map * @return the resolved DataSource (never {@code null}) * @throws IllegalArgumentException in case of an unsupported value type */ protected DataSource resolveSpecifiedDataSource(Object dataSource) throws IllegalArgumentException { if (dataSource instanceof DataSource) { return (DataSource) dataSource; } else if (dataSource instanceof String) { return this.dataSourceLookup.getDataSource((String) dataSource); } else { throw new IllegalArgumentException( "Illegal data source value - only [javax.sql.DataSource] and String supported: " + dataSource); } }

/** * Retrieve the current target DataSource. Determines the * {@link #determineCurrentLookupKey() current lookup key}, performs * a lookup in the {@link #setTargetDataSources targetDataSources} map, * falls back to the specified * {@link #setDefaultTargetDataSource default target DataSource} if necessary. * @see #determineCurrentLookupKey() */

  //此方法在本类的 getConnection() 方法中调用,获取当前数据源的连接Connection,从而进行数据库操作
protected DataSource determineTargetDataSource() { Assert.notNull(this.resolvedDataSources, "DataSource router not initialized"); Object lookupKey = determineCurrentLookupKey(); DataSource dataSource = this.resolvedDataSources.get(lookupKey); if (dataSource == null && (this.lenientFallback || lookupKey == null)) { dataSource = this.resolvedDefaultDataSource; } if (dataSource == null) { throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]"); } return dataSource; } /** * Determine the current lookup key. This will typically be * implemented to check a thread-bound transaction context. * <p>Allows for arbitrary keys. The returned key needs * to match the stored lookup key type, as resolved by the * {@link #resolveSpecifiedLookupKey} method. */

  //获取当前数据源的名称;在本类的子类中需要重写此方法 protected abstract Object determineCurrentLookupKey(); }




    <!-- 配置数据库注解aop -->
    <bean id="manyDataSourceAspect" class="com.imzhitu.admin.common.dataSourceMasterSlave.DataSourceAspect"/>
        <aop:aspect id="dataSourceCut" ref="manyDataSourceAspect"><-- 切面类 -->
            <aop:pointcut expression="execution(* com.imzhitu.admin..*.mapper.*.*(..))" id="dataSourceCutPoint"/><-- 配置切点 -->
            <aop:before pointcut-ref="dataSourceCutPoint" method="before"/><-- 切面执行方法 -->



package com.imzhitu.admin.common.dataSourceMasterSlave;

import java.lang.reflect.Method;

import org.apache.log4j.Logger;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.reflect.MethodSignature;

 * 数据源动态选择切面
public class DataSourceAspect {
    private Logger log = Logger.getLogger(DataSourceAspect.class);
    public void before(JoinPoint 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 = classz[0].getMethod(method, parameterTypes);
            if ( m != null && m.isAnnotationPresent(DataSource.class)) {
                DataSource data = m.getAnnotation(DataSource.class);//获取访问mapper中的注释
        } catch (Exception e) {
            // handle exception



  DataSource.java<-- 自定义注释 -->

package com.imzhitu.admin.common.dataSourceMasterSlave;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

 * 数据库annotation定义
 * @DataSource('master') / @DataSource('slave')

public @interface DataSource {
    String value();//唯一值,所以注释中没有写@DataSource(value = 'master');也可以写成 String value() defalut "master";即默认访问主数据库




package com.imzhitu.admin.ztworld.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.hts.web.common.pojo.HTWorldSubtitleDto;
import com.imzhitu.admin.common.dataSourceMasterSlave.DataSource;
import com.imzhitu.admin.common.pojo.ZTWorldSubtitle;

public interface SubtitleMapper {

    public List<HTWorldSubtitleDto> queryCacheSubtitle(@Param("transTo")String transTo,
            @Param("limit")Integer limit);
    public List<ZTWorldSubtitle> queryTitles(ZTWorldSubtitle title);
    public long queryTotal(ZTWorldSubtitle title);
    public void saveSubtitle(ZTWorldSubtitle title);
    public void update(ZTWorldSubtitle title);

    public void deleteByIds(Integer[] ids);
    public void updateSerialById(@Param("id")Integer id, 
            @Param("serial")Integer serial);






 2.执行切面DataSourceAspect中的before方法,将数据源名称放入 DynamicDataSourceHolder中

 3.Spring JDBC调用determineCurrentLookupKey()方法<DynamicDataSource中重写AbstractRoutingDataSource类中的方法> ,从DynamicDataSourceHolder取出当前的数据库名称,并返回






2.Spring JDBC中 AbstractRoutingDataSource




posted @ 2016-09-25 12:46 孙猴子 阅读(...) 评论(...) 编辑 收藏