Reading in MySQL Timestamps that default to ‘0000-00-00 00:00:00’ using JPA can result in errors if your MySQL connection is not properly configured. You may run into an exception such as this:
java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 5 to TIMESTAMP.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
The solution here is easy enough, simply add zeroDateTimeBehavior=convertToNull to your connection URL string
jdbc:mysql://localhost/mydatabase?zeroDateTimeBehavior=convertToNull
And viola! no more exceptions. But what about performing the opposite task? You may run into a situation where you need to write a null Timestamp to a column, but the column cannot be null. The persistence layer may prevent you from setting a null value to the Timestamp field in your JPA object.
`somedate` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’
An OpenJPA solution is to force the writing of ‘0000-00-00 00:00:00’ to the Timestamp field when persisting the object to the database. To do this we need to add the @Strategy annotation to the Timestamp field and create a ValueHandler to perform the conversion to and from the database.
Here is the example JPA Entity Timestamp field with the OpenJPA @Strategy annotation:
@Basic
@Column(name = "replication_date", columnDefinition = "TIMESTAMP", nullable = false)
@Temporal(TemporalType.TIMESTAMP)
@Strategy("com.mypackage.openjpa.TimestampValueHandler")
private Date replicationDate;
And here we have the ValueHandler class specified by the @Strategy annotation. As you can see it instructs OpenJPA to handle Timestamps as Strings then performs the conversion using the MySQL date pattern.
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.openjpa.jdbc.identifier.DBIdentifier;
import org.apache.openjpa.jdbc.kernel.JDBCStore;
import org.apache.openjpa.jdbc.meta.ValueMapping;
import org.apache.openjpa.jdbc.meta.strats.AbstractValueHandler;
import org.apache.openjpa.jdbc.schema.Column;
import org.apache.openjpa.jdbc.schema.ColumnIO;
import org.apache.openjpa.jdbc.sql.DBDictionary;
import org.apache.openjpa.meta.JavaTypes;
import org.apache.openjpa.util.StoreException;
@SuppressWarnings("serial")
public class TimestampValueHandler extends AbstractValueHandler {
/** The Constant TIMESTAMP_FORMAT. */
private static final String TIMESTAMP_FORMAT = "yyyy-MM-dd HH:mm:ss";
/* (non-Javadoc)
* @see org.apache.openjpa.jdbc.meta.ValueHandler#map(org.apache.openjpa.jdbc.meta.ValueMapping, java.lang.String, org.apache.openjpa.jdbc.schema.ColumnIO, boolean)
*/
@Override
public Column[] map(ValueMapping vm, String name, ColumnIO io, boolean adapt) {
DBDictionary dict = vm.getMappingRepository().getDBDictionary();
DBIdentifier colName = DBIdentifier.newColumn(name,
dict != null ? dict.delimitAll() : false);
return map(vm, colName, io, adapt);
}
/**
* Map.
*
* @param vm the vm
* @param name the name
* @param io the io
* @param adapt the adapt
* @return the column[]
*/
public Column[] map(ValueMapping vm, DBIdentifier name, ColumnIO io,
boolean adapt) {
Column col = new Column();
col.setIdentifier(name);
col.setJavaType(JavaTypes.STRING);
return new Column[] { col };
}
/* (non-Javadoc)
* @see org.apache.openjpa.jdbc.meta.strats.AbstractValueHandler#toDataStoreValue(org.apache.openjpa.jdbc.meta.ValueMapping, java.lang.Object, org.apache.openjpa.jdbc.kernel.JDBCStore)
*/
@Override
public Object toDataStoreValue(ValueMapping vm, Object val, JDBCStore store) {
if (val == null)
return "0000-00-00 00:00:00";
SimpleDateFormat df = new SimpleDateFormat(TIMESTAMP_FORMAT);
return df.format((Date)val);
}
/* (non-Javadoc)
* @see org.apache.openjpa.jdbc.meta.strats.AbstractValueHandler#toObjectValue(org.apache.openjpa.jdbc.meta.ValueMapping, java.lang.Object)
*/
@Override
public Object toObjectValue(ValueMapping vm, Object val) {
if (val == null)
return val;
SimpleDateFormat df = new SimpleDateFormat(TIMESTAMP_FORMAT);
Date date = null;
try {
date = df.parse(val.toString());
} catch (ParseException ex) {
throw new StoreException(ex);
}
return date;
}
}
Thats it! Enjoy!