SpringBoot2+Mybatis+Druid+mysql+Kylin 配置多数据源详解+BUG

李指导、

已于 2023-11-08 14:34:48 修改

阅读量1.4k

点赞数 2

分类专栏： JAVA 文章标签： java kylin spring boot mybatis 多数据源 mysql druid

于 2021-05-27 21:51:44 首次发布

本文链接：https://blog.csdn.net/LiHaoHang6/article/details/117334275

版权

JAVA 专栏收录该内容

10 篇文章 5 订阅

订阅专栏

参考文档:

一: 添加pom

二: 添加application.yml

三: 添加主数据源和从数据源Config。

参考文档:

Springboot2实现多数据源_springboot2多数据源-CSDN博客

https://www.cnblogs.com/aizen-sousuke/p/11756279.html

SpringBoot2+Mybatis+Druid+Kylin 配置多数据源+BUG

整体目录树

一: 添加pom

  <!-- 数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!--SpringBoot MyBatis启动器-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.2.2</version>
        </dependency>     

      <dependency>

         <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-web</artifactId>

     </dependency>

二: 添加application.yml

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: org.apache.kylin.jdbc.Driver
    # 配置Druid的其他参数，以下配置必须增加一个配置文件才能有效
    druid:
      # 主库数据源
      kylin1:
        url: jdbc:kylin://ip:port/gxy   #生产主节点
        username: 账号        #生产的
        password: 密码        #生产的
        connectionInitSqls: select count(1) from V_GXY_STUDENT_STATUS where SCHOOL_ID = 'a' and BATCH = 'a' LIMIT 1 OFFSET 0
        validation-query: select count(1) from V_GXY_STUDENT_STATUS where SCHOOL_ID = 'a' and BATCH = 'a' LIMIT 1 OFFSET 0
      # 从数据库
      kylin2:
        url: jdbc:kylin://ip:port/steaming   #生产主节点
        username: 账号        #生产的
        password: 密码        #生产的
        connectionInitSqls: select count(1) from KAFKSIGN where SCHOOLID = 'a' and PLANID = 'a' LIMIT 1 OFFSET 0
        validation-query: select count(1) from KAFKSIGN where SCHOOLID = 'a' and PLANID = 'a' LIMIT 1 OFFSET 0
      # 连接池配置
      initial-size: 3
      max-active: 40
      min-idle: 3
      max-wait: 600000
      pool-prepared-statements: false
      max-pool-prepared-statement-per-connection-size: -1
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*
        login-username: 账号
        login-password: 密码
      filter:
        stat:
          log-slow-sql: true
          slow-sql-millis: 1000
          merge-sql: false
        wall:
          config:
            multi-statement-allow: true

三: 添加主数据源和从数据源Config。

Kylin1:

package com.*.*.bigdata.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"com.*.*.bigdata.dao.mapper"}, sqlSessionFactoryRef = "kylin1SqlSessionFactory")
public class Kylin1DataSourcesConfig {
    //主数据源mapper xml文件夹路径
    private static final String MAPPER_LOCAL = "classpath:com/*/*/bigdata/dao/mapper/*.xml";

    @ConfigurationProperties("spring.datasource.druid.kylin1")
    @Primary
    @Bean(name = "kylin1DataSource")
    public DruidDataSource druidDataSource() {
        return new DruidDataSource();
    }

    @Bean(name = "kylin1TransactionManager")
    @Primary
    public DataSourceTransactionManager kylin1TransactionManager() {
        return new DataSourceTransactionManager(druidDataSource());
    }

    @Bean(name = "kylin1SqlSessionFactory")
    @Primary
    public SqlSessionFactory kylin1SqlSessionFactory(@Qualifier("kylin1DataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCAL));
        return sessionFactoryBean.getObject();
    }
}

kylin2:

package com.zhangtao.moguding.bigdata.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"com.*.*.bigdata.dao.steaming"}, sqlSessionFactoryRef = "kylin2SqlSessionFactory")
public class kylin2DataSourceConfig {
    //主数据源mapper xml文件夹路径
    private static final String MAPPER_LOCAL = "classpath:com/*/*/bigdata/dao/steaming/*.xml";

    @ConfigurationProperties("spring.datasource.druid.kylin2")
    @Bean(name = "kylin2DataSource")
    public DruidDataSource druidDataSource() {
        return new DruidDataSource();
    }

    @Bean(name = "kylin2TransactionManager")
    public DataSourceTransactionManager kylin2TransactionManager() {
        return new DataSourceTransactionManager(druidDataSource());
    }

    @Bean(name = "kylin2SqlSessionFactory")
    public SqlSessionFactory kylin2SqlSessionFactory(@Qualifier("kylin2DataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCAL));
        return sessionFactoryBean.getObject();
    }

}

四:测试接口

Controller：

package com.*.*.bigdata.controller;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;


@RestController
@RequestMapping("/bigdata")
public class GxyTestController {
    @Autowired
    GxyTestService gxyTestService;

    @RequestMapping("/v1/test")
    @ApiOperation(value = "测试接口")
    public ObjectResponse testController(@RequestBody StatisticsQuery query) throws Exception {
        return  gxyTestService.testService();
    }

    @RequestMapping("/v2/test")
    @ApiOperation(value = "测试接口")
    public ObjectResponse test2Controller(@RequestBody StatisticsQuery query) throws Exception {
        return  gxyTestService.test2Service();
    }

}

Service：

package com.*.*.bigdata.service;


public interface GxyTestService {

    ObjectResponse testService();

    ObjectResponse test2Service();
}

ServiceImpl:

package com.*.*.bigdata.service.impl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/** 
* @Description: 测试接口 
*/

@Service
public class GxyTestServiceImpl implements GxyTestService {
    @Autowired
    GxyTestMapper gxyTestMapper;
    @Autowired
    GxyTestSteamingMaapper GxyTestSteamingMaapper;

    @Override
    public ObjectResponse testService() {

        Integer test =  gxyTestMapper.testCount();
        System.out.println("test"+test);
        return ObjectResponse.resObj(test);
    }

    @Override
    public ObjectResponse test2Service() {
        Integer test =  GxyTestSteamingMaapper.testSteaming();
        System.out.println("test"+test);
        return ObjectResponse.resObj(test);
    }

}

Mapper:

package com.*.*.bigdata.dao.mapper;
import org.springframework.stereotype.Repository;

import java.util.Date;

@Repository
public interface GxyTestMapper {
    Integer testCount();

}

<?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.*.*.bigdata.dao.mapper.GxyTestMapper">
    <!-- 查询学生状态表最新分区 -->
    <select id="testCount"  resultType="java.lang.Integer" >
        <!--select count(1) from KAFKSIGN -->

        select count(1) from GXY_JOB02 where NOWDATE ='2021-05-27'
        <!--这里加where条件只为了命中状态表新的cube,不然会命中老的cube导致日期获取不准-->
    </select>

</mapper>

xml:

package com.*.*.bigdata.dao.steaming;

import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

@Repository
public interface GxyTestSteamingMaapper {
    Integer testSteaming();
}

<?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.*.*.bigdata.dao.steaming.GxyTestSteamingMaapper">
<!-- 查询学生状态表最新分区 -->
<select id="testSteaming"  resultType="java.lang.Integer" >
        select count(1) from KAFKSIGN
    </select>

</mapper>

五:接口调用:

kylin1：

kylin2:

BUG记录

报错一:

java.sql.SQLException: url not set

org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: url not set

springboot application.properties druid：url 和 jdbc-url的区别

写 jdbc-url 是因为这是两种数据库类型不一样时候的写法,同一种数据库用url就可以

如果我这里写的是jdbc-url, 那么会报标题的错误，还有一种原因是因为config文件夹多了一个DruidConfig。

在DruidConfig中配置了 @ConfigurationProperties(prefix = "spring.datasource.druid") 但其实DruidConfig的作用是如果只有一个数据源的情况下默认的配置,有两个数据源可以讲这个类删掉或者整个类都注释掉即可

报错二:

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com...*.dao.steaming.GxyTestSteamingMaapper.testSteaming

这个错误是xml和mapper接口类无法识别, 图中框起来的 <resource> 如果不写那么编译后target下的class内也不会有xml文件,在pom文件的build增加对应的resource配置后,运行正常。

配置如下：

    <build>
        <resources>
            <!-- 打包java里mapper的xml文件 -->
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <!-- 打包resource里的项目配置文件 -->
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.yml</include>
                    <include>**/*.xml</include>
                    <include>**/*.properties</include>
                    <include>**/*.docx</include>
                    <include>**/*.jpg</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
    </build>

报错三:

ERROR 2224 --- [reate-192486017] com.alibaba.druid.pool.DruidDataSource : create connection RuntimeException

java.lang.NullPointerException: null
   at org.apache.kylin.jdbc.shaded.org.apache.calcite.avatica.UnregisteredDriver.acceptsURL(UnregisteredDriver.java:144)
   at org.apache.kylin.jdbc.shaded.org.apache.calcite.avatica.UnregisteredDriver.connect(UnregisteredDriver.java:130)
   at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1596)
   at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1662)
   at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2697)

这个原因是因为DruidConfig的 @ConfigurationProperties(prefix = "spring.datasource") 配置到了datasource,但datasource下面有两个数据源,而DruidConfig的作用是有一个数据源的时候的一些默认配置,所以有两个数据源的话,DruidConfig类可以注释掉或者删掉。

报错四：

mapper-locations:

#mybatis
mybatis-plus:
  mapper-locations: classpath*:com/zhangtao/moguding/*/dao/**/*.xml
  #实体扫描，多个package用逗号或者分号分隔
  typeAliasesPackage: com.zhangtao.moguding.*.dto
  configuration:
        #配置返回数据库(column下划线命名&&返回java实体是驼峰命名)，自动匹配无需as（没开启这个，SQL需要写as： select user_id as userId）
        map-underscore-to-camel-case: true
        cache-enabled: false
        #配置JdbcTypeForNull, oracle数据库必须配置
        jdbc-type-for-null: 'null'

application.yml中mapper-locations 配置项主要用于mapper.xml和mapper接口不在同一个目录下的时候使用,如果mapper.xml和mapper接口在同一个目录,那么该配置项无作用

参考博客:mybatis mapper-locations作用 - 灰信网（软件开发博客聚合）

报错五:

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2021-05-27 20:25:28.199 ERROR 1824 --- [ main] o.s.boot.SpringApplication : Application run failed

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name '': Unsatisfied dependency expressed through field ''; nested exception is org.springframework.beans.factory

这个错的原因是在Kylin1DataSourceConfig.java类中sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources());方法的调用中xml指定错误,导致无法正常绑定bean,错误写法是:

正确写法有两种:

private static final String MAPPER_LOCAL = "classpath*:/mapper/*.xml";
private static final String MAPPER_LOCAL = "classpath:com/*/*/bigdata/dao/mapper/*.xml";

报错六:

org.apache.ibatis.binding.BindingException: Incalid bound statement (not found) : com...bigdata.dao.GxyTestSteamingMapper.testSteaming

报这个错的原因是因为Mapper类和xml没有映射到。下面是我原目录结构:Mapper类和xml没有在同一个文件夹中

然后把目录结构改为:

将Mapper类和xml放在同一个文件夹中,项目可以运行接口可以正常调用,据体原因不是太清楚但是看第二篇参考链接得知大佬的目录结构都是划分很明确的,不知道是不是这个原因。还有就是在Kylin2DataSourceConfig中, @MapperScan注解扫描的是mapper下面的mapper类,但是我最开始的目录结构steaming下面没有mapper类只有xml,所以报错。而 sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources()) 方法扫描的是xml。

李指导、

关注

2
点赞
踩
6

收藏

觉得还不错? 一键收藏
打赏
0
评论
SpringBoot2+Mybatis+Druid+mysql+Kylin 配置多数据源详解+BUG

目录参考文档:一: 添加pom二: 添加application.yml三: 添加主数据源和从数据源Config。四:测试接口五:接口调用: BUG记录报错一:报错二:报错三:报错四：报错五:报错六:参考文档:https://blog.csdn.net/userlhj/article/details/108748290https://www.cnblogs.com/aizen-sousuke/p/11756279.htmlSp...
复制链接

扫一扫