Spring JDBC主从数据库访问配置
通过昨天学习的自定义配置注释的知识，探索了解一下web主从数据库的配置：
背景：
主从数据库：主要是数据上的读写分离；
数据库的读写分离的好处？
1. 将读操作和写操作分离到不同的数据库上，避免主服务器出现性能瓶颈；
2. 主服务器进行写操作时，不影响查询应用服务器的查询性能，降低阻塞，提高并发；
3. 数据拥有多个容灾副本，提高数据安全性，同时当主服务器故障时，可立即切换到其他服务器，提高系统可用性；
读写分离的基本原理就是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE）操作，而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到其他从数据库。以SQL为例，主库负责写数据、读数据。读库仅负责读数据。每次有写库操作，同步更新到读库。写库就一个，读库可以有多个，采用日志同步的方式实现主库和多个读库的数据同步。
配置步骤：
此文暂时不包含数据同步问题，数据同步移步
1.配置数据源
<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> <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> </bean>
2.配置切换数据源类
xml
<-- 此类继承了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"/> </map> </property> <property name="defaultTargetDataSource" ref="masterDataSource"/> </bean>
DynamicDataSource.java
package com.imzhitu.admin.common.dataSourceMasterSlave; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; /** * 动态数据源，动态获取数据源的实现 * */ public class DynamicDataSource extends AbstractRoutingDataSource{ /** * 用户返回当且切换到的数据库 */ @Override protected Object determineCurrentLookupKey() { return DynamicDataSourceHolder.getDataSource();//DynamicDataSourceHolder有获取和设置当前数据库的方法get & put } }
DynamicDataSourceHolder.java
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) { holder.set(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, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * 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()); } @Override 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() */
//通过调用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(); }
3.配置AOP，自定义注释，获取访问是应该链接的数据库源名
xml
<!-- 配置数据库注解aop --> <aop:aspectj-autoproxy></aop:aspectj-autoproxy> <bean id="manyDataSourceAspect" class="com.imzhitu.admin.common.dataSourceMasterSlave.DataSourceAspect"/> <aop:config> <aop:aspect id="dataSourceCut" ref="manyDataSourceAspect"><-- 切面类 --> <aop:pointcut expression="execution(* com.imzhitu.admin..*.mapper.*.*(..))" id="dataSourceCutPoint"/><-- 配置切点 --> <aop:before pointcut-ref="dataSourceCutPoint" method="before"/><-- 切面执行方法 --> </aop:aspect> </aop:config>
DataSourceAspect.java
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中的注释 DynamicDataSourceHolder.putDataSource(data.value());//获取注释中的value值，确定访问的数据源 if(log.isDebugEnabled()){ log.debug("DataSourceAspect:======================="+data.value()); } } } catch (Exception e) { // handle exception e.printStackTrace(); } } }
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') * */ @Retention(RetentionPolicy.RUNTIME) @Target(ElementType.METHOD) public @interface DataSource { String value();//唯一值，所以注释中没有写@DataSource(value = 'master');也可以写成 String value() defalut "master";即默认访问主数据库
}
mapper.java<读写分离>
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 { @DataSource("slave") public List<HTWorldSubtitleDto> queryCacheSubtitle(@Param("transTo")String transTo, @Param("limit")Integer limit); @DataSource("slave") public List<ZTWorldSubtitle> queryTitles(ZTWorldSubtitle title); @DataSource("slave") public long queryTotal(ZTWorldSubtitle title); @DataSource("master") public void saveSubtitle(ZTWorldSubtitle title); @DataSource("master") public void update(ZTWorldSubtitle title); @DataSource("master") public void deleteByIds(Integer[] ids); @DataSource("master") public void updateSerialById(@Param("id")Integer id, @Param("serial")Integer serial); }
数据流转顺序：
1.xml<aop>拦截到数据源名称
2.执行切面DataSourceAspect中的before方法，将数据源名称放入 DynamicDataSourceHolder中
3.Spring JDBC调用determineCurrentLookupKey()方法<DynamicDataSource中重写AbstractRoutingDataSource类中的方法> ，从DynamicDataSourceHolder取出当前的数据库名称，并返回
4.AbstractRoutingDataSource类中determineTargetDataSource()方法调用determineCurrentLookupKey()匹配到指定的数据库，并建立链接，即为切换到相应的数据库；
5.在指定的数据库中执行相应的sql
总结：
1.注释自定义
2.Spring JDBC中 AbstractRoutingDataSource
3.xml中数据源配置，aop配置
以上三者为主从数据库实现的核心技术
