[GitHub] [shardingsphere] Mr-Zhe commented on issue #12768: I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries

[GitBox <gi...@apache.org>]

Mr-Zhe commented on issue #12768:
URL: https://github.com/apache/shardingsphere/issues/12768#issuecomment-927617069


   #### My Example:
   ```yml
   spring:
     shardingsphere:
       props:
         sql-show: true
       datasource:
         names: database-0,database-1
         database-0:
           type: com.zaxxer.hikari.HikariDataSource
           driver-class-name: com.mysql.cj.jdbc.Driver
           jdbc-url: jdbc:mysql://127.0.0.1:3306/spring-sharding-jdbc1?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&rewriteBatchedStatements=true
           username: xxx
           password: xxx
         database-1:
           type: com.zaxxer.hikari.HikariDataSource
           driver-class-name: com.mysql.cj.jdbc.Driver
           jdbc-url: jdbc:mysql://127.0.0.1:3306/spring-sharding-jdbc2?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&rewriteBatchedStatements=true
           username: xxx
           password: xxx
       rules:
         sharding:
           binding-tables: t_order,t_user
           default-database-strategy:
             complex:
               sharding-columns: order_id,user_id
               sharding-algorithm-name: database-complex
           tables:
             t_user:
               actual-data-nodes: database-$->{0..1}.t_user$->{0..3}
               table-strategy:
                 standard:
                   sharding-column: user_id
                   sharding-algorithm-name: user-inline
               key-generate-strategy:
                 column: user_id
                 key-generator-name: snowflake
             t_order:
               actual-data-nodes: database-$->{0..1}.t_order$->{0..3}
               table-strategy:
                 complex:
                   sharding-columns: order_id,user_id
                   sharding-algorithm-name: order-complex-keys
               key-generate-strategy:
                 column: order_id
                 key-generator-name: snowflake
           sharding-algorithms:
             database-complex:
               type: CLASS_BASED
               props:
                 strategy: COMPLEX
                 algorithmClassName: com.tencent.springshardjdbc.sharding.algorithm.DatabaseCustomComplexKeysShardingAlgorithm
             user-inline:
               type: INLINE
               props:
                 algorithm-expression: t_user$->{user_id % 4}
             order-complex-keys:
               type: CLASS_BASED
               props:
                 strategy: COMPLEX
                 algorithmClassName: com.tencent.springshardjdbc.sharding.algorithm.OrderTableCustomComplexKeysShardingAlgorithm
           key-generators:
             snowflake:
               type: SNOWFLAKE
               props:
                 worker-id: 123
   ```
   
   `DatabaseCustomComplexKeysShardingAlgorithm`
   ```java
   public class DatabaseCustomComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
   
       @Getter
       @Setter
       private Properties props = new Properties();
   
   
       @Override
       public Collection<String> doSharding(Collection<String> availableTargetNames,
               ComplexKeysShardingValue<Long> shardingValue) {
           if (availableTargetNames.size() == 1) {
               return availableTargetNames;
           }
           Map<String, Collection<Long>> shardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
           long sum = shardingValuesMap.values().stream().flatMap(Collection::stream).mapToLong(num -> num).sum();
           int index = (int) (sum % 2);
           return Lists.newArrayList(new ArrayList<>(availableTargetNames).get(index));
       }
   
       @Override
       public void init() {
           System.out.println("init");
       }
   
       @Override
       public String getType() {
           return "CustomComplexKeys";
       }
   }
   ```
   
   `OrderTableCustomComplexKeysShardingAlgorithm`
   ```java
   public class OrderTableCustomComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
   
       @Getter
       @Setter
       private Properties props = new Properties();
   
   
       @Override
       public Collection<String> doSharding(Collection<String> availableTargetNames,
               ComplexKeysShardingValue<Long> shardingValue) {
           if (availableTargetNames.size() == 1) {
               return availableTargetNames;
           }
           Map<String, Collection<Long>> shardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
           long sum = shardingValuesMap.values().stream().flatMap(Collection::stream).mapToLong(num -> num).sum();
           int index = (int) (sum % 4);
           return Lists.newArrayList(new ArrayList<>(availableTargetNames).get(index));
       }
   
       @Override
       public void init() {
           System.out.println("init");
       }
   
       @Override
       public String getType() {
           return "CustomComplexKeys";
       }
   }
   ```
   #### SQL:
   ```sql
           select t1.*
           from t_order t1,
                t_user t2
           where t1.user_id = t2.user_id and t2.user_id = #{pageDTO.userId}
             limit #{pageDTO.pageNumber},#{pageDTO.pageSize}
   ```
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org