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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.