shardingsphere-jdbc sqlserver读写分离踩坑记录

1，shardingsphere-jdbc-core-spring-boot-starter
pom maven依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.1.1</version>
</dependency>

yml配置：

shardingsphere:
  datasource:
    names: master,slave
    master:
      type: com.zaxxer.hikari.HikariDataSource
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      url: jdbc:sqlserver://192.168.80.226:1433;DatabaseName=SkyBlueWhole
      username: sa
      password: sql
      hikari:
        minimum-idle: 10
        idle-timeout: 600000
        maximum-pool-size: 10
        auto-commit: true
        pool-name: MyHakariCP
        max-lifetime: 1800000
        connection-timeout: 30000
        connection-test-query: SELECT 1
    slave:
      type: com.zaxxer.hikari.HikariDataSource
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=slaveSkyBlue
      username: sa
      password: sql
      hikari:
        minimum-idle: 10
        idle-timeout: 600000
        maximum-pool-size: 10
        auto-commit: true
        pool-name: MyHakariCP
        max-lifetime: 1800000
        connection-timeout: 30000
        connection-test-query: SELECT 1
  enabled: true
  mode:
    type: Memory
  props:
    sql:
      show: true
    sql-show: true #打印sql
    sql-federation-enabled: true  #嵌套查询和子查询
  rules:
    readwrite-splitting:
      data-sources:
        load-balancers:
          roundRobin:
            type: ROUND_ROBIN
        master-slave:
          props:
            auto-aware-data-source-name: master
            load-balancer-name: roundRobin
            read-data-source-names: slave
            write-data-source-name: master
          type: Static

发现只支持普通单表查询
嵌套查询，表达式会报错

c.s.p.business.BusinessExceptionHandler  : Error attempting to get column '' from result set.  Cause: java.sql.SQLFeatureNotSupportedException: can't get index from columnLabel[].
; can't get index from columnLabel[].; nested exception is java.sql.SQLFeatureNotSupportedException: can't get index from columnLabel[].

2，改用sharding-jdbc-spring-boot-starter
说是4.1.1支持子查询和关联查询

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

配置和shardingsphere-jdbc-core-spring-boot-starter基本一样，除了url必须要改成jdbc-url其他都一样。shardingsphere-jdbc-core-spring-boot-starter用url和jdbc-url都可以

shardingsphere:
  datasource:
    names: master,slave
    master:
      type: com.zaxxer.hikari.HikariDataSource
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      jdbc-url: jdbc:sqlserver://192.168.80.226:1433;DatabaseName=SkyBlueWhole
      username: sa
      password: sql
      hikari:
        minimum-idle: 10
        idle-timeout: 600000
        maximum-pool-size: 10
        auto-commit: true
        pool-name: MyHakariCP
        max-lifetime: 1800000
        connection-timeout: 30000
        connection-test-query: SELECT 1
    slave:
      type: com.zaxxer.hikari.HikariDataSource
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      jdbc-url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=slaveSkyBlue
      username: sa
      password: sql
      hikari:
        minimum-idle: 10
        idle-timeout: 600000
        maximum-pool-size: 10
        auto-commit: true
        pool-name: MyHakariCP
        max-lifetime: 1800000
        connection-timeout: 30000
        connection-test-query: SELECT 1
  enabled: true
  mode:
    type: Memory
  props:
    sql:
      show: true
    sql-show: true #打印sql
    sql-federation-enabled: true  #嵌套查询和子查询
  sharding:
    default-data-source-name: master
  rules:
    readwrite-splitting:
      data-sources:
        load-balancers:
          roundRobin:
            type: ROUND_ROBIN
        master-slave:
          props:
            auto-aware-data-source-name: master
            load-balancer-name: roundRobin
            read-data-source-names: slave
            write-data-source-name: master
          type: Static

启动报错：
和springBoot健康检查冲突了
增加DataSourceHealthConfig之后启动不报错了

import org.springframework.beans.factory.ObjectProvider;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.actuate.autoconfigure.jdbc.DataSourceHealthContributorAutoConfiguration;
import org.springframework.boot.actuate.health.AbstractHealthIndicator;
import org.springframework.boot.actuate.jdbc.DataSourceHealthIndicator;
import org.springframework.boot.jdbc.metadata.DataSourcePoolMetadataProvider;
import org.springframework.context.annotation.Configuration;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.util.Map;

@Configuration
public class DataSourceHealthConfig extends DataSourceHealthContributorAutoConfiguration {
 
    @Value("${spring.datasource.dbcp2.validation-query:select 1}")
    private String defaultQuery;
 
 
    public DataSourceHealthConfig(Map<String, DataSource> dataSources, ObjectProvider<DataSourcePoolMetadataProvider> metadataProviders) {
        super(dataSources, metadataProviders);
    }
 
    @Override
    protected AbstractHealthIndicator createIndicator(DataSource source) {
        DataSourceHealthIndicator indicator = (DataSourceHealthIndicator) super.createIndicator(source);
        if (!StringUtils.hasText(indicator.getQuery())) {
            indicator.setQuery(defaultQuery);
        }
        return indicator;
    }
}

说是支持子查询，发现连普通查询都不支持，各种报错
可能shardingsphere相对mysql,oracle对sqlserver支持没那么好，放弃了