好得很程序员自学网

<tfoot draggable='sEl'></tfoot>

springboot + mybatis + druid + 多数据源的问题详解

一. 简介

           俩个数据库db1,db2， db1数据库的mapper.xml和db2数据库的mapper.xml分别放到不同的目录下, 通过给不同的目录配置不同的数据源，并分别监控各自的事务。

已有新版方案：  Mybatis Plus整合多数据源和读写分离 ，请使用新版；

 二. sql脚本

           db1数据库的user表：

?

1

2

3

4

5

CREATE TABLE ` user ` (

   `id` int (11) NOT NULL ,

   ` name ` varchar (255) DEFAULT NULL ,

   PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

           db2数据库的role表：

?

1

2

3

4

5

CREATE TABLE `role` (

   `id` int (11) NOT NULL ,

   ` name ` varchar (255) DEFAULT NULL ,

   PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

三. 工程搭建

3.1 目录结构图

3.2 pom.xml文件

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

<?xml version= "1.0" encoding= "UTF-8" ?>

<project xmlns= "http://maven.apache.org/POM/4.0.0" xmlns:xsi= "http://www.w3.org/2001/XMLSchema-instance"

          xsi:schemaLocation= "http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd" >

     <modelVersion> 4.0 . 0 </modelVersion>

 

     <groupId>com.example</groupId>

     <artifactId>datasources</artifactId>

     <version> 0.0 . 1 -SNAPSHOT</version>

     <packaging>jar</packaging>

 

     <name>datasources</name>

     <description>Demo project for Spring Boot</description>

 

     <parent>

         <groupId>org.springframework.boot</groupId>

         <artifactId>spring-boot-starter-parent</artifactId>

         <version> 2.0 . 4 .RELEASE</version>

         <relativePath/>

     </parent>

 

     <properties>

         <project.build.sourceEncoding>UTF- 8 </project.build.sourceEncoding>

         <project.reporting.outputEncoding>UTF- 8 </project.reporting.outputEncoding>

         <java.version> 1.8 </java.version>

     </properties>

 

     <dependencies>

 

         <dependency>

             <groupId>org.springframework.boot</groupId>

             <artifactId>spring-boot-starter-web</artifactId>

         </dependency>

 

         <dependency>

             <groupId>org.mybatis.spring.boot</groupId>

             <artifactId>mybatis-spring-boot-starter</artifactId>

             <version> 1.3 . 2 </version>

         </dependency>

 

         <dependency>

             <groupId>mysql</groupId>

             <artifactId>mysql-connector-java</artifactId>

             <scope>runtime</scope>

         </dependency>

 

         <dependency>

             <groupId>org.springframework.boot</groupId>

             <artifactId>spring-boot-starter-test</artifactId>

             <scope>test</scope>

         </dependency>

 

         <dependency>

             <groupId>org.apache.commons</groupId>

             <artifactId>commons-lang3</artifactId>

             <version> 3.4 </version>

         </dependency>

 

         <dependency>

             <groupId>com.fasterxml.jackson.core</groupId>

             <artifactId>jackson-core</artifactId>

         </dependency>

         <dependency>

             <groupId>com.fasterxml.jackson.core</groupId>

             <artifactId>jackson-databind</artifactId>

         </dependency>

         <dependency>

             <groupId>com.fasterxml.jackson.datatype</groupId>

             <artifactId>jackson-datatype-joda</artifactId>

         </dependency>

         <dependency>

             <groupId>com.fasterxml.jackson.module</groupId>

             <artifactId>jackson-module-parameter-names</artifactId>

         </dependency>

         <dependency>

             <groupId>org.springframework.boot</groupId>

             <artifactId>spring-boot-starter-aop</artifactId>

         </dependency>

 

         <!-- 分页插件 -->

         <dependency>

             <groupId>com.github.pagehelper</groupId>

             <artifactId>pagehelper-spring-boot-starter</artifactId>

             <version> 1.2 . 5 </version>

         </dependency>

 

         <!-- alibaba的druid数据库连接池 -->

         <dependency>

             <groupId>com.alibaba</groupId>

             <artifactId>druid-spring-boot-starter</artifactId>

             <version> 1.1 . 9 </version>

         </dependency>

 

         <dependency>

             <groupId>org.springframework.boot</groupId>

             <artifactId>spring-boot-starter-thymeleaf</artifactId>

         </dependency>

 

         <dependency>

             <groupId>org.springframework.boot</groupId>

             <artifactId>spring-boot-starter-jdbc</artifactId>

         </dependency>

 

         <!-- atomikos transaction management -->

         <dependency>

             <groupId>org.springframework.boot</groupId>

             <artifactId>spring-boot-starter-jta-atomikos</artifactId>

         </dependency>

 

         <!-- swagger -->

         <dependency>

             <groupId>io.springfox</groupId>

             <artifactId>springfox-swagger2</artifactId>

             <version> 2.6 . 1 </version>

         </dependency>

         <dependency>

             <groupId>io.springfox</groupId>

             <artifactId>springfox-swagger-ui</artifactId>

             <version> 2.6 . 1 </version>

         </dependency>

         <!-- swagger -->

 

 

     </dependencies>

 

     <build>

         <plugins>

             <plugin>

                 <groupId>org.springframework.boot</groupId>

                 <artifactId>spring-boot-maven-plugin</artifactId>

             </plugin>

         </plugins>

     </build>

 

</project>

3.3 application.yml

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

server:

   port: 8080

 

 

spring:

   datasource:

     db1:

       driverClassName: com.mysql.jdbc.Driver

       username: 用户名

       password: 密码

       # spring2. 0 此处为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: 用户名

       password: 密码

       # spring2. 0 此处为jdbc-url

       jdbc-url: jdbc:mysql: //IP:3306/db2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true

       type: com.alibaba.druid.pool.DruidDataSource

 3.4 数据源配置类

3.4.1 db1数据库的数据源 （主数据源@Primary）

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

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 db2数据库的数据源

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

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 db1的UserController

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

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<User> selectUserList() {

         return this .userService.selectUserList();

     }

 

     @GetMapping ( "/save" )

     public void saveUser(User user) {

         this .userService.saveUser(user);

     }

 

 

}

3.5.2 db2的RoleController

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

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<Role> selectRoleList() {

         return this .roleService.selectRoleList();

     }

 

     @GetMapping ( "/save" )

     public void saveRole(Role role) {

         this .roleService.saveRole(role);

     }

 

 

}

3.6 Service

3.6.1 db1的UserService

?

1

2

3

4

5

6

7

8

9

10

11

12

13

package com.example.datasources.service;

 

import com.example.datasources.entity.db1.User;

 

import java.util.List;

 

public interface UserService {

 

     List<User> selectUserList();

 

     void saveUser(User user);

 

}

3.6.2 db2的RoleService

?

1

2

3

4

5

6

7

8

9

10

11

12

13

package com.example.datasources.service;

 

import com.example.datasources.entity.db2.Role;

 

import java.util.List;

 

public interface RoleService {

 

     List<Role> selectRoleList();

 

     void saveRole(Role role);

 

}

3.7 serviceImpl

3.7.1 db1的UserServiceImpl

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

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;

 

@Service

public class UserServiceImpl implements UserService {

 

     @Autowired

     private UserMapper userMapper;

 

     @Override

     public List<User> selectUserList() {

         return this .userMapper.selectUserList();

     }

 

     @Transactional

     @Override

     public void saveUser(User user) {

         this .userMapper.saveUser(user);

//        throw new RuntimeException();

     }

}

3.7.2 db2的RoleServiceImpl

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

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;

 

@Service

public class RoleServiceImpl implements RoleService {

 

     @Autowired

     private RoleMapper roleMapper;

 

     @Override

     public List<Role> selectRoleList() {

         return this .roleMapper.selectRoleList();

     }

 

     // 注：不是主数据源必须要声明其数据源，否则事务不起作用

     @Transactional (value = "db2TransactionManager" )

     @Override

     public void saveRole(Role role) {

         this .roleMapper.saveRole(role);

//        throw new RuntimeException();

     }

}

3.8 mapper

3.8.1 db1的UserMapper

?

1

2

3

4

5

6

7

8

9

10

11

12

13

package com.example.datasources.mapper.db1;

 

import com.example.datasources.entity.db1.User;

 

import java.util.List;

 

public interface UserMapper {

 

     List<User> selectUserList();

 

     void saveUser(User user);

 

}

3.8.2 db2的RoleMapper

?

1

2

3

4

5

6

7

8

9

10

11

12

package com.example.datasources.mapper.db2;

 

import com.example.datasources.entity.db2.Role;

 

import java.util.List;

 

public interface RoleMapper {

 

     List<Role> selectRoleList();

 

     void saveRole(Role role);

}

3.9 mapper.xml

3.9.1 db1的UserMapper.xml

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

<?xml version= "1.0" encoding= "UTF-8" ?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >

<mapper namespace= "com.example.datasources.mapper.db1.UserMapper" >

 

     <resultMap id= "BaseResultMap" type= "com.example.datasources.entity.db1.User" >

         <id column= "id" property= "id" jdbcType= "BIGINT" />

         <result column= "name" property= "name" jdbcType= "VARCHAR" />

     </resultMap>

 

 

     <sql id= "Base_Column_List" >

         id, `name`

     </sql>

 

 

     <select id= "selectUserList" resultMap= "BaseResultMap"   >

         SELECT

           <include refid= "Base_Column_List" />

         FROM `user`

     </select>

 

 

     <insert id= "saveUser" parameterType= "com.example.datasources.entity.db1.User" >

         INSERT INTO `user`

            (id, `name`)

         VALUES

            ( #{id}, #{name} )

     </insert>

 

</mapper>

3.9.2 db2的RoleMapper.xml

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

<?xml version= "1.0" encoding= "UTF-8" ?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >

<mapper namespace= "com.example.datasources.mapper.db2.RoleMapper" >

 

     <resultMap id= "BaseResultMap" type= "com.example.datasources.entity.db2.Role" >

         <id column= "id" property= "id" jdbcType= "BIGINT" />

         <result column= "name" property= "name" jdbcType= "VARCHAR" />

     </resultMap>

 

     <sql id= "Base_Column_List" >

         id, name

     </sql>

 

     <select id= "selectRoleList" resultMap= "BaseResultMap"   >

         SELECT

           <include refid= "Base_Column_List" />

         FROM

           role

     </select>

 

 

     <insert id= "saveRole" parameterType= "com.example.datasources.entity.db2.Role" >

         INSERT INTO `role`

            (id, `name`)

         VALUES

            ( #{id}, #{name} )

     </insert>

 

</mapper>

3.10 entity

3.10.1 db1的User

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

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 db2的Role 

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

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  启动类

?

1

2

3

4

5

6

7

8

9

10

11

12

package com.example.datasources;

 

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

 

@SpringBootApplication

public class DatasourcesApplication {

 

     public static void main(String[] args) {

         SpringApplication.run(DatasourcesApplication. class , args);

     }

}

四. 测试

      可以直接在浏览器测试，测试事务的时候可以将异常打开。

            需要注意的是： 非主数据源必须要在@Transactional注解中指定数据源，否则事务不起作用。主数据库不需要。 

到此这篇关于springboot + mybatis + druid + 多数据源的问题详解的文章就介绍到这了,更多相关springboot druid多数据源内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

原文链接：https://blog.csdn.net/qq_35206261/article/details/81778224

查看更多关于springboot + mybatis + druid + 多数据源的问题详解的详细内容...

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did211680
  阅读：13次

上一篇： Java8 HashMap遍历方式性能探讨

下一篇：Java16 JDK安装并设置环境变量的方法步骤

相关资讯