wiki

用户工具

站点工具

您的足迹: • spring_mybatis_多数据源配置_2
分享:技术:数据源:spring_mybatis_多数据源配置_2

目录

Spring+Mybatis 多数据源配置_2

方法描述

采用spring配置文件配置动态数据源，利于强大的AOP切面，根据service实现类的自定义注解或者指定参数判断，在spring事务管理获取动态数据源之前，设置我们希望连接的数据源，从而灵活配置

优缺点

  1. 一套统一配置，通过注解或者入参来灵活判断设置数据源，代码级别判断，配置不需要动，方便可控
  2. 不仅适合两个数据库没有相关性的情况，而且还适合master-slave性的多数据源的配置
  3. vo，dao，mapper，service仅需要一套

碰到的坑

  1. spring-mvc对controller的扫描路径一定要写到web不能把sevice包含进去，或者指定扫描@Controller注解，否则会把service扫进去，那spring事务管理扫面将不起作用
  2. 事务管理注解驱动[transaction]一定要配置在AOP切面[transactionPointcut](事务管理获取动态数据源)之前，否则注解会无效
  3. xml中如果已经配置事务transactionAdvice，service方法上可以不配置注解
  4. 如果在事务中抛出异常Exception，则会回滚，不需要配置rollback-for=“Exception”
  5. AOP切面[dataSourceAspect](动态设置数据源)一定要配置在AOP切面[transactionPointcut](事务管理获取动态数据源)之前，否则先获取完再设置数据源会无效

暂未解决

使用自定义注解打在service的方法上，不知道为啥，切面里获取不到注解。但是，换了一种方法，使用入参送入枚举，切面里获取入参判断枚举值来设置数据源，可以搞定！

主要代码

spring-mvc.xml

spring-mvc.xml
<?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:context="http://www.springframework.org/schema/context"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xmlns:util="http://www.springframework.org/schema/util"	
	xmlns:cache="http://www.springframework.org/schema/cache"
	xsi:schemaLocation="http://www.springframework.org/schema/beans	http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
				http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd
                http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
                http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache-4.0.xsd
                http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd">
 
	<!-- XML转码器 -->
	<bean id="fmXmlEscape" class="freemarker.template.utility.XmlEscape" />
 
	<!-- 配置freeMarker的模板路径 -->
	<bean id="freemarkerConfig" class="org.springframework.web.servlet.view.freemarker.FreeMarkerConfigurer">
		<property name="templateLoaderPath" value="/WEB-INF/ftl/" />
		<property name="freemarkerVariables">
			<map>
				<entry key="xml_escape" value-ref="fmXmlEscape" />
			</map>
		</property>
		<property name="freemarkerSettings">
			<props>
				<!-- <prop key="datetime_format">MM/dd/yyyy</prop> <prop key="number_format">0.######</prop> -->
				<prop key="defaultEncoding">UTF-8</prop>
				<!-- <prop key="template_update_delay">0</prop> -->
				<prop key="template_exception_handler">ignore</prop>
				<prop key="number_format">0.##</prop>
			</props>
		</property>
	</bean>
 
	<!-- 配置freeMarker视图解析器 -->
	<bean id="freeMarkerViewResolver" class="org.springframework.web.servlet.view.freemarker.FreeMarkerViewResolver">
		<property name="viewClass" value="org.springframework.web.servlet.view.freemarker.FreeMarkerView" />
		<property name="contentType" value="text/html;charset=UTF-8" />
		<property name="suffix" value=".ftl" />
		<property name="order" value="0"/>
		<property name="prefix" value="/" />
 
		<property name="requestContextAttribute" value="rc" />
		<property name="exposeRequestAttributes" value="true" />
		<property name="exposeSpringMacroHelpers" value="true" />
	</bean>
 
	<!-- jsp视图解析器 -->  
	<bean id="jstlViewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="viewClass" value="org.springframework.web.servlet.view.JstlView" />
		<property name="prefix" value="/WEB-INF/ftl/" />
		<property name="suffix" value=".jsp" />
		<property name="order" value="2" />
	</bean>
 
	<!-- 扫描控制器类 -->
	<!-- 注意路径一定要写到web，或者指定扫描@Controller注解，否则会把service扫进去，那spring事务管理扫面将不起作用 -->
	<context:component-scan base-package="com.gxx.record.web" />
 
	<!-- 采用注解方式配置MVC -->
	<mvc:annotation-driven content-negotiation-manager="contentNegotiationManager">
	    <mvc:message-converters>
	    	<bean class="org.springframework.http.converter.StringHttpMessageConverter">
                <constructor-arg value="UTF-8" />
            </bean>
	        <bean class="org.springframework.http.converter.json.MappingJackson2HttpMessageConverter">
	            <!-- <property name="objectMapper" ref="jackson2ObjectMapperFactoryBean" />  -->
	            <property name="objectMapper">  
                    <bean class="com.fasterxml.jackson.databind.ObjectMapper">  
                        <property name="dateFormat">  
                            <bean class="java.text.SimpleDateFormat">  
                                <constructor-arg type="java.lang.String" value="yyyy-MM-dd HH:mm:ss" />  
                            </bean>  
                        </property>  
                    </bean>  
                </property>
	            <property name="supportedMediaTypes">  
                    <list>  
	                    <value>text/plain;charset=utf-8</value>
	                    <value>html/text;charset=utf-8</value>
		                <value>text/html;charset=utf-8</value>
		                <value>text/json;charset=utf-8</value>
		                <value>application/json;charset=utf-8</value>
                    </list>  
                 </property> 
	        </bean>     
	    </mvc:message-converters>
	</mvc:annotation-driven>
 
	<!-- Supporty medieType Content Manager Config-->
	<bean id="contentNegotiationManager" class="org.springframework.web.accept.ContentNegotiationManagerFactoryBean">
		<property name="favorPathExtension" value="false" />
	    <property name="favorParameter" value="true" />
		<property name="mediaTypes">
			<value>
				atom=application/atom+xml
				html=text/html
				json=application/json
				xml=application/xml
				*=*/*
			</value>
		</property>
	</bean>
 
</beans>

application-context.xml

application-context.xml
<?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:tx="http://www.springframework.org/schema/tx"
	xmlns:jpa="http://www.springframework.org/schema/data/jpa"
	xmlns:security="http://www.springframework.org/schema/security"
	xsi:schemaLocation="
		http://www.springframework.org/schema/aop 
		http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
		http://www.springframework.org/schema/security 
		http://www.springframework.org/schema/security/spring-security-3.2.xsd
		http://www.springframework.org/schema/beans 
		http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
		http://www.springframework.org/schema/data/jpa 
		http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
		http://www.springframework.org/schema/tx 
		http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
		http://www.springframework.org/schema/context 
		http://www.springframework.org/schema/context/spring-context-4.0.xsd">
 
	<!-- Activates annotation-based bean configuration -->
	<context:annotation-config />
 
	<!-- Scans for application @Components to deploy -->
	<context:component-scan base-package="com.gxx.record" />
 
	<!-- 数据库配置文件位置 -->
	<context:property-placeholder location="classpath:/jdbc.properties,classpath:/redis.properties,classpath:/memcached.properties,classpath:/mongodb.properties,classpath:/activemq.properties" />
 
	<!-- 动态数据源配置 -->
	<bean id="dynamicDataSource" class="com.gxx.record.core.DynamicDataSource">
        <property name="targetDataSources">
            <map key-type="java.lang.String">
            	<!-- 可以配置多个数据源 -->
                <entry value-ref="master_dataSource" key="MASTER"></entry>
                <entry value-ref="slave_dataSource" key="SLAVE"></entry>
            </map>
        </property>
        <property name="defaultTargetDataSource" ref="master_dataSource">
        </property>
    </bean>
 
	<!-- 使用JDBC事务，使用动态数据源 -->
	<bean id="transactionManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dynamicDataSource" />
	</bean>
 
	<!-- AOP配置事物 -->
	<tx:advice id="transactionAdvice" transaction-manager="transactionManager">
		<tx:attributes>
			<tx:method name="get*" read-only="true" propagation="REQUIRED" />
			<tx:method name="delete*"  propagation="REQUIRED" />
			<tx:method name="update*" propagation="REQUIRED" />
			<tx:method name="insert*" propagation="REQUIRED" />
			<tx:method name="*" propagation="REQUIRED" />
		</tx:attributes>
	</tx:advice>
 
	<!-- 使用annotation注解方式配置事务 -->
	<!-- 注意该事务管理注解驱动[transaction]一定要配置在AOP切面[transactionPointcut](事务管理获取动态数据源)之前，否则注解会无效 -->
	<tx:annotation-driven transaction-manager="transactionManager" />
 
	<!-- 配置AOP切面 -->
	<!-- 注意该AOP切面[dataSourceAspect](动态设置数据源)一定要配置在AOP切面[transactionPointcut](事务管理获取动态数据源)之前，否则先获取完再设置数据源会无效 -->
	<aop:config>
        <aop:aspect id="dataSourceAspect" ref="dataSourceInterceptor">
            <aop:pointcut id="determineDataSource" expression="execution(* com.gxx.record.service.impl.*.*(..))" />
            <aop:before pointcut-ref="determineDataSource" method="setDataSource" />
        </aop:aspect>
    </aop:config>
 
	<!-- 配置AOP切面 -->
	<aop:config>
        <aop:pointcut id="transactionPointcut" expression="execution(* com.gxx.record.service.impl.*.*(..))"/>
        <aop:advisor pointcut-ref="transactionPointcut" advice-ref="transactionAdvice"/>
    </aop:config>
 
	<!-- sql会话工厂，使用动态数据源 -->
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dynamicDataSource" />
		<property name="configLocation" value="classpath:mybatis.xml"></property>
		<property name="mapperLocations" value="classpath:com/gxx/record/base/mapping/*.xml"></property>
	</bean>
 
	<!-- 配置SQLSession模板 -->
	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg index="0" ref="sqlSessionFactory" />
	</bean>
 
	<!--扫描basePackage下所有以@Repository注解的接口  -->
	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
        <property name="basePackage" value="com.gxx.record.base"/>
        <property name="annotationClass" value="org.springframework.stereotype.Repository"/>
    </bean>
</beans>

Datasource.java

Datasource.java
package com.gxx.record.core;
 
/**
 * <dl>
 *    <dt><b>Title:</b></dt>
 *    <dd>
 *    	none
 *    </dd>
 *    <dt><b>Description:</b></dt>
 *    <dd>
 *    	<p>none
 *    </dd>
 * </dl>
 *
 * @author Gxx
 * @version 1.0, 2016年1月12日
 * @since record
 *
 */
public enum Datasource {
	//主库
	MASTER,
	//备库
	SLAVE
}

DatabaseContextHolder.java

DatabaseContextHolder.java
package com.gxx.record.core;
 
/**
 * <dl>
 *    <dt><b>Title:</b></dt>
 *    <dd>
 *    	none
 *    </dd>
 *    <dt><b>Description:使用ThreadLocal设置和获取当前线程的数据源KEY</b></dt>
 *    <dd>
 *    	<p>none
 *    </dd>
 * </dl>
 *
 * @author Gxx
 * @version 1.0, 2016年1月12日
 * @since record
 *
 */
public class DatabaseContextHolder {
	/**
	 * ThreadLocal
	 */
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
 
    /**
     * 设置数据源KEY
     * @param customerType
     */
    public static void setCustomerType(String customerType) {
        contextHolder.set(customerType);
    }
 
    /**
     * 获取数据源KEY
     * @return
     */
    public static String getCustomerType() {
        return contextHolder.get();  
    }
 
    /**
     * 清空数据源KEY
     */
    public static void clearCustomerType() {
        contextHolder.remove();
    }
}

DynamicDataSource.java

DynamicDataSource.java
package com.gxx.record.core;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;  
 
/**
 * <dl>
 *    <dt><b>Title:</b></dt>
 *    <dd>
 *    	none
 *    </dd>
 *    <dt><b>Description:动态获取数据源KEY，继承父类AbstractRoutingDataSource</b></dt>
 *    <dd>
 *    	<p>none
 *    </dd>
 * </dl>
 *
 * @author Gxx
 * @version 1.0, 2016年1月12日
 * @since record
 *
 */
public class DynamicDataSource extends AbstractRoutingDataSource{  
 
	/**
	 * 获取数据源值KEY
	 */
    @Override  
    protected Object determineCurrentLookupKey() { 
        return DatabaseContextHolder.getCustomerType();   
    }  
 
}

DataSourceInterceptor.java

DataSourceInterceptor.java
package com.gxx.record.core;
import org.aspectj.lang.JoinPoint;
import org.springframework.stereotype.Component;
 
/**
 * <dl>
 *    <dt><b>Title:</b></dt>
 *    <dd>
 *    	none
 *    </dd>
 *    <dt><b>Description:数据源切面拦截器</b></dt>
 *    <dd>
 *    	<p>none
 *    </dd>
 * </dl>
 *
 * @author Gxx
 * @version 1.0, 2016年1月12日
 * @since record
 *
 */
@Component  
public class DataSourceInterceptor {
	/**
	 * 设置数据源KEy
	 * @param jp
	 */
    public void setDataSource(JoinPoint jp) {
    	/**
    	 * 清空数据源KEY
    	 */
    	DatabaseContextHolder.clearCustomerType();
    	/**
    	 * 这里不知道为啥，使用注册方式注解方法或者入参，这里取不到注解，才改成入参送指定枚举的方式
    	 */
    	for(Object param : jp.getArgs()){
    		if(param instanceof Datasource){
    			Datasource datasource = (Datasource)param;
    			if(Datasource.MASTER.name().equals(datasource.name())){
					DatabaseContextHolder.setCustomerType(Datasource.MASTER.name());
				} else if(Datasource.SLAVE.name().equals(datasource.name())){
					DatabaseContextHolder.setCustomerType(Datasource.SLAVE.name());
				}
    		}
    	}
    }
}

UserService.java

UserService.java
package com.gxx.record.service;
 
import com.gxx.record.base.vo.User;
import com.gxx.record.core.Datasource;
 
/**
 * <dl>
 *    <dt><b>Title:</b></dt>
 *    <dd>
 *    	用户服务接口
 *    </dd>
 *    <dt><b>Description:</b></dt>
 *    <dd>
 *    	<p>none
 *    </dd>
 * </dl>
 *
 * @author Administrator
 * @version 1.0, 2015年6月18日
 * @since record
 *
 */
public interface UserService {
	/**
	 * 新增用户
	 * @param datasource 使用枚举参数，动态设置数据源
	 * @param user
	 */
	public void doSaveUser(Datasource datasource, User user);
 
	/**
	 * 根据姓名查用户
	 * @param datasource 使用枚举参数，动态设置数据源
	 * @param name
	 * @return
	 */
	public User getUserByName(Datasource datasource, String name);
}

UserServiceImpl.java

UserServiceImpl.java
package com.gxx.record.service.impl;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
import com.gxx.record.base.dao.UserMapper;
import com.gxx.record.base.vo.User;
import com.gxx.record.core.Datasource;
import com.gxx.record.service.UserService;
 
/**
 * <dl>
 *    <dt><b>Title:</b></dt>
 *    <dd>
 *    	用户服务实现类
 *    </dd>
 *    <dt><b>Description:</b></dt>
 *    <dd>
 *    	<p>none
 *    </dd>
 * </dl>
 *
 * @author Administrator
 * @version 1.0, 2015年6月18日
 * @since record
 *
 */
@Service("userService")
public class UserServiceImpl implements UserService {
 
	@Autowired
	private UserMapper userDao;
 
	/**
	 * 新增用户
	 * @param datasource 使用枚举参数，动态设置数据源
	 * @param user
	 */
	//xml中已经配置transactionAdvice，这里可以不配置注解
	//@Transactional(readOnly=false, propagation=Propagation.REQUIRED)
	public void doSaveUser(Datasource datasource, User user){
		userDao.insert(user);
		//如果在事务中抛出异常，则会回滚
		//throw new RuntimeException("异常发生");
	}
 
	/**
	 * 根据姓名查用户
	 * @param datasource 使用枚举参数，动态设置数据源
	 * @param name
	 * @return
	 */
	public User getUserByName(Datasource datasource, String name){
		return userDao.getUserByName(name);
	}
}

UserController.java

UserController.java
package com.gxx.record.web.user;
 
import javax.servlet.http.HttpServletRequest;
 
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
 
import com.gxx.record.base.vo.User;
import com.gxx.record.core.Datasource;
import com.gxx.record.dto.UserDto;
import com.gxx.record.service.UserService;
 
/**
 * <dl>
 *    <dt><b>Title:</b></dt>
 *    <dd>
 *    	none
 *    </dd>
 *    <dt><b>Description:用户控制器</b></dt>
 *    <dd>
 *    	<p>none
 *    </dd>
 * </dl>
 *
 * @author Administrator
 * @version 1.0, 2016年1月12日
 * @since record
 *
 */
@Controller
@RequestMapping("/user/")
public class UserController {
	/**
	 * 日志处理器
	 */
	private final Logger logger = Logger.getLogger(UserController.class);
 
	@Autowired
	private UserService userService;
 
	@RequestMapping(value = "/preRegistFtl", method = RequestMethod.GET)
	public String preRegistFtl() {
		return "user/preRegistFtl";
	}
 
	@RequestMapping(value = "/preRegistJsp", method = RequestMethod.GET)
	public String preRegistJsp() {
		return "user/preRegistJsp";
	}
 
	/**
	 * 注册
	 * @param request
	 * @param userDto
	 * @return
	 */
	@RequestMapping(value = "/registJsp",produces="application/json")
	public @ResponseBody UserDto registJsp(HttpServletRequest request, UserDto userDto) {
		logger.info("用户注册：主从=[" + userDto.getMasterOrSlave() + "]，姓名=[" + userDto.getName() + "]，密码=[" + userDto.getPassword() + "]");
		/**
		 * 1.判用户名是否存在
		 */
		User user = null;
		if(StringUtils.equals(userDto.getMasterOrSlave(), UserDto.MASTER)){
			user = userService.getUserByName(Datasource.MASTER, userDto.getName());
		} else if(StringUtils.equals(userDto.getMasterOrSlave(), UserDto.SLAVE)){
			user = userService.getUserByName(Datasource.SLAVE, userDto.getName());
		} else {
			userDto.setSuccess(Boolean.FALSE.booleanValue());
			userDto.setMessage("主还是从值[" + userDto.getMasterOrSlave() + "]有误!");
			return userDto;
		}
		if(user != null){
			userDto.setSuccess(Boolean.FALSE.booleanValue());
			userDto.setMessage("用户名[" + userDto.getName() + "]已存在!");
			return userDto;
		}
		/**
		 * 2.创建用户对象 并 新增用户
		 */
		user = new User();
		user.setName(userDto.getName());
		user.setPassword(userDto.getPassword());
		user.setCreateDate("20150618");
		user.setCreateTime("000000");
		if(StringUtils.equals(userDto.getMasterOrSlave(), UserDto.MASTER)){
			userService.doSaveUser(Datasource.MASTER, user);
		} else if(StringUtils.equals(userDto.getMasterOrSlave(), UserDto.SLAVE)){
			userService.doSaveUser(Datasource.SLAVE, user);
		}
		/**
		 * 3.返回结果
		 */
		userDto.setSuccess(Boolean.TRUE.booleanValue());
		userDto.setMessage("注册成功!");
		return userDto;
	}
 
	/**
	 * 注册
	 * @param request
	 * @param userDto
	 * @return
	 */
	@RequestMapping(value = "/registFtl")
	public String registFtl(HttpServletRequest request, UserDto userDto) {
		logger.info("用户注册：主从=[" + userDto.getMasterOrSlave() + "]，姓名=[" + userDto.getName() + "]，密码=[" + userDto.getPassword() + "]");
		/**
		 * 1.判用户名是否存在
		 */
		User user = null;
		if(StringUtils.equals(userDto.getMasterOrSlave(), UserDto.MASTER)){
			user = userService.getUserByName(Datasource.MASTER, userDto.getName());
		} else if(StringUtils.equals(userDto.getMasterOrSlave(), UserDto.SLAVE)){
			user = userService.getUserByName(Datasource.SLAVE, userDto.getName());
		} else {
			userDto.setSuccess(Boolean.FALSE.booleanValue());
			userDto.setMessage("主还是从值[" + userDto.getMasterOrSlave() + "]有误!");
			return "user/result";
		}
		if(user != null){
			userDto.setSuccess(Boolean.FALSE.booleanValue());
			userDto.setMessage("用户名[" + userDto.getName() + "]已存在，请更改用户名!");
			return "user/result";
		}
		/**
		 * 2.创建用户对象 并 新增用户
		 */
		user = new User();
		user.setName(userDto.getName());
		user.setPassword(userDto.getPassword());
		user.setCreateDate("20150618");
		user.setCreateTime("000000");
		if(StringUtils.equals(userDto.getMasterOrSlave(), UserDto.MASTER)){
			userService.doSaveUser(Datasource.MASTER, user);
		} else if(StringUtils.equals(userDto.getMasterOrSlave(), UserDto.SLAVE)){
			userService.doSaveUser(Datasource.SLAVE, user);
		}
		/**
		 * 3.返回结果
		 */
		userDto.setSuccess(Boolean.TRUE.booleanValue());
		userDto.setMessage("注册成功!");
		return "user/result";
	}
}

附上代码

record_dynamicdatasource.zip

分享/技术/数据源/spring_mybatis_多数据源配置_2.txt · 最后更改: 2016/01/12 14:26 由 gxx

页面工具

除额外注明的地方外，本维基上的内容按下列许可协议发布： CC Attribution-Share Alike 3.0 Unported
CC Attribution-Share Alike 3.0 Unported Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki