Hibernate：无法执行本地批量操作查询 [英] Hibernate: could not execute native bulk manipulation query

问题描述

尝试使用原生SQL更新数据时出现此错误。这是我的脚本：

  update weight_note_receipt set pledge_id =：pledge其中wn_id in（：wns）
  

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 
  

当我更新时（使用 query.executeUpdate（）），它会引发以下错误：

请求处理失败;嵌套的异常是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，'

是因为输入字符串太长了吗？

这就是为什么你不能为以下查询准备语句：

• select * from myTable order by？


• select id，f1，？从myTable


• select * from？。

，因为为每个参数赋值会改变查询执行路径（请记住准备好的语句的查询会被解析一次并生成一个执行路径）。

同样的规则适用于Hibernate查询解析器，您不应该为参数赋一个改变查询结构的值。

赋值一个字符串 1，2，3 到SHOULD-TO-BE-A-NUMBER参数是一样的，事实上第一个查询将被翻译成与<$ c $相同c> update weight_note_receipt set pledge_id =：pledge其中wn_id =：wns 但第二个将被翻译为 update weight_note_receipt set pledge_id =：pledge其中（wn_id =：x1或wn_id =：x2或wn_id =：x3），显然不同的查询具有不同的执行路径。因此，即使Hibernate没有抛出异常，你的数据库会。

I got this error when trying to update data using native SQL. This is my script:

update weight_note_receipt set pledge_id =:pledge  where wn_id in (:wns)

wns is the string that contains more than 1 wn_id like this:

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:

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,'

Is it because the input string is too long?

解决方案

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 myTable
• select * 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.

Assigning an string with values 1, 2, 3 to 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 = :wns but 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.

So even if Hibernate did not throw an exception, your database would.

这篇关于Hibernate：无法执行本地批量操作查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！