java的JDBC连接mysql数据库时对于Datetime类型出现Cannot convert value '0000-00-00 00:00:00' from column 9 to TIMESTAMP

http://blog.youkuaiyun.com/pjchen/archive/2008/04/19/2308245.aspx

 

采用Hibernate连接mysql5的数据库，由于表中采用了一个datetime时段为此老是出现如下的 

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 9 to TIMESTAMP.
 at com.mysql.jdbc.ResultSet.getTimestampFromBytes(ResultSet.java:6864)
 at com.mysql.jdbc.ResultSet.getTimestampInternal(ResultSet.java:6899)
 at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:6218)
 at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:6256)
 at org.hibernate.type.TimestampType.get(TimestampType.java:30)
 at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
 at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)

查看了mysql5的帮助文档对于datetime的解释如下

Datetimes with all-zero components (0000-00-00 ...) — These values can not be represented 关于所有Datetime类型由0组成的数据，这些值不能在java中被可靠的表示
reliably in Java.
Connector/J 3.0.x always converted them to NULL when being read from a ResultSet.
当这些值正在从ResultSet容器中读取时候，Connector/J 3.0.x 一直把他们转换为NULL值。

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.
依照JDBC和SQL的标准这些值碰到的最正确的处理方式就是在缺省情况下产生异常
This behavior can be modified using the zeroDateTimeBehavior configuration property. The allowable values are:
JDBC允许用下列的值对zeroDateTimeBehavior 属性来设置这些处理方式，

exception (the default), which throws an SQLException with an SQLState of S1009.
设置为exception 异常（缺省）用一个SQLState的s1009错误号来抛出一个异常
convertToNull, which returns NULL instead of the date.
设置为convertToNull，用NULL值来代替这个日期类型
round, which rounds the date to the nearest closest value which is 0001-01-01.
设置为round，则围绕这个日期最接近的值（0001-01-01）来代替

你可以修改你的jdbc连接

jdbc:mysql://localhost/schoolmis?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull

///////////////////////////////////////////

http://www.javaeye.com/problems/11339

Java代码

1. <Resource name = "jdbc/sqlds" auth = "Container" type = "javax.sql.DataSource" maxIdle = "30"       
2. maxWait = "10000" maxActive = "10" username = "root" password = "123456"     
3. driverClassName = "com.mysql.jdbc.Driver"     
4. url = "jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull" />     
5.   
6. <Resource name = "jdbc/sqlds" auth = "Container" type = "javax.sql.DataSource" maxIdle = "30"    
7. maxWait = "10000" maxActive = "10" username = "root" password = "123456"  
8. driverClassName = "com.mysql.jdbc.Driver"  
9. url = "jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull" />  

<Resource name = "jdbc/sqlds" auth = "Container" type = "javax.sql.DataSource" maxIdle = "30"    
maxWait = "10000" maxActive = "10" username = "root" password = "123456"  
driverClassName = "com.mysql.jdbc.Driver"  
url = "jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull" />  

<Resource name = "jdbc/sqlds" auth = "Container" type = "javax.sql.DataSource" maxIdle = "30" 
maxWait = "10000" maxActive = "10" username = "root" password = "123456"
driverClassName = "com.mysql.jdbc.Driver"
url = "jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull" />

applicationContext.xml中配置如下：

Java代码

1. <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">      
2.         <property name="jndiName" value="java:comp/env/jdbc/sqlds"></property>      
3.     </bean>      
4.     <bean id="sessionFactory"     
5.         class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">      
6.         <property name="dataSource">      
7.             <ref bean="dataSource" />      
8.         </property>     
9.   
10. <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">   
11.         <property name="jndiName" value="java:comp/env/jdbc/sqlds"></property>   
12.     </bean>   
13.     <bean id="sessionFactory"  
14.         class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">   
15.         <property name="dataSource">   
16.             <ref bean="dataSource" />   
17.         </property>  

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">   
        <property name="jndiName" value="java:comp/env/jdbc/sqlds"></property>   
    </bean>   
    <bean id="sessionFactory"  
        class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">   
        <property name="dataSource">   
            <ref bean="dataSource" />   
        </property>  

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
        <property name="jndiName" value="java:comp/env/jdbc/sqlds"></property>
    </bean>
    <bean id="sessionFactory"
        class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource">
            <ref bean="dataSource" />
        </property>

是由于MySQL对于超过8小时的连接就会强行关闭，而数据库连接池并不知道，仍然持有失效的连接，当从连接池中取出并使用这种连接的时候就会出错 ,这个该怎么改呢？万分感谢！

采纳的答案

2009-02-16 LucasLee (中级程序员)

设置
<Resource...
validationQuery=SELECT 1
testOnBorrow=true
...>
这样可以在每次从连接池取连接时验证连接是否有效。
或者可以考虑使用testWhileIdle=true

参考DBCP Reference：http://commons.apache.org/dbcp/configuration.html

---

提问者对于答案的评价：
不错，HRoger 也很好，谢谢两位了。

////////////////////////////////////////

转帖自http://www.blogjava.net/hilor/articles/164814.html

在使用MySql 时, 数据库中的字段类型是timestamp的,默认为0000-00-00, 会发生异常:java.sql.SQLException:   Value   '0000-00-00 '   can   not   be   represented   as   java.sql.Timestamp


解决办法:

给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