Java Hibernate：无法执行本机批量操作查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20625692/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate: could not execute native bulk manipulation query
提问by Kien Dang Ngoc
I got this error when trying to update data using native SQL. This is my script:
尝试使用本机 SQL 更新数据时出现此错误。这是我的脚本：
update weight_note_receipt set pledge_id =:pledge where wn_id in (:wns)
wnsis the string that contains more than 1 wn_idlike this:
wns是包含多于 1 的字符串，wn_id如下所示：
222,226,228,251,256,262,263,264,265,266,267,272,281,286,294,296,299,301,302,303,306,307,330,332,333,337,338,339,341,368,371,376,377,378,379,380,381,385,391,397,423,424,443,452,454,461,462,463,464,490,503,504,521,525,528,529,530,532,533,549,554,560,561,564,565,566,567,569,570,595,598,600,603,605,606,607,644,646,649,653,661,662,663,667,669,678,683,752,1039,1075,258,259,260,261,268,269,270,287,304,305,308,325,334,604,643,647,648,659,660,664,665,666,704,709,753,754,757,758,809,834,846,861,872,879,882,911,913,916,919,920,164
When I update (using query.executeUpdate()), it throws the following error:
当我更新（使用query.executeUpdate()）时，它会引发以下错误：
Request processing failed; nested exception is org.hibernate.exception.DataException: could not execute native bulk manipulation query] with root cause com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: '222,226,228,251,256,262,263,264,265,266,267,272,281,286,294,296,299,301,302,303,306,307,330,332,333,337,338,339,341,368,371,376,'
请求处理失败；嵌套异常是org.hibernate.exception.DataException：无法根本原因com.mysql.jdbc.MysqlDataTruncation执行本机本体操纵查询]：数据截断：截断不正确DOUBLE值：“222,226,228,251,256,262,263,264,265,266,267,272,281,286,294,296,299,301,302,303,306,307,330,332,333,337,338,339,341,368,371,376，”
Is it because the input string is too long?
是不是因为输入字符串太长？
采纳答案by Amir Pashazadeh
When having parameterized queries in databases (prepared statements), assigning values to parameters MUST NOT change the structure and execution path of the query (otherwise databases don't treat them as parameterized queries and will throw exception).
当在数据库中进行参数化查询（准备语句）时，为参数赋值不能改变查询的结构和执行路径（否则数据库不会将它们视为参数化查询并会抛出异常）。
That's why you can't have prepared statements for queries like:
这就是为什么您不能为以下查询准备好语句的原因：
select * from myTable order by ?select id, f1, ? from myTableselect * from ?.
select * from myTable order by ?select id, f1, ? from myTableselect * from ?.
because assigning a value to each parameter changes the query execution path (remember that prepared statements' query is parsed once and results a single execution path).
因为为每个参数分配一个值会改变查询执行路径（请记住，准备好的语句的查询被解析一次并产生单个执行路径）。
The same rules applies to Hibernate query parser, you shall not assign a parameter a value which changes the query structure.
相同的规则适用于 Hibernate 查询解析器，您不应为参数分配改变查询结构的值。
Assigning an string with values 1, 2, 3to a SHOULD-TO-BE-A-NUMBER parameters is just the same, in fact the first query will be translated just the same as update weight_note_receipt set pledge_id =:pledge where wn_id = :wnsbut the second one will be translated as update weight_note_receipt set pledge_id =:pledge where (wn_id = :x1 or wn_id = :x2 or wn_id = :x3), obviously different queries with different execution paths.
将带有值的字符串分配1, 2, 3给 SHOULD-TO-BE-A-NUMBER 参数是一样的，实际上第一个查询将被翻译为update weight_note_receipt set pledge_id =:pledge where wn_id = :wns与第二个update weight_note_receipt set pledge_id =:pledge where (wn_id = :x1 or wn_id = :x2 or wn_id = :x3)查询相同，显然不同的查询具有不同的执行路径.
So even if Hibernate did not throw an exception, your database would.
因此，即使 Hibernate 没有抛出异常，您的数据库也会。
回答by Karthikeyan M
If you are using the SQLQuery Hibernate API, you could use the setParameterList(PARAM, COLLECTION)method.
如果您使用的是 SQLQuery Hibernate API，则可以使用该setParameterList(PARAM, COLLECTION)方法。
Your query string can remain the same with its inclause and braces.
您的查询字符串可以与其in子句和大括号保持不变。
回答by Biswajit Sahu
If client is sending a data which size is more than the size specified in the database , then it throws org.hibernate.exception.GenericJDBCException: could not execute native bulk manipulation query at org.hibernate.exception.SQLStateConverter.handledNonSpecificException..
如果客户端发送的数据大小大于数据库中指定的大小，则会抛出 org.hibernate.exception.GenericJDBCException: could not execute native bulk operation query at org.hibernate.exception.SQLStateConverter.handledNonSpecificException..
