Full error message:
Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not execute query at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103) at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2223) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104) at org.hibernate.loader.Loader.list(Loader.java:2099) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) at admin.MembersTableModel.<init>(MembersTableModel.java:43) at admin.AdminOptionsForm.initComponents(AdminOptionsForm.java:98) at admin.AdminOptionsForm.<init>(AdminOptionsForm.java:32) at managemembers.Main.main(Main.java:45) Caused by: 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 com.mchange.v2.c3p0.impl.NewProxyResultSet.getTimestamp(NewProxyResultSet.java:3394) at org.hibernate.type.TimestampType.get(TimestampType.java:30) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154) at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81) at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2096) at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1380) at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1308) at org.hibernate.loader.Loader.getRow(Loader.java:1206) at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:580) at org.hibernate.loader.Loader.doQuery(Loader.java:701) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) at org.hibernate.loader.Loader.doList(Loader.java:2220) ... 11 more
My example:
// the Java class
public class Member { // some private member attributes private Date addedDate; // proper getter and setter methods }
<!– the Hibernate mapping–>
<hibernate-mapping> <class name="managemembers.Member" table="member"> <!-- some mappings here--> <property name="addedDate" type="timestamp" column="added_date"/> </class> </hibernate-mapping>
# The MySQL table
CREATE TABLE `member` ( # some fields here `added_date` DATETIME, ) ;
So I was mapping a java.util.Date field in the POJO to a MySQL DATETIME field.
The error occured while I was trying to retrieve the data from the member database table.
The problem was that I had DATETIME values in the database with all-zero components (0000-00-00 …), so the solution was to set all the added_date field values with all-zero components to NULL and to be careful to set a valid DATETIME value or NULL into the added_date field each time I inserted a new row.
Conclusion: You have 2 choices: either set the date field in the table to a NULL value either set it to a valid date.
3 replies on “java.sql.SQLException: Cannot convert value ‘0000-00-00 00:00:00’ from column 9 to TIMESTAMP”
I am having the same problem. i have set the default value of every datetime field in MySQL database to “0000-00-00 00:00:00” and when i map this table to a bean where i mapped this datatime to java.util.Date. It throws the same exception.
No idea what to do now
http://ondra.zizka.cz/stranky/programovani/java/index.texy
Look there for “SQLException for zero DATETIME or TIMESTAMP column? Use zeroDateTimeBehavior”.
Actually all you have to do is change the default date settings from 0000-00-00 to 0000-01-01 and the problem goes away.
Found this tip here:
http://stackoverflow.com/questions/11133759/0000-00-00-000000-can-not-be-represented-as-java-sql-timestamp-error