shardingJDBC5水平分片报错。

Question

shardingJDBC4和5都遇到了垂直分片没事，水平分片报错。
链接数据库超时，实则管理软件可以用。
跟着尚硅谷sharding5课程走的。

# 应用名称
spring.application.name=sharging-jdbc-demo
# 开发环境设置
spring.profiles.active=dev
# 内存模式
spring.shardingsphere.mode.type=Memory

#========================数据源配置
# 配置真实数据源
spring.shardingsphere.datasource.names=server-user,server-order0,server-order1

# 配置第 1 个数据源
spring.shardingsphere.datasource.server-user.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-user.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-user.jdbc-url=jdbc:mysql://192.168.100.201:3301/db_user
spring.shardingsphere.datasource.server-user.username=root
spring.shardingsphere.datasource.server-user.password=123456

# 配置第 2 个数据源
spring.shardingsphere.datasource.server-order0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-order0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-order0.jdbc-url=jdbc:mysql://192.168.100.201:3310/db_order
spring.shardingsphere.datasource.server-order0.username=root
spring.shardingsphere.datasource.server-order0.password=123456

# 配置第 3 个数据源
spring.shardingsphere.datasource.server-order1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-order1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-order1.jdbc-url=jdbc:mysql://192.168.100.201:3311/db_order
spring.shardingsphere.datasource.server-order1.username=root
spring.shardingsphere.datasource.server-order1.password=123456

#========================标准分片表配置（数据节点配置）
# spring.shardingsphere.rules.sharding.tables.<table-name>.actual-data-nodes=值
# 值由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持 inline 表达式。
# <table-name>：逻辑表名
spring.shardingsphere.rules.sharding.tables.t_user.actual-data-nodes=server-user.t_user
#spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=server-order0.t_order0,server-order0.t_order1,server-order1.t_order0,server-order1.t_order1
#inline表达式写法
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=server-order$->{0..1}.t_order$->{0..1}

#------------------------分库策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-column=user_id
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-algorithm-name=alg_inline_userid

#------------------------分片算法配置
# 行表达式分片算法
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_inline_userid.type=INLINE
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_inline_userid.props.algorithm-expression=server-order$->{user_id % 2}

#------------------------分表策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=order_no
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=alg_hash_mod


#------------------------分片算法配置
# 哈希取模分片算法
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash_mod.type=HASH_MOD
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_hash_mod.props.sharding-count=2


# 取模分片算法
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_mod.type=MOD
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_mod.props.sharding-count=2


# 打印SQl
spring.shardingsphere.props.sql-show=true

java.lang.IllegalStateException: Failed to load ApplicationContext

    xxx（省略了直接看Caused by）
Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'userController': Unsatisfied dependency expressed through field 'userMapper'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'userMapper' defined in file [E:\XUEXI\workspace\S5\target\classes\com\example\s5\Mapper\UserMapper.class]: Unsatisfied dependency expressed through bean property 'sqlSessionFactory'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'sqlSessionFactory' defined in class path resource [com/baomidou/mybatisplus/autoconfigure/MybatisPlusAutoConfiguration.class]: Unsatisfied dependency expressed through method 'sqlSessionFactory' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'shardingSphereDataSource' defined in class path resource [org/apache/shardingsphere/spring/boot/ShardingSphereAutoConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'shardingSphereDataSource' threw exception; nested exception is java.lang.IllegalArgumentException: Communications link failure
    
    ... 84 more
Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'userMapper' defined in file [E:\XUEXI\workspace\S5\target\classes\com\example\s5\Mapper\UserMapper.class]: Unsatisfied dependency expressed through bean property 'sqlSessionFactory'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'sqlSessionFactory' defined in class path resource [com/baomidou/mybatisplus/autoconfigure/MybatisPlusAutoConfiguration.class]: Unsatisfied dependency expressed through method 'sqlSessionFactory' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'shardingSphereDataSource' defined in class path resource [org/apache/shardingsphere/spring/boot/ShardingSphereAutoConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'shardingSphereDataSource' threw exception; nested exception is java.lang.IllegalArgumentException: Communications link failure
    
    ... 105 more
Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'sqlSessionFactory' defined in class path resource [com/baomidou/mybatisplus/autoconfigure/MybatisPlusAutoConfiguration.class]: Unsatisfied dependency expressed through method 'sqlSessionFactory' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'shardingSphereDataSource' defined in class path resource [org/apache/shardingsphere/spring/boot/ShardingSphereAutoConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'shardingSphereDataSource' threw exception; nested exception is java.lang.IllegalArgumentException: Communications link failure
    
    ... 117 more
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'shardingSphereDataSource' defined in class path resource [org/apache/shardingsphere/spring/boot/ShardingSphereAutoConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'shardingSphereDataSource' threw exception; nested exception is java.lang.IllegalArgumentException: Communications link failure
    
Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'shardingSphereDataSource' threw exception; nested exception is java.lang.IllegalArgumentException: Communications link failure
    
Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
    
    ... 164 more
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure
    
    ... 175 more
Caused by: java.net.ConnectException: Connection timed out: connect
    
    ... 178 more

Answer 1

• 给你找了一篇非常好的博客，你可以看看是否有帮助，链接：数据库分库分表（五）shardingjdbc水平分库+水平分表demo
• 除此之外, 这篇博客: springBoot基于yml配置整合ShardingJDBC实现分库分表中的 三、水平分库水平分表 部分也许能够解决你的问题, 你可以仔细阅读以下内容或跳转源博客中阅读:
  

• application-shardingDataBase.yml

  #水平分库水平分表配置,两个数据库，每个数据库中两张表
  spring:
    shardingsphere:
      # 数据源名称 db1 、db2
      datasource:
        names: db1,db2
        db1:
          type: com.alibaba.druid.pool.DruidDataSource
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://192.168.119.51:3306/test1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
          username: root
          password: root
        db2:
          type: com.alibaba.druid.pool.DruidDataSource
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://192.168.119.52:3306/test1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
          username: root
          password: root
  
      # 配置数据库的分布，表的分布
      sharding:
        tables:
          user_info:
            # db1:user_info1 user_info2; db2:user_info1,user_info2;
            actual-data-nodes: db$->{1..2}.user_info$->{1..2}
            # 指定user_info表 主键id 生成策略为 SNOWFLAKE
            key-generator:
              column: id
              type: SNOWFLAKE
            # 指定数据库分片策略 约定user_info值是偶数添加到user_info1中，奇数添加到user_info2中
            database-strategy:
              inline:
                sharding-column: id
                algorithm-expression: db$->{id%2+1}
              # 指定表分片策略
            table-strategy:
              inline:
                #根据年龄插入不同的表，如果年龄大于15岁插入user_info1表，如果年龄小于或等于15插入user_info2表
  #              sharding-column: age
  #              algorithm-expression: user_info$->{age>15?1:2}
                #约定id值是偶数添加到user_info1表，如果id是奇数添加到user_info2表
                sharding-column: id
                algorithm-expression: user_info$->{id%2+1}
      props:
        sql:
          show: true
  
  

  开始测试，application.yml中的active值改成shardingDataBase启动项目
  出现下面信息标识启动成功
  
  开始测试
  
  返回值为偶数
  控制台日志
  

  根据策略，在第一个数据库的user_info1表中
  
  
  
  
  查询测试结果
  

• 以下回答由chatgpt基于相关博客总结生成:

  很抱歉，参考资料中并没有提到具体的错误和提示信息，请提供更详细的信息以便定位问题。另外，需要提供使用的数据库类型和版本，以及相关的配置信息和网络设置，例如是否存在防火墙等。