search

apache / shardingsphere

Distributed SQL transaction & query engine for data sharding, scaling, encryption, and more - on any database.
Apache License 2.0
19.87k stars 6.73k forks source link

[jdbc]how to use Distributed Primary Key #19541

Closed peixingzhe closed 2 years ago

peixingzhe commented 2 years ago

spring boot config:

rules:
      sharding:
        tables:
          t_order: 
            actual-data-nodes: ds$->{0..1}.t_order_$->{1..31}
            database-strategy: 
              standard:
                sharding-column: phone 
                sharding-algorithm-name: database-class-base 
            table-strategy: 
              standard:
                sharding-column: logtime
                sharding-algorithm-name: table-class-base
            key-generate-strategy: 
              column: order_id 
              key-generator-name: snowflake 
        sharding-algorithms:
          database-class-base:
            type: CLASS_BASED
            props:
              strategy: STANDARD
              algorithmClassName: com.lhx.shardingjdbcdemo.MyShardingAlgorithm
          table-class-base:
            type: CLASS_BASED
            props:
              strategy: STANDARD
              algorithmClassName: com.lhx.shardingjdbcdemo.MyShardingAlgorithm11
        key-generators:
          snowflake:
            type: SNOWFLAKE

How do I specify the primary key column value when I insert a piece of data using MyBatis? example: insert into t_order(order_id, logtime, phone) values (#{orderId}, #{logtime}, #{phone}) How do I get #{orderId} with the Snowflake algorithm

TeslaCN commented 2 years ago

Hi @PeiMouRen You don't need to specify orderId. Just

insert into t_order (logtime, phone) values (#{logtime}, #{phone})
peixingzhe commented 2 years ago

Hi @TeslaCN This is the result of not specifying a primary key image

TeslaCN commented 2 years ago

Could you try putting a breakpoint into org.apache.shardingsphere.sharding.rule.ShardingRule and checking the configurations?

peixingzhe commented 2 years ago

image As you can see, the configuration is read, but it does not take effect。

peixingzhe commented 2 years ago

image This method is not called when executing INSERT, can someone help to solve this problem？

peixingzhe commented 2 years ago

Environment: jdk:1.8 spring boot:2.6.4 shardingsphere-jdbc:5.1.2 postgresql:12.6

spring boot config:

spring:
  shardingsphere:
    database:
      name:
    mode:
      type: Standalone
      repository:
        type: File
        props:
          path: .shardingsphere
      overwrite: true
    datasource:
      names: ds0, ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: org.postgresql.Driver
        jdbc-url: jdbc:postgresql://localhost:5432/shard1?stringtype=unspecified
        username: root
        password: 123
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: org.postgresql.Driver
        jdbc-url: jdbc:postgresql://localhost:5432/shard2?stringtype=unspecified
        username: root
        password: 123
    rules:
      sharding:
        tables:
          t_order: 
            actual-data-nodes: ds$->{0..1}.t_order_$->{0..1}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: database-inline
            table-strategy:
              standard:
                sharding-column: order_id 
                sharding-algorithm-name: t-order-inline
            key-generate-strategy: 
              column: order_id 
              key-generator-name: snowflake
        sharding-algorithms:
          database-inline:
            type: INLINE 
            props:
              algorithm-expression: ds$->{user_id % 2}
          t-order-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_$->{order_id % 2}
        key-generators:
          snowflake: 
            type: SNOWFLAKE 
    props:
      sql-show: true

DDL:

# ds0
create table t_order_0(order_id bigint, user_id int, remark varchar(200));
create table t_order_1(order_id bigint, user_id int, remark varchar(200));
# ds1
create table t_order_0(order_id bigint, user_id int, remark varchar(200));
create table t_order_1(order_id bigint, user_id int, remark varchar(200));

sql:


...
   @Insert("insert into t_order(user_id, remark) values ( #{userId}, #{remark})")
    void add4(@Param("userId") int userId, @Param("remark") String remark);
...

orderDao.add4(100, "test");

error: Because no order_id was generated. this error disappeared when I switched the data source to mysql.

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: java.lang.IllegalStateException: Insert statement does not support sharding table routing to multiple data nodes.
### The error may exist in com/lhx/shardingjdbcdemo/dao/OrderDao.java (best guess)
### The error may involve com.lhx.shardingjdbcdemo.dao.OrderDao.add4-Inline
### The error occurred while setting parameters
### SQL: insert into t_order(user_id, remark) values ( ?, ?)
TeslaCN commented 2 years ago

Thanks for your input, we will check this.

TeslaCN commented 2 years ago

I modify the example https://github.com/apache/shardingsphere/tree/master/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-spring-boot-mybatis-example to adapt PostgreSQL and run it. And the key generator worked.

The logic SQL doesn't have column order_id, the generated key is inserted into the actual SQL.

---------------------------- Insert Data ----------------------------
[INFO ] 2022-07-29 10:27:53,672 --main-- [ShardingSphere-SQL] Logic SQL: INSERT INTO t_order (user_id, address_id, status) VALUES (?, ?, ?); 
[INFO ] 2022-07-29 10:27:53,672 --main-- [ShardingSphere-SQL] SQLStatement: PostgreSQLInsertStatement(super=InsertStatement(super=AbstractSQLStatement(parameterCount=3, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=58, stopIndex=58, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=61, stopIndex=61, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=64, stopIndex=64, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), table=SimpleTableSegment(tableName=TableNameSegment(startIndex=12, stopIndex=18, identifier=IdentifierValue(value=t_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), insertColumns=Optional[InsertColumnsSegment(startIndex=20, stopIndex=48, columns=[ColumnSegment(startIndex=21, stopIndex=27, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Optional.empty), ColumnSegment(startIndex=30, stopIndex=39, identifier=IdentifierValue(value=address_id, quoteCharacter=NONE), owner=Optional.empty), ColumnSegment(startIndex=42, stopIndex=47, identifier=IdentifierValue(value=status, quoteCharacter=NONE), owner=Optional.empty)])], insertSelect=Optional.empty, values=[InsertValuesSegment(startIndex=57, stopIndex=65, values=[ParameterMarkerExpressionSegment(startIndex=58, stopIndex=58, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=61, stopIndex=61, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=64, stopIndex=64, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)])]), withSegment=Optional.empty) 
[INFO ] 2022-07-29 10:27:53,672 --main-- [ShardingSphere-SQL] Actual SQL: ds-1 ::: INSERT INTO t_order (user_id, address_id, status, order_id) VALUES (?, ?, ?, ?); ::: [1, 1, INSERT_TEST, 759722980832968704] 
[INFO ] 2022-07-29 10:27:53,679 --main-- [ShardingSphere-SQL] Logic SQL: INSERT INTO t_order_item (order_id, user_id, status) VALUES (?, ?, ?); 
[INFO ] 2022-07-29 10:27:53,679 --main-- [ShardingSphere-SQL] SQLStatement: PostgreSQLInsertStatement(super=InsertStatement(super=AbstractSQLStatement(parameterCount=3, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=61, stopIndex=61, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=64, stopIndex=64, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=67, stopIndex=67, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), table=SimpleTableSegment(tableName=TableNameSegment(startIndex=12, stopIndex=23, identifier=IdentifierValue(value=t_order_item, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), insertColumns=Optional[InsertColumnsSegment(startIndex=25, stopIndex=51, columns=[ColumnSegment(startIndex=26, stopIndex=33, identifier=IdentifierValue(value=order_id, quoteCharacter=NONE), owner=Optional.empty), ColumnSegment(startIndex=36, stopIndex=42, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Optional.empty), ColumnSegment(startIndex=45, stopIndex=50, identifier=IdentifierValue(value=status, quoteCharacter=NONE), owner=Optional.empty)])], insertSelect=Optional.empty, values=[InsertValuesSegment(startIndex=60, stopIndex=68, values=[ParameterMarkerExpressionSegment(startIndex=61, stopIndex=61, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=64, stopIndex=64, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=67, stopIndex=67, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)])]), withSegment=Optional.empty) 
[INFO ] 2022-07-29 10:27:53,679 --main-- [ShardingSphere-SQL] Actual SQL: ds-1 ::: INSERT INTO t_order_item (order_id, user_id, status, order_item_id) VALUES (?, ?, ?, ?); ::: [759722980832968704, 1, INSERT_TEST, 759722981160124417] 
[INFO ] 2022-07-29 10:27:53,681 --main-- [ShardingSphere-SQL] Logic SQL: INSERT INTO t_order (user_id, address_id, status) VALUES (?, ?, ?); 
[INFO ] 2022-07-29 10:27:53,681 --main-- [ShardingSphere-SQL] SQLStatement: PostgreSQLInsertStatement(super=InsertStatement(super=AbstractSQLStatement(parameterCount=3, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=58, stopIndex=58, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=61, stopIndex=61, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=64, stopIndex=64, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), table=SimpleTableSegment(tableName=TableNameSegment(startIndex=12, stopIndex=18, identifier=IdentifierValue(value=t_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), insertColumns=Optional[InsertColumnsSegment(startIndex=20, stopIndex=48, columns=[ColumnSegment(startIndex=21, stopIndex=27, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Optional.empty), ColumnSegment(startIndex=30, stopIndex=39, identifier=IdentifierValue(value=address_id, quoteCharacter=NONE), owner=Optional.empty), ColumnSegment(startIndex=42, stopIndex=47, identifier=IdentifierValue(value=status, quoteCharacter=NONE), owner=Optional.empty)])], insertSelect=Optional.empty, values=[InsertValuesSegment(startIndex=57, stopIndex=65, values=[ParameterMarkerExpressionSegment(startIndex=58, stopIndex=58, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=61, stopIndex=61, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=64, stopIndex=64, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)])]), withSegment=Optional.empty) 
[INFO ] 2022-07-29 10:27:53,681 --main-- [ShardingSphere-SQL] Actual SQL: ds-0 ::: INSERT INTO t_order (user_id, address_id, status, order_id) VALUES (?, ?, ?, ?); ::: [2, 2, INSERT_TEST, 759722981168513024] 
[INFO ] 2022-07-29 10:27:53,683 --main-- [ShardingSphere-SQL] Logic SQL: INSERT INTO t_order_item (order_id, user_id, status) VALUES (?, ?, ?); 
[INFO ] 2022-07-29 10:27:53,683 --main-- [ShardingSphere-SQL] SQLStatement: PostgreSQLInsertStatement(super=InsertStatement(super=AbstractSQLStatement(parameterCount=3, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=61, stopIndex=61, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=64, stopIndex=64, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=67, stopIndex=67, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), table=SimpleTableSegment(tableName=TableNameSegment(startIndex=12, stopIndex=23, identifier=IdentifierValue(value=t_order_item, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), insertColumns=Optional[InsertColumnsSegment(startIndex=25, stopIndex=51, columns=[ColumnSegment(startIndex=26, stopIndex=33, identifier=IdentifierValue(value=order_id, quoteCharacter=NONE), owner=Optional.empty), ColumnSegment(startIndex=36, stopIndex=42, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Optional.empty), ColumnSegment(startIndex=45, stopIndex=50, identifier=IdentifierValue(value=status, quoteCharacter=NONE), owner=Optional.empty)])], insertSelect=Optional.empty, values=[InsertValuesSegment(startIndex=60, stopIndex=68, values=[ParameterMarkerExpressionSegment(startIndex=61, stopIndex=61, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=64, stopIndex=64, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=67, stopIndex=67, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)])]), withSegment=Optional.empty) 
[INFO ] 2022-07-29 10:27:53,683 --main-- [ShardingSphere-SQL] Actual SQL: ds-0 ::: INSERT INTO t_order_item (order_id, user_id, status, order_item_id) VALUES (?, ?, ?, ?); ::: [759722981168513024, 2, INSERT_TEST, 759722981172707329] 
[INFO ] 2022-07-29 10:27:53,684 --main-- [ShardingSphere-SQL] Logic SQL: INSERT INTO t_order (user_id, address_id, status) VALUES (?, ?, ?); 
[INFO ] 2022-07-29 10:27:53,684 --main-- [ShardingSphere-SQL] SQLStatement: PostgreSQLInsertStatement(super=InsertStatement(super=AbstractSQLStatement(parameterCount=3, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=58, stopIndex=58, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=61, stopIndex=61, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=64, stopIndex=64, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), table=SimpleTableSegment(tableName=TableNameSegment(startIndex=12, stopIndex=18, identifier=IdentifierValue(value=t_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), insertColumns=Optional[InsertColumnsSegment(startIndex=20, stopIndex=48, columns=[ColumnSegment(startIndex=21, stopIndex=27, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Optional.empty), ColumnSegment(startIndex=30, stopIndex=39, identifier=IdentifierValue(value=address_id, quoteCharacter=NONE), owner=Optional.empty), ColumnSegment(startIndex=42, stopIndex=47, identifier=IdentifierValue(value=status, quoteCharacter=NONE), owner=Optional.empty)])], insertSelect=Optional.empty, values=[InsertValuesSegment(startIndex=57, stopIndex=65, values=[ParameterMarkerExpressionSegment(startIndex=58, stopIndex=58, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=61, stopIndex=61, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=64, stopIndex=64, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)])]), withSegment=Optional.empty) 
[INFO ] 2022-07-29 10:27:53,684 --main-- [ShardingSphere-SQL] Actual SQL: ds-1 ::: INSERT INTO t_order (user_id, address_id, status, order_id) VALUES (?, ?, ?, ?); ::: [3, 3, INSERT_TEST, 759722981181095936] 
[INFO ] 2022-07-29 10:27:53,685 --main-- [ShardingSphere-SQL] Logic SQL: INSERT INTO t_order_item (order_id, user_id, status) VALUES (?, ?, ?); 
[INFO ] 2022-07-29 10:27:53,685 --main-- [ShardingSphere-SQL] SQLStatement: PostgreSQLInsertStatement(super=InsertStatement(super=AbstractSQLStatement(parameterCount=3, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=61, stopIndex=61, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=64, stopIndex=64, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=67, stopIndex=67, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), table=SimpleTableSegment(tableName=TableNameSegment(startIndex=12, stopIndex=23, identifier=IdentifierValue(value=t_order_item, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), insertColumns=Optional[InsertColumnsSegment(startIndex=25, stopIndex=51, columns=[ColumnSegment(startIndex=26, stopIndex=33, identifier=IdentifierValue(value=order_id, quoteCharacter=NONE), owner=Optional.empty), ColumnSegment(startIndex=36, stopIndex=42, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Optional.empty), ColumnSegment(startIndex=45, stopIndex=50, identifier=IdentifierValue(value=status, quoteCharacter=NONE), owner=Optional.empty)])], insertSelect=Optional.empty, values=[InsertValuesSegment(startIndex=60, stopIndex=68, values=[ParameterMarkerExpressionSegment(startIndex=61, stopIndex=61, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=64, stopIndex=64, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=67, stopIndex=67, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)])]), withSegment=Optional.empty) 
[INFO ] 2022-07-29 10:27:53,685 --main-- [ShardingSphere-SQL] Actual SQL: ds-1 ::: INSERT INTO t_order_item (order_id, user_id, status, order_item_id) VALUES (?, ?, ?, ?); ::: [759722981181095936, 3, INSERT_TEST, 759722981185290241]

Changes:

wuweijie@wuweijie-ubuntu:~/projects/shardingsphere/examples (branch: master!)
$ git diff | cat
diff --git a/examples/example-core/example-spring-mybatis/src/main/resources/META-INF/mappers/AddressMapper.xml b/examples/example-core/example-spring-mybatis/src/main/resources/META-INF/mappers/AddressMapper.xml
index 2ade012b8c3..690a8481fe9 100644
--- a/examples/example-core/example-spring-mybatis/src/main/resources/META-INF/mappers/AddressMapper.xml
+++ b/examples/example-core/example-spring-mybatis/src/main/resources/META-INF/mappers/AddressMapper.xml
@@ -24,7 +24,7 @@
     </resultMap>

     <update id="createTableIfNotExists">
-        CREATE TABLE IF NOT EXISTS t_address (address_id BIGINT NOT NULL, address_name VARCHAR(100) NOT NULL, PRIMARY KEY (address_id));
+        CREATE TABLE IF NOT EXISTS t_address (address_id BIGINT primary key, address_name VARCHAR(100) NOT NULL);
     </update>

     <update id="truncateTable">
diff --git a/examples/example-core/example-spring-mybatis/src/main/resources/META-INF/mappers/OrderItemMapper.xml b/examples/example-core/example-spring-mybatis/src/main/resources/META-INF/mappers/OrderItemMapper.xml
index 48aae459c00..d605bc541ec 100644
--- a/examples/example-core/example-spring-mybatis/src/main/resources/META-INF/mappers/OrderItemMapper.xml
+++ b/examples/example-core/example-spring-mybatis/src/main/resources/META-INF/mappers/OrderItemMapper.xml
@@ -26,7 +26,7 @@
     </resultMap>

     <update id="createTableIfNotExists">
-        CREATE TABLE IF NOT EXISTS t_order_item (order_item_id BIGINT AUTO_INCREMENT, order_id BIGINT, user_id INT NOT NULL, status VARCHAR(50) , PRIMARY KEY (order_item_id));
+        CREATE TABLE IF NOT EXISTS t_order_item (order_item_id BIGINT primary key, order_id BIGINT, user_id INT NOT NULL, status VARCHAR(50));
     </update>

     <update id="truncateTable">
diff --git a/examples/example-core/example-spring-mybatis/src/main/resources/META-INF/mappers/OrderMapper.xml b/examples/example-core/example-spring-mybatis/src/main/resources/META-INF/mappers/OrderMapper.xml
index d32c53c0296..040b2d08b29 100644
--- a/examples/example-core/example-spring-mybatis/src/main/resources/META-INF/mappers/OrderMapper.xml
+++ b/examples/example-core/example-spring-mybatis/src/main/resources/META-INF/mappers/OrderMapper.xml
@@ -26,7 +26,7 @@
     </resultMap>

     <update id="createTableIfNotExists">
-        CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT AUTO_INCREMENT, user_id INT NOT NULL, address_id BIGINT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));
+        CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT primary key, user_id INT NOT NULL, address_id BIGINT NOT NULL, status VARCHAR(50));
     </update>

     <update id="truncateTable">
diff --git a/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-spring-boot-mybatis-example/pom.xml b/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-spring-boot-mybatis-example/pom.xml
index 0a2d2a1125a..158661be90f 100644
--- a/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-spring-boot-mybatis-example/pom.xml
+++ b/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-spring-boot-mybatis-example/pom.xml
@@ -48,5 +48,10 @@
             <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-starter-test</artifactId>
         </dependency>
+        <dependency>
+            <groupId>com.h2database</groupId>
+            <artifactId>h2</artifactId>
+            <version>1.4.200</version>
+        </dependency>
     </dependencies>
 </project>
diff --git a/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-spring-boot-mybatis-example/src/main/resources/application-sharding-databases.properties b/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-spring-boot-mybatis-example/src/main/resources/application-sharding-databases.properties
index 1630ddc75b2..b06120ed98c 100644
--- a/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-spring-boot-mybatis-example/src/main/resources/application-sharding-databases.properties
+++ b/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-spring-boot-mybatis-example/src/main/resources/application-sharding-databases.properties
@@ -17,17 +17,17 @@

 spring.shardingsphere.datasource.names=ds-0,ds-1

-spring.shardingsphere.datasource.ds-0.jdbc-url=jdbc:mysql://localhost:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
+spring.shardingsphere.datasource.ds-0.jdbc-url=jdbc:postgresql://localhost:5432/demo_ds_0
 spring.shardingsphere.datasource.ds-0.type=com.zaxxer.hikari.HikariDataSource
-spring.shardingsphere.datasource.ds-0.driver-class-name=com.mysql.jdbc.Driver
-spring.shardingsphere.datasource.ds-0.username=root
-spring.shardingsphere.datasource.ds-0.password=
+spring.shardingsphere.datasource.ds-0.driver-class-name=org.postgresql.Driver
+spring.shardingsphere.datasource.ds-0.username=postgres
+spring.shardingsphere.datasource.ds-0.password=postgres

-spring.shardingsphere.datasource.ds-1.jdbc-url=jdbc:mysql://localhost:3306/demo_ds_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
+spring.shardingsphere.datasource.ds-1.jdbc-url=jdbc:postgresql://localhost:5432/demo_ds_1
 spring.shardingsphere.datasource.ds-1.type=com.zaxxer.hikari.HikariDataSource
-spring.shardingsphere.datasource.ds-1.driver-class-name=com.mysql.jdbc.Driver
-spring.shardingsphere.datasource.ds-1.username=root
-spring.shardingsphere.datasource.ds-1.password=
+spring.shardingsphere.datasource.ds-1.driver-class-name=org.postgresql.Driver
+spring.shardingsphere.datasource.ds-1.username=postgres
+spring.shardingsphere.datasource.ds-1.password=postgres

 spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=user_id
 spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=database-inline
strongduanmu commented 2 years ago

Hi @PeiMouRen, can you check your config file in user home .shardingsphere dirctory? It may be caused by old config. You can also provide a demo to reproduce this exception.

peixingzhe commented 2 years ago

I think the problem has to do with the schema of PG,because my table is placed under the new schema, instead of the default public schema, we can see someinformation with DEBUG, the generatedKeyContextis null,because the shema does't contain any tables. image

image image

peixingzhe commented 2 years ago

When I put the table into the default schema(public), distributed primary keys were generated normally.

strongduanmu commented 2 years ago

@PeiMouRen Yes, currently ShardingSphere only supports specifying schema in SQL, otherwise the default public schema is used. You can try to specify schema in SQL to solve this problem.

peixingzhe commented 2 years ago

ok,the problem is solved when i specifying the schema in sql!!! May I ask again if ShardingSphere plans to make adjustments to this problem?

strongduanmu commented 2 years ago

ok,the problem is solved when i specifying the schema in sql!!! May I ask again if ShardingSphere plans to make adjustments to this problem?

If there are no other higher priority requirements, we plan to provide support for PostgreSQL search path in the next release.

peixingzhe commented 2 years ago

ok. thank you.