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.