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
 }
}
at

2 comments:

  1. AnonymousSeptember 22, 2008 at 6:14 PM

    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?

    ReplyDelete
  2. Henrik FjorbackNovember 2, 2008 at 10:18 PM

    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!

    ReplyDelete