Java. SQL. sqlexception: cannot convert value '2017-00-00 00:00:00 'from Column 7 to times

Source: Internet
Author: User
In the MySQL database, the datetime type is used to store the time. When reading this field using JDBC, The resultset should be used. gettimestamp () to get a java. SQL. timestamp type data. In this case, neither resultset. getdate () nor resultset. gettime () can be used, because the former does not include time data, and the latter does not include date data. However, you are using resultset. gettimestamp () is not completely secure. For example, if the value of the timestamp type field in the database is '2017-00-00 00:00:00 ', use this method to read data, an exception is thrown: cannot convert value '2017-00-00 00:00:00 'from Column 1 to timestamp, this is because JDBC cannot convert '2017-00-00 00:00:00 'into a java. SQL. timestamp. in Java, you want to create a java. util. it is also impossible to set the value to '2014-00-00 '. The oldest date should be '2014-01-01 00:00:00 '. So what should we do in the program? Solution here: datetimes with all-zero components (0000-00-00 ...) -These values can not be represented reliably in Java. connector/J 3.0.x always converted them to null when being read from a resultset. connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards. this behavior can be modified using the zero Datetimebehavior configuration property. the allowable values are: exception (the default), which throws an sqlexception with an sqlstate of s1009.converttonull, which returns NULL instead of the date. round, which rounds the date to the nearest closest value which is 0001-01.starting with connector/J 3.1.7, resultset. getstring () can be decoupled from this behavior via nodatetimestringsync = true (The default value is false) so that you can retrieve the unaltered all-zero value as a string. it shoshould be noted that this also precludes using any time zone conversions, therefore the driver will not allow you to enable nodatetimestringsync and usetimezone at the same time. therefore, add the zerodatetimebehavior information to the jdbc url to solve the problem: String url = "JDBC: mysql: // 10.149.51.80: 3306/test? Relaxautocommit = true & zerodatetimebehavior = converttonull ";

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.