java的JDBC串連mysql資料庫時對於Datetime類型出現Cannot convert value ’0000-00-00 00:00:00′ from column

文章目錄

• 採納的答案

http://blog.csdn.net/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