How do I map a hibernate Timestamp to a MySQL BIGINT?

Question

I am using Hibernate 3.x, MySQL 4.1.20 with Java 1.6. I am mapping a Hibernate Timestamp to a MySQL TIMESTAMP. So far so good. The problem is that MySQL stores the TIMESTAMP in seconds and discards the milliseconds and I now need millisecond precision. I figure I can use a BIGINT instead of TIMESTAMP in my table and convert the types in my Java code. I'm trying to figure out if there is a better way of doing this using hibernate, mysql, JDBC or some combination so I can still use date functions in my HSQL and/or SQL queries?

Answer 1

Why not use it in addition to the TIMESTAMP field? You would have one field (which is already defined) for storing the date, without the milliseconds, and another field for the milliseconds. You can still run your HSQL queries on the first field, except you will have to ensure that you take care of storing the millisecond properly (via parsing of your Java Date object before you store it using Hibernate).

Answer 2

Also, look at creating a custom Hibernate Type implementation. Something along the lines of (psuedocode as I don't have a handy environment to make it bulletproof):

public class CalendarBigIntType extends org.hibernate.type.CalendarType {
    public Object get(ResultSet rs, String name) {
        return cal = new GregorianCalendar(rs.getLong(name));
    }
    public void set(PreparedStatement stmt, Object value, int index) {
        stmt.setParameter(index, ((Calendar) value).getTime());
    }
}

Then, you'll need to map your new object using a hibernate TypeDef and Type mappings. If you are using Hibernate annotations, it be along the lines of:

@TypeDef (name="bigIntCalendar", typeClass=CalendarBigIntType.class)
@Entity
public class MyEntity {
    @Type(type="bigIntCalendar")
    private Calendar myDate;
}

Answer 3

I altered my datatyp from timestamp to decimal(17,3) and wrote some helper methods

public static Calendar bigDec2Cal(BigDecimal tsp) {
    Calendar cal = Calendar.getInstance();
    cal.setTimeInMillis(tsp.longValue());
    return cal;
}

public static Date bigDec2Date(BigDecimal tsp) {
    Calendar cal = Calendar.getInstance();
    cal.setTimeInMillis(tsp.longValue());
    return cal.getTime();
}

public static BigDecimal cal2BigDec(Calendar cal) {
    BigDecimal tsp = new BigDecimal(cal.getTimeInMillis());
    return tsp;
}

public static BigDecimal date2BigDec(Date date) {
    Calendar cal = Calendar.getInstance();
    cal.setTime(date);
    BigDecimal tsp = new BigDecimal(cal.getTimeInMillis());
    return tsp;
}