1, Configure Druid data source

Druid is a relational database connection pool. It is an open source project of Alibaba. Address: https://github.com/alibaba/druid .

Druid not only provides the function of connection pool, but also provides monitoring function, which can view the working conditions of database connection pool and SQL query in real time.

1. Add Druid dependency

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

2.Druid data source configuration

Detailed configuration description: https://github.com/alibaba/druid/wiki/ common problem

server:
  port: 8888
spring:
  datasource:
    druid:
      # Database access configuration, using druid data source
      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
      # Specify database type
      db-type: mysql
      # Connection pool configuration
      initial-size: 5
      min-idle: 5
      max-active: 20
      # Connection wait timeout
      max-wait: 30000
      # Configure the interval between idle connections that can be closed by detection
      time-between-eviction-runs-millis: 60000
      # Configure the minimum lifetime of connections in the pool
      min-evictable-idle-time-millis: 300000
      validation-query: select '1' from dual
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      # Open PSCache and specify the size of PSCache on each connection
      pool-prepared-statements: true
      max-open-prepared-statements: 20
      max-pool-prepared-statement-per-connection-size: 20
      # Configure the filters for monitoring statistics interception. After removal, the sql in the monitoring interface cannot be counted, 'wall' is used for firewall
      filters: stat,wall
      # Spring monitors AOP pointcuts, such as x.y.z.service. *, and configures multiple English comma separated pointcuts
      aop-patterns: com.springboot.servie.*
      
    
      # WebStatFilter configuration
      web-stat-filter:
        enabled: true
        # Add filter rule
        url-pattern: /*
        # Ignore filtered formats
        exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
      
      # StatViewServlet configuration 
      stat-view-servlet:
        enabled: true
        # When the access path is / druid, jump to StatViewServlet
        url-pattern: /druid/*
        # Can I reset the data
        reset-enable: false
        # You need an account password to access the console
        login-username: druid
        login-password: druid
        # IP whitelist
        # allow: 127.0.0.1
        #IP blacklist (deny takes precedence over allow in case of co existence)
        # deny: 192.168.1.218
      
      # Configure StatFilter
      filter: 
        stat: 
          log-slow-sql: true
     

3.Druid monitoring background

http://localhost:8888/druid

2, Configuring Mybatis multiple data sources

1. Add data source configuration

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. Create data source configuration class

Create data source configuration classes datasource1 and Datasource2. Datasource1 configuration is similar to Datasource2.

//Define the current class as the configuration class, which is equivalent to xml, and inject the method into the spring container with Bean annotation
@Configuration
//Define the package scanning range of the persistence layer and specify which data source the package is configured with.
@MapperScan(basePackages = "cn.ybzy.demo.test01", sqlSessionTemplateRef = "sqlSessionTemplate1")
public class Datasource1 {
    /**
     * Configure data sources
     *
     * @return
     */
    @Bean(name = "dataSource1")
    //Specify which data source configuration in the application file to use
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource dataSource1() {
        return DataSourceBuilder.create().build();
    }


    /**
     * Configure SQL session factory
     *
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean(name = "sqlSessionFactory1")
    public SqlSessionFactory sqlSessionFactory1(@Qualifier("dataSource1") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //If you do not use xml to configure mapper, you can omit the configuration of mapper location in the following line.
//        Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test1/*.xml");
//        bean.setMapperLocations(resources);
        return bean.getObject();
    }


    /**
     * Configuration management
     *
     * @param dataSource
     * @return
     */
    @Bean(name = "transactionManager1")
    public DataSourceTransactionManager transactionManager1(@Qualifier("dataSource1") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

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

If an error is reported during startup:

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

Add the @ Primary annotation to identify a data source as the default data source. For example, add @ Primary annotation to all methods of Datasource1.

@The Primary flag indicates that if this Bean is candidate for multiple beans of the same kind, this Bean will be given priority. When configuring multiple data sources, you must have a Primary data source and mark the Bean with @ Primary.

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

3. Add Mapper interface and Service class

Subcontract new Mapper interface and Service class

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. Perform the test

@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("Xiaobai 1", 20);
        userService2.addUser("Xiaobai 2", 22);
    }
}

3, Configuring Spring Data Jpa multiple data sources

1. Configure application.yml

spring:
  #Database connection pool
  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:
      #Database platform
      databasePlatform: mysql
      #sql information printing
      showSql: true
      # Request bound to thread
      openInView: true
      hibernate:
        # Table generation strategy
        ddl-auto: update
        #Defines the dialect of the database
        dialect: org.hibernate.dialect.MySQL57Dialect

DDL auto attribute value:

create: The table is recreated each time the program runs
create-drop: Each time the program runs, the table structure will be created first, and then the table will be cleared at the end of the program
upadte: Every time you run the program, a table will be created when there is no table. If the object changes, the table structure will be updated. The original data will not be cleared, but only updated (recommended)
validate: The running program will verify whether the field types of the data and the database are the same. If the fields are different, an error will be reported
none: Disable DDL handle

2. Configure multiple data sources

Create data source configuration class DatasourceConfig1 And DatasourceConfig2，DatasourceConfig2 Configuration reference DatasourceConfig1 to configure

@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();
    }


    /**
     * Configure jpa vendor adapter
     *
     * @return
     */
    @Primary
    @Bean(name = "jpaVendorAdapter1")
    public JpaVendorAdapter jpaVendorAdapter1() {
        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        // Set the Database type (Database enumeration class under org.springframework.orm.jpa.vendor package)
        jpaVendorAdapter.setDatabase(Database.MYSQL);
        // Set print sql statement
        jpaVendorAdapter.setShowSql(true);
        // Set generation ddl statement
        jpaVendorAdapter.setGenerateDdl(true);
        // Set hibernate dialect
        jpaVendorAdapter.setDatabasePlatform("org.hibernate.dialect.MySQL5Dialect");
        return jpaVendorAdapter;
    }

    /**
     * Create entityManagerFactory factory
     *
     * @return
     */
    @Primary
    @Bean(name = "entityManager1")
    public LocalContainerEntityManagerFactoryBean entityManager1(@Qualifier("dataSource1") DataSource dataSource, @Qualifier("jpaVendorAdapter1") JpaVendorAdapter jpaVendorAdapter) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        //Configure scanned entity class package
        em.setPackagesToScan(new String[]{"cn.ybzy.demo.entity"});
        em.setJpaVendorAdapter(jpaVendorAdapter);
        return em;
    }

    /**
     * Create transaction manager
     *
     * @return
     */
    @Primary
    @Bean(name = "transactionManager1")
    public PlatformTransactionManager transactionManager1(@Qualifier("entityManager1") EntityManagerFactory entityManager) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManager);
        return transactionManager;
    }
}

3. Configure entity class

Automatically generate corresponding tables in the database

@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. Create interface

Configure the package by data source and subcontract to create new interfaces UserDao1 and UserDao2

public interface UserDao1 extends JpaRepository<User,Integer> {


     List<User> findByAge(Integer id);

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

    /**
     * If the sql statement is not a query, the @ Modifying annotation must be added
     * When performing DML(update,delete,insert) operations on the database, you must add things, that is, @ 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. Test

@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");
    }
}    

4, Spring Boot transaction management

springboot integrates things by default, and only adds @ Transactional to the method.

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

rollbackFor :Specify the exception type that can trigger transaction rollback. The default value is UncheckedException，include RuntimeException and Error

transactionManager : Specify the specific transaction manager to use

1. Single transaction management

Based on the integration of multiple data sources by SpringBoot, the following methods are added to the Service class.

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

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

Because the default data source is Datasource1,Its transaction function is userMapper1 The class under the package, userMapper2 The class transaction under the package is invalid.

This transaction is a single data source processing method. If two data sources are in the same transaction, they cannot be rolled back completely

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

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

Use the default transaction manager

Test test1: userMapper1.addUser("Xiaobai 1", 11); Transaction rollback

Test test2: userMapper2.addUser("Xiaobai 2", 33); Transaction invalidation

Specify the specific transaction manager to use

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

Test test1: userMapper1.addUser("Xiaobai 1", 11); Transaction invalidation

Test test2: userMapper2.addUser("Xiaobai 2", 33); Transaction rollback

2. Distributed transaction management

Using springboot+jta+atomikos for distributed transaction management

1. Introduce dependency

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

2. Configure the application file

#Data source 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

#Data source 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. Read configuration information

Create DBConfig1 and DBConfig2 configuration classes, and the properties are consistent with the data source configuration in the application file

@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. Create multiple data sources

Use JTA atomikos for transaction management, create TransactionManager1 and TransactionManager2 classes, and configure data sources respectively.

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

	@Primary//Specify the default data source, and optionally configure one of TransactionManager1 and 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. Load configuration information

Add the startup class of SpringBoot: @ enableconfigurationproperties (value = {dbconfig1. Class, dbconfig2. Class})

@EnableConfigurationProperties(value = { DBConfig1.class, DBConfig2.class })
@SpringBootApplication
public class Application {
    public static void main(String[] args) {
    	//Run the SpringBoot startup class. The parameter is the bytecode object of the SpringBoot startup class
        SpringApplication.run(Application.class,args);
    }
}

6. Perform the test

It is tested based on SpringBoot single transaction management and found that distributed transactions take effect.