springboot中mysql数据库集群 springboot mybatis多数据库

转载

IT剑客风云 2023-10-10 13:12:11

文章标签 数据库 spring boot mybatis java spring 文章分类 MySQL 数据库

一、简介

近年来，由于项目结构越来越大，并且经常会用到不同的数据库以满足项目的实际需求。本节就配置多数据源进行记录

二、准备条件

1.框架：Springboot+MyBatis

2.数据库：mysql的两个数据库及各自一张表（为了演示简单及结构明显）

1.(db1)kings库的tb_user表
2.(db2)peace库的tb_users表
3.下边是tb_user的创建，tb_users一样，这是表名不一样

CREATE TABLE tb_user (
id int(11) NOT NULL auto_increment COMMENT ‘id’,
name varchar(20) NOT NULL default ‘’ COMMENT ‘名字’,
age int(11) NOT NULL default ‘0’ COMMENT ‘年纪’,
password varchar(225) NOT NULL default ‘’ COMMENT ‘密码’,
 PRIMARY KEY (id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.先看项目结构图如下

springboot中mysql数据库集群 springboot mybatis多数据库_java

三、实现

1.创建Springboot项目boot，导入核心依赖

<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>${mybatis.version}</version>
</dependency>

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>${mysql.version}</version>
</dependency>

2.修改全局配置文件application.yml

注意：此处使用的是jdbc-url，不是url。

因为连接池的实际类型没有被公开，所以在您的自定义数据源的元数据中没有生成密钥，而且在IDE中没有完成(因为DataSource接口没有暴露属性)。另外，如果您碰巧在类路径上有Hikari，那么这个基本设置就不起作用了，因为Hikari没有url属性(但是确实有一个jdbcUrl属性)。在这种情况下，您必须重写您的配置如下

spring:
  datasource:
    db1:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/kings?allowPublicKeyRetrieval=true&useSSL=false&characterEncoding=utf8
      username: root
      password: mysql
    db2:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/peace?allowPublicKeyRetrieval=true&useSSL=false&characterEncoding=utf8
      username: root
      password: mysql

3.对数据源进行创建配置

1.创建datasource包
2.分别创建DataSource1.java和DataSource2.java进行管理配置数据源db1和db2
1.DataSource1.java

package com.test.boot.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;
/**
* @Description:    db1的数据源配置
* @Author:         Joe
* @CreateDate:     2020/4/21 15:15
*/
@Configuration //注册到spring容器中
@MapperScan(basePackages = "com.test.boot.db1.mapper",sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DataSource1 {

    /**
     * 配置db1数据库
     * @return
     */
    @Bean(name = "db1Datasource")
    @ConfigurationProperties(prefix = "spring.datasource.db1")//获取全局配置文件中数据
    @Primary //指定默认数据源
    public DataSource testDatasource(){
        DataSource build = DataSourceBuilder.create().build();
        return build;
    }

    /**
     * 创建SqlSessionFactory
     * @param dataSource
     * @return
     */
    @Bean(name = "db1SqlSessionFactory")
    @Primary
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("db1Datasource")DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //TbUserMapper.xml的位置;如果使用的是注解sql的话可以省去此行代码
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:db1/mapper/*.xml"));
        return bean.getObject();
    }

    /**
     * 配置事务管理
     * @param dataSource
     * @return
     */
    @Bean(name = "db1TransactionManager")
    @Primary
    public DataSourceTransactionManager testTransactionManager(@Qualifier("db1Datasource")DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * 封装数据库操作，线程安全的
     * @param sqlSessionFactory
     * @return
     */
    @Bean(name = "db1SqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("db1SqlSessionFactory")SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

2.DataSource2.java

package com.test.boot.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 //注册到spring容器中
@MapperScan(basePackages = "com.test.boot.db2.mapper",sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSource2 {

    /**
     * 配置db2数据库
     * @return
     */
    @Bean(name = "db2Datasource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")//获取全局配置文件中数据
    public DataSource testDatasource(){
        DataSource build = DataSourceBuilder.create().build();
        return build;
    }

    /**
     * 创建SqlSessionFactory
     * @param dataSource
     * @return
     */
    @Bean(name = "db2SqlSessionFactory")
    @Primary
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("db2Datasource")DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //TbUsersMapper.xml的位置;如果使用的是注解sql的话可以省去此行代码
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:db2/mapper/*.xml"));
        return bean.getObject();
    }

    /**
     * 配置事务管理
     * @param dataSource
     * @return
     */
    @Bean(name = "db2TransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("db2Datasource")DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * 封装数据库操作，线程安全的
     * @param sqlSessionFactory
     * @return
     */
    @Bean(name = "db2SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("db2SqlSessionFactory")SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

3.注意：DataSource1.java的每个方法必须加 @Primary注解意为主、默认数据源

4.使用mybatis逆向工程生成实体类、mapper、mapper.xml(过程略去)

5.在对应mapper接口上添加对应数据源注解

注意

1.mapper下的方法请忽略

2.TbUserMapper.java对应的是DataSource1.java
@Qualifier(“db1SqlSessionFactory”)指向DataSource1.java的sqlSessionFactoryRef命名

springboot中mysql数据库集群 springboot mybatis多数据库_java_02

springboot中mysql数据库集群 springboot mybatis多数据库_数据库_03

3.TbUsersMapper.java对应的是DataSource2.java

@Qualifier(“db2SqlSessionFactory”)指向DataSource2.java的sqlSessionFactoryRef命名

springboot中mysql数据库集群 springboot mybatis多数据库_spring boot_04

springboot中mysql数据库集群 springboot mybatis多数据库_mybatis_05

6.创建对应service与impl进行测试准备工作

1.UsersService.java

package com.test.boot.db2.service;

import com.test.boot.entity.TbUsers;

public interface UsersService {

    void addUser(TbUsers user);
}

2.UsersServiceImpl.java

package com.test.boot.db2.service.impl;

import com.test.boot.db2.mapper.TbUsersMapper;
import com.test.boot.db2.service.UsersService;
import com.test.boot.entity.TbUsers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UsersServiceImpl implements UsersService {
    @Autowired
    private TbUsersMapper usersMapper;
    @Override
    public void addUser(TbUsers user) {
        usersMapper.insertSelective(user);
    }
}

3.UsersService.java

package com.test.boot.db2.service;

import com.test.boot.entity.TbUsers;

public interface UsersService {

    void addUser(TbUsers user);
}

4.UsersServiceImpl.java

package com.test.boot.db2.service.impl;

import com.test.boot.db2.mapper.TbUsersMapper;
import com.test.boot.db2.service.UsersService;
import com.test.boot.entity.TbUsers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UsersServiceImpl implements UsersService {
    @Autowired
    private TbUsersMapper usersMapper;
    @Override
    public void addUser(TbUsers user) {
        usersMapper.insertSelective(user);
    }
}

7.创建controller进行测试

IndexController.java

package com.test.boot.controller;

import com.test.boot.db1.service.UserService;
import com.test.boot.db2.service.UsersService;
import com.test.boot.entity.TbUser;
import com.test.boot.entity.TbUsers;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

@RestController
@Slf4j
public class IndexController {

    @Autowired
    private UserService userService;
    @Autowired
    private UsersService usersService;

    @RequestMapping(value = "/addUser",method = RequestMethod.GET)
    public String addUser(){
        TbUser user1 = new TbUser();
        user1.setName("joe");
        user1.setAge(25);
        user1.setPassword("111111");
        userService.saveUser(user1);
        log.info("db1的");
        return "ok";
    }

    @RequestMapping(value = "/addUsers",method = RequestMethod.GET)
    public String addUsers(){
        TbUsers user1 = new TbUsers();
        user1.setName("an");
        user1.setAge(23);
        user1.setPassword("111111");
        usersService.addUser(user1);
        log.info("db2的");
        return "ok";
    }
}