Hibernate: could not execute native bulk manipulation query

Question

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?

Answer 1

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.

Answer 2

If you are using the SQLQuery Hibernate API, you could use the setParameterList(PARAM, COLLECTION) method.

Your query string can remain the same with its in clause and braces.

Answer 3

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..