Tuesday, April 15, 2008

Grails UI for Integer Dates

Legacy tables often have dates kept in integer, zoned, or packed fields. This is unfortunate because the UI developer has to do all the validation and formatting of the numeric value. But there's a simple solution for Grails applications: create getter/setter methods to simulate a Date attribute.

Grails' generate-view will use the g:datePicker tag in the create and edit GSPs. Typically I replace g:datePicker with the RichUi tag.

In the following example, the domain has an integer sales date, in month/day/year format but getSaleDate() and setSaleDate() surfaces a Date attribute. The trick is to not present the integer date attribute for update (otherwise the value will overwrite the change made by setSaleDate()).


class Sale {
String last
int saleMDY
BigDecimal amount

Date getSaleDate() {
Calendar cal = Calendar.getInstance();
if (!saleMDY) {
return new Date()
}
int year = (saleMDY % 100)
int day = saleMDY / 100
int month = day / 100
day %= 100
cal.set((2000+year), (month-1), day)
return cal.time
}
void setSaleDate(Date date) {
Calendar cal = Calendar.getInstance();
cal.setTime(date)
int day = cal.get(Calendar.DAY_OF_MONTH)
int month = cal.get(Calendar.MONTH) + 1
int year = cal.get(Calendar.YEAR) % 100
saleMDY = (month * 10000) + (day * 100) + year
}
}

2 comments:

Kevin said...

I tried this and I get the error "column SALE_DATE not in table".

It seems to me that since the methods getSaleDate and SetSaleDate are in the domain class grails thinks there is a field SALE_DATE in the table.

Any Ideas?

Henrik said...

Hi!

We are using a Hibernate User type instead, since that allows us to put all the data conversion down a few levels - to the DB mapping, instead of to the domain object.

We have defined user types for the integer date (stored as YYYYMMDD in our case), as well as for the "0/1" database fields, used to store boolean values. The latter is mapped to "boolean" in Grails domain object.


Mapping in the domain object:

...
class Employee {
Date hired

static mapping = {
version false
columns {
hired column: 'TSANST', type:'DecimalDateUserType'
...

DecimalDateUserType (stored in ./src/groovy)

import org.hibernate.*;
import org.hibernate.usertype.*;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.*;
import java.text.SimpleDateFormat;
import java.io.Serializable;
import java.math.BigDecimal;

public class DecimalDateUserType implements UserType {

def SQL_TYPES = [Hibernate.STRING.sqlType()];
private static SimpleDateFormat dateFormatYYYMMDD = new SimpleDateFormat("yyyyMMdd");

public int[] sqlTypes() {
return SQL_TYPES;
}

public Class returnedClass() {
return Date.class;
}

public boolean equals(Object x, Object y)
throws HibernateException {
if (x == y) {
return true;
} else if (x == null || y == null) {
return false;
} else {
return x.equals(y);
}
}

public Object nullSafeGet(ResultSet resultSet,
String[] names, Object owner)
throws HibernateException, SQLException {
Date result = null;
BigDecimal dateAsDecimal = resultSet.getBigDecimal(names[0]);
if (!resultSet.wasNull()) {
if (0.equals(dateAsDecimal.intValue()))
{
result = null
}
else
{
result = convertIntegerToDate(dateAsDecimal)
}

}
return result;
}

public void nullSafeSet(PreparedStatement statement,
Object value, int index)
throws HibernateException, SQLException {
if (value == null) {
statement.setBigDecimal(index, new BigDecimal(0));
} else {
BigDecimal dateAsDecimal =
convertDateToInteger((Date) value);
statement.setBigDecimal(index, dateAsDecimal);
}
}

public Object deepCopy(Object value) throws HibernateException {
return value;
}

public boolean isMutable() {
return false;
}

public Object replace(Object original, Object target, Object owner)
{ return original; }

public Object assemble(Serializable cached, Object owner)
{ return cached; }

public Serializable disassemble(Object value)
{ return (Serializable) value; }

public int hashCode(Object x)
{
return x.hashCode();
}

private Date convertIntegerToDate(BigDecimal someDate){
Date date = new Date();
try {
date = dateFormatYYYMMDD.parse(someDate.toString());
} catch (Exception e) {}
return date;
}

private BigDecimal convertDateToInteger(Date someDate) {
BigDecimal decDate = null;
try {
decDate = new BigDecimal(dateFormatYYYMMDD.format(someDate));
} catch (Exception e) {}
return decDate;
}
}


That's it!