java.sql.SQLException: Cannot convert value ‘0000-00-00 00:00:00’ from column 9 to TIMESTAMP

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.