MySQL JDBC processing 0 date format Egg pain problem-also considered a bug

Recently in writing a database access to the intermediate platform, using MySQL JDBC processing Some date data, encountered some abnormal problem, to everyone happy!

First of all, what kind of date data does this hurt?

DATE 0000-00-00

DATETIME 0000-00-00 00:00:00

TIMESTAMP 0000-00-00 00:00:00

Time 25:21:22

For the first 3 cases, the direct use of JDBC Read, will definitely error, the error message is similar to this:

Value ' 0000-00-00 ' can not represented as Java.sql.Date

Or

Value ' 0000-00-00 ' can not represented as Java.sql.Timestamp

Why? Date value even 0, also corresponds to 1970-01-01, this perverted date format, do not know that the odd man thought out.

For such a problem, the first way many small partners think of is to set one on the jdbc URL parameter:zerodatetimebehavior=converttonull

You can let the Java program without error! Because JDBC internal discovery is a 0 days-period format, it is converted to null return.

Validation can solve the problem, but, however, a user's database is written to such a piece of data, but the program does return NULL, the user believes that this is not the data he wants (for example, when doing data migration, the target field is not empty, this will be an error), The user would like to see data in this format in the 0000-00-00. What do we do?

First of all, the parameter zerodatetimebehavior=converttonull must be removed, or the program will always get null, do not know what the database data is. But this procedure will error, whether with GetString or GetObject will be error.

It is verified that GetBytes () will not give an error, and then by the resulting bytes[] array, new string (bytes[]), you can get a string that is consistent with the database. Seems to have solved the problem?

No, more egg-ache problem arises, when JDBC enabled flow mode or cursor mode, getBytes () will also report the same error, after verifying that get various types will error, this is too painful, no time to see the source code, according to my estimation, MySQL jdbc in the flow mode and cursor mode, For some type conversion processing of the result set, the handling of code that does not reuse normal mode (which is instinctively static data by default) causes this problem.

But for some big data processing, the flow pattern or cursor pattern must be enabled in the business application, so it's stuck in a dead end-so I think it's a bug for MySQL jdbc.

But the user's problem must be solved, for this, have to use a special treatment method, abnormal judgment, I think you see here should think this is the world's most Earth way, hehe! That is to capture the message described above, if found to be the 0 days-year format to resolve, ready to submit the official bug, hope to fix it as soon as possible.

In the end, the time type, MySQL, is the length of the egg, not the hour on the date: minutes: seconds, so its number of hours can be more than 24, but this value allows Java to parse the error, As a result, the time type of MySQL is processed to avoid problems. Usually use GetBytes () to get the value, and then use the new String (byte[]) to get the specific value, of course, the first to empty.