南京网站建设哪家专业/百度关键字搜索排名

在开发中我们经常遇到多数据源的情况，比如读写分离、分库分表等情况，而且不仅仅是要配置多个数据源，还得在代码中动态的切换数据源。比如写操作访问主库数据源(master)，读操作访问从库数据源(slave)。
Spring框架中提供了AbstractRoutingDataSource接口来帮助我们灵活动态的切换数据源。

假设有一个订单表tb_order，表结构如下：

CREATE TABLE `tb_order` (`id` BIGINT(10) NOT NULL AUTO_INCREMENT COMMENT '自增id主键',`customer_name` VARCHAR(40) NOT NULL COMMENT '客户名称',`total_price` DOUBLE (8,2) NOT NULL COMMENT '商品总价',`amount` INT(6) NOT NULL COMMENT '商品数量',`address` VARCHAR(200) NOT NULL COMMENT '客户详细地址',PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

tb_order表对应的dao如下：

package com.ricky.codelab.spring.jdbc.dao.impl;import com.ricky.codelab.spring.domain.Order;
import com.ricky.codelab.spring.jdbc.dao.IOrderDao;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;/*** ${DESCRIPTION}** @author Ricky Fung* @create 2016-10-18 23:06*/
@Repository("orderDao")
public class OrderDaoImpl implements IOrderDao {@Resource(name = "jdbcTemplate")private JdbcTemplate jdbcTemplate;@Overridepublic long insert(Order order) {String sql = "INSERT INTO tb_order(customer_name,total_price,amount,address) VALUES (?,?,?,?)";return jdbcTemplate.update(sql, order.getCustomerName(),order.getTotalPrice(), order.getAmount(), order.getAddress());}@Overridepublic List<Order> queryOrders(){return jdbcTemplate.query("SELECT * FROM tb_order", new RowMapper<Order>() {@Overridepublic Order mapRow(ResultSet rs, int i) throws SQLException {Order order = new Order();order.setId(rs.getLong("id"));order.setCustomerName(rs.getString("customer_name"));order.setTotalPrice(rs.getDouble("total_price"));order.setAmount(rs.getInt("amount"));order.setAddress(rs.getString("address"));return order;}});}
}

Order类如下：

package com.ricky.codelab.spring.domain;/*** ${DESCRIPTION}** @author Ricky Fung* @create 2016-10-18 23:02*/
public class Order {private Long id;private String customerName;private double totalPrice;private int amount;private String address;public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public Long getId() {return id;}public void setId(Long id) {this.id = id;}public String getCustomerName() {return customerName;}public void setCustomerName(String customerName) {this.customerName = customerName;}public double getTotalPrice() {return totalPrice;}public void setTotalPrice(double totalPrice) {this.totalPrice = totalPrice;}public int getAmount() {return amount;}public void setAmount(int amount) {this.amount = amount;}
}

Spring数据源配置如下：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:tx="http://www.springframework.org/schema/tx"xmlns:context="http://www.springframework.org/schema/context"xmlns:util="http://www.springframework.org/schema/util"xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx.xsdhttp://www.springframework.org/schema/utilhttp://www.springframework.org/schema/util/spring-util.xsdhttp://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context.xsd"default-lazy-init="false"><context:annotation-config /><context:component-scan base-package="com.ricky.codelab.spring" /><bean id="parentDataSource" class="com.alibaba.druid.pool.DruidDataSource"destroy-method="close"  abstract="true" init-method="init" ><!-- 初始化连接大小 --><property name="initialSize" value="2" /><!-- 连接池最大使用连接数量 --><property name="maxActive" value="10" /><!-- 连接池最小空闲 --><property name="minIdle" value="5" /><!-- 获取连接最大等待时间 --><property name="maxWait" value="30000" /><!-- <property name="poolPreparedStatements" value="true" /> --><!-- <property name="maxPoolPreparedStatementPerConnectionSize" value="33" /> --><property name="validationQuery" value="SELECT 1" /><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" /><!-- 打开removeAbandoned功能 --><property name="removeAbandoned" value="true" /><!-- 1800秒，也就是30分钟 --><property name="removeAbandonedTimeout" value="1800" /><!-- 关闭abanded连接时输出错误日志 --><property name="logAbandoned" value="true" /><!-- 监控数据库 --><!-- <property name="filters" value="stat" /> --><property name="filters" value="mergeStat" /></bean><!-- 配置数据源--><bean id="masterDataSource" parent="parentDataSource"><property name="url" value="#{jdbc['master.jdbc.url']}" /><property name="username" value="#{jdbc['master.jdbc.username']}" /><property name="password" value="#{jdbc['master.jdbc.password']}" /><property name="driverClassName" value="#{jdbc['master.jdbc.driver']}" /><property name="maxActive" value="15" /></bean><bean id="slave1DataSource" parent="parentDataSource"><property name="url" value="#{jdbc['slave1.jdbc.url']}" /><property name="username" value="#{jdbc['slave1.jdbc.username']}" /><property name="password" value="#{jdbc['slave1.jdbc.password']}" /><property name="driverClassName" value="#{jdbc['slave1.jdbc.driver']}" /></bean><bean id="slave2DataSource" parent="parentDataSource"><property name="url" value="#{jdbc['slave2.jdbc.url']}" /><property name="username" value="#{jdbc['slave2.jdbc.username']}" /><property name="password" value="#{jdbc['slave2.jdbc.password']}" /><property name="driverClassName" value="#{jdbc['slave2.jdbc.driver']}" /></bean><!--动态数据源--><bean id="dataSource" class="com.ricky.codelab.spring.ds.DynamicRoutingDataSource"><property name="targetDataSources"><map key-type="com.ricky.codelab.spring.ds.RouteStrategy"><entry key="slave1" value-ref="slave1DataSource"/><entry key="slave2" value-ref="slave2DataSource"/></map></property><!-- 默认目标数据源为主库数据源 --><property name="defaultTargetDataSource" ref="masterDataSource"/></bean><!--Spring JdbcTemplate--><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"/></bean></beans>

jdbc.properties

master.jdbc.driver=com.mysql.jdbc.Driver
master.jdbc.url=jdbc:mysql://localhost:3306/process?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
master.jdbc.username=root
master.jdbc.password=rootslave1.jdbc.driver=com.mysql.jdbc.Driver
slave1.jdbc.url=jdbc:mysql://localhost:3308/process?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
slave1.jdbc.username=root
slave1.jdbc.password=rootslave2.jdbc.driver=com.mysql.jdbc.Driver
slave2.jdbc.url=jdbc:mysql://localhost:3309/process?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
slave2.jdbc.username=root
slave2.jdbc.password=root

DynamicRoutingDataSource.java

package com.ricky.codelab.spring.ds;import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;/*** 动态数据源切换** @author Ricky Fung* @create 2016-10-18 22:41*/
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {@Overrideprotected Object determineCurrentLookupKey() {return DynamicRoutingContextHolder.getRouteStrategy();}
}

package com.ricky.codelab.spring.ds;import org.springframework.util.Assert;/*** ${DESCRIPTION}** @author Ricky Fung* @create 2016-10-18 22:44*/
public class DynamicRoutingContextHolder {private static final ThreadLocal<RoutingStrategy> contextHolder =new ThreadLocal<>();public static void setRouteStrategy(RoutingStrategy customerType) {Assert.notNull(customerType, "customerType cannot be null");contextHolder.set(customerType);}public static RoutingStrategy getRouteStrategy() {return (RoutingStrategy) contextHolder.get();}public static void clearRouteStrategy() {contextHolder.remove();}
}

package com.ricky.codelab.spring.ds;/*** 路由策略** @author Ricky Fung* @create 2016-10-18 22:47*/
public enum RoutingStrategy {Master(true, "master"), Slave(false, "slave");private boolean write;private String key;RoutingStrategy(boolean write, String key) {this.write = write;this.key = key;}public boolean isWrite() {return write;}public String getKey() {return key;}}

测试用例

package com.ricky.codelab.spring.dao;import com.ricky.codelab.spring.domain.Order;
import com.ricky.codelab.spring.ds.DynamicRoutingContextHolder;
import com.ricky.codelab.spring.ds.RoutingStrategy;
import com.ricky.codelab.spring.jdbc.dao.IOrderDao;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.List;/*** ${DESCRIPTION}** @author Ricky Fung* @create 2016-10-23 15:25*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:applicationContext.xml"})
public class OrderDaoTest {@Autowiredprivate IOrderDao orderDao;@Testpublic void testDynamic() {//访问主库DynamicRoutingContextHolder.setRouteStrategy(RoutingStrategy.Master);long id = orderDao.insert(new Order());//访问从库DynamicRoutingContextHolder.setRouteStrategy(RoutingStrategy.Slave);List<Order> list = orderDao.queryOrders();}
}

参考：
https://spring.io/blog/2007/01/23/dynamic-datasource-routing/