【掘金者说】多数据源抛出dbType not support

掘金者说
4,864 阅读4分钟

使用springboot然后添加mybatis-plus，添加阿里druid来做连接，采用了多数据源进行配置dynamic-datasource-spring-boot-starter然后就出现其中个别问题：多数据源抛出dbType not support : sqlite异常。

issue添加

https://gitee.com/baomidou/dynamic-datasource-spring-boot-starter/issues/I1NLER

【异常1】dbType not support异常排查处理

采用的是苞米豆baomidou基于 SpringBoot多数据源、动态数据源、主从分离、快速启动器 支持分布式事务。刚刚验证了数据库：mysql、mariadb、oracle、db2、h2、hsql、postgresql、sqlserver、sqlite，唯独在多数据源暂不支持sqlite 报错误，错误如下：

java.lang.IllegalStateException: dbType not support : sqlite, url jdbc:sqlite:D:/sqlite/database/db_demo.db

排查的半天，上网也搜寻了，就是说防火墙别开启。打开PSCache，并且指定每个连接上PSCache的大小（防火墙这一块一定不能开，sqlite不支持，会导致奇怪的异常）。配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙（启动sqlite时候需要关闭wall）。把filters中的stat,wall,slf4j改成stat,slf4j

# 连接池的配置信息 DRUID POOL Congif
# 全局druid参数，绝大部分值和默认保持一致。(现已支持的参数如下,不清楚含义不要乱设置)
# 初始化大小，最小，最大
spring.datasource.dynamic.druid.initial-size=5
spring.datasource.dynamic.druid.min-idle=5
spring.datasource.dynamic.druid.max-active=20
# 配置获取连接等待超时的时间
spring.datasource.dynamic.druid.max-wait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.dynamic.druid.time-between-eviction-runs-millis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.dynamic.druid.min-evictable-idle-time-millis=300000
#spring.datasource.dynamic.druid.validation-query=SELECT 1 FROM DUAL
spring.datasource.dynamic.druid.test-while-idle=true
spring.datasource.dynamic.druid.test-on-borrow=false
spring.datasource.dynamic.druid.test-on-return=false
# 打开PSCache，并且指定每个连接上PSCache的大小（防火墙这一块一定不能开，sqlite不支持，会导致奇怪的异常）
spring.datasource.dynamic.druid.pool-prepared-statements=true
spring.datasource.dynamic.druid.max-pool-prepared-statement-per-connection-size=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙（启动sqlite时候需要关闭wall）
#spring.datasource.dynamic.druid.filters=stat,wall,slf4j
spring.datasource.dynamic.druid.filters=stat,slf4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.dynamic.druid.connectionProperties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
# 合并多个DruidDataSource的监控数据
spring.datasource.dynamic.druid.use-global-data-source-stat=true

详细配置

server.port=8080
mybatis-plus.mapper-locations=classpath:/mapper/*Mapper.xml
mybatis-plus.type-aliases-package=

# 去除druid配置 排除SpringBoot自带数据源
spring.autoconfigure.exclude=com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure

# 默认数据源 mysql、mariadb、oracle、db2、h2、hsql、postgresql、sqlserver、sqlite
# 多数据源暂不支持sqlite 报错误(处理方式关闭防火墙,spring.datasource.dynamic.druid.filters=stat,slf4j)
# java.lang.IllegalStateException: dbType not support : sqlite, url jdbc:sqlite:E:/test.db
# https://gitee.com/baomidou/dynamic-datasource-spring-boot-starter/issues/IVC5W
# https://gitee.com/baomidou/dynamic-datasource-spring-boot-starter/issues/I1NLER

spring.datasource.dynamic.primary=sqlite

# sqlite数据源配置
spring.datasource.dynamic.datasource.sqlite.username=test
spring.datasource.dynamic.datasource.sqlite.password=test
spring.datasource.dynamic.datasource.sqlite.url=jdbc:sqlite:D:/sqlite/database/db_demo.db
spring.datasource.dynamic.datasource.sqlite.driver-class-name=org.sqlite.JDBC

## dm数据源配置
#spring.datasource.dynamic.datasource.dm.username=SYSDBA
#spring.datasource.dynamic.datasource.dm.password=SYSDBA
#spring.datasource.dynamic.datasource.dm.url=jdbc:dm://localhost:5236/DMSERVER?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
#spring.datasource.dynamic.datasource.dm.driver-class-name=dm.jdbc.driver.DmDriver
#
## mariadb数据源配置
#spring.datasource.dynamic.datasource.mariadb.username=pig4cloud
#spring.datasource.dynamic.datasource.mariadb.password=pig4cloud
#spring.datasource.dynamic.datasource.mariadb.url=jdbc:mariadb://localhost:3306/teset?characterEncoding=utf-8&useSSL=false&useTimezone=true&serverTimezone=GMT%2B8
#spring.datasource.dynamic.datasource.mariadb.driver-class-name=org.mariadb.jdbc.Driver
#
## postgresql数据源配置
#spring.datasource.dynamic.datasource.postgresql.username=pig4cloud
#spring.datasource.dynamic.datasource.postgresql.password=pig4cloud
#spring.datasource.dynamic.datasource.postgresql.url=jdbc:postgresql://localhost:5432/switcher_pg
#spring.datasource.dynamic.datasource.postgresql.driver-class-name=org.postgresql.Driver
#
## h2数据源配置
#spring.datasource.dynamic.datasource.h2.username=pig4cloud
#spring.datasource.dynamic.datasource.h2.password=pig4cloud
#spring.datasource.dynamic.datasource.h2.url=jdbc:h2:mem:test
#spring.datasource.dynamic.datasource.h2.driver-class-name=org.h2.Driver
#
## mysql数据源配置
#spring.datasource.dynamic.datasource.mysql.username=root
#spring.datasource.dynamic.datasource.mysql.password=12345678
#spring.datasource.dynamic.datasource.mysql.url=jdbc\:mysql\://localhost\:3306/boss?useOldAliasMetadataBehavior\=true&useUnicode\=true&characterEncoding\=UTF-8&autoReconnect\=true&autoReconnectForPools\=true&failOverReadOnly\=false&connectTimeout\=0&socketTimeout\=0&allowMultiQueries\=true&useSSL\=false&serverTimezone\=Hongkong&rewriteBatchedStatements\=true
#spring.datasource.dynamic.datasource.mysql.driver-class-name=com.mysql.jdbc.Driver
#
## sqlserver数据源配置
#spring.datasource.dynamic.datasource.sqlserver.username=pig4cloud
#spring.datasource.dynamic.datasource.sqlserver.password=pig4cloud
#spring.datasource.dynamic.datasource.sqlserver.url=jdbc:sqlserver://localhost:2433;DatabaseName=test_db
#spring.datasource.dynamic.datasource.sqlserver.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
#
## oracle数据源配置
#spring.datasource.dynamic.datasource.oracle.username=pig4cloud
#spring.datasource.dynamic.datasource.oracle.password=pig4cloud
#spring.datasource.dynamic.datasource.oracle.url=jdbc:oracle:thin:@localhost:1521:orcl
#spring.datasource.dynamic.datasource.oracle.driver-class-name=oracle.jdbc.OracleDriver


# 连接池的配置信息 DRUID POOL Congif
# 全局druid参数，绝大部分值和默认保持一致。(现已支持的参数如下,不清楚含义不要乱设置)
# 初始化大小，最小，最大
spring.datasource.dynamic.druid.initial-size=5
spring.datasource.dynamic.druid.min-idle=5
spring.datasource.dynamic.druid.max-active=20
# 配置获取连接等待超时的时间
spring.datasource.dynamic.druid.max-wait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.dynamic.druid.time-between-eviction-runs-millis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.dynamic.druid.min-evictable-idle-time-millis=300000
#spring.datasource.dynamic.druid.validation-query=SELECT 1 FROM DUAL
spring.datasource.dynamic.druid.test-while-idle=true
spring.datasource.dynamic.druid.test-on-borrow=false
spring.datasource.dynamic.druid.test-on-return=false
# 打开PSCache，并且指定每个连接上PSCache的大小（防火墙这一块一定不能开，sqlite不支持，会导致奇怪的异常）
spring.datasource.dynamic.druid.pool-prepared-statements=true
spring.datasource.dynamic.druid.max-pool-prepared-statement-per-connection-size=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙（启动sqlite时候需要关闭wall）
#spring.datasource.dynamic.druid.filters=stat,wall,slf4j
spring.datasource.dynamic.druid.filters=stat,slf4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.dynamic.druid.connectionProperties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
# 合并多个DruidDataSource的监控数据
spring.datasource.dynamic.druid.use-global-data-source-stat=true
        
logging.level.druid=debug

pom添加驱动

        <!-- mysql数据源配置 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
        <!-- sqlserver数据源配置 -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
        </dependency>
        <!-- oracle数据源配置 -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>
        <!-- sqlite数据源配置 -->
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.28.0</version>
        </dependency>
        <!-- h2数据源配置 -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.200</version>
        </dependency>
        <!-- postgresql数据源配置 -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.14</version>
        </dependency>
        <!-- mariadb数据源配置 -->
        <dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
            <version>2.6.1</version>
        </dependency>
        <!-- dm数据源配置 -->
        <dependency>
            <groupId>com.dameng</groupId>
            <artifactId>Dm7JdbcDriver17</artifactId>
            <version>7.6.0.77</version>
        </dependency>

【异常2】dynamic-datasource Please check the setting of primary异常

Caused by: java.lang.RuntimeException: dynamic-datasource Please check the setting of primary

原因:根据不同的数据源进行切换，默认数据源

spring.datasource.dynamic.primary=oracle

参考文献

【掘金者说】配置支持在线yml互转properties

springboot整合druid抛出dbType not support : sqlite异常

dynamic-datasource-spring-boot-starter 多数据源配置

dynamic-datasource-spring-boot-starter

解决java.lang.ClassNotFoundException: com.mysql.jdbc.Driver问题

Spring Boot 集成 MyBatis和 SQL Server实践

dynamic-datasource-spring-boot-starter读写分离分析

springboot+mybatis多数据库切换功能，及如何兼容达梦等非常用数据库

docker centos7 命令行模式安装达梦数据库（dm7）

avatar
文章
阅读
粉丝