How to silently truncate strings while storing them when they are longer than the column length definition?

Question

I have a web app, using EclipseLink and MySQL for storing data. Some of these data are strings, ie varchars in the DB. In the code of entities, the strings have attributes like this:

@Column(name = "MODEL", nullable = true, length = 256)
private String model;

The database is not created by eclipseLink from the code, but the length matches the varchar length in the DB. When the length of such a string data is greater than the length attribute, an exception is raised during the call to javax.persistence.EntityTransaction.commit():

javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.0.v20100614-r7608): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'MODEL' at row 1

Then the transaction is rolled back. While I understand that it is the default behaviour, this is not the one I want. I would like the data to be silently truncated, and the transaction to be committed.

Can I do this without adding a call to substring to each and every set method of string data for the concerned entities?

Answer 1

A simple solution that works for me is to modify the set method of the attributes you want to truncate. For your case:

public void setModel(String model) {
    model= model!= null && model.length() > 256? girObsTxt.substring(0, 255) : model;
    this.model= model;
}

Answer 2

There was already answer mentioned Converters, but I want to add more details. My answer also assumes Converters from JPA, not EclipseLink specific.

At first create this class - special type converter which responsibility will be to truncate value at the persistence moment:

import javax.persistence.AttributeConverter;
import javax.persistence.Convert;

@Convert
public class TruncatedStringConverter implements AttributeConverter<String, String> {
  private static final int LIMIT = 999;

  @Override
  public String convertToDatabaseColumn(String attribute) {
    if (attribute == null) {
      return null;
    } else if (attribute.length() > LIMIT) {
      return attribute.substring(0, LIMIT);
    } else {
      return attribute;
    }
  }

  @Override
  public String convertToEntityAttribute(String dbData) {
    return dbData;
  }
}

Then you can use it in your entities like this:

@Entity(name = "PersonTable")
public class MyEntity {
    
    @Convert(converter = TruncatedStringConverter.class)
    private String veryLongValueThatNeedToBeTruncated;
 
    //...
}

Related article about JPA Converters: http://www.baeldung.com/jpa-attribute-converters

Answer 3

Another option is to declare a constant and reference it everywhere you need that length starting with the @Column annotation itself.

This constant can be then forwarded to a truncate function or a validation function that throw a preventive exception if the passed string is too long. This constant can also be re-used on some other layers such as a UI.

For example:

@Entity
public class MyEntity {
    public static final int NAME_LENGTH=32;

    private Long id;
    private String name;

    @Id @GeneratedValue
    public Long getId() {
        return id;
    }
    protected void setId(Long id) {
        this.id = id;
    }

    @Column(length=NAME_LENGTH)
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = JpaUtil.truncate(name, NAME_LENGTH);
    }
}

public class JpaUtil {
    public static String truncate(String value, int length) {
        return value != null && value.length() > length ? value.substring(0, length) : value;
    }
}

Answer 4

One can truncate a string according to the JPA annotations in the setter for the corresponding field:

public void setX(String x) {
    try {
        int size = getClass().getDeclaredField("x").getAnnotation(Column.class).length();
        int inLength = x.length();
        if (inLength>size)
        {
            x = x.substring(0, size);
        }
    } catch (NoSuchFieldException ex) {
    } catch (SecurityException ex) {
    }
    this.x = x;
}

The annotation itself should look like:

@Column(name = "x", length=100)
private String x;

(Based on https://stackoverflow.com/a/1946901/16673)

The annotations can be recreated from the database if the database changes, as hinted in the comment to https://stackoverflow.com/a/7648243/16673

Answer 5

You have different solutions and false solutions.

Using trigger or any database level trick

This will create inconsistency between the objects in the ORM and their serialized form in the DB. If you use second level caching: it can leads to lots of troubles. In my opinion this is not a real solution for a general use case.

Using pre-insert, pre-update hooks

You will silently modify the user data just before persisting it. So your code may behave differently depending on the fact that the object is already persisted or not. It may cause trouble too. Additionally, you must be careful with the order of hooks invocation: be sure that your "field-truncator-hook" is the very first one called by the persistence provider.

Using aop to intercept call to setters

This solution will more or less silently modify user/automatic input, but your objects won't be modified after you use them to do some business logic. So this is more acceptable than the 2 previous solutions, but the setters won't follow the contract of usual setters. Additionally, if you use field injection: it will bypass the aspect (depending on your configuration, jpa provider may use field injection. Most of the time: Spring use setters. I guess some other frameworks may use field injection, so even if you don't use it explicitelly be aware of the underlying implementation of frameworks you are using).

Using aop to intercept field modification

Similar to the previous solution except that field injection will also be intercepted by the aspect. (note that I never wrote an aspect doing this kind of things, but I think it's feasible)

Adding a controller layer to check field length before calling the setter

Probably the best solution in terms of data integrity. But it may requires a lot of refactoring. For a general use case, this is (in my opinion) the only acceptable solution.

Depending on your use case, you may choose any of those solutions. Be aware of the drawbacks.

Answer 6

Two very important features of UI design:

1. You should never silently alter user data - the user should be aware, in control and consent to such changes.
2. You should never allow data to be entered that cannot be handled - use UI/HTML attributes and validation to restrict data to legal values

The answer to your problem is very simple. Simply limit your UI input fields to 256 characters, to match the database field length:

<input type="text" name="widgetDescription" maxlength="256">

This is a system limitation - the user should not be able to enter more data than this. If this is insufficient, change the database.

Answer 7

If you want to do this on a field-by-field basis rather than globally, then you might be able to make a custom type mapping that truncates the value to the given length before inserting it into the table. Then you can attach the converter to the entity by an annotation like:

@Converter(name="myConverter", class="com.example.MyConverter")

and to the relevant fields via:

@Convert("myConverter")

This is really meant for supporting custom SQL types, but it might work for normal varchar type fields as well. Here is a tutorial on making one of these converters.

Answer 8

Maybe AOP will help:

Intercept all set method in you JavaBean/POJO, and then get the filed to be set. Check if the field is annotate with @Column and field type is String. Then truncate the field if it's too long than length.

Answer 9

Since the Exception seems to be raised at the database level during the commit() process, a before-insert Trigger on the target table/s could truncate the new values before they are committed, bypassing the error.

Answer 10

You can set your database to work in a non strict mode as explained here: Automatically trimming length of string submitted to MySQL

Note that it might cancel other validations too so be careful in what you wish for

Answer 11

There is another way to do it, may be even faster (at least it works on 5th version of MySql):

First, check your sql_mode setting: there is a detailed description how to do it. This setting should have value of "" for windows, and "modes" for Unix.

That didn't help me, so I found another setting, this time in jdbc:

jdbcCompliantTruncation=false. 

In my case (I used persistence) it is defined in persistence.xml:

<property name="javax.persistence.jdbc.url" value="jdbc:mysql://127.0.0.1:3306/dbname?jdbcCompliantTruncation=false"/>

These 2 settings work only together, I tried to use them separately and had no effect.

Notice: remember that by setting sql_mode as described above you switch off important database checks, so please do it carefully.

Answer 12

You can use a Descriptor PreInsert/PreUpdate event for this, or possibly just use JPA PreInsert and PreUpdate events.

Just check the size of the fields and truncate them in the event code.

If required, you could get the field size from the descriptor's mapping's DatabaseField, or use Java reflection to get it from the annotation.

It would probably be even better to just do the truncation in your set methods. Then you do not need to worry about events.

You may also be able to truncate it on the database, check your MySQL settings, or possible use a trigger.

Answer 13

I don't know anything about the EclipseLink, but in the Hibernate it's doable - you could make an org.hibernate.Interceptor and in onFlushDirty method modify the object's currentState using entity meta data.