业务场景:我们开发一个webService服务,这个服务,要对很多的数据库访问
对外提供数据接口~(一个项目连接多个数据库)
解决思路:一个项目对应多个数据库,就应该是一个项目有多个数据源,多个sqlSessionFactory,
多个事物,然后调用不同的controle或action访问
不同的持久层达到访问不同数据库来解决问题!
上代码(一):基于配置实现
- <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
- <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
- <property name="url" value="${center.connectionURL}"/>
- <property name="username" value="${userName}"/>
- <property name="password" value="${password}"/>
- </bean>
- <!--
- 或者基于jndi的数据源也OK
- -->
- <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
- <property name="basePackage" value="com.xxx.dao.center"/>
- <property name="sqlSessionFactoryBeanName" value="cneterSqlSessionFactory"/>
- </bean>
- <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean" name="cneterSqlSessionFactory">
- <property name="dataSource" ref="dataSource"></property>
- <property name="mapperLocations" value="classpath*:mapperConfig/center/*.xml"/>
- <property name="configLocation" value="classpath:mybatis-config.xml"/>
- </bean>
- <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
- <property name="dataSource" ref="dataSource"/>
- <span><span class="tag"><</span><span class="tag-name">qualifier</span><span> </span><span class="attribute">value</span><span>=</span><span class="attribute-value">"oracle"</span><span> </span><span class="tag">/></span><span> </span></span>
- </bean>
- <tx:annotation-driven transaction-manager="transactionManager"/>
- <!--center db end-->
- <!--exdb-->
- <bean id="dataSourceEx" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
- <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
- <property name="url" value="${ex.connectionURL}"/>
- <property name="username" value="${userName}"/>
- <property name="password" value="${password}"/>
- </bean>
- <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
- <property name="basePackage" value="com.xxx.dao.ex"/>
- <property name="sqlSessionFactoryBeanName" value="exSqlSessionFactory"/>
- </bean>
- <bean id="sqlSessionFactoryEx" class="org.mybatis.spring.SqlSessionFactoryBean" name="exSqlSessionFactory">
- <property name="dataSource" ref="dataSourceEx"></property>
- <property name="mapperLocations" value="classpath*:mapperConfig/ex/*.xml"/>
- <property name="configLocation" value="classpath:mybatis-config.xml"/>
- </bean>
- <bean id="transactionManagerEx" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
- <property name="dataSource" ref="dataSourceEx"/>
- <span><span class="tag"><</span><span class="tag-name">qualifier</span><span> </span><span class="attribute">value</span><span>=</span><span class="attribute-value">"mysql"</span><span> </span><span class="tag">/></span><span> </span></span>
- </bean>
上代码(二):基于注解(参考,并未亲测)
- import org.apache.ibatis.session.SqlSessionFactory;
- import org.apache.ibatis.type.JdbcType;
- import org.mybatis.spring.SqlSessionFactoryBean;
- import org.mybatis.spring.mapper.MapperScannerConfigurer;
- import org.springframework.context.annotation.Bean;
- import org.springframework.context.annotation.ComponentScan;
- import org.springframework.context.annotation.Configuration;
- import org.springframework.jdbc.datasource.DataSourceTransactionManager;
- import org.springframework.jdbc.datasource.lookup.JndiDataSourceLookup;
- import org.springframework.transaction.PlatformTransactionManager;
- import org.springframework.transaction.annotation.EnableTransactionManagement;
- import javax.sql.DataSource;
- @Configuration
- @ComponentScan(basePackages = "com.mycompany")
- @EnableTransactionManagement(proxyTargetClass = true)
- public class ApplicationConfig2 {
- public static final String DATA_SOURCE_NAME_1 = "jdbc/dataSource1";
- public static final String DATA_SOURCE_NAME_2 = "jdbc/dataSource2";
- public static final String SQL_SESSION_FACTORY_NAME_1 = "sqlSessionFactory1";
- public static final String SQL_SESSION_FACTORY_NAME_2 = "sqlSessionFactory2";
- public static final String MAPPERS_PACKAGE_NAME_1 = "com.mycompany.mappers.dao1";
- public static final String MAPPERS_PACKAGE_NAME_2 = "com.mycompany.mappers.dao2";
- @Bean
- public DataSource dataSource1() {
- JndiDataSourceLookup dsLookup = new JndiDataSourceLookup();
- return dsLookup.getDataSource(DATA_SOURCE_NAME_1);
- }
- @Bean
- public DataSource dataSource2() {
- JndiDataSourceLookup dsLookup = new JndiDataSourceLookup();
- return dsLookup.getDataSource(DATA_SOURCE_NAME_2);
- }
- @Bean
- public PlatformTransactionManager transactionManager() {
- return new DataSourceTransactionManager(dataSource());
- }
- @Bean(name = SQL_SESSION_FACTORY_NAME_1)
- public SqlSessionFactory sqlSessionFactory1(DataSource dataSource1) throws Exception {
- SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
- sqlSessionFactoryBean.setTypeHandlersPackage(DateTimeTypeHandler.class.getPackage().getName());
- sqlSessionFactoryBean.setDataSource(dataSource1);
- SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBean.getObject();
- sqlSessionFactory.getConfiguration().setMapUnderscoreToCamelCase(true);
- sqlSessionFactory.getConfiguration().setJdbcTypeForNull(JdbcType.NULL);
- return sqlSessionFactory;
- }
- @Bean(name = SQL_SESSION_FACTORY_NAME_2)
- public SqlSessionFactory sqlSessionFactory2(DataSource dataSource2) throws Exception {
- SqlSessionFactoryBean diSqlSessionFactoryBean = new SqlSessionFactoryBean();
- diSqlSessionFactoryBean.setTypeHandlersPackage(DateTimeTypeHandler.class.getPackage().getName());
- diSqlSessionFactoryBean.setDataSource(dataSource2);
- SqlSessionFactory sqlSessionFactory = diSqlSessionFactoryBean.getObject();
- sqlSessionFactory.getConfiguration().setMapUnderscoreToCamelCase(true);
- sqlSessionFactory.getConfiguration().setJdbcTypeForNull(JdbcType.NULL);
- return sqlSessionFactory;
- }
- @Bean
- public MapperScannerConfigurer mapperScannerConfigurer1() {
- MapperScannerConfigurer configurer = new MapperScannerConfigurer();
- configurer.setBasePackage(MAPPERS_PACKAGE_NAME_1);
- configurer.setSqlSessionFactoryBeanName(SQL_SESSION_FACTORY_NAME_1);
- return configurer;
- }
- @Bean
- public MapperScannerConfigurer mapperScannerConfigurer2() {
- MapperScannerConfigurer configurer = new MapperScannerConfigurer();
- configurer.setBasePackage(MAPPERS_PACKAGE_NAME_2);
- configurer.setSqlSessionFactoryBeanName(SQL_SESSION_FACTORY_NAME_2);
- return configurer;
- }
- }
上代码(三):基于注解(亲测实现,需要注意MapperScannerConfigurer防止死循环出现放到配置中)
- // datasource1
- @Configuration
- @Profile("default")
- @PropertySource("file:${xxx.config.path}/xxx.properties")
- public class DataSourceConfig {
- @Autowired
- private Environment env;
- @Bean(name = "dataSource1")
- public DataSource dataSource1() {
- final JndiDataSourceLookup dsLookup = new JndiDataSourceLookup();
- dsLookup.setResourceRef(true);
- return dsLookup.getDataSource(env.getRequiredProperty("xxx.datasource.name1"));
- }
- }
- @Configuration
- @EnableTransactionManagement(proxyTargetClass = true)
- public class IocMappingConfig {
- @Autowired
- @Qualifier("dataSource1")
- private DataSource dataSource1;
- @Bean(name="sqlSessionFactoryBean")
- public SqlSessionFactoryBean sqlSessionFactoryBean() throws SQLException, IOException {
- SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
- sqlSessionFactory.setDataSource(dataSource1);
- PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
- sqlSessionFactory.setMapperLocations(resolver.getResources("com/xxx/mapping1/*.xml"));
- return sqlSessionFactory;
- }
- // 下面用配置替换,避免死循环(分割多spring-application-config可解决此问题)
- // @Bean
- // public MapperScannerConfigurer mappperConfigurer(){
- // MapperScannerConfigurer msc = new MapperScannerConfigurer();
- // msc.setBasePackage("com.xxx.mapping1");
- // msc.setSqlSessionFactoryBeanName("sqlSessionFactoryBean");
- // // 对应的接口
- // return msc;
- // }
- @Bean
- @Qualifier("utils") // 用于逻辑层事物的区分(@Transactional(value="utils",propagation=Propagation.REQUIRED,rollbackFor=Exception.class))
- public DataSourceTransactionManager transactionManager() throws SQLException{
- DataSourceTransactionManager txManager = new DataSourceTransactionManager();
- txManager.setDataSource(dataSource1);
- return txManager;
- }
- }
- <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
- <property name="basePackage" value="com.xxx.mapping1" />
- <property name="SqlSessionFactoryBeanName" value="sqlSessionFactoryBean"/>
- </bean>
- // datasource2
- @Configuration
- @Profile("default")
- @PropertySource("file:${xxx.config.path}/xxx.properties")
- public class DataSourceConfig {
- @Autowired
- private Environment env;
- @Bean(name = "dataSource2")
- public DataSource dataSource2() {
- final JndiDataSourceLookup dsLookup = new JndiDataSourceLookup();
- dsLookup.setResourceRef(true);
- return dsLookup.getDataSource(env.getRequiredProperty("xxx.datasource.name2"));
- }
- }
- @Configuration
- @EnableTransactionManagement(proxyTargetClass = true)
- public class IocMappingConfig {
- @Autowired
- @Qualifier("dataSource2")
- private DataSource dataSource2;
- @Bean(name="sqlSessionFactoryBean2")
- public SqlSessionFactoryBean sqlSessionFactoryBean2() throws SQLException, IOException {
- SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
- sqlSessionFactory.setDataSource(dataSource2);
- PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
- sqlSessionFactory.setMapperLocations(resolver.getResources("com/xxx/mapping2/*.xml"));
- return sqlSessionFactory;
- }
- // 下面用配置替换,避免死循环(分割多spring-application-config可解决此问题)
- // @Bean
- // public MapperScannerConfigurer mappperConfigurer2(){
- // MapperScannerConfigurer msc = new MapperScannerConfigurer();
- // msc.setBasePackage("com.xxx.mapping2");
- // msc.setSqlSessionFactoryBeanName("sqlSessionFactoryBean2");
- // // 对应的接口
- // return msc;
- // }
- @Bean
- @Qualifier("utils2") // 用于逻辑层事物的区分(@Transactional(value="utils2",propagation=Propagation.REQUIRED,rollbackFor=Exception.class))
- public DataSourceTransactionManager transactionManager2() throws SQLException{
- DataSourceTransactionManager txManager = new DataSourceTransactionManager();
- txManager.setDataSource(dataSource2);
- return txManager;
- }
- }
- <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
- <property name="basePackage" value="com.xxx.mapping2" />
- <property name="SqlSessionFactoryBeanName" value="sqlSessionFactoryBean2"/>
- </bean>
单元测用H2数据库也是两套,数据源名字要对应,此处就不重复写了
参考地址:http://zhuchengzzcc.iteye.com/blog/1827633
http://stackoverflow.com/questions/18201075/mybatis-spring-multiple-databases-java-configuration