Shulou(Shulou.com)06/03 Report--

This article mainly introduces the problem analysis of springboot+mybatis+druid+ multi-data sources, the article is very detailed, has a certain reference value, interested friends must read it!

one。 Brief introduction

The two databases db1,db2, the mapper.xml of the db1 database and the mapper.xml of the db2 database are placed in different directories, configure different data sources for different directories, and monitor their respective transactions.

There is a new version of the solution: Mybatis Plus integrates multiple data sources and read-write separation, please use the new version

two。 Sql script

The user table of the db1 database:

CREATE TABLE `user` (`id` int (11) NOT NULL, `name` varchar (255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

The role table of the db2 database:

CREATE TABLE `role` (`id` int (11) NOT NULL, `name` varchar (255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; 3. Project Construction 3.1 Catalog structure Diagram

3.2 pom.xml File 4.0.0 com.example datasources 0.0.1-SNAPSHOT jar datasources Demo project for Spring Boot org.springframework.boot spring-boot-starter-parent 2.0.4.RELEASE UTF-8 UTF-8 1.8 org.springframework.boot Spring-boot-starter-web org.mybatis.spring.boot mybatis-spring-boot-starter 1.3.2 mysql mysql-connector-java runtime org.springframework.boot spring-boot-starter-test Test org.apache.commons commons-lang3 3.4 com.fasterxml.jackson.core jackson-core com.fasterxml.jackson.core jackson-databind com.fasterxml.jackson.datatype jackson- Datatype-joda com.fasterxml.jackson.module jackson-module-parameter-names org.springframework.boot spring-boot-starter-aop com.github.pagehelper pagehelper-spring-boot-starter 1.2.5 Com.alibaba druid-spring-boot-starter 1.1.9 org.springframework.boot spring-boot-starter-thymeleaf org.springframework.boot spring-boot-starter-jdbc org.springframework.boot Spring-boot-starter-jta-atomikos io.springfox springfox-swagger2 2.6.1 io.springfox springfox-swagger-ui 2.6.1 org. Springframework.boot spring-boot-maven-plugin 3.3application.ymlserver: port: 8080 spring: datasource: db1: driverClassName: com.mysql.jdbc.Driver username: username password: password # spring2.0 here is jdbc-url jdbc-url: jdbc:mysql://IP:3306/db1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true type: com .alibaba.druid.pool.DruidDataSource db2: driverClassName: com.mysql.jdbc.Driver username: username password: password # spring2.0 here is the jdbc-url jdbc-url: jdbc:mysql://IP:3306/db2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true type: com.alibaba.druid.pool.DruidDataSource3.4 data source configuration class

3.4.1 data source for db1 database (primary data source @ Primary)

Package com.example.datasources.datasource; 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.Qualifier;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.boot.jdbc.DataSourceBuilder;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.jdbc.datasource.DataSourceTransactionManager; import javax.sql.DataSource; @ Configuration@MapperScan (basePackages = "com.example.datasources.mapper.db1", sqlSessionTemplateRef = "db1SqlSessionTemplate") public class DataSource1Config {@ Bean @ ConfigurationProperties (prefix = "spring.datasource.db1") @ Primary public DataSource db1DataSource () {return DataSourceBuilder.create () .build () } @ Bean @ Primary public SqlSessionFactory db1SqlSessionFactory (@ Qualifier ("db1DataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean (); bean.setDataSource (dataSource); bean.setMapperLocations (new PathMatchingResourcePatternResolver (). GetResources ("classpath*:com/example/datasources/mapper/db1/*.xml")); return bean.getObject () @ Bean @ Primary public DataSourceTransactionManager db1TransactionManager (@ Qualifier ("db1DataSource") DataSource dataSource) {return new DataSourceTransactionManager (dataSource);} @ Bean @ Primary public SqlSessionTemplate db1SqlSessionTemplate (@ Qualifier ("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {return new SqlSessionTemplate (sqlSessionFactory);}}

3.4.2 data sources for the db2 database

Package com.example.datasources.datasource; 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.Qualifier;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.boot.jdbc.DataSourceBuilder;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.core.io.support.PathMatchingResourcePatternResolver;import org.springframework.jdbc.datasource.DataSourceTransactionManager Import javax.sql.DataSource; @ Configuration@MapperScan (basePackages = "com.example.datasources.mapper.db2", sqlSessionTemplateRef = "db2SqlSessionTemplate") public class DataSource2Config {@ Bean @ ConfigurationProperties (prefix = "spring.datasource.db2") public DataSource db2DataSource () {return DataSourceBuilder.create () .build ();} @ Bean public SqlSessionFactory db2SqlSessionFactory (@ Qualifier ("db2DataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean (); bean.setDataSource (dataSource) Bean.setMapperLocations (new PathMatchingResourcePatternResolver (). GetResources ("classpath*:com/example/datasources/mapper/db2/*.xml")); return bean.getObject ();} @ Bean public DataSourceTransactionManager db2TransactionManager (@ Qualifier ("db2DataSource") DataSource dataSource) {return new DataSourceTransactionManager (dataSource);} @ Bean public SqlSessionTemplate db2SqlSessionTemplate (@ Qualifier ("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {return new SqlSessionTemplate (sqlSessionFactory);}} 3.5 Controller

3.5.1 UserController for db1

Package com.example.datasources.controller; import com.example.datasources.entity.db1.User;import com.example.datasources.service.UserService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.*; import java.util.List; @ RestController@RequestMapping ("/ user") public class UserController {@ Autowired private UserService userService; @ GetMapping ("/ select/list") public List selectUserList () {return this.userService.selectUserList () } @ GetMapping ("/ save") public void saveUser (User user) {this.userService.saveUser (user);}}

3.5.2 RoleController for db2

Package com.example.datasources.controller; import com.example.datasources.entity.db2.Role;import com.example.datasources.service.RoleService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController; import java.util.List; @ RestController@RequestMapping ("/ role") public class RoleController {@ Autowired private RoleService roleService @ GetMapping ("/ select/list") public List selectRoleList () {return this.roleService.selectRoleList ();} @ GetMapping ("/ save") public void saveRole (Role role) {this.roleService.saveRole (role);}} 3.6 Service

3.6.1 UserService for db1

Package com.example.datasources.service; import com.example.datasources.entity.db1.User; import java.util.List; public interface UserService {List selectUserList (); void saveUser (User user);}

3.6.2 RoleService for db2

Package com.example.datasources.service; import com.example.datasources.entity.db2.Role; import java.util.List; public interface RoleService {List selectRoleList (); void saveRole (Role role);} 3.7serviceImpl

3.7.1 UserServiceImpl for db1

Package com.example.datasources.service.impl; import com.example.datasources.entity.db1.User;import com.example.datasources.mapper.db1.UserMapper;import com.example.datasources.service.UserService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import org.springframework.transaction.annotation.Transactional; import java.util.List; @ Servicepublic class UserServiceImpl implements UserService {@ Autowired private UserMapper userMapper; @ Override public List selectUserList () {return this.userMapper.selectUserList () } @ Transactional @ Override public void saveUser (User user) {this.userMapper.saveUser (user); / / throw new RuntimeException ();}}

3.7.2 RoleServiceImpl for db2

Package com.example.datasources.service.impl; import com.example.datasources.entity.db2.Role;import com.example.datasources.mapper.db2.RoleMapper;import com.example.datasources.service.RoleService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import org.springframework.transaction.annotation.Transactional; import java.util.List; @ Servicepublic class RoleServiceImpl implements RoleService {@ Autowired private RoleMapper roleMapper; @ Override public List selectRoleList () {return this.roleMapper.selectRoleList () } / Note: if it is not the primary data source, its data source must be declared, otherwise the transaction will not work @ Transactional (value = "db2TransactionManager") @ Override public void saveRole (Role role) {this.roleMapper.saveRole (role); / / throw new RuntimeException ();}} 3.8 mapper

3.8.1 UserMapper of db1

Package com.example.datasources.mapper.db1; import com.example.datasources.entity.db1.User; import java.util.List; public interface UserMapper {List selectUserList (); void saveUser (User user);}

3.8.2 RoleMapper of db2

Package com.example.datasources.mapper.db2; import com.example.datasources.entity.db2.Role; import java.util.List; public interface RoleMapper {List selectRoleList (); void saveRole (Role role);} 3.9 mapper.xml

3.9.1 UserMapper.xml of db1

Id, `name` SELECT FROM `user` INSERT INTO `user` (id, `name`) VALUES (# {id}, # {name})

3.9.2 RoleMapper.xml of db2

Id, name SELECT FROM role INSERT INTO `role` (id, `name`) VALUES (# {id}, # {name}) 3.10 entity

3.10.1 User of db1

Package com.example.datasources.entity.db1; public class User {private Integer id; private String name; public User () {} public User (Integer id, String name) {this.id = id; this.name = name;} public Integer getId () {return id;} public void setId (Integer id) {this.id = id } public String getName () {return name;} public void setName (String name) {this.name = name;}}

3.10.2 Role of db2

Package com.example.datasources.entity.db2; public class Role {private Integer id; private String name; public Role () {} public Role (Integer id, String name) {this.id = id; this.name = name;} public Integer getId () {return id;} public void setId (Integer id) {this.id = id } public String getName () {return name;} public void setName (String name) {this.name = name;}} 3.11 launch class package com.example.datasources; import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication; @ SpringBootApplicationpublic class DatasourcesApplication {public static void main (String [] args) {SpringApplication.run (DatasourcesApplication.class, args);}} four. test

You can test it directly in the browser, and you can open the exception when testing the transaction.

It is important to note that the non-primary data source must specify the data source in the @ Transactional annotation, otherwise the transaction will not work. The master database is not required.

The above is all the content of the article "problem Analysis of springboot+mybatis+druid+ Multi-sources". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.