You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "Miliving (via GitHub)" <gi...@apache.org> on 2023/04/25 04:48:07 UTC
[GitHub] [shardingsphere] Miliving opened a new issue, #25314: foreach batchInsert Error
Miliving opened a new issue, #25314:
URL: https://github.com/apache/shardingsphere/issues/25314
I use shardingsphere-jdbc-core-spring-boot-starter(v5.0.0) into my springBoot project. Encountered a batch insert error problem, found through the log: the sql parsing parameters of batch insert can only get the first element of the parameter list array, the following elements cannot be taken, and an exception is thrown:Error updating database. Cause: java.sql.SQLException: No value specified for parameter 4.
interface mapper is:
int batchInsert(@Param("entities") Collection<BootConfigStaffSyncEntity> entities);
mybatis code is:
```html
<insert id="batchInsert">
<foreach collection="entities" item="one" separator=";">
INSERT INTO `boot_config_staff_sync`
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="one.id!=null"> `id`, </if>
<if test="one.bootId!=null"> `boot_id`, </if>
<if test="one.staffId!=null"> `staff_id`, </if>
<if test="one.createStaffId!=null"> `create_staff_id`, </if>
<if test="one.createTime!=null"> `create_time`, </if>
<if test="one.updateStaffId!=null"> `update_staff_id`, </if>
<if test="one.updateTime!=null"> `update_time`, </if>
</trim>
<trim prefix="VALUE (" suffix=")" suffixOverrides=",">
<if test="one.id!=null"> #{one.id}, </if>
<if test="one.bootId!=null"> #{one.bootId}, </if>
<if test="one.staffId!=null"> #{one.staffId}, </if>
<if test="one.createStaffId!=null"> #{one.createStaffId}, </if>
<if test="one.createTime!=null"> #{one.createTime}, </if>
<if test="one.updateStaffId!=null"> #{one.updateStaffId}, </if>
<if test="one.updateTime!=null"> #{one.updateTime}, </if>
</trim>
</foreach>;
</insert>
```
--
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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #25314: foreach batchInsert Error
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #25314:
URL: https://github.com/apache/shardingsphere/issues/25314#issuecomment-1524518965
You can refer ShardingSpherePreparedStatement#addBatch
--
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] JHangADC commented on issue #25314: foreach batchInsert Error
Posted by "JHangADC (via GitHub)" <gi...@apache.org>.
JHangADC commented on issue #25314:
URL: https://github.com/apache/shardingsphere/issues/25314#issuecomment-1659648810
so how to fix this ?
--
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] Miliving commented on issue #25314: foreach batchInsert Error
Posted by "Miliving (via GitHub)" <gi...@apache.org>.
Miliving commented on issue #25314:
URL: https://github.com/apache/shardingsphere/issues/25314#issuecomment-1524403316
Can you tell me which specific class or interface it is?
--
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] Miliving commented on issue #25314: foreach batchInsert Error
Posted by "Miliving (via GitHub)" <gi...@apache.org>.
Miliving commented on issue #25314:
URL: https://github.com/apache/shardingsphere/issues/25314#issuecomment-1524353347
After testing, I found that non sharded tables support batch updates and only support batch inserts in the form of insert (xxx) values ((..), (..), (..)). Partitioned tables do not support batch updates, and support batch inserts in the form of insert (xxx) values ((..), (..), (..)).
--
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] strongduanmu commented on issue #25314: foreach batchInsert Error
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #25314:
URL: https://github.com/apache/shardingsphere/issues/25314#issuecomment-1521158126
In addition, I searched the history issue #6665, and ShardingSphere currently does not support multiple SQL statements in a logical SQL. You can try the JDBC addBatch and executeBatch interfaces.
--
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] Miliving commented on issue #25314: foreach batchInsert Error
Posted by "Miliving (via GitHub)" <gi...@apache.org>.
Miliving commented on issue #25314:
URL: https://github.com/apache/shardingsphere/issues/25314#issuecomment-1524530680
thx
--
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] github-actions[bot] commented on issue #25314: foreach batchInsert Error
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #25314:
URL: https://github.com/apache/shardingsphere/issues/25314#issuecomment-1707902466
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
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] strongduanmu commented on issue #25314: foreach batchInsert Error
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #25314:
URL: https://github.com/apache/shardingsphere/issues/25314#issuecomment-1521151382
Hi @Miliving, can you debug into orm framework? ShardingSphere is only responsible for handling the JDBC interface layer logic.
--
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] Miliving commented on issue #25314: foreach batchInsert Error
Posted by "Miliving (via GitHub)" <gi...@apache.org>.
Miliving commented on issue #25314:
URL: https://github.com/apache/shardingsphere/issues/25314#issuecomment-1522605794
Does this mean that I can use the original jdbc API to bypass shardingshsphere's syntax parsing? For example:
```java
Connection connection =getConnection();
connection.setAutoCommit(false);
PreparedStatement statement = connection.prepareStatement(“INSERT INTO TABLEX VALUES(?, ?)”);
//record1
statement.setInt(1, 1);
statement.setString(2, “Cujo”);
statement.addBatch();
//record2
statement.setInt(1, 2);
statement.setString(2, “Fred”);
statement.addBatch();
//record3
statement.setInt(1, 3);
statement.setString(2, “Mark”);
statement.addBatch();
int [] counts = statement.executeBatch();
connection.commit();
```
--
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] Miliving commented on issue #25314: foreach batchInsert Error
Posted by "Miliving (via GitHub)" <gi...@apache.org>.
Miliving commented on issue #25314:
URL: https://github.com/apache/shardingsphere/issues/25314#issuecomment-1667101173
不分片的表：1、批量拆入：只支持insert (xxx) values ((111),(222),(333))形式 2、批量更新：支持BatchUpdate(符合条件数据都会被更新)
--
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] JHangADC commented on issue #25314: foreach batchInsert Error
Posted by "JHangADC (via GitHub)" <gi...@apache.org>.
JHangADC commented on issue #25314:
URL: https://github.com/apache/shardingsphere/issues/25314#issuecomment-1667104171
> 不分片的表：1、批量插入：只支持insert (xxx) values ((111),(222),(333))形式；2、批量更新：支持BatchUpdate(符合条件数据都会被更新) 分片表：1、批量插入：只支持insert (xxx) values ((111),(222),(333))形式；2、批量更新：不支持，只会更新第一条数据，并且更新条件必须带分片字段 对于批量插入需要自己计算要插入的表名然后分组批量执行。
跟我现在的解决一样
--
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] strongduanmu commented on issue #25314: foreach batchInsert Error
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #25314:
URL: https://github.com/apache/shardingsphere/issues/25314#issuecomment-1524385007
> Does this mean that I can use the original jdbc API to bypass shardingshsphere's syntax parsing? For example:
No. I mean to use the batch interface of shardingsphere jdbc to execute 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] Miliving commented on issue #25314: foreach batchInsert Error
Posted by "Miliving (via GitHub)" <gi...@apache.org>.
Miliving commented on issue #25314:
URL: https://github.com/apache/shardingsphere/issues/25314#issuecomment-1667103143
不分片的表：1、批量插入：只支持insert (xxx) values ((111),(222),(333))形式；2、批量更新：支持BatchUpdate(符合条件数据都会被更新)
分片表：1、批量插入：只支持insert (xxx) values ((111),(222),(333))形式；2、批量更新：不支持，只会更新第一条数据，并且更新条件必须带分片字段
对于批量插入需要自己计算要插入的表名然后分组批量执行。
--
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