基于druid和spring的动态数据库以及读写分离

灰太狼 2022-04-06 01:52 301阅读 0赞

spring与druid可以实现动态数据源，夸库查询，读写分离等功能。现在说一下配置：

1、需要配置多个spring数据源

spring-data.xml

<bean id="dynamicDataSource" class="com.myproject.common.db.util.DynamicDataSource">
    		
    		<property name="targetDataSources">
    			<map>
    				<entry value-ref="dataSourceWrite" key="dataSourceWrite"></entry>
    				<entry value-ref="dataSourceRead" key="dataSourceRead"></entry>
    			</map>
    		</property>
    		<property name="defaultTargetDataSource" ref="dataSourceWrite" />
    	</bean>
    
    	
    	<bean id="sqlSessionFactory" name="sqlSessionFactory"
    		class="org.mybatis.spring.SqlSessionFactoryBean">
    		<property name="configLocation" value="classpath:mybatis.xml"></property>
    		<property name="mapperLocations" value="classpath*:mapper/*.xml" />
    		<property name="dataSource" ref="dynamicDataSource" />
    	</bean>
    
    	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
    		<constructor-arg index="0" ref="sqlSessionFactory" />
    	</bean>
    	
    	
    	<bean id="transactionManager"  
            class="org.springframework.jdbc.datasource.DataSourceTransactionManager">  
            <property name="dataSource" ref="dynamicDataSource" />  
        </bean>  
        
        <tx:annotation-driven transaction-manager="transactionManager" /> 
    
    	
    	<bean id="dataSourceWrite" class="com.alibaba.druid.pool.DruidDataSource"
    		init-method="init" destroy-method="close">
    		<property name="url" value="${urlOracle}" />
    		<property name="username" value="${usernameOracle}" />
    		<property name="password" value="${passwordOracle}" />
    		
    		<property name="initialSize" value="5" />
    		
    		<property name="maxActive" value="200" />
    		
    		<property name="minIdle" value="5" />
    		
    		<property name="maxWait" value="60000" />
    		
    		
    		<property name="testOnBorrow" value="false" />
    		<property name="testOnReturn" value="false" />
    		<property name="testWhileIdle" value="true" />
    		
    		<property name="timeBetweenEvictionRunsMillis" value="60000" />
    		
    		<property name="minEvictableIdleTimeMillis" value="25200000" />
    		
    		<property name="removeAbandoned" value="true" />
    		
    		<property name="removeAbandonedTimeout" value="1800" />
    		
    		<property name="logAbandoned" value="true" />
    		
    		
    		<property name="filters" value="stat" />
    		<property name="defaultAutoCommit" value="true" />
    	</bean>
    	
    	<bean id="dataSourceRead" class="com.alibaba.druid.pool.DruidDataSource"
    		init-method="init" destroy-method="close">
    		<property name="url" value="${urlMysql}" />
    		<property name="username" value="${usernameMysql}" />
    		<property name="password" value="${passwordMysql}" />
    		
    		<property name="initialSize" value="5" />
    		
    		<property name="maxActive" value="200" />
    		
    		<property name="minIdle" value="5" />
    		
    		<property name="maxWait" value="60000" />
    		
    		
    		<property name="testOnBorrow" value="false" />
    		<property name="testOnReturn" value="false" />
    		<property name="testWhileIdle" value="true" />
    		
    		<property name="timeBetweenEvictionRunsMillis" value="60000" />
    		
    		<property name="minEvictableIdleTimeMillis" value="25200000" />
    		
    		<property name="removeAbandoned" value="true" />
    		
    		<property name="removeAbandonedTimeout" value="1800" />
    		
    		<property name="logAbandoned" value="true" />
    		
    		
    		<property name="filters" value="stat" />
    		<property name="defaultAutoCommit" value="true" />
    	</bean>

2、需要写一个DynamicDataSource类继承AbstractRoutingDataSource，并实现determineCurrentLookupKey方法

public class DynamicDataSource extends AbstractRoutingDataSource {  
    	  
        
        /** 
         *  
         * override determineCurrentLookupKey 
         * <p> 
         * Title: determineCurrentLookupKey 
         * </p> 
         * <p> 
         * Description: 自动查找datasource 
         * </p> 
         *  
         * @return 
         */  
        @Override  
        protected Object determineCurrentLookupKey() {  
            return DBContextHolder.getDSType();  
        }  
      
    }

3、参考spring事务管理，使用线程变量来切换数据源

public class DBContextHolder {
    
    	/**
    	 * 线程threadlocal
    	 */
    	private static ThreadLocal<String> contextHolder = new ThreadLocal<>();
    	private static Logger logger = LoggerFactory
    			.getLogger(DBContextHolder.class);
    
    	public static String getDSType() {
    		try {
    
    		} catch (Exception e) {
    			e.printStackTrace();
    			logger.error("get DBTYPE faild with error:[" + e.getMessage() + "]");
    		}
    
    		String db = contextHolder.get();
    		 if (db == null) {
    		 db =UrlConnect.getKey(ConfigHelper.getToWriteKey());// 默认是读写库
    		 }
    		return db;
    	}
    
    	/**
    	 * 
    	 * 设置本线程的dbtype
    	 * 
    	 * @param str
    	 * @see [相关类/方法](可选)
    	 * @since [产品/模块版本](可选)
    	 */
    	public static boolean setDSType(String str) {
    		try {
    			clearDBType();
    			if (str != null&&!str.equals("")) {
    				contextHolder.set(str);
    				logger.info("change thread[" + str + "] success!");
    				return true;
    			} else {
    				logger.info("change thread[" + str + "] faild!");
    				return false;
    			}
    		} catch (Exception e) {
    			e.printStackTrace();
    			logger.error("change thread[" + str + "] faild!");
    			return false;
    		}
    	}
    
    	/**
    	 * clearDBType
    	 * 
    	 * @Title: clearDBType
    	 * @Description: 清理连接类型
    	 */
    	public static void clearDSType() {
    		contextHolder.remove();
    	}
    }

4、在dao中切换数据源

@Repository
    public class BaseDAO extends SqlSessionDaoSupport {
           @Resource
    	private SqlSessionTemplate sqlSessionTemplate;
    @Resource
    	public void setSqlSessionTemplate(SqlSessionTemplate sqlSessionTemplate) {
    		super.setSqlSessionTemplate(sqlSessionTemplate);
    	}
    
    public  <T> PageList<T> selectPublicListPage(String countSqlID,String sqlID,
    			PageList<T> page, Object obj) {
    		DBContextHolder.setDbType("dataSourceRead");
    		//查询总数
    		Integer total = this.getSqlSession().selectOne(countSqlID, obj);
    		RowBounds rowBounds=new RowBounds(page.getFirstResult(),page.getPageSize());
    		// 查询列表信息
    		List<T> list = this.getSqlSession().selectList(
    				sqlID, obj,rowBounds);
    		page.setTotalRecord(total!=null?total:0);
    		page.setDataSource(list);
    		page.setTotalPage((total + page.getPageSize() - 1)
    				/ page.getPageSize());
    		return page;
    	}
    
    	public int insert(String sqlID, Object paramObj) {
    		DBContextHolder.setDbType("dataSourceWrite");
    		return this.getSqlSession().insert(sqlID, paramObj);
    	}
    
    
    }