OpenJPA and MySQL Timestamps

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!

Leave a Reply

Your email address will not be published. Required fields are marked *