mysql-tip java.sql.SQLException:Cannot Convert value ' 0000-00-00 00:00:00 ' from column 7 to TIMESTAMP.

Use the datetime type to store the time in the MySQL database, use Resultset.gettimestamp () When reading this field in JDBC, and get a java.sql.Timestamp type of data. Neither resultset.getdate () nor resultset.gettime () can be used here, as the former does not include the time data, which does not include the date data.

However, it is not completely secure when using Resultset.gettimestamp (), for example, when a field value of type timestamp in a database is ' 0000-00-00 00:00:00 ', an exception is thrown when reading with this method: cannot Convert value ' 0000-00-00 00:00:00 ' from column 1 to TIMESTAMP, this is because JDBC cannot be ' 0000-00-00 00:00:00 ' Conversion to a java.sql.Timestamp, in Java, want to create a java.util.Date, so its value is ' 0000-00-00 ' is also impossible, the oldest date should be ' 0001-01-01 00:00:00 '.

So what to do in the program to pinch? Here's the solution:

DateTimes with All-zero components (0000-00-00 ...)-these values can is not a represented reliably in Java. CONNECTOR/J 3.0. x always converted them to NULL when being read from a resultset.connector/j 3.1throwsAn exception bydefaultWhen these values is encountered as ThisThe most correct behavior according to the JDBC and SQL standards. This behavior can is modified using the Zerodatetimebehavior configuration property. The allowable values are:exception ( thedefault), whichthrowsAn SQLException with an SQLState of S1009.converttonull, which returns NULL instead of the date.round, which rounds T He date to the nearest closest value which is0001-01-01. Starting with Connector/j 3.1.7, resultset.getstring () can be decoupled from ThisBehavior via nodatetimestringsync=true(ThedefaultValue isfalseSo, you can retrieve the unaltered All-zero value as a String. It should is noted that ThisAlso precludes using any time zone conversions, therefore the driver won't allow you to enable Nodatetimestringsync and Usetimezone at the same time.

So, by adding zerodatetimebehavior information to the JDBC URL, you can either resolve:

String url = "Jdbc:mysql://10.149.51.80:3306/test?relaxautocommit=true&zerodatetimebehavior=converttonull";

mysql-prompts Java.sql.SQLException:Cannot convert value ' 0000-00-00 00:00:00 ' from column 7 to TIMESTAMP.