How to map hibernation timestamp with MySQL BIGINT?
I am using Hibernate 3.x, MySQL 4.1.20 with Java 1.6. I am mapping Hibernate Timestamp to MySQL TIMESTAMP. So far, so good. The problem is that MySQL keeps TIMESTAMP in seconds and discards milliseconds and now I need millisecond precision. I suppose I can use BIGINT instead of TIMESTAMP in my table and convert types to my Java code. I'm trying to figure out if there is a better way to do this using hibernate, mysql, JDBC, or some combination so that I can use date functions in my HSQL and / or SQL queries?
source to share
Also consider creating a custom implementation of the Hibernate type. Something along the lines (psuedocode as I don't have a convenient 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 will need to map the new object using the Hibernate TypeDef and Type mappings. If you are using Hibernate annotations it will look like this:
@TypeDef (name="bigIntCalendar", typeClass=CalendarBigIntType.class)
@Entity
public class MyEntity {
@Type(type="bigIntCalendar")
private Calendar myDate;
}
source to share
Why not use it in addition to the TIMESTAMP field? You will have one field (which is already defined) for storing the date, without milliseconds, and another field for milliseconds. You can still run your HSQL queries on the first field, except you will need to ensure that the millisecond is stored correctly (by parsing your Java Date object before storing it with Hibernate).
source to share
I changed 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;
}
source to share