Springboot连接两个数据库

说明

一个SpringBoot项目，同时连接两个数据库：比如一个是pgsql数据库，一个是oracle数据库

（啥数据库都一样，连接两个同为oracle的数据库，或两个不同的数据库，只需要更改对应的driver-class-name和jdbc-url等即可）注意：连接什么数据库，要引入对应数据库的包

1：修改application.yml，添加一个数据库连接配置

#配置数据源两个（masterdb主数据库配置核心  devdb为从数据库配置getway）
spring:
  datasource:
    masterdb:
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: admin
      password: Passw0rd
      jdbc-url: jdbc:mysql://111.111.111.21:3306/test1?characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai
      type: com.alibaba.druid.pool.DruidDataSource
    devdb:
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: admin
      password: Passw0rd
      jdbc-url: jdbc:mysql://111.111.111.11:3306/test?characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai
      type: com.alibaba.druid.pool.DruidDataSource
  #配置rabbitMq 服务器
  rabbitmq:
    host: localhost
    port: 5672
    username: guest
    password: guest
    #虚拟host 可以不设置,使用server默认host
    # virtual-host: JCcccHost
server:
  port: 8181
  servlet:
    context-path: /tmnchpinan
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.tmnch.tmnchpinan.entity

 注意：

masterdb为主，devdb为从

2：使用代码进行数据源注入，和扫描dao层路径（以前是在yml文件里配置mybatis扫描dao的路径）

3：

（1）DBMasterConfig为主数据库DBMasterConfig.java，项目启动默认连接此数据库

package com.tmnch.tmnchpinan.config;

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: 
 *  *
 *  * @author thw
 *  * @date 2020/7/30 9:59
 *  
 */
@Configuration

@MapperScan(basePackages = "com.tmnch.tmnchpinan.dao.Master", sqlSessionTemplateRef = "MasterSqlSessionTemplate")
public class DBMasterConfig {


    @Bean(name = "MasterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.masterdb")
    @Primary
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "MasterSqlSessionFactory")
    @Primary
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("MasterDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "MasterTransactionManager")
    @Primary
    public DataSourceTransactionManager testTransactionManager(@Qualifier("MasterDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "MasterSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("MasterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

（2）第二个数据库作为从数据库  DBSlaverConfig.java

package com.tmnch.tmnchpinan.config;

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;
/**
 *  * Description: 
 *  *
 *  * @author thw
 *  * @date 2020/7/30 10:00
 *  
 */
@Configuration
@MapperScan(basePackages = "com.tmnch.tmnchpinan.dao.Slaver", sqlSessionTemplateRef = "SalverSqlSessionTemplate")
public class DBSlaverConfig {

    @Bean(name = "SalverDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.devdb")
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "SalverSqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("SalverDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "SalverTransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("SalverDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "SalverSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("SalverSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

4: 在dao文件夹下，新建UserMapper和UlossnoMapper两个包，分别放两个不同数据库的dao层文件

UserMapper.java

package com.tmnch.tmnchpinan.dao.Master;


import com.tmnch.tmnchpinan.entity.User;
import org.springframework.stereotype.Component;

@Component
public interface UserMapper {

    int insertUserInfo(User user);
    String selectUserInfo(String Priority);
}

UlossnoMapper.java

package com.tmnch.tmnchpinan.dao.Slaver;

import com.tmnch.tmnchpinan.entity.Ulossno;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

/**
 *  * Description: 
 *  *
 *  * @author thw
 *  * @date 2020/7/30 11:00
 *  
 */
@Component
public interface UlossnoMapper {
    @Transactional(value = "SalverTransactionManager")
    int insertUlossno(Ulossno user);
    @Transactional(value = "SalverTransactionManager")
    String selectUlossno(String Priority) ;
}

注意：主数据库不需要加

@Transactional(value = "SalverTransactionManager")

从数据库需要添加。

 

5；在resource下新建UlossnoMapper和UserMapper两个文件夹，分别放入对应dao层的xml文件

（我原来项目的dao的xml文件在resource目录下，你们在自己的项目对应目录下即可）

注意dao的java文件和dao的xml文件名字要一致

UlossnoMapper.xml

<?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.tmnch.tmnchpinan.dao.Slaver.UlossnoMapper">

    <insert id="insertUlossno" parameterType="com.tmnch.tmnchpinan.entity.Ulossno">
        INSERT  INTO u_lossno VALUES
        (#{id,jdbcType=INTEGER},#{CaseNo,jdbcType=VARCHAR},
		#{ccLossItemNo,jdbcType=VARCHAR},#{accidentNo,jdbcType=VARCHAR},#{lossItemSerialNo,jdbcType=VARCHAR},
		#{createTime,jdbcType=TIMESTAMP},#{status,jdbcType=VARCHAR},#{remark,jdbcType=VARCHAR},#{flag,jdbcType=VARCHAR})
    </insert>

    <select id="selectUlossno" parameterType="com.tmnch.tmnchpinan.entity.Ulossno" resultType="java.lang.String">
        select CaseNo from u_lossno where
          Priority= #{Priority,jdbcType=VARCHAR}
    </select>
</mapper>

UserMapper.java

<?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.tmnch.tmnchpinan.dao.Master.UserMapper">

    <insert id="insertUserInfo" parameterType="com.tmnch.tmnchpinan.entity.User">
        INSERT  INTO a_group_user VALUES
        (#{Id,jdbcType=VARCHAR},#{GroupCode,jdbcType=VARCHAR},
		#{UserCode,jdbcType=VARCHAR},#{UserName,jdbcType=VARCHAR},#{GroupName,jdbcType=VARCHAR},
		#{Priority,jdbcType=VARCHAR},#{DutyState,jdbcType=VARCHAR})
    </insert>

    <select id="selectUserInfo" parameterType="com.tmnch.tmnchpinan.entity.User" resultType="java.lang.String">
        select UserCode from a_group_user where
            Priority= #{Priority,jdbcType=VARCHAR}
    </select>
</mapper>

6:测试

实体类代码：

Ulossno.java

package com.tmnch.tmnchpinan.entity;

import java.util.Date;

/**
 *  * Description: 
 *  *
 *  * @author thw
 *  * @date 2020/7/30 10:58
 *  
 */
public class Ulossno {
    private int id;
    private String CaseNo;
    private String ccLossItemNo;
    private String accidentNo;
    private String lossItemSerialNo;
    private Date createTime;
    private String status;
    private String remark;
    private String flag;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getCaseNo() {
        return CaseNo;
    }

    public void setCaseNo(String caseNo) {
        CaseNo = caseNo;
    }

    public String getCcLossItemNo() {
        return ccLossItemNo;
    }

    public void setCcLossItemNo(String ccLossItemNo) {
        this.ccLossItemNo = ccLossItemNo;
    }

    public String getAccidentNo() {
        return accidentNo;
    }

    public void setAccidentNo(String accidentNo) {
        this.accidentNo = accidentNo;
    }

    public String getLossItemSerialNo() {
        return lossItemSerialNo;
    }

    public void setLossItemSerialNo(String lossItemSerialNo) {
        this.lossItemSerialNo = lossItemSerialNo;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }

    public String getFlag() {
        return flag;
    }

    public void setFlag(String flag) {
        this.flag = flag;
    }
}

User.java

package com.tmnch.tmnchpinan.entity;

/**
 *  * Description: 
 *  *
 *  * @author thw
 *  * @date 2020/7/30 10:58
 *  
 */
public class User {


    private String Id;
    private String GroupCode;
    private String UserCode;
    private String UserName;
    private String GroupName;
    private String Priority;
    private String DutyState;

    public String getId() {
        return Id;
    }

    public void setId(String id) {
        this.Id = id;
    }

    public String getGroupCode() {
        return GroupCode;
    }

    public void setGroupCode(String groupCode) {
        GroupCode = groupCode;
    }

    public String getUserCode() {
        return UserCode;
    }

    public void setUserCode(String userCode) {
        UserCode = userCode;
    }

    public String getUserName() {
        return UserName;
    }

    public void setUserName(String userName) {
        UserName = userName;
    }

    public String getGroupName() {
        return GroupName;
    }

    public void setGroupName(String groupName) {
        GroupName = groupName;
    }

    public String getPriority() {
        return Priority;
    }

    public void setPriority(String priority) {
        Priority = priority;
    }

    public String getDutyState() {
        return DutyState;
    }

    public void setDutyState(String dutyState) {
        DutyState = dutyState;
    }
}

控制层代码

UlossnoController.java

package com.tmnch.tmnchpinan.controller;

import com.tmnch.tmnchpinan.service.UlossnoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
public class UlossnoController {
    @Autowired
    private UlossnoService ulossnoService;

    //上传用户信息
    @GetMapping("/AddUlossno")
    @ResponseBody
    public int AddUserInfo(@RequestParam("userID")String userID,
                           @RequestParam("userName")String userName,
                           @RequestParam("userAge")String userAge){

        int res = ulossnoService.AddUserInfo(userID,userName,userAge);
        return res;
    }

    //上传用户信息
    @GetMapping("/selectUlossno")
    @ResponseBody
    public String selectUserInfo(@RequestParam("Priority")String Priority){

        String res = ulossnoService.selectUserInfo(Priority);
        return res;
    }


}

UserController.java

package com.tmnch.tmnchpinan.controller;

import com.tmnch.tmnchpinan.*;
import com.tmnch.tmnchpinan.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

@Controller
public class UserController {
    @Autowired
    private UserService userService;

    //上传用户信息
    @GetMapping("/AddUserInfo")
    @ResponseBody
    public int AddUserInfo(@RequestParam("userID")String userID,
                           @RequestParam("userName")String userName,
                           @RequestParam("userAge")String userAge){

        int res = userService.AddUserInfo(userID,userName,userAge);
        return res;
    }

    //上传用户信息
    @GetMapping("/selectUserInfo")
    @ResponseBody
    public String selectUserInfo(@RequestParam("Priority")String Priority){

        String res = userService.selectUserInfo(Priority);
        return res;
    }


}

直接启动项目即可。