JDBC cannot automatically truncate problems when inserting data for too long

Problem

　　When JDBC operates the MySQL database, the following error is reported when the data length of the insert or update operation exceeds the declared maximum length of the table field, resulting in a failure to insert properly:

2

However, when working directly on the MySQL client, it is possible to find that the inserted data is only truncated automatically:

The ' diff ' field has a length of 3, and the following inserts an extra-long data ' 1234 ':

It is possible to insert successfully and truncate the data and insert the result as ' 123 ', but MySQL warns (warning).

But by the way, JDBC is sqlexception.

Solve

By default, JDBC's jdbccomplianttruncation (truncated) parameter is true, and modification to false resolves. (MySQL itself can also set the relevant configuration, see Mysql--sql mode detailed)

Change the URL of the JDBC connection to:

Jdbc:mysql://localhost:3306/table_name?jdbccomplianttruncation=false

However, there is another problem, try to update the data of type int, if the insert data is very long (that is, more than the size that can be loaded), the maximum value of the field will be inserted:

So the best solution is to make a judgment in the program + TRUNCATE it!

It's a way of solving it.

The problem is that JDBC does not automatically truncate the long inserted fields and insert the data smoothly, so if you get the declaration length of the field in SQL, and then use the left truncation function provided by MySQL, it can be realized, but it needs the subquery to be efficient.

To obtain the declared length of a field in a library table sql:

Plus the Left () function:

StringUpdate ="UpdateWm_poi_dispatch_settingSetDiff=left('4567',
(SelectCharacter_maximum_length fromInformation_schema. COLUMNS
WHEREtable_name= 'wm_poi_dispatch_setting'  andTable_schema= 'poi_test'  andcolumn_name= 'diff'Limit0,1)
) "+"wherewm_poi_id= 1
";

Low efficiency, please ignore.

JDBC cannot automatically truncate problems when inserting data for too long