Stripping trailing whitespace from char fields in a legacy database with Grails GORM

Question

What are the possible solutions for stripping the trailing whitespace when mapping char fields in a legacy database?

I see the following options:

Calling .trim() at the point of use (controller, view, etc)
Override property accessors to return .trim()
Using a Hibernate UserType to trim the whitespace

I'm leaning toward overriding the property accessor so that the domain properties remain consistent throughout the application.

score 3 · Answer 1 · answered Feb 05 '13 at 16:45

I use a globally mapped Hibernate UserType and it works great (implementation based on http://www.hibernate.org/388.html, but updated for breaking changes to the UserType interface):

package company

import org.hibernate.Hibernate
import org.hibernate.usertype.UserType

import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.SQLException
import java.sql.Types

/**
 * Map CHAR(x) types to String: trim when getting and setting the CHAR(x)
 * based on www.hibernate.org/388.html
 */
public class TrimmedString implements UserType {
    public TrimmedString() {
    }

    public int[] sqlTypes() {
        return [Types.CHAR] as int[];
    }

    @SuppressWarnings("unchecked")
    public Class returnedClass() {
        return String.class;
    }

    public boolean equals(Object x, Object y) {
        return (x == y) || (x != null && y != null && (x.equals(y)));
    }

    public Object nullSafeGet(ResultSet inResultSet, String[] names, Object o) throws SQLException {
        String val = (String) Hibernate.STRING.nullSafeGet(inResultSet, names[0]);
        return val == null ? null : val.trim();
    }

    public void nullSafeSet(PreparedStatement inPreparedStatement, Object o, int i) throws SQLException {
        String val = (String) o;
        inPreparedStatement.setString(i, val);
    }

    public Object deepCopy(Object o) {
        if (o == null) {
            return null;
        }
        return new String(((String) o));
    }

    public boolean isMutable() {
        return false;
    }

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

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

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

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

global mapping in Groovy.config:

grails.gorm.default.mapping = {
    'user-type'(type: company.TrimmedString, class: String) //map Char(x) columns mapped to string fields as trimmed string
}

Michael J. Lee · Accepted Answer · 2011-07-12T18:41:43.753

2

I had a similar problem and I could not alter the legacy data. I ended up overriding the accessor for the sake of transparency to my fellow developers. I would recommend using a transient field so you don't have to trim the String on every call. However; if you can just clean up the data in the table, create a view, or transfer to a new table I would do that instead.

edited Jul 12 '11 at 18:41

answered Jul 12 '11 at 18:36

Michael J. Lee

12,278
3
23
39

How did you handle validation? Apparently Grails doesn't validate constraints on transient properties. – James Allman Jul 16 '11 at 18:57
Yeah - Grails doesn't like using the standard validation for transient fields. You'll have to handle it manually and deal with add the messages to flash scope. Maybe you can look at using the beforeSave interceptor to make it feel a little more transparent and put the validation there. – Michael J. Lee Jul 19 '11 at 10:56
1

**Views is nice solution**. Views just replaced as subqueries, some DB engine can optimize resulted queries (avoid subqueries). Also it is possible build **materialized view** if you have a lot of joins in view. Any **indexes** from table take in account in view. – gavenkoa May 07 '14 at 14:00

Stripping trailing whitespace from char fields in a legacy database with Grails GORM

2 Answers2

Linked