You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/01/20 10:48:33 UTC
[GitHub] [shardingsphere] yage-czy opened a new issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
yage-czy opened a new issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949
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?
--
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
[GitHub] [shardingsphere] zjcnb commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
zjcnb commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1018110139
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/
--
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
[GitHub] [shardingsphere] yage-czy commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
yage-czy commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1046397681
> @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官方可以兼容这个问题。
--
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
[GitHub] [shardingsphere] zjcnb commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
zjcnb commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1018118506
> 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.
--
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
[GitHub] [shardingsphere] zjcnb commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
zjcnb commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1018146126
>
Transaction english language, please.
--
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
[GitHub] [shardingsphere] yage-czy commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
yage-czy commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1018105210
> > > 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>
`
--
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
[GitHub] [shardingsphere] terrymanu commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1042895124
@yage-czy Hi, any updage?
--
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
[GitHub] [shardingsphere] yage-czy removed a comment on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
yage-czy removed a comment on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1018472864
**方案一 -> 使用JPA的EmptyInterceptor**
- insert : sharding可天然支持
- select : sharding可天然支持
- update : 继承org.hibernate.EmptyInterceptor，在onPrepareStatement(String sql)方法中，对sql进行解析，获取其中的分片键，然后手动在sql语句后面拼接字符串" and 分片键 = 获取到的分片值"，即可实现
- delete : 使用@Query注解手动写delete语句，需要注意的是原始的"SimpleJpaRepository.delete"方法不能再使用，否则同样会发生路由到所有数据节点的问题
--
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
[GitHub] [shardingsphere] yage-czy commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
yage-czy commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1018113931
> 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?
--
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
[GitHub] [shardingsphere] zjcnb commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
zjcnb commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1018109453
> > > 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)](https://github.com/apache/shardingsphere/issues/970#issuecomment-1017241107)
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.");
}
```
--
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
[GitHub] [shardingsphere] zjcnb commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
zjcnb commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1018103443
> > 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.
--
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
[GitHub] [shardingsphere] yage-czy commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
yage-czy commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1017424985
> could you provide a demo with GitHub to reproduce your issue.
try this "https://gitee.com/contraller/yage-hello/tree/hello-jpa/"
--
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
[GitHub] [shardingsphere] zjcnb commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
zjcnb commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1018105042
> 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.
--
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
[GitHub] [shardingsphere] menghaoranss commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
menghaoranss commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1017417338
could you provide a demo with GitHub to reproduce your issue.
--
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
[GitHub] [shardingsphere] yage-czy commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
yage-czy commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1018106031
> > 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.
https://github.com/apache/shardingsphere/issues/970#issuecomment-1017241107
--
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
[GitHub] [shardingsphere] yage-czy commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
yage-czy commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1018112691
> > > > 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)](https://github.com/apache/shardingsphere/issues/970#issuecomment-1017241107)
>
> 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]
`
--
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
[GitHub] [shardingsphere] zjcnb commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
zjcnb commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1018121073
> > 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?
--
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
[GitHub] [shardingsphere] yage-czy commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
yage-czy commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1018132277
想到另一个办法，我可以在ComplexKeysShardingAlgorithm的实现类里获取到SQL信息吗？如果可以的话我也可以尝试着重新指定数据节点。
--
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
[GitHub] [shardingsphere] yage-czy commented on issue #14949: how to use CrudRepository.save of jpa？i dont want my sql route to all data node
Posted by GitBox <gi...@apache.org>.
yage-czy commented on issue #14949:
URL: https://github.com/apache/shardingsphere/issues/14949#issuecomment-1018472864
**方案一 -> 使用JPA的EmptyInterceptor**
- insert : sharding可天然支持
- select : sharding可天然支持
- update : 继承org.hibernate.EmptyInterceptor，在onPrepareStatement(String sql)方法中，对sql进行解析，获取其中的分片键，然后手动在sql语句后面拼接字符串" and 分片键 = 获取到的分片值"，即可实现
- delete : 使用@Query注解手动写delete语句，需要注意的是原始的"SimpleJpaRepository.delete"方法不能再使用，否则同样会发生路由到所有数据节点的问题
--
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