how to use CrudRepository.save of jpa？i dont want my sql route to all data node

[yage-czy]

hello, my project use jpa + shardingjdbc
when i use CrudRepository.save to save my data to database，jpa auto generated sql like
update table_a set field_a = 1 , field_ab = 2 where id = 3
but my sharding-key is another field "dealership_id"。So my sql execute route to all data node.
I would like to ask, is there any way to deal with this situation?

[menghaoranss]

could you provide a demo with GitHub to reproduce your issue.

[yage-czy]

could you provide a demo with GitHub to reproduce your issue.

try this "https://gitee.com/contraller/yage-hello/tree/hello-jpa/"

[zhaojinchao95]

could you provide a demo with GitHub to reproduce your issue.

try this "https://gitee.com/contraller/yage-hello/tree/hello-jpa/"

I download your project demo, not found about ShardingSphere anywhere.

[zhaojinchao95]

hello, my project use jpa + shardingjdbc when i use CrudRepository.save to save my data to database，jpa auto generated sql like update table_a set field_a = 1 , field_ab = 2 where id = 3 but my sharding-key is another field "dealership_id"。So my sql execute route to all data node. I would like to ask, is there any way to deal with this situation?

I think your sql must be contains sharding_key column.

[yage-czy]

could you provide a demo with GitHub to reproduce your issue.

try this "https://gitee.com/contraller/yage-hello/tree/hello-jpa/"

I download your project demo, not found about ShardingSphere anywhere.

https://gitee.com/contraller/yage-hello/blob/hello-jpa/yage-demo/pom.xml
i use
<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId> <version>5.0.0</version> </dependency>

[yage-czy]

hello, my project use jpa + shardingjdbc when i use CrudRepository.save to save my data to database，jpa auto generated sql like update table_a set field_a = 1 , field_ab = 2 where id = 3 but my sharding-key is another field "dealership_id"。So my sql execute route to all data node. I would like to ask, is there any way to deal with this situation?

I think your sql must be contains sharding_key column.

#970 (comment)

[zhaojinchao95]

hello, my project use jpa + shardingjdbc when i use CrudRepository.save to save my data to database，jpa auto generated sql like update table_a set field_a = 1 , field_ab = 2 where id = 3 but my sharding-key is another field "dealership_id"。So my sql execute route to all data node. I would like to ask, is there any way to deal with this situation?

I think your sql must be contains sharding_key column.

#970 (comment)

ShardingSphere not supprot route all nodes when you execute insert or save sql, Now.

  if (!routeContext.isSingleRouting() && !shardingRule.isBroadcastTable(tableName)) {
            boolean isSingleDataNode = routeContext.getOriginalDataNodes().stream().allMatch(dataNodes -> dataNodes.size() == 1);
            Preconditions.checkState(isSingleDataNode, "Insert statement does not support sharding table routing to multiple data nodes.");
        }

[zhaojinchao95]

Maybe you can use sql comment way to update one data source, https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/special-api/sharding/hint/

[yage-czy]

hello, my project use jpa + shardingjdbc when i use CrudRepository.save to save my data to database，jpa auto generated sql like update table_a set field_a = 1 , field_ab = 2 where id = 3 but my sharding-key is another field "dealership_id"。So my sql execute route to all data node. I would like to ask, is there any way to deal with this situation?

I think your sql must be contains sharding_key column.

#970 (comment)

ShardingSphere not supprot route all nodes when you execute insert or save sql, Now.

  if (!routeContext.isSingleRouting() && !shardingRule.isBroadcastTable(tableName)) {
            boolean isSingleDataNode = routeContext.getOriginalDataNodes().stream().allMatch(dataNodes -> dataNodes.size() == 1);
            Preconditions.checkState(isSingleDataNode, "Insert statement does not support sharding table routing to multiple data nodes.");
        }

here is my log, this means my sql route to 2 node,aoo node?
|- 7d70a0473c4bb3b2 -|- 7d70a0473c4bb3b2 -|- yage-demo -|- 2022-01-21 10:37:23.730 -|- INFO -|- [nio-9000-exec-1] -|- ShardingSphere-SQL [74 ] -|- Logic SQL: update ygdm_user set active_flag=?, create_time=?, create_user=?, modify_time=?, modify_user=?, version_num=?, password=?, username=? where id=? |- 7d70a0473c4bb3b2 -|- 7d70a0473c4bb3b2 -|- yage-demo -|- 2022-01-21 10:37:23.730 -|- INFO -|- [nio-9000-exec-1] -|- ShardingSphere-SQL [74 ] -|- SQLStatement: MySQLUpdateStatement(orderBy=Optional.empty, limit=Optional.empty) |- 7d70a0473c4bb3b2 -|- 7d70a0473c4bb3b2 -|- yage-demo -|- 2022-01-21 10:37:23.730 -|- INFO -|- [nio-9000-exec-1] -|- ShardingSphere-SQL [74 ] -|- Actual SQL: master-1 ::: update ygdm_user set active_flag=?, create_time=?, create_user=?, modify_time=?, modify_user=?, version_num=?, password=?, username=? where id=? ::: [1, 2022-01-20 14:02:34.0, 1, 2022-01-20 14:02:34.0, 1, 17, yage, 古力娜扎, 12] |- 7d70a0473c4bb3b2 -|- 7d70a0473c4bb3b2 -|- yage-demo -|- 2022-01-21 10:37:23.731 -|- INFO -|- [nio-9000-exec-1] -|- ShardingSphere-SQL [74 ] -|- Actual SQL: master-2 ::: update ygdm_user set active_flag=?, create_time=?, create_user=?, modify_time=?, modify_user=?, version_num=?, password=?, username=? where id=? ::: [1, 2022-01-20 14:02:34.0, 1, 2022-01-20 14:02:34.0, 1, 17, yage, 古力娜扎, 12]

[yage-czy]

Maybe you can use sql comment way to update one data source, https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/special-api/sharding/hint/

so the solution is to find some way to change the source code of CrudRepository.save of spring jpa? to add some comment?

[zhaojinchao95]

Actual SQL: master-1 ::: update ygdm_user set active_flag=?, create_time=?, create_user=?, modify_time=?, modify_user=?, version_num=?, password=?,

Yes, Please add your sharding_key in your sql where.

[zhaojinchao95]

Maybe you can use sql comment way to update one data source, https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/special-api/sharding/hint/

so the solution is to find some way to change the source code of CrudRepository.save of spring jpa? to add some comment?

You can see SQL HINT? Is this suit you?

[yage-czy]

想到另一个办法，我可以在ComplexKeysShardingAlgorithm的实现类里获取到SQL信息吗？如果可以的话我也可以尝试着重新指定数据节点。

[zhaojinchao95]

Transaction english language, please.

[terrymanu]

@yage-czy Hi, any updage?

[yage-czy]

@yage-czy Hi, any updage?

https://gitee.com/contraller/yage-hello/blob/hello-spring-batch&mybatis/yage-demo/src/main/java/org/yage/hello/demo/config/YageEmptyInterceptor.java
我自己重写了jpa的拦截器暂时解决了这个问题，但是我认为这不是一个完美的解决方案，还是希望shardingsphere官方可以兼容这个问题。

[github-actions[bot]]

Hello , this issue has not received a reply for several days.
This issue is supposed to be closed.

[yage-czy]

Hi, any update?

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.