首页 > 代码库 > java实现mysql数据库读写分离之定义多数据源方式
java实现mysql数据库读写分离之定义多数据源方式
该示例是基于spring提供的AbstractRoutingDataSource，实现了一个动态数据源的功能，在spring配置中定义多个数据库分为主、从数据库，实现效果为当进行保存和修改记录时则对主表操作，查询则对从表进行操作，从而实现对数据库表的读写分离。这样做有利于提高网站的性能，特别是在数据库这一层。因为在实际的应用中，数据库都是读多写少（读取数据的频率高，更新数据的频率相对较少），而读取数据通常耗时比较长，占用数据库服务器的CPU较多，从而影响用户体验。我们通常的做法就是把查询从主库中抽取出来，采用多个从库，使用负载均衡，减轻每个从库的查询压力。该示例并未对数据库同步进行说明，只对读写操作的分离实现：
在进行操作之前，先简单说一下AbstractRoutingDataSource相关的东西：
1 AbstractRoutingDataSource继承了AbstractDataSource ，而AbstractDataSource 又是DataSource 的子类。DataSource 是javax.sql 的数据源接口，定义如下： 2 3 public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {} 4 5 public interface DataSource extends CommonDataSource,Wrapper { 6 7 /** 8 * <p>Attempts to establish a connection with the data source that 9 * this <code>DataSource</code> object represents. 10 * 11 * @return a connection to the data source 12 * @exception SQLException if a database access error occurs 13 */ 14 Connection getConnection() throws SQLException; 15 16 /** 17 * <p>Attempts to establish a connection with the data source that 18 * this <code>DataSource</code> object represents. 19 * 20 * @param username the database user on whose behalf the connection is 21 * being made 22 * @param password the user‘s password 23 * @return a connection to the data source 24 * @exception SQLException if a database access error occurs 25 * @since 1.4 26 */ 27 Connection getConnection(String username, String password) 28 throws SQLException; 29 30 } 31 32 33 public Connection getConnection() throws SQLException { 34 return determineTargetDataSource().getConnection(); 35 } 36 37 public Connection getConnection(String username, String password) throws SQLException { 38 return determineTargetDataSource().getConnection(username, password); 39 } 40 41 protected DataSource determineTargetDataSource() { 42 Assert.notNull(this.resolvedDataSources, "DataSource router not initialized"); 43 Object lookupKey = determineCurrentLookupKey(); 44 DataSource dataSource = this.resolvedDataSources.get(lookupKey); 45 if (dataSource == null && (this.lenientFallback || lookupKey == null)) { 46 dataSource = this.resolvedDefaultDataSource; 47 } 48 if (dataSource == null) { 49 throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]"); 50 } 51 return dataSource; 52 }
从上面的代码中不难看出，获取数据源首先是通过对determineCurrentLookupKey（）的调用获取resolvedDataSources对应key的值，故执行创建一个动态数据源类继承AbstractRoutingDataSource，复写determineCurrentLookupKey（）去自定义设置和获取resolvedDataSources的key就可以实现了
具体步骤如下：
第一步：
<?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:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context" xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:jpa="http://www.springframework.org/schema/data/jpa" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa.xsd" default-lazy-init="true"> <!-- 引入配置文件 --> <context:component-scan base-package="com.he" /> <bean id="masterdataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="http://www.mamicode.com/com.mysql.jdbc.Driver" /> <property name="url" value="http://www.mamicode.com/jdbc:mysql://localhost:3306/test" /> <property name="username" value="http://www.mamicode.com/root" /> <property name="password" value="http://www.mamicode.com/111111" /> </bean> <bean id="slavedataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="http://www.mamicode.com/com.mysql.jdbc.Driver" /> <property name="url" value="http://www.mamicode.com/jdbc:mysql://localhost:3306/test2" /> <property name="username" value="http://www.mamicode.com/root" /> <property name="password" value="http://www.mamicode.com/111111" /> </bean> <bean id="dataSource" class="com.he.mysql.test.DynamicDataSource"> <property name="targetDataSources"> <map key-type="java.lang.String"> <!-- write --> <entry key="masterdataSource" value-ref="masterdataSource"/> <!-- read --> <entry key="slavedataSource" value-ref="slavedataSource"/> </map> </property> <property name="defaultTargetDataSource" ref="masterdataSource"/> </bean> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="mapperLocations" value="http://www.mamicode.com/classpath:com/he/dao/*.xml"></property> </bean> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="http://www.mamicode.com/com.he.dao" /> <property name="sqlSessionFactoryBeanName" value="http://www.mamicode.com/sqlSessionFactory"></property> </bean> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <!-- 注解式事务管理，需要在Service类上标注@Transactional --> <tx:annotation-driven transaction-manager="transactionManager" /> </beans>
第二步：
1 public class DynamicDataSource extends AbstractRoutingDataSource { 2 3 @Override 4 protected Object determineCurrentLookupKey() { 5 6 return DynamicDataSourceHolder.getDataSouce(); 7 } 8 9 }
第三步：
1 public class DynamicDataSourceHolder { 2 public static final ThreadLocal<String> holder = new ThreadLocal<String>(); 3 4 public static void putDataSource(String name) { 5 holder.set(name); 6 } 7 8 public static String getDataSouce() { 9 return holder.get(); 10 } 11 }
第四步：
1 @Service("userService") 2 @Transactional 3 public class UserServiceImpl implements UserService{ 4 5 @Autowired 6 private UserMapper userDao;public void add(User user) { 7 8 DynamicDataSourceHolder.putDataSource("masterdataSource"); 9 userDao.add(user); 10 } 11 12 public void update(User user) { 13 14 DynamicDataSourceHolder.putDataSource("masterdataSource"); 15 userDao.updates(user); 16 17 18 } 19 20 @Transactional(propagation = Propagation.NOT_SUPPORTED) 21 public List<User> query() { 22 23 DynamicDataSourceHolder.putDataSource("slavedataSource"); 24 List<User> user = userDao.query(); 25 return user; 26 27 } 28 29 30 31 32 }
上述为实现读写分离的关键部分，只是为了简单的做一个示例，完成上面操作以后，可自行的对数据库进行新增和查询操作，查看效果
java实现mysql数据库读写分离之定义多数据源方式
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。