springboot + mybatis+多数据源配置-CSDN博客

本文链接：https://blog.csdn.net/qq_19550657/article/details/86588824

此示例支持多种数据类型的数据源。

思路：

1.springboot默认配置是单数据源的，Mapper也是自动绑定到数据源的。所以要实现多数据源，必须先禁用掉springboot的默认配置。然后实现自定义的数据源，Mapper也需要自定义绑定数据源。

2.关于事务，对于跨数据源的事务，最好根据实际业务，通过不同数据源Service组合和实现。

3.同一个service方法只能用一个事务管理器，多个事务管理器@Transactional(rollbackFor = Exception.class,propagation=Propagation.REQUIRED,value="db1TxManager")，需要指定事务管理器。

4.同一个service方法里面可以从N个数据源里面查询数据，但是增删改的操作只能保持在一个数据源进行。不同的数据源增删改用

不同的service方法。

以下是代码

HelloController.java

package com.xxh.controller;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.xxh.service.HelloService;

@Controller
public class HelloController {

   private static Logger logger=LoggerFactory.getLogger(HelloController.class);
   @Autowired
   private HelloService helloService;

   @RequestMapping(value="/hello",method=RequestMethod.GET)
   @ResponseBody
   public String hello() {
       logger.debug("come into controller");
       return helloService.sayHello();
   }

}

HelloDB1Mapper .java

package com.xxh.db1.mapper;

import com.xxh.entity.HelloBean;

public interface HelloDB1Mapper {

   int deleteByPrimaryKey(String id);

int insertSelective(HelloBean record);

HelloBean selectByPrimaryKey(String id);

int updateByPrimaryKeySelective(HelloBean record);

int updateByPrimaryKey(HelloBean record);

}

HelloDB2Mapper .java

package com.xxh.db2.mapper;

import com.xxh.entity.HelloBean;

public interface HelloDB2Mapper {

   int deleteByPrimaryKey(String id);

int insertSelective(HelloBean record);

HelloBean selectByPrimaryKey(String id);

int updateByPrimaryKeySelective(HelloBean record);

int updateByPrimaryKey(HelloBean record);

}

DB1Config

package com.xxh.dbconfig;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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 com.alibaba.druid.pool.DruidDataSource;

@Configuration
@MapperScan(basePackages = DB1Config.BASE_PACKAGE, sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DB1Config {
// 精确到 master 目录，以便跟其他数据源隔离
static final String BASE_PACKAGE = "com.xxh.db1.mapper";
static final String MAPPER_LOCATION = "classpath:db1mapper/*.xml";

@Value("${db1.datasource.url}")
private String url;

@Value("${db1.datasource.username}")
private String user;

@Value("${db1.datasource.password}")
private String password;

@Value("${db1.datasource.driverClassName}")
private String driverClass;

@Bean(name = "db1DataSource")
public DataSource db1DataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}

@Bean(name = "db1TransactionManager")
public DataSourceTransactionManager db1TransactionManager() {
return new DataSourceTransactionManager(db1DataSource());
}

@Bean(name = "db1SqlSessionFactory")
public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource db1DataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(db1DataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(DB1Config.MAPPER_LOCATION));
return sessionFactory.getObject();

}
}

DB2Config.java

package com.xxh.dbconfig;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSource;

@Configuration
@MapperScan(basePackages = DB2Config.BASE_PACKAGE, sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DB2Config {
// 精确到 master 目录，以便跟其他数据源隔离
static final String BASE_PACKAGE = "com.xxh.db2.mapper";
static final String MAPPER_LOCATION = "classpath:db2mapper/*.xml";

@Value("${db2.datasource.url}")
private String url;

@Value("${db2.datasource.username}")
private String user;

@Value("${db2.datasource.password}")
private String password;

@Value("${db2.datasource.driverClassName}")
private String driverClass;

@Bean(name = "db2DataSource")
public DataSource db2DataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}

@Bean(name = "db2TransactionManager")
public DataSourceTransactionManager db2TransactionManager() {
return new DataSourceTransactionManager(db2DataSource());
}

@Bean(name = "db2SqlSessionFactory")
public SqlSessionFactory db2SqlSessionFactory(@Qualifier("db2DataSource") DataSource db2DataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(db2DataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(DB2Config.MAPPER_LOCATION));
return sessionFactory.getObject();

}
}

HelloBean .java

package com.xxh.entity;

public class HelloBean {

private String id;

private String name;

private String code;

public String getId() {
return id;
}

public void setId(String id) {
this.id = id == null ? null : id.trim();
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name == null ? null : name.trim();
}

public String getCode() {
return code;
}

public void setCode(String code) {
this.code = code == null ? null : code.trim();
}

   @Override
   public String toString() {
       return "HelloBean [id=" + id + ", name=" + name + ", code=" + code
               + "]";
   }

}

HelloService .java

package com.xxh.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.xxh.db1.mapper.HelloDB1Mapper;
import com.xxh.db2.mapper.HelloDB2Mapper;
import com.xxh.entity.HelloBean;

@Service("helloService")
public class HelloService {

   @Autowired
   private HelloDB1Mapper helloDB1Mapper;

   @Autowired
   private HelloDB2Mapper helloDB2Mapper;

   public String sayHello() {
       HelloBean org1=helloDB1Mapper.selectByPrimaryKey("26668");
       HelloBean org2=helloDB2Mapper.selectByPrimaryKey("26669");
       return org1.toString()+org2.toString();
   }

}

App .java

package com.xxh.springboot_multidatasource;

import org.mybatis.spring.boot.autoconfigure.MybatisAutoConfiguration;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;

/**
* Hello world!
*
*/
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class,MybatisAutoConfiguration.class})
@ComponentScan("com.xxh.controller,com.xxh.service,com.xxh.dbconfig")
public class App
{
public static void main( String[] args )
{
SpringApplication.run(App.class, args);
}
}

HelloDB1Mapper.xml 位于src/main/resources/db1mapper/

<?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.xxh.db1.mapper.HelloDB1Mapper" >
<resultMap id="BaseResultMap" type="com.xxh.entity.HelloBean" >
<id column="ID" property="id" jdbcType="VARCHAR" />
<result column="CODE" property="code" jdbcType="VARCHAR" />
<result column="NAME" property="name" jdbcType="VARCHAR" />
</resultMap>

<sql id="Base_Column_List" >
ID,CODE,NAME
</sql>

<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" >
select
<include refid="Base_Column_List" />
from db1_hello_table
where ID = #{id,jdbcType=VARCHAR}
</select>

<delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
delete from db1_hello_table
where ID = #{id,jdbcType=VARCHAR}
</delete>

<insert id="insertSelective" parameterType="com.xxh.entity.HelloBean" >
insert into db1_hello_table
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
ID,
</if>
<if test="name != null" >
NAME,
</if>
<if test="code != null" >
CODE,
</if>

</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=VARCHAR},
</if>
<if test="name != null" >
#{name,jdbcType=VARCHAR},
</if>
<if test="code != null" >
#{code,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.xxh.entity.HelloBean" >
update db1_hello_table
<set >
<if test="name != null" >
NAME = #{name,jdbcType=VARCHAR},
</if>
<if test="code != null" >
CODE = #{code,jdbcType=VARCHAR},
</if>
</set>
where ID = #{id,jdbcType=VARCHAR}
</update>
<update id="updateByPrimaryKey" parameterType="com.xxh.entity.HelloBean" >
update db1_hello_table
set
NAME = #{name,jdbcType=VARCHAR},
CODE = #{code,jdbcType=VARCHAR}
where ID = #{id,jdbcType=VARCHAR}
</update>


</mapper>

HelloDB2Mapper.xml 位于src/main/resources/db2mapper/

<?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.xxh.db2.mapper.HelloDB2Mapper" >
<resultMap id="BaseResultMap" type="com.xxh.entity.HelloBean" >
<id column="ID" property="id" jdbcType="VARCHAR" />
<result column="CODE" property="code" jdbcType="VARCHAR" />
<result column="NAME" property="name" jdbcType="VARCHAR" />
</resultMap>

<sql id="Base_Column_List" >
ID,CODE,NAME
</sql>

<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" >
select
<include refid="Base_Column_List" />
from db2_hello_table
where ID = #{id,jdbcType=VARCHAR}
</select>

<delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
delete from db2_hello_table
where ID = #{id,jdbcType=VARCHAR}
</delete>

<insert id="insertSelective" parameterType="com.xxh.entity.HelloBean" >
insert into db2_hello_table
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
ID,
</if>
<if test="name != null" >
NAME,
</if>
<if test="code != null" >
CODE,
</if>

</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=VARCHAR},
</if>
<if test="name != null" >
#{name,jdbcType=VARCHAR},
</if>
<if test="code != null" >
#{code,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.xxh.entity.HelloBean" >
update db2_hello_table
<set >
<if test="name != null" >
NAME = #{name,jdbcType=VARCHAR},
</if>
<if test="code != null" >
CODE = #{code,jdbcType=VARCHAR},
</if>
</set>
where ID = #{id,jdbcType=VARCHAR}
</update>
<update id="updateByPrimaryKey" parameterType="com.xxh.entity.HelloBean" >
update db2_hello_table
set
NAME = #{name,jdbcType=VARCHAR},
CODE = #{code,jdbcType=VARCHAR}
where ID = #{id,jdbcType=VARCHAR}
</update>


</mapper>

application.properties

server.port=14000

db1.datasource.driverClassName=your_driver_class_name
db1.datasource.url = your_datasource_url
db1.datasource.username = your_username
db1.datasource.password = your_userpasswd

db2.datasource.driverClassName=your_driver_class_name
db2.datasource.url = your_datasource_url
db2.datasource.username = your_username
db2.datasource.password = your_userpasswd

logback.xml

<?xml version="1.0"?>
<configuration scan="true" scanPeriod="60 seconds" debug="false">

<property name="rootPath" value="${catalina.base}/logs" />
   
   <appender name="app_debugAppender" class="ch.qos.logback.core.rolling.RollingFileAppender">
<File>${rootPath}/app_debug.log</File>
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<FileNamePattern>
   ${rootPath}/app_debug-%d{yyyy-MM-dd}-%i.log.gz
</FileNamePattern>
<MaxHistory>31</MaxHistory>
<TimeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
   <MaxFileSize>100MB</MaxFileSize>
</TimeBasedFileNamingAndTriggeringPolicy>
</rollingPolicy>
<filter class="ch.qos.logback.classic.filter.LevelFilter">
<level>DEBUG</level>
<onMatch>ACCEPT</onMatch>
<onMismatch>DENY</onMismatch>
</filter>
<layout class="ch.qos.logback.classic.PatternLayout">
<pattern>
   [%d{yyyy-MM-dd HH:mm:ss.SSS}] [%t] [%-5p] %logger{36} | %msg%n
</pattern>
</layout>
</appender>


<appender name="app_errorAppender" class="ch.qos.logback.core.rolling.RollingFileAppender">
<File>${rootPath}/app_error.log</File>
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<FileNamePattern>
   ${rootPath}/app_error-%d{yyyy-MM-dd}-%i.log.gz
</FileNamePattern>
<MaxHistory>31</MaxHistory>
<TimeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
   <MaxFileSize>100MB</MaxFileSize>
</TimeBasedFileNamingAndTriggeringPolicy>
</rollingPolicy>
<filter class="ch.qos.logback.classic.filter.LevelFilter">
<level>ERROR</level>
<onMatch>ACCEPT</onMatch>
<onMismatch>DENY</onMismatch>
</filter>
<layout class="ch.qos.logback.classic.PatternLayout">
<pattern>
   [%d{yyyy-MM-dd HH:mm:ss.SSS}] [%t] [%-5p] %logger{36} | %msg %n
</pattern>
</layout>
</appender>


   <appender name="consoleAppender" class="ch.qos.logback.core.ConsoleAppender">
       <encoder>
           <pattern>
               [%d{yyyy-MM-dd HH:mm:ss.SSS}] [%t] [%-5p] %logger{36} | %msg%n
           </pattern>
       </encoder>
   </appender>

   
   <logger name="com.xxh.controller" additivity="false">
       <level value="ALL" />
       <appender-ref ref="app_debugAppender"/>
       <appender-ref ref="app_errorAppender"/>
       <appender-ref ref="consoleAppender" />
   </logger>

   
   <root>
       <level value="DEBUG" />
       <appender-ref ref="consoleAppender" />
   </root>

</configuration>