springboot使用mybatis如何配置多数据源

最新推荐文章于 2023-09-20 16:27:26 发布

大树叶

最新推荐文章于 2023-09-20 16:27:26 发布

阅读量232

点赞数

分类专栏： sprint boot java 文章标签：动态多数据源 spring boot MapperScan sqlSessionFactoryRef

本文链接：https://blog.csdn.net/bigtree_3721/article/details/89426320

版权

java 同时被 2 个专栏收录

494 篇文章 1 订阅

订阅专栏

sprint boot

11 篇文章 0 订阅

订阅专栏

经过本人尝试，这有两种方法都可以work.

方法一：注释法

https://blog.csdn.net/weixin_41349389/article/details/84291023

方法二：动态指定法

https://www.cnblogs.com/java-zhao/p/5413845.html

另外，如果使用transaction的话，要看看这个文章：

SpringBoot 使用 @Transactional 注解配置事务

注意：如果要为多个数据源使用TransactionManager的话，则要用方法1！方法2有坑，导致很难搞定无法切换数据源的问题！

下面就用一个实际的例子来说明如何在多数据源中，在注释法环境下使用transaction。

package com.yyy.rentcar.crm.migration.config;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.util.*;


/**
 * springboot集成mybatis的基本入口
 * 1）创建数据源(如果采用的是默认的tomcat-jdbc数据源，则不需要)
 * 2）创建SqlSessionFactory
 * 3）配置事务管理器，除非需要使用事务，否则不用配置
 */
// 该注解类似于spring bean 配置文件
@Configuration
@MapperScan(basePackages = "com.yyyy.mapper.crm", sqlSessionFactoryRef = "crmSqlSessionFactory")
public class CRMDSConfig {

    @Autowired
    private Environment env;

    /**
     * 创建数据源(数据源的名称：方法名可以取为myXXXDataSource()
     */
    @Bean
    public DataSource myCRMDataSource() throws Exception {
        Properties props = new Properties();
        props.put("driverClassName", env.getProperty("spring.datasource.crm.driverClassName"));
        props.put("url", env.getProperty("spring.datasource.crm.url"));
        props.put("username", env.getProperty("spring.datasource.crm.username"));
        props.put("password", env.getProperty("spring.datasource.crm.password"));
        return DruidDataSourceFactory.createDataSource(props);
    }

    /**
     * 根据数据源创建SqlSessionFactory
     */
    @Bean(name = "crmSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("myCRMDataSource") DataSource usersDataSource) throws Exception {
        SqlSessionFactoryBean fb = new SqlSessionFactoryBean();
        fb.setDataSource(usersDataSource);// 指定数据源(这个必须有，否则报错)
       // org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
       //  configuration.setMapUnderscoreToCamelCase(true);
       // fb.setConfiguration(configuration);

        return fb.getObject();
    }

    /**
     * 配置事务管理器
     */
    @Bean(name = "crmTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("myCRMDataSource") DataSource myds) {
        return new DataSourceTransactionManager(myds);
    }

}

package com.yyy.rentcar.crm.migration.config;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.util.Properties;

/**
 * springboot集成mybatis的基本入口
 * 1）创建数据源(如果采用的是默认的tomcat-jdbc数据源，则不需要)
 * 2）创建SqlSessionFactory
 * 3）配置事务管理器，除非需要使用事务，否则不用配置
 */
// 该注解类似于spring bean 配置文件
@Configuration
@MapperScan(basePackages = "com.yyy.mapper.usercenter", sqlSessionFactoryRef = "userCentersqlSessionFactory")
public class UserCenterDSConfig {

    @Autowired
    private Environment env;

    /**
     * 创建数据源(数据源的名称：方法名可以取为myXXXDataSource()
     */
    @Bean
    public DataSource myUserCenterDataSource() throws Exception {
        Properties props = new Properties();
        props.put("driverClassName", env.getProperty("spring.datasource.usercenter.driverClassName"));
        props.put("url", env.getProperty("spring.datasource.usercenter.url"));
        props.put("username", env.getProperty("spring.datasource.usercenter.username"));
        props.put("password", env.getProperty("spring.datasource.usercenter.password"));
        return DruidDataSourceFactory.createDataSource(props);
    }

    /**
     * 根据数据源创建SqlSessionFactory
     */
    @Bean(name="userCentersqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("myUserCenterDataSource") DataSource usersDataSource) throws Exception {
        SqlSessionFactoryBean fb = new SqlSessionFactoryBean();
        fb.setDataSource(usersDataSource);// 指定数据源(这个必须有，否则报错)
        //org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        //configuration.setMapUnderscoreToCamelCase(true);
       // fb.setConfiguration(configuration);

        return fb.getObject();
    }

    /**
     * 配置事务管理器
     */
    @Bean(name = "userCenterTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("myUserCenterDataSource") DataSource myds) {
        return new DataSourceTransactionManager(myds);
    }

}

package com.yyy.mapper.crm;

import com.yyy.rentcar.crm.migration.entity.UserAllocation;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserAllocationMapper {


    @Update("update tab1 set seller_uid=#{sellerOrgId},user_id=#{yyyUserId}, city_code=#{yyyCityCode},user_phone_encrypt=#{enPhoneNum} where cp_clueid=#{clueId} order by updated_at DESC ")
    int updateUAByClueId(@Param("clueId") Integer clueId, @Param("sellerOrgId") Long sellerOrgId, @Param("yyyUserId") Long yyyUserId, @Param("yyyCityCode") Long yyyCityCode, @Param("enPhoneNum") String enPhoneNum);
}



package com.yyy.mapper.usercenter;


import com.yyy.rentcar.crm.migration.entity.User;
import org.apache.ibatis.annotations.*;

import java.util.List;


@Mapper
public interface UserMapper {


    //@Select("SELECT * FROM users WHERE (yyy_user_id = '' OR phone_encrypt = '' ) AND  updated_at >= SUBDATE(now(),interval #{myintervaltime} MINUTE ) order by updated_at DESC limit #{limit}")
    @Select("SELECT * FROM users WHERE yyy_user_id = '' OR phone_encrypt = '' order by updated_at DESC limit #{limit}")
    List<User> listUsers(@Param("limit") int limit, @Param("myintervaltime") int myintervaltime);


    @Update("update users set yyy_user_id=#{yyyUserId}, phone_encrypt=#{enppPhone}, backup_phone_encrypt=#{enppBackPhone} where user_id=#{userId}")
    int updateyyyIdAndPhoneByUserId(@Param("userId") Integer userId, @Param("yyyUserId") String yyyUserId, @Param("enppPhone") String enppPhone, @Param("enppBackPhone") String enppBackPhone);


}

package com.yyy.dao;

import com.yyy.rentcar.crm.migration.entity.SellerToOrgMap;
import com.yyy.rentcar.crm.migration.entity.UserAllocation;
import com.yyy.rentcar.crm.migration.mapper.crm.CityCodeMapMapper;
import com.yyy.rentcar.crm.migration.mapper.crm.SellerToOrgMapper;
import com.yyy.rentcar.crm.migration.mapper.crm.UserAllocationMapper;
import io.sentry.Sentry;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.List;

@Slf4j
@Component
public class CRMDao {

    @Resource
    private SellerToOrgMapper sellerToOrgMapper;
    @Resource
    private UserAllocationMapper userAllocationMapper;
    @Resource
    private CityCodeMapMapper cityCodeMapMapper;


    public List<SellerToOrgMap> listSellerMap(int limit, int timerInterval) {
        return sellerToOrgMapper.listSellerMap( limit, timerInterval );
    }

    @Transactional(value="crmTransactionManager")
    public int updateByPPUId(Long ppSellerId, Long staffOrgId) {
        return sellerToOrgMapper.updateByPPUId(ppSellerId, staffOrgId);
    }


    

    @Transactional(value="crmTransactionManager")
    public int updateUAByClueId(Integer clueId, Long sellerOrgId, Long yyyUserId, Long yyyyCityCode, String enPhoneNum) {
       

        if (clueId != null && clueId != 0) {
            result = userAllocationMapper.updateUAByClueId(clueId, sellerOrgId, yyyUserId, yyyyCityCode, enPhoneNum);
            if (result == 0) {
                log.error( "updateUAByClueId 失败" );
                Sentry.capture("更新User allocation 失败. clueID:"+clueId);
            }

        }
        return result;
    }
}

package com.yyy.dao;

import com.yyy.rentcar.crm.migration.entity.*;
import com.yyy.rentcar.crm.migration.mapper.*;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;

@Slf4j
@Component
public class UserCenterDao {
    @Resource
    private UserMapper userMapper;
    @Resource
    private UserICMapper userICMapper;
    @Resource
    private UserDriverLicenseMapper userDriverLicenseMapper;

    @Resource
    private LongrentUserMapper longrentUserMapper;

    public List<User> listUsers(int limit, int timerInterval) {
        return userMapper.listUsers( limit, timerInterval );
    }

    @Transactional(value="userCenterTransactionManager")
    public int updateyyyIdAndPhoneByUserId(Integer userId, String yyyUserId, String enppPhone, String enppBackPhone) {
        return userMapper.updateyyyIdAndPhoneByUserId(userId, yyyUserId, enppPhone, enppBackPhone);
    }

    public List<UserIC> listUserICs(int limit, int timerInterval) {
        return userICMapper.listUserICs( limit, timerInterval );

    }

    @Transactional(value="userCenterTransactionManager")
    public int updateIcNoByuserId(Integer userId, String enIdNo, Integer gender, Date birthday) {
        return userICMapper.updateIcNoByuserId(userId, enIdNo, gender, birthday);
    }

    public List<UserDriverLicense> listDrivers(int limit, int interval) {
        return userDriverLicenseMapper.listDrivers( limit, interval );
    }

    @Transactional(value="userCenterTransactionManager")
    public int updateDriverLicenseByuserId(Long userId, String enIdNo, Integer gender, Date birthday, String enFileNo) {
        return userDriverLicenseMapper.updateDriverLicenseByuserId(userId, enIdNo, gender, birthday, enFileNo);
    }

    @Transactional(value="userCenterTransactionManager")
    public void updateOrInsertLongRentUser(LongrentUser longrentUser) {
        Integer myuser = longrentUserMapper.getlongrentUserviaID( longrentUser.getUser_id() );
        if (myuser == null || myuser == 0) {
            longrentUserMapper.insertLongRentUser( longrentUser );
        } else {
            longrentUserMapper.updateLongRentUser( longrentUser );
        }
        return;
    }

}