个人随笔
目录
springboot-mybatis多数据配置
2022-06-08 22:48:20

在我们项目访问人数比较多,数据量比较大的情况下,我们可能需要使用多数据源的情况,将一部分数据放到另一个数据库中,比如数据库主从同步,我们项目可以写主库,读从库,降低单个数据库的压力,提高程序的性能。接下来简单搭建一下springboot+mybatis的多数据环境。

目的

在同一个项目中集成两个数据源,可以通过程序控制指定访问的数据库。

环境

框架:springboot+mybatis
数据库:mysql
连接池:Druid

搭建步骤

1、springboot+mybatis项目

项目结构如下图

pom.xml
  1. <parent>
  2. <groupId>org.springframework.boot</groupId>
  3. <artifactId>spring-boot-starter-parent</artifactId>
  4. <version>2.0.5.RELEASE</version>
  5. </parent>
  6. <dependencies>
  7. <dependency>
  8. <groupId>org.springframework.boot</groupId>
  9. <artifactId>spring-boot-starter-web</artifactId>
  10. </dependency>
  11. <!-- 数据库操作 -->
  12. <dependency>
  13. <groupId>mysql</groupId>
  14. <artifactId>mysql-connector-java</artifactId>
  15. </dependency>
  16. <dependency>
  17. <groupId>org.mybatis.spring.boot</groupId>
  18. <artifactId>mybatis-spring-boot-starter</artifactId>
  19. <version>2.1.4</version>
  20. </dependency>
  21. <dependency>
  22. <groupId>com.alibaba</groupId>
  23. <artifactId>druid-spring-boot-starter</artifactId>
  24. <version>1.1.16</version>
  25. </dependency>
  26. </dependencies>
application.yml
  1. ###服务启动端口号
  2. server:
  3. port: 80
  4. spring:
  5. #durid
  6. datasource:
  7. one:
  8. type: com.alibaba.druid.pool.DruidDataSource
  9. url: jdbc:mysql://192.168.157.6:3306/test?useSSL=false
  10. #开发环境
  11. username: root
  12. password: 123456
  13. driverClassName: com.mysql.jdbc.Driver
  14. #连接池的配置信息
  15. initialSize: 5
  16. minIdle: 5
  17. maxActive: 20
  18. maxWait: 60000
  19. timeBetweenEvictionRunsMillis: 60000
  20. minEvictableIdleTimeMillis: 300000
  21. validationQuery: SELECT 1 FROM DUAL
  22. testWhileIdle: true
  23. testOnBorrow: false
  24. testOnReturn: false
  25. poolPreparedStatements: true
  26. maxPoolPreparedStatementPerConnectionSize: 20
  27. two:
  28. type: com.alibaba.druid.pool.DruidDataSource
  29. url: jdbc:mysql://192.168.157.7:3306/test?useSSL=false
  30. username: root
  31. password: 123456
  32. driverClassName: com.mysql.jdbc.Driver
  33. #连接池的配置信息
  34. initialSize: 5
  35. minIdle: 5
  36. maxActive: 20
  37. maxWait: 60000
  38. timeBetweenEvictionRunsMillis: 60000
  39. minEvictableIdleTimeMillis: 300000
  40. validationQuery: SELECT 1 FROM DUAL
  41. testWhileIdle: true
  42. testOnBorrow: false
  43. testOnReturn: false
  44. poolPreparedStatements: true
  45. maxPoolPreparedStatementPerConnectionSize: 20

上面是用one和two来区分两个数据源,一个是192.168.157.6服务器一个是192.168.157.7服务器

2、DataSourcesConfig.java

spring容器中建立DataSource

  1. @Configuration
  2. public class DataSourcesConfig {
  3. @Bean
  4. @ConfigurationProperties(prefix = "spring.datasource.one")
  5. DataSource dsOne(){
  6. return DruidDataSourceBuilder.create().build();
  7. }
  8. @Bean
  9. @ConfigurationProperties(prefix = "spring.datasource.two")
  10. DataSource dsTwo(){
  11. return DruidDataSourceBuilder.create().build();
  12. }
  13. }

3、MyBatisConfigOne.java

  1. @Configuration
  2. @MapperScan(basePackages = "com.suibibk.mapper.mapper1",
  3. sqlSessionFactoryRef = "sqlSessionFactory1",
  4. sqlSessionTemplateRef = "sqlSessionTemplate1")
  5. public class MyBatisConfigOne {
  6. @Resource
  7. DataSource dsOne;
  8. @Bean
  9. SqlSessionFactory sqlSessionFactory1()throws Exception {
  10. SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
  11. bean.setDataSource(dsOne);
  12. bean.setMapperLocations(new PathMatchingResourcePatternResolver().
  13. getResources("classpath:mapper/mapper1/*.xml"));
  14. return bean.getObject();
  15. }
  16. @Bean
  17. SqlSessionTemplate sqlSessionTemplate1() throws Exception{
  18. return new SqlSessionTemplate(sqlSessionFactory1());
  19. }
  20. }

指定了扫包路径以及xml路径,当然如果是将sql直接写在mapper里面,不用xml配置文件的话可以不指定内容

  1. bean.setMapperLocations(new PathMatchingResourcePatternResolver().
  2. getResources("classpath:mapper/mapper1/*.xml"));

4、MyBatisConfigTwo.java

  1. @Configuration
  2. @MapperScan(basePackages = "com.suibibk.mapper.mapper2",
  3. sqlSessionFactoryRef = "sqlSessionFactory2",
  4. sqlSessionTemplateRef = "sqlSessionTemplate2")
  5. public class MyBatisConfigTwo {
  6. @Resource
  7. DataSource dsTwo;
  8. @Bean
  9. SqlSessionFactory sqlSessionFactory2()throws Exception {
  10. SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
  11. bean.setDataSource(dsTwo);
  12. bean.setMapperLocations(new PathMatchingResourcePatternResolver().
  13. getResources("classpath:mapper/mapper2/*.xml"));
  14. return bean.getObject();
  15. }
  16. @Bean
  17. SqlSessionTemplate sqlSessionTemplate2() throws Exception{
  18. return new SqlSessionTemplate(sqlSessionFactory2());
  19. }
  20. }

5、UserMapper1.java

  1. public interface UserMapper1 {
  2. //@Select("select * from user")
  3. List<User> getUsers();
  4. }

6、UserMapper2.java

  1. public interface UserMapper2 {
  2. //@Select("select * from user")
  3. List<User> getUsers();
  4. }

如果在这里面写sql的话,那么MyBatisConfig就不需要指定xml位置。

7、UserMapper1.xml

  1. <mapper namespace="com.suibibk.mapper.mapper1.UserMapper1" >
  2. <select id="getUsers" resultType="com.suibibk.entity.User">
  3. select * from user
  4. </select>
  5. </mapper>

8、UserMapper2.xml

  1. <mapper namespace="com.suibibk.mapper.mapper2.UserMapper2" >
  2. <select id="getUsers" resultType="com.suibibk.entity.User">
  3. select * from user
  4. </select>
  5. </mapper>

9、service

  1. public interface UserService {
  2. List<User> getUsers1();
  3. List<User> getUsers2();
  4. }
  5. @Service
  6. public class UserServiceImpl implements UserService{
  7. @Autowired
  8. UserMapper1 userMapper1;
  9. @Autowired
  10. UserMapper2 userMapper2;
  11. public List<User> getUsers1() {
  12. return userMapper1.getUsers();
  13. }
  14. public List<User> getUsers2() {
  15. return userMapper2.getUsers();
  16. }
  17. }

10、controller

  1. /**
  2. * http://localhost/test1 访问数据源1
  3. * http://localhost/test2 访问数据源2
  4. */
  5. @Controller
  6. public class TestController {
  7. @Autowired
  8. UserService userService;
  9. @ResponseBody
  10. @RequestMapping("/test1")
  11. public String test1() {
  12. List<User> users = userService.getUsers1();
  13. return users.toString();
  14. }
  15. @ResponseBody
  16. @RequestMapping("/test2")
  17. public String test2() {
  18. List<User> users = userService.getUsers2();
  19. return users.toString();
  20. }
  21. }

11、User.java

  1. public class User {
  2. private String name;
  3. private String passwd;
  4. public String getName() {
  5. return name;
  6. }
  7. public void setName(String name) {
  8. this.name = name;
  9. }
  10. public String getPasswd() {
  11. return passwd;
  12. }
  13. public void setPasswd(String passwd) {
  14. this.passwd = passwd;
  15. }
  16. @Override
  17. public String toString() {
  18. return "User [name=" + name + ", passwd=" + passwd + "]";
  19. }
  20. }

12、启动App.java

  1. @SpringBootApplication
  2. public class App {
  3. public static void main(String[] args) {
  4. SpringApplication.run(App.class, args);
  5. }
  6. }

测试

启动后访问

  1. http://localhost/test1
  2. http://localhost/test2

可以看到test1查询到的是192.168.157.6服务器数据库的数据,test2查询到的是192.168.157.7服务器的数据

 18

啊!这个可能是世界上最丑的留言输入框功能~


当然,也是最丑的留言列表

有疑问发邮件到 : suibibk@qq.com 侵权立删
Copyright : 个人随笔   备案号 : 粤ICP备18099399号