springboot学习笔记：9.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
posted on 2018-01-31 17:13 zhaojiatao 阅读(8210) 评论(0) 编辑 收藏 举报
