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?

Tantalic

I think that the answer is "No, you can't". – Retardant

Nobody seems to be sure enough to say it as an answer. – Tantalic

As an extreme solution, you can consider using an AOP framework (e.g. AspectJ) for intercepting the calls to the setters. This is really tricky so I'm not suggesting it as a solution. – Swords

If EclipseLink can be configured to generate INSERT IGNORE and UPDATE IGNORE statements, then MySQL will truncate on these operations. But be careful, ignore modifiers should be only set where truncation is really needed and controlled. Truncation is dangerous and can lead to vulnerabilities. – Swollen

Answer 1

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://mcmap.net/q/409632/-how-to-reuse-fieldlength-in-form-validation-and-ddl)

The annotations can be recreated from the database if the database changes, as hinted in the comment to https://mcmap.net/q/401377/-how-to-silently-truncate-strings-while-storing-them-when-they-are-longer-than-the-column-length-definition

Undesirable

Note: I am aware the answer does exactly what the original poster excluded as a possible solution ("without adding a call to substring to each and every set method"). Still, I think there are benefits of such solution, and it is worth documenting it for others looking for they ways how to solve a problem like this. – Undesirable

Answer 2

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.

Sportswear

Answer 3

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.

Frager

Seems a dangerous way of doing things since you bypass the ORM. Assume you have a second level cache: it will contains non-truncated string while your DB will have truncated values. I wouldn't go that way ! – Sportswear

Answer 4

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.

Georgie

"do the truncation in your set methods." I would like to do this, but I would like to avoid specifying the column size in my code, I want to read it from the database. I think the answer would benefit from an added detail. – Undesirable

Perhaps this might help in improving the answer: #674454 – Undesirable

@Undesirable if you want to read it from the database, why don't you just get hibernate tools to reverse engineer the database for you? That would give you annotated classes with the correct column lengths. AFAIK, there isn't a way to get database metadata (like column names, lengths and whatnot) from Hibernate itself. – Stodge

Answer 5

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.

Extemporize

Answer 6

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

Fanestil

Do you know if there's any way to access the field name inside the Converter? My idea is to read the Size of that field instead of using LIMIT – Ipomoea

Answer 7

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.

Highstrung

Answer 8

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

Reliquiae

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.

Kwangchowan

Answer 10

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.

Pickwickian

Answer 11

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.

Anguished

I agree in principle, however sometimes the data source is not the user. In my app the data are coming from and error message and are stored for later debugging or analysis. – Undesirable

I would argue that even when the data are coming from another app and not directly from the user, the physical/logical/conceptual message or api contract still needs to spell out the data type restrictions. Validation, data type conversion and error handling still apply. If you decide that the contract should allow more than 256 characters then you have a simple solution - accept the longer string, then check its length and truncate as necessary and store the truncated string to the DB and the full string with the error message. Here you wouldn't need automatic truncation in JPA persist. – Anguished

Obviously, it is possible to declare the DB column as a CLOB. From the JPA spec: @Column(name="DESC", columnDefinition="CLOB NOT NULL", table="EMP_DETAIL") @Lob public String getDescription() { return description; } – Anguished

Answer 12

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

Lordship

Answer 13

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

Afterdinner