springboot + druid + mybatis 多数据配置

最新推荐文章于 2023-04-27 10:06:11 发布

taopenglove

最新推荐文章于 2023-04-27 10:06:11 发布

阅读量348

点赞数

分类专栏： spring cloud

本文链接：https://blog.csdn.net/taopenglove/article/details/115202141

版权

spring cloud 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

一，maven依赖

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.2.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.order.web</groupId>
    <artifactId>order_server</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>order_server</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
        <spring-cloud.version>Hoxton.SR8</spring-cloud.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.4.2</version>
            <scope>compile</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/io.seata/seata-spring-boot-starter -->
        <dependency>
            <groupId>io.seata</groupId>
            <artifactId>seata-spring-boot-starter</artifactId>
            <version>1.4.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.23</version>
        </dependency>
        <!-- mybatis plus 代码生成器 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.28</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.75</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.alibaba.cloud/spring-cloud-starter-alibaba-seata -->
        <dependency>
            <groupId>com.alibaba.cloud</groupId>
            <artifactId>spring-cloud-starter-alibaba-seata</artifactId>
            <version>2.2.5.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.cloud/spring-cloud-starter-openfeign -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-openfeign</artifactId>
            <version>3.0.0</version>
        </dependency>



        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.cloud/spring-cloud-starter-netflix-hystrix -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-netflix-hystrix</artifactId>
            <version>2.2.7.RELEASE</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/io.github.openfeign/feign-hystrix -->
        <dependency>
            <groupId>io.github.openfeign</groupId>
            <artifactId>feign-hystrix</artifactId>
            <version>11.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework.cloud/spring-cloud-starter-netflix-eureka-client -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
            <version>3.0.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework.cloud/spring-cloud-starter-netflix-ribbon -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-netflix-ribbon</artifactId>
            <version>2.2.7.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.cloud/spring-cloud-starter-eureka -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-eureka</artifactId>
            <version>1.4.7.RELEASE</version>
        </dependency>


        <!-- https://mvnrepository.com/artifact/org.redisson/redisson -->
        <dependency>
            <groupId>org.redisson</groupId>
            <artifactId>redisson</artifactId>
            <version>3.15.1</version>
        </dependency>

        <dependency>
            <groupId>redis.clients</groupId>
            <artifactId>jedis</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-redis -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
            <version>2.3.5.RELEASE</version>
        </dependency>
    </dependencies>



    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.yml</include>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.yml</include>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
    </build>

</project>

二，properties配置

spring.application.name=order-server
server.port=6681
eureka.client.service-url.defaultZone=http://localhost:4444/eureka/,http://localhost:3333/eureka/
eureka.instance.prefer-ip-address=true
eureka.client.fetch-registry=true

#超时时间
feign.httpclient.connection-timeout=30000

#mvc接收参数时对日期进行格式化
spring.mvc.date-format=yyyy-MM-dd HH:mm:ss
#jackson对响应回去的日期参数进行格式化
spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
spring.jackson.time-zone=GMT+8
spring.main.allow-bean-definition-overriding=true

spring.datasource.bank1.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.bank1.url=jdbc:mysql://localhost:3306/bank1?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
spring.datasource.bank1.username=root
spring.datasource.bank1.password=123123
spring.datasource.bank1.driver-class-name=com.mysql.cj.jdbc.Driver
#初始化时建立物理连接的个数
spring.datasource.bank1.druid.initial-size=3
#最小连接池数量
spring.datasource.bank1.druid.min-idle=3
#最大连接池数量
spring.datasource.bank1.druid.max-active=10
#获取连接时最大等待时间
spring.datasource.bank1.druid.max-wait=60000
#配置监控页面访问登录名称
spring.datasource.bank1.druid.stat-view-servlet.login-username=admin
#配置监控页面访问密码
spring.datasource.bank1.druid.stat-view-servlet.login-password=admin
#是否开启慢sql查询监控
spring.datasource.bank1.druid.filter.stat.log-slow-sql=true
#慢SQL执行时间
spring.datasource.bank1.druid.filter.stat.slow-sql-millis=1
spring.datasource.bank1.druid.filter.commons-log.statement-log-enabled=true
spring.datasource.bank1.druid.filter.commons-log.statement-executable-sql-log-enable=true

spring.datasource.bank3.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.bank3.url=jdbc:mysql://localhost:3306/bank3?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
spring.datasource.bank3.username=root
spring.datasource.bank3.password=123123
spring.datasource.bank3.driver-class-name=com.mysql.cj.jdbc.Driver
#初始化时建立物理连接的个数
spring.datasource.bank3.druid.initial-size=3
#最小连接池数量
spring.datasource.bank3.druid.min-idle=3
#最大连接池数量
spring.datasource.bank3.druid.max-active=10
#获取连接时最大等待时间
spring.datasource.bank3.druid.max-wait=60000
#配置监控页面访问登录名称
spring.datasource.bank3.druid.stat-view-servlet.login-username=admin
#配置监控页面访问密码
spring.datasource.bank3.druid.stat-view-servlet.login-password=admin
#是否开启慢sql查询监控
spring.datasource.bank3.druid.filter.stat.log-slow-sql=true
#慢SQL执行时间
spring.datasource.bank3.druid.filter.stat.slow-sql-millis=1
spring.datasource.bank3.druid.filter.commons-log.statement-log-enabled=true
spring.datasource.bank3.druid.filter.commons-log.statement-executable-sql-log-enable=true

spring.datasource.bank2.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.bank2.url=jdbc:mysql://localhost:3306/bank2?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
spring.datasource.bank2.username=root
spring.datasource.bank2.password=123123
spring.datasource.bank2.driver-class-name=com.mysql.cj.jdbc.Driver
#初始化时建立物理连接的个数
spring.datasource.bank2.druid.initial-size=3
#最小连接池数量
spring.datasource.bank2.druid.min-idle=3
#最大连接池数量
spring.datasource.bank2.druid.max-active=10
#获取连接时最大等待时间
spring.datasource.bank2.druid.max-wait=60000
#配置监控页面访问登录名称
spring.datasource.bank2.druid.stat-view-servlet.login-username=admin
#配置监控页面访问密码
spring.datasource.bank2.druid.stat-view-servlet.login-password=admin
#是否开启慢sql查询监控
spring.datasource.bank2.druid.filter.stat.log-slow-sql=true
#慢SQL执行时间
spring.datasource.bank2.druid.filter.stat.slow-sql-millis=1
spring.datasource.bank2.druid.filter.commons-log.statement-log-enabled=true
spring.datasource.bank2.druid.filter.commons-log.statement-executable-sql-log-enable=true

#spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#spring.datasource.url=jdbc:mysql://localhost:3306/bank1?useUnicode=true&characterEncoding=UTF-8
#spring.datasource.username=root
#spring.datasource.password=123123
#spring.datasource.initialSize=5
#spring.datasource.minIdle=5
#spring.datasource.maxActive=20
#spring.datasource.maxWait=60000
#spring.datasource.timeBetweenEvictionRunsMillis=60000
#spring.datasource.minEvictableIdleTimeMillis=300000
#spring.datasource.validationQuery=SELECT 1 FROM DUAL
#spring.datasource.testWhileIdle=true
#spring.datasource.testOnBorrow=false
#spring.datasource.testOnReturn=false
#spring.datasource.poolPreparedStatements=true
#spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
#spring.datasource.filters=stat,wall,log4j
#spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
#spring.datasource.useGlobalDataSourceStat=true

mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.configuration.auto-mapping-behavior=full
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
mybatis-plus.mapper-locations=classpath*:/mapper/**/*.xml
mybatis-plus.global-config.db-config.logic-not-delete-value=1
mybatis-plus.global-config.db-config.logic-delete-value=0

ribbon.eureka.enabled=true

#redisson
spring.redisson.cluster.nodes[0]=127.0.0.1:6380
spring.redisson.cluster.nodes[1]=127.0.0.1:6381
spring.redisson.cluster.nodes[2]=127.0.0.1:6382
spring.redisson.cluster.nodes[3]=127.0.0.1:6383
spring.redisson.cluster.nodes[4]=127.0.0.1:6384
spring.redisson.cluster.nodes[5]=127.0.0.1:6385

#redis cluster config
#RedisCluster集群节点及端口信息
share.redis.jedis.cluster.nodes=127.0.0.1:6380,127.0.0.1:6381,127.0.0.1:6382,127.0.0.1:6383,127.0.0.1:6384,127.0.0.1:6385
#Redis密码
share.redis.jedis.cluster.password=
#在群集中执行命令时要遵循的最大重定向数目
share.redis.jedis.cluster.maxredirects=5
#Redis连接池在给定时间可以分配的最大连接数。使用负值无限制
share.redis.jedis.cluster.maxactive=1000
#以毫秒为单位的连接超时时间
share.redis.jedis.cluster.timeout=2000
#池中“空闲”连接的最大数量。使用负值表示无限数量的空闲连接
share.redis.jedis.cluster.maxidle=8
#目标为保持在池中的最小空闲连接数。这个设置只有在设置max-idle的情况下才有效果
share.redis.jedis.cluster.minidle=5
#连接分配在池被耗尽时抛出异常之前应该阻塞的最长时间量（以毫秒为单位）。使用负值可以无限期地阻止
share.redis.jedis.cluster.maxwait=1000
#redis cluster只使用db0
share.redis.jedis.cluster.index=0
#从jedis连接池获取连接时，校验并返回可用的连接
share.redis.jedis.cluster.testborrow=true
share.redis.jedis.cluster.maxtotal=2000



seata.tx-service-group=my_test_tx_group
seata.service.grouplist.default=127.0.0.1:8091
logging.level.io.seata=info
logging.level.io.seata.samples.account.persistence.AccountMapper=debug

三，数据源Java配置

1,监控配置

@Configuration
public class DruidConfig {
    /**
     * 配置Druid监控
     * 后台管理Servlet
     * @return
     */
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String,String> initParams = new HashMap<>();//这是配置的druid监控的登录密码
        initParams.put("loginUsername","admin");
        initParams.put("loginPassword","admin");
        //默认就是允许所有访问
        initParams.put("allow","");
        //黑名单的IP
        initParams.put("deny","192.168.15.21");
        bean.setInitParameters(initParams);
        return bean;
    }
    /**
     * 配置web监控的filter
     * @return
     */
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        Map<String,String> initParams = new HashMap<>();
        initParams.put("exclusions","/static/*,*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");//过滤掉需要监控的文件
        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Arrays.asList("/*"));
        return  bean;
    }
}

2，主数据源配置

@Configuration
@MapperScan(basePackages = "com.order.web.account", sqlSessionTemplateRef  = "accountSqlSessionTemplate")
public class UserDataSourceConfig {

    @Bean(name = "accountDataSource")
    @Primary   //配置默认数据源
    @ConfigurationProperties(prefix = "spring.datasource.bank1")
    public DataSource dataSource() {
        return  new DruidDataSource();
    }

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

    @Primary
    @Bean(name = "accountTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("accountDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Primary
    @Bean(name = "accountSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("accountSqlSessionFactory") SqlSessionFactory sqlSessionFactory)
            throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }



}

3，从数据元配置一

@Configuration
@MapperScan(basePackages = "com.order.web.goods", sqlSessionTemplateRef  = "goodsSqlSessionTemplate")
public class GoodsDataSourceConfig {

    @Bean(name = "goodsDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.bank3")
    public DataSource dataSource() {
        return  new DruidDataSource();
    }

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

    @Bean(name = "goodsTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("goodsDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "goodsSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("goodsSqlSessionFactory") SqlSessionFactory sqlSessionFactory)
            throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

从数据源配置二

@Configuration
@MapperScan(basePackages = "com.order.web.order", sqlSessionTemplateRef  = "orderSqlSessionTemplate")
public class OrderDataSourceConfig {

    @Bean(name = "orderDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.bank2")
    public DataSource dataSource() {
        return  new DruidDataSource();
    }

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

    @Bean(name = "orderTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("orderDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "orderSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("orderSqlSessionFactory") SqlSessionFactory sqlSessionFactory)
            throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

四，项目目录

五，测试代码

@ResponseBody
    @PostMapping(value = "saveBussinessInfo",produces = "application/json; charset=utf-8")
    public AccountInfo getAccount(@RequestBody JSONObject jsonObject){
        AccountInfo accountInfo = new AccountInfo();
        accountInfo.setId(2L);
        accountInfo.setAccountBalance(23456d);
        accountInfo.setAccountName("万三千");
        accountInfo.setAccountNo("123454321");
        accountInfo.setAccountPassword("password");
        accountInfoService.saveAccountInfo(accountInfo);

        OrderInfo orderInfo = new OrderInfo();
        orderInfo.setId(2L);
        orderInfo.setOrderDesc("万三千的订单");
        orderInfo.setOrderId("122121");
        orderInfo.setOrderStatus("1");
        orderInfo.setUserId(2);
        orderInfoService.saveOrderInfo(orderInfo);

        GoodsInfo goodsInfo = new GoodsInfo();
        goodsInfo.setGoodsAmount(11111L);
        goodsInfo.setGoodsDesc("万三千的货物");
        goodsInfo.setGoodsId("22222");
        goodsInfo.setId(2L);
        goodsInfoService.saveGoodsInfo(goodsInfo);

        return accountInfoService.getAccountById(2);
    }

六，测试结果