spring-boot mybadis多数据源配置

最新推荐文章于 2024-07-16 20:21:54 发布

网寻星

最新推荐文章于 2024-07-16 20:21:54 发布

阅读量701

点赞数 1

分类专栏： JAVA springBoot 文章标签： springboot

JAVA 同时被 2 个专栏收录

37 篇文章 0 订阅

订阅专栏

springBoot

10 篇文章 0 订阅

订阅专栏

强烈推荐:只要一行注解，使用mybatis的多数据源，无需要任何配置。

github地址:https://gitee.com/xiaojiezhu/mybadis-starter.git

只需要一行注解，就可以配置mybadis的多数据源

这是一个基于spring boot 的mybadis的starter

支持多个数据源

使用方法很简单，先定义一些配置文件，放到application.yml中

mysql:
  server:
    saas:
      url: "jdbc:mysql://localhost:3306/saas?useUnicode=true&characterEncoding=utf8"
      username: root
      password: 123
      driverClassName: com.mysql.jdbc.Driver
      initialSize: 1  #初始化大小
      minIdle: 1  #空闲连接池的大小
      maxActive: 50 #最大激活数量
    saas2:
      url: "jdbc:mysql://localhost:3306/saas2?useUnicode=true&characterEncoding=utf8"
      username: root
      password: 123
      driverClassName: com.mysql.jdbc.Driver
      initialSize: 1  #初始化大小
      minIdle: 1  #空闲连接池的大小
      maxActive: 50 #最大激活数量

main方法

@MyBadisLoader({"saas = com.llc.admin.web.dao.saas = classpath:mapper/*xml" , 
                "saas2 = com.llc.admin.web.dao.saas2 = classpath:mapper/*.xml,classpath:mapper/user/*.xml"})
@SpringBootApplication
public class WebApplication {

    public static void main(String[] args) {
        SpringApplication.run(WebApplication.class,args);
    }
}

上面的注解中 saas是上方配置文件，数据源的名称，后面是扫描的接口包名，可以用逗号分隔传入多个，再后面是扫描xml的配置文件路径，也支持多个注解中接收的是一个数组，所以支持多个数据源，除此不需要任何代码就可以使用

需要的包依赖 pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.xiaojiezhu.mybadis.starter</groupId>
<artifactId>mybadis-starter</artifactId>
<version>1.0</version>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-autoconfigure</artifactId>
<version>1.4.7.RELEASE</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.20</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.27</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.0</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.0</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.2</version>
</dependency>
</dependencies>
</project>

详细使用看码云上代码

纯代码配置的spring boot mybadis多数据源的配置

github地址:https://gitee.com/xiaojiezhu/spring-boot-ds-mybadis

需要的包依赖 pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>datasource-mybatis</groupId>
    <artifactId>ds-mybatis</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <parent>
        <groupId>com.framework.sass.service</groupId>
        <artifactId>service-root</artifactId>
        <version>1.0</version>
    </parent>

    <dependencies>
        <!-- Begin of DB related -->
        <dependency> <!-- for ChainedTransactionManager configuration -->
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-commons</artifactId>
        </dependency>
        <dependency> <!-- exclude掉缺省的jdbc配置 -->
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.tomcat</groupId>
                    <artifactId>tomcat-jdbc</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- 同项目的其它包...开始 -->
        <dependency>
            <groupId>com.framework.recWeiMsg</groupId>
            <artifactId>msg-message</artifactId>
            <version>1.0</version>
        </dependency>
<!--        <dependency>
            <groupId>com.framework.recWeiMsg</groupId>
            <artifactId>msg-commons</artifactId>
            <version>1.0</version>
        </dependency>-->
<!--        <dependency>
            <groupId>com.framework.recWeiMsg</groupId>
            <artifactId>msg-weichat</artifactId>
            <version>1.0</version>
        </dependency>-->
        <!-- 同项目的其它包...结束 -->

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.0</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.3.0</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.3</version>
        </dependency>


        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency> <!-- mybatis 分页插件 -->
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>4.1.6</version>
        </dependency>

    </dependencies>


</project>

目录：resources/ mapper中xml文件 a.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.zxj.ds.mybatis.dao.saas2.DbSaas2Dao">

    <select id="getOilOrder" resultType="com.framework.msg.db.businessTransaction.OrderRecord">
        select * from orderRecord
    </select>
</mapper>

启动类

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * 1：去掉配置中的basePackage路径 测试无法去掉，或者说多数据源情况下无法去掉，需要配置一个包，可以扫描到子包
 * 2:全部写在一个类里面          不行，读取不到
 * 3:注解class，使用class.forName        可以，(Class<? extends Annotation>) Class.forName("com.zxj.ds.mybatis.annotation.SaasDao")
 * 4:SQL写在配置文件中         可以
 * 5:两个数据源是否都支持事务   只有一个数据源支持事务，那就是dataSource中配置了@Primary注解的数据源
 * 6：两个数据源是不是可以一起回滚事务     只有一个注解支持事务
 * 7:自动转驼峰       可以，但是设置了驼峰就强制转驼峰，不支持下划线
 */
@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class,args);
    }
}

更换数据源为druid,删除了注解的配置

DataSourceConfiguration.java

package com.zxj.ds.mybatis.configuration;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

/**
 * Created by Administrator on 2017/5/4.
 */
@Configuration
public class DataSourceConfiguration {
    //数据库连接相关的参数：
    private String driverClassName = "com.mysql.jdbc.Driver";
    private String jdbcIpPort = "3306";
    private String jdbcUrl = "jdbc:mysql://server1:3306/%s?useUnicode=true&characterEncoding=utf8";
    private String userName = "root";
    private String password = "123";

    //连接池相关的参数：
    //等待从连接池中获得连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒
    private long connectionTimeout = 30000;
    //一个连接idle状态的最大时长（毫秒），超时则被释放（retired），缺省:10分钟
    private long idleTimeout = 600000;
    //一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），缺省:30分钟，建议设置比数据库超时时长少30秒以上，
    //参考MySQL wait_timeout参数（show variables like '%timeout%';）
    private long maxLifetime = 1765000;
    //连接池中允许的最大连接数。缺省值：10；推荐的公式：((core_count * 2) + effective_spindle_count)
    private int maximumPoolSize = 15;


    @Bean()
    @Primary
    public DataSource dataSource1(){
        DruidDataSource ds = new DruidDataSource();
        ds.setDriverClassName(driverClassName);
        ds.setUrl(String.format(jdbcUrl, "saas"));
        ds.setUsername(userName);
        ds.setPassword(password);
        return ds;
    }

    @Bean()
    public DataSource dataSource2(){
        DruidDataSource ds = new DruidDataSource();
        ds.setDriverClassName(driverClassName);
        ds.setUrl(String.format(jdbcUrl, "saas2"));
        ds.setUsername(userName);
        ds.setPassword(password);
        return ds;
    }
}

新建 MyBatisConfiguration.java 文件

只需要一行注解，就可以配置mybadis的多数据源

这是一个基于spring boot 的mybadis的starter

支持多个数据源

使用方法很简单，先定义一些配置文件，放到application.yml中

package com.zxj.ds.mybatis.configuration;

import com.github.pagehelper.PageHelper;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.data.transaction.ChainedTransactionManager;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.TransactionManagementConfigurer;

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

/**
 * Created by Administrator on 2017/5/4.
 */
@Configuration
@EnableTransactionManagement
@AutoConfigureAfter(DataSourceConfiguration.class)
public class MyBatisConfiguration implements TransactionManagementConfigurer {
    private final static Logger logger = LoggerFactory.getLogger(MyBatisConfiguration.class);

    @Resource(name = "dataSource1")
    private DataSource ds1;
    @Resource(name = "dataSource2")
    private DataSource ds2;


    /**
     * 配置SqlSessionFactory：
     * - 创建SqlSessionFactoryBean，并指定一个dataSource；
     * - 设置这个分页插件：https://github.com/pagehelper/Mybatis-PageHelper；
     * - 指定mapper文件的路径；
     */
    @Bean
    public SqlSessionFactory sqlSessionFactory1() {
        return setMapper(ds1);
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory2() {
        return setMapper(ds2);
    }





    private SqlSessionFactory setMapper(DataSource ds) {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(ds);

        //分页插件
        PageHelper pageHelper = new PageHelper();
        Properties properties = new Properties();
        properties.setProperty("dialect", "mysql");
        properties.setProperty("reasonable", "false");
        properties.setProperty("pageSizeZero", "true");
        pageHelper.setProperties(properties);
        bean.setPlugins(new Interceptor[]{pageHelper});

        try {
            //指定mapper xml目录
            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            bean.setMapperLocations(resolver.getResources("classpath:mapper/*.xml"));
            //设置配置项
            org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
            //自动把下划线转成驼峰
//            configuration.setMapUnderscoreToCamelCase(true);
            bean.setConfiguration(configuration);
            return bean.getObject();
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            throw new RuntimeException(e);
        }
    }

    /**
     * Transaction 相关配置
     * 因为有两个数据源，所有使用ChainedTransactionManager把两个DataSourceTransactionManager包括在一起。
     */
    @Override
    public PlatformTransactionManager annotationDrivenTransactionManager() {
        DataSourceTransactionManager dtm1 = new DataSourceTransactionManager(ds1);
        DataSourceTransactionManager dtm2 = new DataSourceTransactionManager(ds2);

        ChainedTransactionManager ctm = new ChainedTransactionManager(dtm2, dtm1);
        return ctm;
    }


}

创建 MyBatisScanner.java

package com.zxj.ds.mybatis.configuration;

import com.zxj.ds.mybatis.annotation.Saas2Dao;
import com.zxj.ds.mybatis.annotation.SaasDao;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.lang.annotation.Annotation;

/**
 * Created by Administrator on 2017/5/4.
 */
@Configuration
@AutoConfigureAfter(MyBatisConfiguration.class)
public class MyBatisScanner {

    /**
     * - 设置SqlSessionFactory；
     * - 设置dao所在的package路径，路径可以以逗号或者分号进行分隔设置多个
     * - 关联注解在dao类上的Annotation名字；
     * - 上面的注解可以不需要
     */
    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer1() throws ClassNotFoundException, IllegalAccessException, InstantiationException {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory1");
        mapperScannerConfigurer.setBasePackage("com.zxj.ds.mybatis.dao.saas");
        //mapperScannerConfigurer.setAnnotationClass((Class<? extends Annotation>) Class.forName("com.zxj.ds.mybatis.annotation.SaasDao"));
        return mapperScannerConfigurer;
    }

    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer2() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory2");
        mapperScannerConfigurer.setBasePackage("com.zxj.ds.mybatis.dao.saas2");
        //mapperScannerConfigurer.setAnnotationClass(Saas2Dao.class);
        return mapperScannerConfigurer;
    }
}

配置完成，从 controller开始，具体使用

创建 HelloController.java

package com.zxj.ds.mybatis.controller;

import com.framework.msg.db.WeichatAccount;
import com.framework.msg.db.businessTransaction.OrderRecord;
import com.zxj.ds.mybatis.service.HelloService;
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.ResponseBody;

import java.util.List;
import java.util.Map;

/**
 * @Author 朱小杰
 * @Date 2017/5/4 10:41:42
 * 说明这个类的作用...
 */
@Controller
public class HelloController {
    @Autowired
    private HelloService service;

    @RequestMapping("/")
    @ResponseBody
    public String a(){
        List<Map<String,Object>> users = service.getUsers();
        System.out.println(users);

        List<OrderRecord> orders = service.getOilOrder();
        System.out.println(orders);

        List<WeichatAccount> accounts = service.getAccounts();
        System.out.println(accounts);

        return "SUCCESS";
    }

    @ResponseBody
    @RequestMapping("/b")
    public String b(){
        service.b();
        return "b";
    }
}

service新建 HelloService.java 文件

package com.zxj.ds.mybatis.service;

import com.framework.msg.db.WeichatAccount;
import com.framework.msg.db.businessTransaction.OrderRecord;
import com.zxj.ds.mybatis.dao.saas2.DbSaas2Dao;
import com.zxj.ds.mybatis.dao.saas.DbSaasDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;


@Service
public class HelloService {
    @Autowired
    private DbSaasDao saasDao;
    @Autowired
    private DbSaas2Dao saas2Dao;
    public List<Map<String, Object>> getUsers() {
        List<Map<String, Object>> datas = saasDao.getUsers();
        return datas;
    }

    public List<OrderRecord> getOilOrder() {
        List<OrderRecord> d = saas2Dao.getOilOrder();
        return d;
    }

    public List<WeichatAccount> getAccounts() {
        return saasDao.getAccounts();
    }

    @Transactional
    public void b() {
/*        boolean r = saasDao.addApp("002","xxx");
        System.out.println(r);
        throw new RuntimeException("");*/

        boolean r = saas2Dao.addStaff("id1","xx");
        throw new RuntimeException("");

    }
}

Dao中分别创建二个包 saas 和 saas2

saas中创建 DbSaasDao.java 文件

package com.zxj.ds.mybatis.dao.saas;


import com.framework.msg.db.WeichatAccount;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

//@SaasDao
@Mapper
public interface DbSaasDao {

    @Select("select * from weichat_user limit 50")
    List<Map<String, Object>> getUsers();

    @Select("select * from weichat_account limit 50")
    List<WeichatAccount> getAccounts();

    @Insert("INSERT INTO `saas`.`app_version` (`id`, `name`, `version`, `can_use_version`, `update_url`, `create_time`, `update_time`, `desc`, `fileSize`) VALUES (#{id}, #{name}, '1.0.4', '1.0.3', 'http://www.kindpetro.com.cn/admin/sqf.apk', '2017-04-18 11:09:49', '2017-04-18 11:09:52', '1，优化页面显示； \\r\\n2，修复Bug。', '0');\n")
    boolean addApp(@Param("id") String id,@Param("name") String name);
}

saas2中创建 DbSaas2Dao.java 文件

package com.zxj.ds.mybatis.dao.saas2;


import com.framework.msg.db.businessTransaction.OrderRecord;
import com.zxj.ds.mybatis.annotation.Saas2Dao;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
//@Repository
public interface DbSaas2Dao {

    List<OrderRecord> getOilOrder();

    @Insert("INSERT INTO `saas2`.`staff` (`id`, `name`, `appid`, `openid`, `create_time`) VALUES (#{id},#{name}, 'wx2e9a7946612800c5', 'oZnKxwb6QKhv8xNN0of3eAvEd6mM', '2016-12-15 15:04:48');\n")
    boolean addStaff(@Param("id") String id,@Param("name") String name);
}

创建 annotation 包

创建Saas2Dao.java文件

package com.zxj.ds.mybatis.annotation;

import java.lang.annotation.*;

/**
 * 连SAAS2数据库
 */
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface Saas2Dao {
}

创建SaasDao.java文件

package com.zxj.ds.mybatis.annotation;

import java.lang.annotation.*;

/**
 * 连接SAAS库
 */
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface SaasDao {
}

完成