spring+mybatis多数据源（数据库主从）实例

applicationContext.xml

 

[html] view plain copy

1. <?xml version="1.0" encoding="UTF-8"?>  
2. <beans xmlns="http://www.springframework.org/schema/beans"  
3.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"  
4.     xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:aop="http://www.springframework.org/schema/aop"  
5.     xmlns:tx="http://www.springframework.org/schema/tx"  
6.     xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd  
7.         http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd  
8.         http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd  
9.         http://www.springframework.org/schema/aop      
10.         http://www.springframework.org/schema/aop/spring-aop-3.0.xsd    
11.         http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd  
12.         ">  
13.   
14.     <description>Spring公共配置 </description>  
15.   
16.     <!-- 使用annotation 自动注册bean, 并保证@Required、@Autowired的属性被注入 -->  
17.     <context:component-scan base-package="com.test">  
18.     </context:component-scan>  
19.   
20.     <aop:aspectj-autoproxy proxy-target-class="true" />  
21.     <bean  
22.         class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">  
23.         <property name="locations">  
24.             <value>classpath:app.properties</value>  
25.         </property>  
26.     </bean>  
27.     <bean id="transactionManager"  
28.         class="org.springframework.jdbc.datasource.DataSourceTransactionManager">  
29.         <property name="dataSource" ref="dynamicDataSource" />  
30.     </bean>  
31.     <!-- 使用annotation定义事务 -->  
32.     <tx:annotation-driven transaction-manager="transactionManager"  
33.         proxy-target-class="true" />  
34.   
35.     <!-- MyBatis配置 自动扫描entity目录, 省掉Configuration.xml里的手工配置 -->  
36.     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
37.         <property name="typeAliasesPackage" value="com.test.model" />  
38.         <property name="mapperLocations" value="classpath*:/mybatis/*Mapper.xml" />  
39.         <property name="dataSource">  
40.             <ref bean="dynamicDataSource" />  
41.         </property>  
42.     </bean>  
43.   
44.   
45.   
46.   
47.     <!-- 扫描basePackage下所有以@MyBatisRepository标识的 接口 -->  
48.     <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">  
49.         <property name="basePackage" value="com.test.dao.mybatis" />  
50.         <!-- <property name="annotationClass" value="jarlun.framework.core.base.mybatis.MyBatisRepository"   
51.             /> -->  
52.     </bean>  
53.   
54.     <bean id="dynamicDataSource" class="com.test.holder.DynamicDataSource">  
55.         <!-- 通过key-value的形式来关联数据源 -->  
56.         <property name="targetDataSources">  
57.             <map key-type ="java.lang.String">  
58.                 <entry value-ref="master" key="master"></entry>  
59.                 <entry value-ref="slave" key="slave"></entry>  
60.             </map>  
61.         </property>  
62.         <property name="defaultTargetDataSource" ref="master" />  
63.     </bean>  
64.   
65.   
66.   
67.   
68.   
69.   
70.     <!-- JSR303 Validator定义 -->  
71.     <!-- 以下 validator ConversionService 在使用 mvc:annotation-driven 会 自动注册 -->  
72.   
73.   
74.     <!-- 建立视图内拦截器来解决JPA中访问延迟加载属性时产生的无会话异常 LazyInitializationException: could   
75.         not initialize proxy no session -->  
76.     <!-- 此拦截器会注入到servlet配置中的DefaultAnnotationHandlerMapping中 -->  
77.   
78.     <bean id="master" class="org.apache.commons.dbcp.BasicDataSource"  
79.         destroy-method="close">  
80.         <!-- Connection Info -->  
81.         <property name="driverClassName" value="${master.jdbc.driver}" />  
82.         <property name="url" value="${master.jdbc.url}" />  
83.         <property name="username" value="${master.jdbc.username}" />  
84.         <property name="password" value="${master.jdbc.password}" />  
85.   
86.         <!-- Connection Pooling Info -->  
87.         <property name="maxActive" value="${master.jdbc.pool.maxActive}" />  
88.         <property name="maxIdle" value="${master.jdbc.pool.maxIdle}" />  
89.         <property name="initialSize" value="${master.jdbc.pool.initialSize}" />  
90.         <property name="defaultAutoCommit" value="false" />  
91.   
92.         <!-- 连接Idle一个小时后超时 -->  
93.         <property name="timeBetweenEvictionRunsMillis" value="3600000" />  
94.         <property name="minEvictableIdleTimeMillis" value="3600000" />  
95.         <property name="testWhileIdle" value="true" />  
96.         <property name="testOnBorrow" value="true" />  
97.         <property name="testOnReturn" value="false" />  
98.         <property name="validationQuery" value="SELECT 1" />  
99.         <property name="logAbandoned" value="false" />  
100.         <property name="removeAbandoned" value="true" />  
101.         <property name="removeAbandonedTimeout" value="60" />  
102.     </bean>  
103.   
104.   
105.     <bean id="slave" class="org.apache.commons.dbcp.BasicDataSource"  
106.         destroy-method="close">  
107.         <!-- Connection Info -->  
108.         <property name="driverClassName" value="${slave.jdbc.driver}" />  
109.         <property name="url" value="${slave.jdbc.url}" />  
110.         <property name="username" value="${slave.jdbc.username}" />  
111.         <property name="password" value="${slave.jdbc.password}" />  
112.   
113.         <!-- Connection Pooling Info -->  
114.         <property name="maxActive" value="${slave.jdbc.pool.maxActive}" />  
115.         <property name="maxIdle" value="${slave.jdbc.pool.maxIdle}" />  
116.         <property name="initialSize" value="${slave.jdbc.pool.initialSize}" />  
117.         <property name="defaultAutoCommit" value="false" />  
118.   
119.         <!-- 连接Idle一个小时后超时 -->  
120.         <property name="timeBetweenEvictionRunsMillis" value="3600000" />  
121.         <property name="minEvictableIdleTimeMillis" value="3600000" />  
122.         <property name="testWhileIdle" value="true" />  
123.         <property name="testOnBorrow" value="true" />  
124.         <property name="testOnReturn" value="false" />  
125.         <property name="validationQuery" value="SELECT 1" />  
126.         <property name="logAbandoned" value="false" />  
127.         <property name="removeAbandoned" value="true" />  
128.         <property name="removeAbandonedTimeout" value="60" />  
129.     </bean>  
130.   
131.   
132.   
133.   
134.   
135.   
136. </beans>  

app.properties

 

 

[html] view plain copy

1. master.jdbc.driver=com.mysql.jdbc.Driver  
2. master.jdbc.url=jdbc:mysql://120.25.155.55:3306/test  
3. master.jdbc.username=test  
4. master.jdbc.password=!QAZxsw2  
5. master.jdbc.pool.maxActive=60  
6. master.jdbc.pool.maxIdle=20  
7. master.jdbc.pool.initialSize=10  
8.   
9. slave.jdbc.driver=com.mysql.jdbc.Driver  
10. slave.jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true  
11. slave.jdbc.username=root  
12. slave.jdbc.password=  
13. slave.jdbc.pool.maxActive=60  
14. slave.jdbc.pool.maxIdle=20  
15. slave.jdbc.pool.initialSize=10  

DynamicDataSource.java

 

 

 

 

[java] view plain copy

1. package com.test.holder;  
2.   
3. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;  
4.   
5. public class DynamicDataSource extends AbstractRoutingDataSource{    
6.     
7.     @Override    
8.     protected Object determineCurrentLookupKey() {    
9.         return DBContextHolder.getDBType();    
10.     }    
11. }    

DBContextHolder.java

 

 

 

 

[java] view plain copy

1. package com.test.holder;  
2.   
3. public class DBContextHolder {  
4.   
5.         
6.     private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();    
7.         
8.     public static void setDBType(String dbType) {    
9.         contextHolder.set(dbType);    
10.     }    
11.         
12.     public static String getDBType() {    
13.         return contextHolder.get();    
14.     }    
15.         
16.     public static void clearDBType() {    
17.         contextHolder.remove();    
18. }  
19. }  

DataSource.java

 

 

 

 

[java] view plain copy

1. package com.test.holder;  
2.   
3. import java.lang.annotation.Documented;  
4. import java.lang.annotation.ElementType;  
5. import java.lang.annotation.Retention;  
6. import java.lang.annotation.RetentionPolicy;  
7. import java.lang.annotation.Target;  
8.   
9.   
10. @Retention(RetentionPolicy.RUNTIME)  
11. @Target(ElementType.METHOD)  
12. @Documented  
13. public @interface DataSource {  
14.       
15.   
16.     Source value() default Source.master;  
17.   
18. }  

Source.java

 

 

[java] view plain copy

1. package com.test.holder;  
2.   
3. public enum Source {  
4.   
5.     /* 主库 */master, /* 从库 */slave  
6.   
7. }  

UserMapper.java

 

 

[java] view plain copy

1. package com.test.dao.mybatis;  
2.   
3. import org.apache.ibatis.annotations.Param;  
4.   
5. import com.test.model.UserModel;  
6.   
7. public interface UserMapper {  
8.       
9.     UserModel findByUserId(@Param("userId")Long userId);  
10.   
11. }  

UserModel.java

 

 

[java] view plain copy

1. package com.test.model;  
2.   
3. public class UserModel {  
4.       
5.     private long id;  
6.       
7.     private String nickname;  
8.   
9.     public long getId() {  
10.         return id;  
11.     }  
12.   
13.     public void setId(long id) {  
14.         this.id = id;  
15.     }  
16.   
17.     public String getNickname() {  
18.         return nickname;  
19.     }  
20.   
21.     public void setNickname(String nickname) {  
22.         this.nickname = nickname;  
23.     }  
24.       
25.       
26.       
27.   
28. }  

UserMapper.xml

 

 

[html] view plain copy

1. <?xml version="1.0" encoding="UTF-8"?>  
2.     <!DOCTYPE mapper    
3.       PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"    
4.       "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  
5. <mapper namespace="com.test.dao.mybatis.UserMapper">  
6.       
7.     <resultMap  id="userModel"  type="com.test.model.UserModel" >  
8.             <result property="id" column="id" />  
9.             <result property="nickname" column="nickname" />  
10.         </resultMap>  
11.           
12.         <select id="findByUserId"  resultMap="userModel" >   
13.         select id , nickname from db_user where id=#{userId}  
14.         </select>  
15. </mapper>    

 

UserService.java

 

 

 

[java] view plain copy

1. package com.test.service;  
2.   
3. import com.test.model.UserModel;  
4.   
5. public interface UserService {  
6.       
7.   
8.      UserModel findByMasterUserId(long userId);  
9.       
10.       
11.      UserModel findBySlaveUserId(long userId);  
12.   
13. }  

UserServiceImpl.java

 

 

[java] view plain copy

1. package com.test.service.impl;  
2.   
3. import org.springframework.beans.factory.annotation.Autowired;  
4. import org.springframework.stereotype.Service;  
5. import org.springframework.transaction.annotation.Transactional;  
6.   
7. import com.test.dao.mybatis.UserMapper;  
8. import com.test.holder.DataSource;  
9. import com.test.holder.Source;  
10. import com.test.model.UserModel;  
11. import com.test.service.UserService;  
12.   
13. @Service("userServiceImpl")  
14. public class UserServiceImpl implements UserService{  
15.       
16.     @Autowired  
17.     private UserMapper userMapper;  
18.     @Transactional(rollbackFor={Exception.class})  
19.     @DataSource(value=Source.master)  
20.     public UserModel findByMasterUserId(long userId){  
21.         return this.userMapper.findByUserId(userId);  
22.     }  
23.       
24.       
25.     @Transactional(rollbackFor={Exception.class})  
26.     @DataSource(value=Source.slave)  
27.     public UserModel findBySlaveUserId(long userId){  
28.         return this.userMapper.findByUserId(userId);  
29.     }  
30.   
31. }  

aop拦截

 

DataSourceContextAop.Java

 

[java] view plain copy

1. package com.test.aop;  
2.   
3. import java.lang.reflect.Method;  
4.   
5. import org.aspectj.lang.JoinPoint;  
6. import org.aspectj.lang.ProceedingJoinPoint;  
7. import org.aspectj.lang.annotation.Around;  
8. import org.aspectj.lang.annotation.Aspect;  
9. import org.aspectj.lang.reflect.MethodSignature;  
10. import org.springframework.core.annotation.Order;  
11. import org.springframework.stereotype.Component;  
12.   
13. import com.test.holder.DBContextHolder;  
14. import com.test.holder.DataSource;  
15.   
16. @Aspect  
17. @Order(value=1)  
18. @Component  
19. public class DataSourceContextAop {  
20.   
21.     @Around("execution(@com.test.holder.DataSource * com.test.service.impl.*.*(..))")  
22.     public Object setDynamicDataSource(ProceedingJoinPoint pjp) throws Throwable {  
23.         Method method = this.getMethod(pjp);  
24.         DataSource dataSource = method.getAnnotation(DataSource.class);  
25.         if (dataSource != null) {  
26.             DBContextHolder.setDBType(dataSource.value().name());  
27.         }  
28.             return pjp.proceed();  
29.     }  
30.   
31.       
32.     public Method getMethod(JoinPoint pjp) {  
33.         Method method = null;  
34.         MethodSignature signature = (MethodSignature) pjp.getSignature();  
35.         method = signature.getMethod();  
36.         return method;  
37.     }  
38.   
39. }  

最后测试

 

Test.java

 

[java] view plain copy

1. package test;  
2.   
3. import org.springframework.context.ApplicationContext;  
4. import org.springframework.context.support.ClassPathXmlApplicationContext;  
5.   
6. import com.test.service.UserService;  
7.   
8. public class Test {  
9.   
10.       
11.     public static void main(String[] args) {  
12.          ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");    
13.          UserService userService = (UserService) ac.getBean("userServiceImpl");    
14.          System.out.println(userService.findByMasterUserId(1000001).getNickname());    
15.          System.out.println(userService.findBySlaveUserId(1000001).getNickname());    
16.   
17.     }  
18.       
19.       
20. }  

谢谢

而外加个hibernate和mybatis混用的配置

 

[html] view plain copy

1. <?xml version="1.0" encoding="UTF-8"?>  
2. <beans xmlns="http://www.springframework.org/schema/beans"  
3.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"  
4.     xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:aop="http://www.springframework.org/schema/aop"  
5.     xmlns:tx="http://www.springframework.org/schema/tx"  
6.     xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd  
7.         http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd  
8.         http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd  
9.         http://www.springframework.org/schema/aop      
10.         http://www.springframework.org/schema/aop/spring-aop-3.0.xsd    
11.         http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd  
12.         ">  
13.   
14.     <description>Spring公共配置 </description>  
15.   
16.     <!-- 使用annotation 自动注册bean, 并保证@Required、@Autowired的属性被注入 -->  
17.     <context:component-scan base-package="com.test">  
18.     </context:component-scan>  
19.   
20.     <aop:aspectj-autoproxy proxy-target-class="true" />  
21.     <bean  
22.         class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">  
23.         <property name="locations">  
24.             <value>classpath:app.properties</value>  
25.         </property>  
26.     </bean>  
27.     <bean id="transactionManager"  
28.         class="org.springframework.jdbc.datasource.DataSourceTransactionManager">  
29.         <property name="dataSource" ref="dynamicDataSource" />  
30.     </bean>  
31.       
32.         <!-- Jpa Entity Manager 配置 -->  
33.     <bean id="entityManagerFactory"  
34.         class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">  
35.         <property name="dataSource" ref="dynamicDataSource" />  
36.         <property name="jpaVendorAdapter" ref="hibernateJpaVendorAdapter" />  
37.         <property name="packagesToScan" value="com.test.entity" />  
38.         <property name="jpaProperties">  
39.             <props>  
40.                 <!-- <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory  
41.                 </prop>  
42.                 <prop key="net.sf.ehcache.configurationResourceName">ehcache/ehcache-hibernate.xml</prop> -->  
43.                 <!-- 命名规则 My_NAME->MyName -->  
44.                 <prop key="hibernate.ejb.naming_strategy">org.hibernate.cfg.ImprovedNamingStrategy</prop>  
45.                 <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>  
46.             </props>  
47.         </property>  
48.     </bean>  
49.     <bean id="hibernateJpaVendorAdapter"  
50.         class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">  
51.         <property name="databasePlatform">  
52.             <bean factory-method="getDialect"  
53.                 class="org.springside.modules.persistence.Hibernates">  
54.                 <constructor-arg ref="dynamicDataSource" />  
55.             </bean>  
56.         </property>  
57.     </bean>  
58.     <!-- 使用annotation定义事务 -->  
59.     <tx:annotation-driven transaction-manager="transactionManager"  
60.         proxy-target-class="true" />  
61.   
62.     <!-- MyBatis配置 自动扫描entity目录, 省掉Configuration.xml里的手工配置 -->  
63.     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
64.         <property name="typeAliasesPackage" value="com.test.model" />  
65.         <property name="mapperLocations" value="classpath*:/mybatis/*Mapper.xml" />  
66.         <property name="dataSource">  
67.             <ref bean="dynamicDataSource" />  
68.         </property>  
69.     </bean>  
70.   
71.   
72.   
73.   
74.     <!-- 扫描basePackage下所有以@MyBatisRepository标识的 接口 -->  
75.     <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">  
76.         <property name="basePackage" value="com.test.dao.mybatis" />  
77.         <!-- <property name="annotationClass" value="jarlun.framework.core.base.mybatis.MyBatisRepository"   
78.             /> -->  
79.     </bean>  
80.   
81.     <bean id="dynamicDataSource" class="com.test.holder.DynamicDataSource">  
82.         <!-- 通过key-value的形式来关联数据源 -->  
83.         <property name="targetDataSources">  
84.             <map key-type ="java.lang.String">  
85.                 <entry value-ref="master" key="master"></entry>  
86.                 <entry value-ref="slave" key="slave"></entry>  
87.             </map>  
88.         </property>  
89.         <property name="defaultTargetDataSource" ref="master" />  
90.     </bean>  
91.   
92.   
93.   
94.   
95.   
96.   
97.     <!-- JSR303 Validator定义 -->  
98.     <!-- 以下 validator ConversionService 在使用 mvc:annotation-driven 会 自动注册 -->  
99.   
100.   
101.     <!-- 建立视图内拦截器来解决JPA中访问延迟加载属性时产生的无会话异常 LazyInitializationException: could   
102.         not initialize proxy no session -->  
103.     <!-- 此拦截器会注入到servlet配置中的DefaultAnnotationHandlerMapping中 -->  
104.   
105.     <bean id="master" class="org.apache.commons.dbcp.BasicDataSource"  
106.         destroy-method="close">  
107.         <!-- Connection Info -->  
108.         <property name="driverClassName" value="${master.jdbc.driver}" />  
109.         <property name="url" value="${master.jdbc.url}" />  
110.         <property name="username" value="${master.jdbc.username}" />  
111.         <property name="password" value="${master.jdbc.password}" />  
112.   
113.         <!-- Connection Pooling Info -->  
114.         <property name="maxActive" value="${master.jdbc.pool.maxActive}" />  
115.         <property name="maxIdle" value="${master.jdbc.pool.maxIdle}" />  
116.         <property name="initialSize" value="${master.jdbc.pool.initialSize}" />  
117.         <property name="defaultAutoCommit" value="false" />  
118.   
119.         <!-- 连接Idle一个小时后超时 -->  
120.         <property name="timeBetweenEvictionRunsMillis" value="3600000" />  
121.         <property name="minEvictableIdleTimeMillis" value="3600000" />  
122.         <property name="testWhileIdle" value="true" />  
123.         <property name="testOnBorrow" value="true" />  
124.         <property name="testOnReturn" value="false" />  
125.         <property name="validationQuery" value="SELECT 1" />  
126.         <property name="logAbandoned" value="false" />  
127.         <property name="removeAbandoned" value="true" />  
128.         <property name="removeAbandonedTimeout" value="60" />  
129.     </bean>  
130.   
131.   
132.     <bean id="slave" class="org.apache.commons.dbcp.BasicDataSource"  
133.         destroy-method="close">  
134.         <!-- Connection Info -->  
135.         <property name="driverClassName" value="${slave.jdbc.driver}" />  
136.         <property name="url" value="${slave.jdbc.url}" />  
137.         <property name="username" value="${slave.jdbc.username}" />  
138.         <property name="password" value="${slave.jdbc.password}" />  
139.   
140.         <!-- Connection Pooling Info -->  
141.         <property name="maxActive" value="${slave.jdbc.pool.maxActive}" />  
142.         <property name="maxIdle" value="${slave.jdbc.pool.maxIdle}" />  
143.         <property name="initialSize" value="${slave.jdbc.pool.initialSize}" />  
144.         <property name="defaultAutoCommit" value="false" />  
145.   
146.         <!-- 连接Idle一个小时后超时 -->  
147.         <property name="timeBetweenEvictionRunsMillis" value="3600000" />  
148.         <property name="minEvictableIdleTimeMillis" value="3600000" />  
149.         <property name="testWhileIdle" value="true" />  
150.         <property name="testOnBorrow" value="true" />  
151.         <property name="testOnReturn" value="false" />  
152.         <property name="validationQuery" value="SELECT 1" />  
153.         <property name="logAbandoned" value="false" />  
154.         <property name="removeAbandoned" value="true" />  
155.         <property name="removeAbandonedTimeout" value="60" />  
156.     </bean>  
157.   
158.   
159.   
160.   
161.   
162.   
163. </beans>