springboot+mybatis+通用mapper+多數據源 ...
請確保根據上一篇文章的源代碼完成所有測試之後,再跟隨本文,繼續配置多數據源;
1.資料庫準備
資料庫表我們在springboot-mybatis數據之外,新建資料庫springboot-mybatis2;
springboot-mybatis資料庫中有t_class表;
springboot-mybatis2資料庫中有t_teacher表;
2.配置文件增加新數據源連接配置信息:
spring.datasource2.type=com.alibaba.druid.pool.DruidDataSource spring.datasource2.driver-class-name=com.mysql.jdbc.Driver spring.datasource2.url=jdbc:mysql://localhost:3306/springboot-mybatis2?useUnicode=true&characterEncoding=utf-8 spring.datasource2.username=root spring.datasource2.password=root
3.註意第二個數據源DataSource2對應的數據表實體及mapper和service的包結構:
兩套數據源對應的mapper、service包與第一個數據源是平行的
4.DruidConfig.java中增加新數據源:dataSource2(加粗部分)
package com.zjt.config; import com.alibaba.druid.filter.Filter; import com.alibaba.druid.filter.stat.StatFilter; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.support.http.StatViewServlet; import com.alibaba.druid.support.http.WebStatFilter; import com.alibaba.druid.wall.WallConfig; import com.alibaba.druid.wall.WallFilter; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Value; import org.springframework.boot.web.servlet.FilterRegistrationBean; import org.springframework.boot.web.servlet.ServletRegistrationBean; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import javax.sql.DataSource; import java.util.ArrayList; import java.util.List; /** * Druid配置 * * @author zhaojiatao */ @Configuration public class DruidConfig { private Logger logger = LoggerFactory.getLogger(DruidConfig.class); @Value("${spring.datasource.url:#{null}}") private String dbUrl; @Value("${spring.datasource.username: #{null}}") private String username; @Value("${spring.datasource.password:#{null}}") private String password; @Value("${spring.datasource.driverClassName:#{null}}") private String driverClassName; @Value("${spring.datasource2.url:#{null}}") private String dbUrl2; @Value("${spring.datasource2.username: #{null}}") private String username2; @Value("${spring.datasource2.password:#{null}}") private String password2; @Value("${spring.datasource2.driverClassName:#{null}}") private String driverClassName2; @Value("${spring.datasource.initialSize:#{null}}") private Integer initialSize; @Value("${spring.datasource.minIdle:#{null}}") private Integer minIdle; @Value("${spring.datasource.maxActive:#{null}}") private Integer maxActive; @Value("${spring.datasource.maxWait:#{null}}") private Integer maxWait; @Value("${spring.datasource.timeBetweenEvictionRunsMillis:#{null}}") private Integer timeBetweenEvictionRunsMillis; @Value("${spring.datasource.minEvictableIdleTimeMillis:#{null}}") private Integer minEvictableIdleTimeMillis; @Value("${spring.datasource.validationQuery:#{null}}") private String validationQuery; @Value("${spring.datasource.testWhileIdle:#{null}}") private Boolean testWhileIdle; @Value("${spring.datasource.testOnBorrow:#{null}}") private Boolean testOnBorrow; @Value("${spring.datasource.testOnReturn:#{null}}") private Boolean testOnReturn; @Value("${spring.datasource.poolPreparedStatements:#{null}}") private Boolean poolPreparedStatements; @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize:#{null}}") private Integer maxPoolPreparedStatementPerConnectionSize; @Value("${spring.datasource.filters:#{null}}") private String filters; @Value("{spring.datasource.connectionProperties:#{null}}") private String connectionProperties; @Bean @Primary public DataSource dataSource(){ DruidDataSource datasource = new DruidDataSource(); datasource.setUrl(this.dbUrl); datasource.setUsername(username); datasource.setPassword(password); datasource.setDriverClassName(driverClassName); //configuration if(initialSize != null) { datasource.setInitialSize(initialSize); } if(minIdle != null) { datasource.setMinIdle(minIdle); } if(maxActive != null) { datasource.setMaxActive(maxActive); } if(maxWait != null) { datasource.setMaxWait(maxWait); } if(timeBetweenEvictionRunsMillis != null) { datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); } if(minEvictableIdleTimeMillis != null) { datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); } if(validationQuery!=null) { datasource.setValidationQuery(validationQuery); } if(testWhileIdle != null) { datasource.setTestWhileIdle(testWhileIdle); } if(testOnBorrow != null) { datasource.setTestOnBorrow(testOnBorrow); } if(testOnReturn != null) { datasource.setTestOnReturn(testOnReturn); } if(poolPreparedStatements != null) { datasource.setPoolPreparedStatements(poolPreparedStatements); } if(maxPoolPreparedStatementPerConnectionSize != null) { datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize); } if(connectionProperties != null) { datasource.setConnectionProperties(connectionProperties); } List<Filter> filters = new ArrayList<>(); filters.add(statFilter()); filters.add(wallFilter()); datasource.setProxyFilters(filters); return datasource; } @Bean public DataSource dataSource2(){ DruidDataSource datasource = new DruidDataSource(); datasource.setUrl(this.dbUrl2); datasource.setUsername(username2); datasource.setPassword(password2); datasource.setDriverClassName(driverClassName2); //configuration if(initialSize != null) { datasource.setInitialSize(initialSize); } if(minIdle != null) { datasource.setMinIdle(minIdle); } if(maxActive != null) { datasource.setMaxActive(maxActive); } if(maxWait != null) { datasource.setMaxWait(maxWait); } if(timeBetweenEvictionRunsMillis != null) { datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); } if(minEvictableIdleTimeMillis != null) { datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); } if(validationQuery!=null) { datasource.setValidationQuery(validationQuery); } if(testWhileIdle != null) { datasource.setTestWhileIdle(testWhileIdle); } if(testOnBorrow != null) { datasource.setTestOnBorrow(testOnBorrow); } if(testOnReturn != null) { datasource.setTestOnReturn(testOnReturn); } if(poolPreparedStatements != null) { datasource.setPoolPreparedStatements(poolPreparedStatements); } if(maxPoolPreparedStatementPerConnectionSize != null) { datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize); } if(connectionProperties != null) { datasource.setConnectionProperties(connectionProperties); } List<Filter> filters = new ArrayList<>(); filters.add(statFilter()); filters.add(wallFilter()); datasource.setProxyFilters(filters); return datasource; } @Bean public ServletRegistrationBean druidServlet() { ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); //控制台管理用戶,加入下麵2行 進入druid後臺就需要登錄 //servletRegistrationBean.addInitParameter("loginUsername", "admin"); //servletRegistrationBean.addInitParameter("loginPassword", "admin"); return servletRegistrationBean; } @Bean public FilterRegistrationBean filterRegistrationBean() { FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(); filterRegistrationBean.setFilter(new WebStatFilter()); filterRegistrationBean.addUrlPatterns("/*"); filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"); filterRegistrationBean.addInitParameter("profileEnable", "true"); return filterRegistrationBean; } @Bean public StatFilter statFilter(){ StatFilter statFilter = new StatFilter(); statFilter.setLogSlowSql(true); //slowSqlMillis用來配置SQL慢的標準,執行時間超過slowSqlMillis的就是慢。 statFilter.setMergeSql(true); //SQL合併配置 statFilter.setSlowSqlMillis(1000);//slowSqlMillis的預設值為3000,也就是3秒。 return statFilter; } @Bean public WallFilter wallFilter(){ WallFilter wallFilter = new WallFilter(); //允許執行多條SQL WallConfig config = new WallConfig(); config.setMultiStatementAllow(true); wallFilter.setConfig(config); return wallFilter; } }
5.增加MybatisDatasource2Config.java配置dataSource2對應的mapper掃描包路徑、sqlSessionFactory2、以及事務管理器transactionManager2
package com.zjt.config; import com.zjt.util.MyMapper; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.SqlSessionTemplate; import org.mybatis.spring.annotation.MapperScan; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.core.io.support.ResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import javax.sql.DataSource; /** * @author <a href="zhaojiatao"></a> * @version 1.0, 2017/11/24 * @description */ @Configuration // 精確到 mapper 目錄,以便跟其他數據源隔離 @MapperScan(basePackages = "com.zjt.mapper2", markerInterface = MyMapper.class, sqlSessionFactoryRef = "sqlSessionFactory2") public class MybatisDatasource2Config { @Autowired @Qualifier("dataSource2") private DataSource ds; @Bean public SqlSessionFactory sqlSessionFactory2() throws Exception { SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean(); factoryBean.setDataSource(ds); //指定mapper xml目錄 ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver(); factoryBean.setMapperLocations(resolver.getResources("classpath:mapper2/*.xml")); return factoryBean.getObject(); } @Bean public SqlSessionTemplate sqlSessionTemplate2() throws Exception { SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory2()); // 使用上面配置的Factory return template; } //關於事務管理器,不管是JPA還是JDBC等都實現自介面 PlatformTransactionManager // 如果你添加的是 spring-boot-starter-jdbc 依賴,框架會預設註入 DataSourceTransactionManager 實例。 //在Spring容器中,我們手工註解@Bean 將被優先載入,框架不會重新實例化其他的 PlatformTransactionManager 實現類。 @Bean(name = "transactionManager2") @Primary public DataSourceTransactionManager masterTransactionManager() { //MyBatis自動參與到spring事務管理中,無需額外配置,只要org.mybatis.spring.SqlSessionFactoryBean引用的數據源 // 與DataSourceTransactionManager引用的數據源一致即可,否則事務管理會不起作用。 return new DataSourceTransactionManager(ds); } }
6.增加TxAdvice2Interceptor.java配置datasource2數據源對應的事務配置
package com.zjt.interceptor; import org.aspectj.lang.annotation.Aspect; import org.springframework.aop.Advisor; import org.springframework.aop.aspectj.AspectJExpressionPointcut; import org.springframework.aop.support.DefaultPointcutAdvisor; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.transaction.PlatformTransactionManager; import org.springframework.transaction.TransactionDefinition; import org.springframework.transaction.interceptor.*; import java.util.Collections; import java.util.HashMap; import java.util.Map; /** * @author <a href="zhaojiatao"></a> * @version 1.0, 2017/11/29 * @description * 註解聲明式事務 */ @Aspect @Configuration public class TxAdvice2Interceptor { private static final int TX_METHOD_TIMEOUT = 50000;//單位秒 private static final String AOP_POINTCUT_EXPRESSION = "execution (* com.zjt.service2.*.*(..))"; @Autowired @Qualifier("transactionManager2") private PlatformTransactionManager transactionManager; @Bean public TransactionInterceptor txAdvice2() { NameMatchTransactionAttributeSource source = new NameMatchTransactionAttributeSource(); /*只讀事務,不做更新操作*/ RuleBasedTransactionAttribute readOnlyTx = new RuleBasedTransactionAttribute(); readOnlyTx.setReadOnly(true); readOnlyTx.setPropagationBehavior(TransactionDefinition.PROPAGATION_NOT_SUPPORTED ); /*當前存在事務就使用當前事務,當前不存在事務就創建一個新的事務*/ RuleBasedTransactionAttribute requiredTx = new RuleBasedTransactionAttribute(); requiredTx.setRollbackRules( Collections.singletonList(new RollbackRuleAttribute(Exception.class))); requiredTx.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED); requiredTx.setTimeout(TX_METHOD_TIMEOUT); Map<String, TransactionAttribute> txMap = new HashMap<>(); txMap.put("add*", requiredTx); txMap.put("save*", requiredTx); txMap.put("insert*", requiredTx); txMap.put("update*", requiredTx); txMap.put("delete*", requiredTx); txMap.put("get*", readOnlyTx); txMap.put("select*", readOnlyTx); txMap.put("query*", readOnlyTx); source.setNameMap( txMap ); TransactionInterceptor txAdvice = new TransactionInterceptor(transactionManager, source); return txAdvice; } @Bean public Advisor txAdviceAdvisor2() { AspectJExpressionPointcut pointcut = new AspectJExpressionPointcut(); pointcut.setExpression(AOP_POINTCUT_EXPRESSION); return new DefaultPointcutAdvisor(pointcut, txAdvice2()); } }
7.編寫TeacherController實現教師業務控制器
package com.zjt.web; import com.zjt.entity.Teacher; import com.zjt.model.PageRusult; import com.zjt.model.QueryTeacherList; import com.zjt.service2.TeacherService; import com.zjt.util.Page; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.ResponseBody; import tk.mybatis.mapper.util.StringUtil; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; @Controller @RequestMapping("/teacher") public class TeacherController { @Autowired @Qualifier("teacherServiceImpl") private TeacherService teacherService; @ResponseBody @RequestMapping("/queryTeacherList") public PageRusult selectByPages(Page<QueryTeacherList> page){ List<Teacher> teacherList=teacherService.queryTeacherList(page); PageRusult<Teacher> pageRusult =new PageRusult<Teacher>(teacherList); pageRusult.setCode(0); return pageRusult; } @ResponseBody @RequestMapping("/saveOrUpdateTeacher") public Map<String,Object> saveOrUpdateTeacher(Teacher teacher){ LinkedHashMap<String,Object> resultMap=new LinkedHashMap<String,Object>(); try { return teacherService.saveOrUpdateTeacher(teacher); }catch (Exception e){ resultMap.put("state","fail"); resultMap.put("message","操作失敗"); return resultMap; } } @ResponseBody @RequestMapping("/deleteTeacher") public Map<String,Object> deleteTeacher(String id){ LinkedHashMap<String,Object> resultMap=new LinkedHashMap<String,Object>(); try { if(StringUtil.isNotEmpty(id)){ teacherService.delete(id); resultMap.put("state","success"); resultMap.put("message","刪除班級成功"); return resultMap; }else{ resultMap.put("state","fail"); resultMap.put("message","刪除班級失敗"); return resultMap; } }catch (Exception e){ resultMap.put("state","fail"); resultMap.put("message","操作異常,刪除班級失敗"); return resultMap; } } }
8.啟動項目驗證:
8.1查詢驗證
8.2事務驗證:
在TeacherServiceImpl.java的saveOrUpdateTeacher方法的更新操作(updateNotNull(teacher))後認為添加1/0,拋出運行時異常,看看是否回滾;
在TClassServiceImpl.java的saveOrUpdateTClass方法的更新操作(updateNotNull(tclass))後認為添加1/0,拋出運行時異常,看看是否回滾;
可以驗證出兩個數據源的事務均回滾成功,打開druid監控也可以看到兩個數據源的事務均執行了回滾:
9.項目源代碼:
https://github.com/zhaojiatao/springboot-zjt-chapter09-springboot-mybatis-datasources.git