MySQL异常： Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp

待我称王封你为后i 2022-08-06 07:21 185阅读 0赞

http://blog.sina.com.cn/s/blog\_5fbb09180100u6xb.html

如果数据库里timestampe字段为0000-00-00 00:00:00，则ORM取数据时映射到java.sql.Date类型时会有问题，如下：

但是在取数据的时候出现这样一个错误：  
Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp

两种解决办法（高亮颜色我加的），两种方法都是在数据库连接串处追加设置，  
现举例说下两种情况的结果：  
原连接串：driver-url=jdbc:mysql://127.0.0.1/test  
使用参数zeroDateTimeBehavior：  
取值exception：  
driver-url=jdbc:mysql://127.0.0.1/test?zeroDateTimeBehavior=exception  
结果：  
java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp  
  
取值convertToNull：  
driver-url=jdbc:mysql://127.0.0.1/test?zeroDateTimeBehavior=convertToNull  
结果：  
ClientDto\[id:1,email:test@hotmail.com,civilite: 测试用户,prenom:贾,nom:某某,birthday:1992-03-18,telephone:12312345678,address:测 试用户的住址,regionid:1,postcode:111000,city:大 连,country:france,batiment:110\#,escalier:2,code:151515,interphone:050505,etage: 东侧走廊,appartment:205,createdate:2010-03-16 09:30:21.0,status:0,totalprice:0.00,totalnum:0,lastdate:null,lasttotal:0.00,\]  
  
取值round：  
driver-url=jdbc:mysql://127.0.0.1/test?zeroDateTimeBehavior=round  
结果：  
ClientDto\[id:1,email:test@hotmail.com,civilite: 测试用户,prenom:贾,nom:某某,birthday:1992-03-18,telephone:12312345678,address:测 试用户的住址,regionid:1,postcode:111000,city:大 连,country:france,batiment:110\#,escalier:2,code:151515,interphone:050505,etage: 东侧走廊,appartment:205,createdate:2010-03-16 09:30:21.0,status:0,totalprice:0.00,totalnum:0,lastdate:0001-01-01 00:00:00.0,lasttotal:0.00,\]  
  
另一参数noDatetimeStringSync：  
driver-url=jdbc:mysql://127.0.0.1/test?noDatetimeStringSync=true  
结果：  
ClientDto\[id:1,email:test@hotmail.com,civilite: 测试用户,prenom:贾,nom:某某,birthday:1992-03-18,telephone:12312345678,address:测 试用户的住址,regionid:1,postcode:111000,city:大 连,country:france,batiment:110\#,escalier:2,code:151515,interphone:050505,etage: 东侧走廊,appartment:205,createdate:2010-03-16 09:30:21,status:0,totalprice:0.00,totalnum:0,lastdate:0000-00-00 00:00:00,lasttotal:0.00,\]QL