[mysql]java.sql.SQLException: Value'0000-00-00'异常解决办法

今天使用ssm做开发的时候，使用的是mysql数据库，其中一个表的字段是Date类型，如果该字段有值，切是日期类型的时候，显示没有问题；但是，如果是空的情况就会报错。1、解决思路 map.xml中的返回值设置成resultType="hashmap"，这样就不用考虑返回为空的情况了，返回的类型map<string, object>，结果还是报错。2、把返回值设置成类，新建一个类，使用string类型代替date类型，报以上错误

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

解决办法：

给jdbc url加上 zeroDateTimeBehavior参数： datasource.url=jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true

zeroDateTimeBehavior=round是为了指定MySql中的DateTime字段默认值查询时的处理方式；默认是抛出异常， 对于值为0000-00-00 00:00:00（默认值）的纪录，如下两种配置，会返回不同的结果： zeroDateTimeBehavior=round 0001-01-01 00:00:00.0

zeroDateTimeBehavior=convertToNull null

原文如下:

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 zeroDateTimeBehavior 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-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 should 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.

参考文档：

http://www.cnblogs.com/zhanglm125/p/4221344.html