Hibernate Java MySQL

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(
at org.hibernate.exception.SQLStateConverter.convert(
at org.hibernate.exception.JDBCExceptionHelper.convert(
at org.hibernate.loader.Loader.doList(
at org.hibernate.loader.Loader.listIgnoreQueryCache(
at org.hibernate.loader.Loader.list(
at org.hibernate.loader.hql.QueryLoader.list(
at org.hibernate.hql.ast.QueryTranslatorImpl.list(
at org.hibernate.engine.query.HQLQueryPlan.performList(
at org.hibernate.impl.SessionImpl.list(
at org.hibernate.impl.QueryImpl.list(
at admin.MembersTableModel.<init>(
at admin.AdminOptionsForm.initComponents(
at admin.AdminOptionsForm.<init>(
at managemembers.Main.main(
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(
at com.mysql.jdbc.ResultSet.getTimestampInternal(
at com.mysql.jdbc.ResultSet.getTimestamp(
at com.mysql.jdbc.ResultSet.getTimestamp(
at com.mchange.v2.c3p0.impl.NewProxyResultSet.getTimestamp(
at org.hibernate.type.TimestampType.get(
at org.hibernate.type.NullableType.nullSafeGet(
at org.hibernate.type.NullableType.nullSafeGet(
at org.hibernate.type.AbstractType.hydrate(
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(
at org.hibernate.loader.Loader.loadFromResultSet(
at org.hibernate.loader.Loader.instanceNotYetLoaded(
at org.hibernate.loader.Loader.getRow(
at org.hibernate.loader.Loader.getRowFromResultSet(
at org.hibernate.loader.Loader.doQuery(
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(
at org.hibernate.loader.Loader.doList(
... 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–>

<class name="managemembers.Member" table="member">
<!-- some mappings here-->
<property name="addedDate" type="timestamp" column="added_date"/>

# 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

Comments are closed.