一、配置Druid数据源

Druid是一个关系型数据库连接池，是阿里巴巴的一个开源项目，地址：https://github.com/alibaba/druid。

Druid不但提供连接池的功能，还提供监控功能，可以实时查看数据库连接池和SQL查询的工作情况。

1.添加Druid依赖

<dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid-spring-boot-starter</artifactId>
     <version>1.2.5</version>
</dependency>

2.Druid数据源配置

详细配置说明: https://github.com/alibaba/druid/wiki/常见问题

server:
  port: 8888
spring:
  datasource:
    druid:
      # 数据库访问配置, 使用druid数据源
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/demo?serverTimezone=UTC
      username: root
      password: 123456
      # 指定数据库类型
      db-type: mysql
      # 连接池配置
      initial-size: 5
      min-idle: 5
      max-active: 20
      # 连接等待超时时间
      max-wait: 30000
      # 配置检测可以关闭的空闲连接间隔时间
      time-between-eviction-runs-millis: 60000
      # 配置连接在池中的最小生存时间
      min-evictable-idle-time-millis: 300000
      validation-query: select '1' from dual
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      # 打开PSCache，并且指定每个连接上PSCache的大小
      pool-prepared-statements: true
      max-open-prepared-statements: 20
      max-pool-prepared-statement-per-connection-size: 20
      # 配置监控统计拦截的filters, 去掉后监控界面sql无法统计, 'wall'用于防火墙
      filters: stat,wall
      # Spring监控AOP切入点，如x.y.z.service.*,配置多个英文逗号分隔
      aop-patterns: com.springboot.servie.*
      
    
      # WebStatFilter配置
      web-stat-filter:
        enabled: true
        # 添加过滤规则
        url-pattern: /*
        # 忽略过滤的格式
        exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
      
      # StatViewServlet配置 
      stat-view-servlet:
        enabled: true
        # 访问路径为/druid时，跳转到StatViewServlet
        url-pattern: /druid/*
        # 是否能够重置数据
        reset-enable: false
        # 需要账号密码才能访问控制台
        login-username: druid
        login-password: druid
        # IP白名单
        # allow: 127.0.0.1
        #　IP黑名单（共同存在时，deny优先于allow）
        # deny: 192.168.1.218
      
      # 配置StatFilter
      filter: 
        stat: 
          log-slow-sql: true
     

3.Druid监控后台

http://localhost:8888/druid

二、配置Mybatis多数据源

1.新增数据源配置

spring.datasource.db1.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.db1.jdbc-url = jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
spring.datasource.db1.username = root
spring.datasource.db1.password = 123456

spring.datasource.db2.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.db2.jdbc-url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
spring.datasource.db2.username = root
spring.datasource.db2.password = 123456

2.创建数据源配置类

创建数据源配置类Datasource1与Datasource2，Datasource1配置与Datasource2类似。

//定义当前类为配置类，相当于xml,配合Bean注解将方法注入到spring容器中
@Configuration
//定义持久层的扫包范围，并指定这个包配置的是哪个数据源。
@MapperScan(basePackages = "cn.ybzy.demo.test01", sqlSessionTemplateRef = "sqlSessionTemplate1")
public class Datasource1 {
    /**
     * 配置数据源
     *
     * @return
     */
    @Bean(name = "dataSource1")
    //指定使用application文件中哪个数据源的配置
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource dataSource1() {
        return DataSourceBuilder.create().build();
    }


    /**
     * 配置SQL会话工厂
     *
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean(name = "sqlSessionFactory1")
    public SqlSessionFactory sqlSessionFactory1(@Qualifier("dataSource1") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //如果不使用xml的方式配置mapper，则可以省去下面这行mapper location的配置。
//        Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test1/*.xml");
//        bean.setMapperLocations(resources);
        return bean.getObject();
    }


    /**
     * 配置事物管理
     *
     * @param dataSource
     * @return
     */
    @Bean(name = "transactionManager1")
    public DataSourceTransactionManager transactionManager1(@Qualifier("dataSource1") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * 配置sqlSessionTemplate
     *
     * @param sqlSessionFactory
     * @return
     * @throws Exception
     */
    @Bean(name = "sqlSessionTemplate1")
    public SqlSessionTemplate sqlSessionTemplate1(@Qualifier("sqlSessionFactory1") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

启动若报错：

org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type 'org.springframework.transaction.TransactionManager' available: expected single matching bean but found 2: transactionManager1,transactionManager2

添加@Primary注解，指定标识某个数据源为默认数据源。如：将Datasource1的所有方法添加@Primary注解。

@Primary标志这个Bean如果在多个同类Bean候选时，该Bean优先被考虑。多数据源配置的时候，必须要有一个主数据源，用@Primary标志该Bean。

    @Primary
	@Bean(name = "dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource dataSource1() {
        return DataSourceBuilder.create().build();
    }

3.新增Mapper接口与Service类

分包新建Mapper接口与Service类

public interface UserMapper1 {

	@Insert("insert into user values(null,#{name},#{age});")
	public int addUser(@Param("name") String name, @Param("age") Integer age);
}

@Service
public class UserService1 {
	@Autowired
	private UserMapper1 userMapper1;

	public int addUser(String name, Integer age) {
		return userMapper1.addUser(name, age);
	}
}

4.执行测试

@RunWith(SpringRunner.class)
@SpringBootTest(classes = App.class)
public class Test {

    @Autowired
    private UserService1 userService1;

    @Autowired
    private UserService2 userService2;

    @org.junit.Test
    public void test(){
        userService1.addUser("小白1", 20);
        userService2.addUser("小白2", 22);
    }
}

三、配置Spring Data Jpa多数据源

1.配置application.yml

spring:
  #数据库连接池
  datasource:
    db1:
      jdbc-url: jdbc:mysql://localhost:3306/activiti?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
    db2:
      jdbc-url: jdbc:mysql://localhost:3306/business?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver

  jpa:
    properties:
      #数据库平台
      databasePlatform: mysql
      #sql信息打印
      showSql: true
      # 请求绑定到线程
      openInView: true
      hibernate:
        # 表的生成策略
        ddl-auto: update
        #定义数据库的方言
        dialect: org.hibernate.dialect.MySQL57Dialect

ddl-auto属性值：

create：每次运行程序时，都会重新创建表
create-drop：每次运行程序时会先创建表结构，然后待程序结束时清空表
upadte：每次运行程序，没有表时会创建表，如果对象发生改变会更新表结构，原有数据不会清空，只会更新（推荐使用）
validate：运行程序会校验数据与数据库的字段类型是否相同，字段不同会报错
none: 禁用DDL处理

2.配置多数据源

创建数据源配置类DatasourceConfig1 与DatasourceConfig2，DatasourceConfig2配置参考DatasourceConfig1配置

@Configuration
@EnableJpaRepositories(
        basePackages = "cn.ybzy.demo.dao1",
        entityManagerFactoryRef = "entityManager1",
        transactionManagerRef = "transactionManager1"
)
public class JpaRepositoriesConfig {

    @Bean(name = "dataSource1")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource dataSource1() {
        return DataSourceBuilder.create().build();
    }


    /**
     * 配置jpa厂商适配器
     *
     * @return
     */
    @Primary
    @Bean(name = "jpaVendorAdapter1")
    public JpaVendorAdapter jpaVendorAdapter1() {
        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        // 设置数据库类型（org.springframework.orm.jpa.vendor包下的Database枚举类）
        jpaVendorAdapter.setDatabase(Database.MYSQL);
        // 设置打印sql语句
        jpaVendorAdapter.setShowSql(true);
        // 设置生成ddl语句
        jpaVendorAdapter.setGenerateDdl(true);
        // 设置hibernate方言
        jpaVendorAdapter.setDatabasePlatform("org.hibernate.dialect.MySQL5Dialect");
        return jpaVendorAdapter;
    }

    /**
     * 创建entityManagerFactory工厂
     *
     * @return
     */
    @Primary
    @Bean(name = "entityManager1")
    public LocalContainerEntityManagerFactoryBean entityManager1(@Qualifier("dataSource1") DataSource dataSource, @Qualifier("jpaVendorAdapter1") JpaVendorAdapter jpaVendorAdapter) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        //配置扫描的实体类包
        em.setPackagesToScan(new String[]{"cn.ybzy.demo.entity"});
        em.setJpaVendorAdapter(jpaVendorAdapter);
        return em;
    }

    /**
     * 创建事务管理器
     *
     * @return
     */
    @Primary
    @Bean(name = "transactionManager1")
    public PlatformTransactionManager transactionManager1(@Qualifier("entityManager1") EntityManagerFactory entityManager) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManager);
        return transactionManager;
    }
}

3.配置实体类

自动在数据库里生成相对应的表

@Data
@Entity(name = "user")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
  
    @Column(name = "user_name")
    private String userName;

    private Integer age;
}

4.创建接口

按数据源配置包，分包新建接口UserDao1 与UserDao2

public interface UserDao1 extends JpaRepository<User,Integer> {


     List<User> findByAge(Integer id);

    @Query(value = "select count(*) from user",nativeQuery = true)
     long userTotal();

    /**
     * 如果sql语句不是查询，那么必须加@Modifying注解
     * 在对数据库进行DML(update,delete,insert)操作时，必须加上事物，也就是@Transactional
     * @param id
     * @param username
     * @return
     */
    @Transactional
    @Query(value = "update user set user_name=:username  where id=:id",nativeQuery = true)
    @Modifying
     int updateUser(@Param("id") int id ,@Param("username") String username);


}

5.测试

@RunWith(SpringRunner.class)
@SpringBootTest
public class JpaApplicationTests {

    @Autowired
    UserDao1 userDao1;

    @Autowired
    UserDao2 userDao2;

    @Test
    public void insert() {
        User user1 = new User();
        user1.setUserName("name1");
        user1.setAge(22);
        userDao1.save(user1);

        User user2 = new User();
        user2.setUserName("name2");
        user2.setAge(22);
        userDao2.save(user2);
    }

    @Test
    public void select() {
        List<User> userList1 = userDao1.findByAge(22);
        List<User> userList2 = userDao1.findByAge(22);
        System.out.println(userList1.size() + ":" + userList2.size());
    }

    @Test
    public void selectTotal() {
        long userTotal1 = userDao1.userTotal();
        long userTotal2 = userDao2.userTotal();
        System.out.println(userTotal1 + ":" + userTotal2);
    }

    @Test
    public void updateMany() {
        userDao1.updateUser(1, "name1-update");
        userDao2.updateUser(1, "name1-update");
    }
}    

四、Spring Boot事物管理

springboot默认集成事物,只主要在方法上加上@Transactional即可。

@Transactional(rollbackFor = Exception.class,transactionManager = "transactionManager1")

rollbackFor :指定能够触发事务回滚的异常类型，默认值为UncheckedException，包括RuntimeException和Error

transactionManager ：指定使用具体的事务管理器

1.单事物管理

基于SpringBoot整合多数据源的基础之上，在Service类添加如下方法。

	@Transactional
	public int addUser1() {
		userMapper1.addUser("小白1", 11);
		int i = 1 / 0;
		userMapper2.addUser("小白2", 33);
		return 1;
	}

	@Transactional
	public int addUser2() {
		userMapper2.addUser("小白2", 33);
		int i = 1 / 0;
		userMapper1.addUser("小白1", 11);
		return 1;
	}

由于使用默认数据源为Datasource1,其事务作用在userMapper1所在包下的类，userMapper2所在包下的类事务失效。

这种事务处理是单数据源的处理方式，如果两个数据源在同一个事务中时，就无法完全回滚

  	@Test
    public void test1(){
        userService1.addUser1();
    }

	@Test
    public void test2(){
        userService2.addUser2();
    }

使用默认的事务管理器

测试test1： userMapper1.addUser("小白1", 11);事务进行回滚

测试test2： userMapper2.addUser("小白2", 33);事务失效

指定使用具体的事务管理器

@Transactional(rollbackFor = Exception.class,transactionManager = "transactionManager2")

测试test1：userMapper1.addUser("小白1", 11);事务事务失效

测试test2：userMapper2.addUser("小白2", 33);事务进行回滚

2.分布式事物管理

使用springboot+jta+atomikos进行分布式事物管理

1.引入依赖

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jta-atomikos</artifactId>
		</dependency>

2.配置application文件

#数据源1
spring.datasource.db1.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.db1.url = jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
spring.datasource.db1.username = root
spring.datasource.db1.password = 123456

spring.datasource.db1.minPoolSize = 3
spring.datasource.db1.maxPoolSize = 25
spring.datasource.db1.maxLifetime = 20000
spring.datasource.db1.borrowConnectionTimeout = 30
spring.datasource.db1.loginTimeout = 30
spring.datasource.db1.maintenanceInterval = 60
spring.datasource.db1.maxIdleTime = 60

#数据源2
spring.datasource.db2.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.db2.url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
spring.datasource.db2.username = root
spring.datasource.db2.password = 123456

spring.datasource.db2.minPoolSize = 3
spring.datasource.db2.maxPoolSize = 25
spring.datasource.db2.maxLifetime = 20000
spring.datasource.db2.borrowConnectionTimeout = 30
spring.datasource.db2.loginTimeout = 30
spring.datasource.db2.maintenanceInterval = 60
spring.datasource.db2.maxIdleTime = 60

3.读取配置信息

创建DBConfig1与DBConfig2配置类，属性与application文件中的数据源配置一致

@ConfigurationProperties(prefix = "spring.datasource.db1")
public class DBConfig1 {

	private String url;
	private String username;
	private String password;

	private int minPoolSize;

	private int maxPoolSize;

	private int maxLifetime;

	private int borrowConnectionTimeout;

	private int loginTimeout;

	private int maintenanceInterval;

	private int maxIdleTime;
	
	//getter()
	//setter()
}

4.创建多数据源

使用jta-atomikos进行事务管理，创建TransactionManager1与TransactionManager2两个类，分别配置数据源。

@Configuration
@MapperScan(basePackages = "cn.ybzy.demo.test01", sqlSessionTemplateRef = "sqlSessionTemplate1")
public class TransactionManager1{

	@Primary//指定默认数据源，TransactionManager1与TransactionManager2任选配置其一
	@Bean(name = "dataSource1")
	public DataSource dataSource1(DBConfig1 dbConfig1) throws SQLException {
		MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();
		mysqlXaDataSource.setUrl(dbConfig1.getUrl());
		mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
		mysqlXaDataSource.setPassword(dbConfig1.getPassword());
		mysqlXaDataSource.setUser(dbConfig1.getUsername());
		mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);

		AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
		xaDataSource.setXaDataSource(mysqlXaDataSource);
		xaDataSource.setUniqueResourceName("dataSource1");

		xaDataSource.setMinPoolSize(dbConfig1.getMinPoolSize());
		xaDataSource.setMaxPoolSize(dbConfig1.getMaxPoolSize());
		xaDataSource.setMaxLifetime(dbConfig1.getMaxLifetime());
		xaDataSource.setBorrowConnectionTimeout(dbConfig1.getBorrowConnectionTimeout());
		xaDataSource.setLoginTimeout(dbConfig1.getLoginTimeout());
		xaDataSource.setMaintenanceInterval(dbConfig1.getMaintenanceInterval());
		xaDataSource.setMaxIdleTime(dbConfig1.getMaxIdleTime());
		xaDataSource.setTestQuery(dbConfig1.getTestQuery());
		return xaDataSource;
}

	@Bean(name = "sqlSessionFactory1")
	public SqlSessionFactory sqlSessionFactory1(@Qualifier("dataSource1") DataSource dataSource)
			throws Exception {
		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
		bean.setDataSource(dataSource);
		return bean.getObject();
	}

	@Bean(name = "sqlSessionTemplate1")
	public SqlSessionTemplate testSqlSessionTemplate(
			@Qualifier("sqlSessionFactory1") SqlSessionFactory sqlSessionFactory) throws Exception {
		return new SqlSessionTemplate(sqlSessionFactory);
	}
}

5.加载配置信息

SpringBoot的启动类添加：@EnableConfigurationProperties(value = { DBConfig1.class, DBConfig2.class })

@EnableConfigurationProperties(value = { DBConfig1.class, DBConfig2.class })
@SpringBootApplication
public class Application {
    public static void main(String[] args) {
    	//运行SpringBoot的启动类，参数为SpringBoot启动类的字节码对象
        SpringApplication.run(Application.class,args);
    }
}

6.执行测试

基于SpringBoot单事物管理的基础之上进行测试，发现分布式事务生效。