java.sql.DataTruncation: Data truncation exception and LIKE operator relation

Question

I'm working on a spring boot rest project. I have a web service that is searching a text in multiple fields. I'm using java specification to generate query.

Resulting query is like that

select *
from V_AGENCY agencyview0_ 
where 
agencyview0_.nationcode like '%ABCDEFGHIKLMN% 
or agencyview0_.username like '%ABCDEFGHIKLMN%'

The problem is that I'm getting java.sql.DataTruncation: Data truncation exception, because nationcode field has valid length 10 on database. Why I'm getting this exception? I'm not trying to insert a value to this field.

org.firebirdsql.jdbc.field.FBWorkaroundStringField class throws that error.

  public void setString(String value) throws SQLException {
        byte[] data = this.setStringForced(value);
        if (value != null) {
            assert data != null : "Expected non-null data here";

            if (data.length > this.fieldDescriptor.getLength() && !this.isSystemTable(this.fieldDescriptor.getOriginalTableName()) && (value.length() > this.fieldDescriptor.getLength() + 2 || value.charAt(0) != '%' && value.charAt(value.length() - 1) != '%')) {
                throw new DataTruncation(this.fieldDescriptor.getPosition() + 1, true, false, data.length, this.fieldDescriptor.getLength());
            }
        }
    }

UPDATE - Spring Boot codes added

Controller:

    @GetMapping(value = {PATH_SEARCH, PATH_LIST, PATH_VIEW + "/" + PATH_SEARCH, PATH_VIEW + "/" + PATH_LIST}, params = {PARAM_TEXT, PARAM_FIELD})
public List<T> searchInMultipleFields(
        @RequestParam(name = PARAM_START, required = false) String start,
        @RequestParam(name = PARAM_LIMIT, required = false) String limit,
        @RequestParam(name = PARAM_TEXT) String text,
        @RequestParam(name = PARAM_FIELD) List<String> fields
) {

    OoSpecificationsBuilder<T> builder = new MultipleSearchSpecificationBuilder<>();
    for (String field : fields) {
        builder.with(field, ":", text.toUpperCase());
    }
    Specification<T> spec = builder.build();
    return mService.getAll(getValueOf(start), getValueOf(limit, MAX_PAGE_SIZE), spec);
}

Service:

    @Override
public List<T> getAll(int aStart, int aSize, Specification<T> aSpec) {
    return getRepository().findAll((Specification) aSpec, generatePageRequest(aStart, aSize)).getContent();
}

JpaSpecificationExecutor:

Page<T> findAll(@Nullable Specification<T> var1, Pageable var2);

Specification:

public class MultipleSearchSpecification<T extends BaseModel> implements Specification<T> {

private SearchCriteria criteria;

public MultipleSearchSpecification(SearchCriteria aCriteria) {
    criteria = aCriteria;
}

@Override
public Predicate toPredicate
        (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
    if (root.get(criteria.getKey()).getJavaType() == String.class) {
        return builder.like(
                root.get(criteria.getKey()), "%" + criteria.getValue() + "%");
    }
    return null;
}}

Do you know any workaround for this issue?

same issue here: https://forum.hibernate.org/viewtopic.php?f=1&t=964447 — Erce Tilav, Jul 12 '18 at 11:09
Please include the actual code used so I can tailor my answer to the specific situation, because I'm 100% sure that the resulting query is **not** the one shown in your question (but rather using `like ?` and setting a parameter value instead instead). — Mark Rotteveel, Jul 12 '18 at 11:46
What do you mean with actual code. I'm using org.springframework.data.jpa.domain.Specification class to create dynamic query. — Erce Tilav, Jul 13 '18 at 14:04
You need to include a [mcve] because then I can try it out and offer a tailored solution/workaround without having to do the ground work of creating the necessary conditions to reproduce it. Otherwise the only answer is I can give is "Unfortunately Firebird restricts the maximum length of the parameter to the declared length of the field it is compared to, see also [JDBC-477](http://tracker.firebirdsql.org/browse/JDBC-477). You either need to oversize the column definition or find a way to explicitly cast the parameter to be wider.". — Mark Rotteveel, Jul 13 '18 at 14:07
Thanks for the edit, I'll see if I can reproduce the problem with this and find a workaround for it. What is the fully-qualified name of this `MultipleSearchSpecificationBuilder` (or if it is not part of standard spring: what is its code)? — Mark Rotteveel, Jul 17 '18 at 16:13

score 2 · Answer 1 · answered Jul 14 '18 at 07:22

The code you use is not generating that literal query, but instead uses parameters (ie agencyview0_.nationcod like ?) and setting the parameter value to "%ABCDEFGHIKLMN%".

Unfortunately, Firebird restricts the maximum length of the parameter to the declared length of the field it is compared to, see also JDBC-477, and Jaybird can't automatically override that. You either need to oversize the column definition or find a way to explicitly cast the parameter to be wider, that is, ensure the code generates something like agencyview0_.nationcod like cast(? as varchar(100)).

Erce Tilav · Accepted Answer · 2018-09-06T13:57:18.370

Add service registration to path src/main/resources/META-INF/services/org.hibernate.boot.spi.SessionFactoryBuilderFactory with data: systems.config.orm.CustomDataTypesRegistration

public class CustomDataTypesRegistration implements SessionFactoryBuilderFactory {

private static final org.slf4j.Logger logger = LoggerFactory.getLogger(CustomDataTypesRegistration.class);

@Override
public SessionFactoryBuilder getSessionFactoryBuilder(final MetadataImplementor metadata, final SessionFactoryBuilderImplementor defaultBuilder) {
    logger.info("Registering custom Hibernate data types");
    //Deprecated. (since 5.3) No replacement, access to and handling of Types will be much different in 6.0
    metadata.getTypeResolver().registerTypeOverride(new OoStringType());
    return defaultBuilder;
}}

Custom String Type:

public class OoStringType extends StringType {

public OoStringType() {
    setSqlTypeDescriptor(OoVarcharTypeDescriptor.INSTANCE);
}}

Custom VarcharTypeDescriptor:

public class OoVarcharTypeDescriptor extends VarcharTypeDescriptor {
protected static final Logger log = LoggerFactory.getLogger(OoVarcharTypeDescriptor.class.getName());
public static final OoVarcharTypeDescriptor INSTANCE = new OoVarcharTypeDescriptor();
public static final String TRUNCATED_PARAMETER = "truncated parameter [%s] as [%s] - [%s]";
public static final char PERCENTAGE_CHAR = '%';

public <X> ValueBinder<X> getBinder(final JavaTypeDescriptor<X> javaTypeDescriptor) {
    return new BasicBinder<X>(javaTypeDescriptor, this) {
        protected void doBind(PreparedStatement st, X value, int index, WrapperOptions options) throws SQLException {
            FBParameterMetaData parameterMetaData = (FBParameterMetaData) st.getParameterMetaData();
            int precision = parameterMetaData.getPrecision(index);
            String unwrappedValue = javaTypeDescriptor.unwrap(value, String.class, options);
            if (unwrappedValue.charAt(0) == PERCENTAGE_CHAR && unwrappedValue.charAt(unwrappedValue.length() - 1) == PERCENTAGE_CHAR) {
                String coreValue = unwrappedValue.substring(1, unwrappedValue.length() - 1);
                if (coreValue.length() > precision) {
                    unwrappedValue = PERCENTAGE_CHAR + coreValue.substring(0, precision - 2) + PERCENTAGE_CHAR;
                    log.info(String.format(TRUNCATED_PARAMETER, index, JdbcTypeNameMapper.getTypeName(this.getSqlDescriptor().getSqlType()), unwrappedValue));
                }
            } else if (unwrappedValue.length() > precision) {
                unwrappedValue = unwrappedValue.substring(0, precision);
                log.info(String.format(TRUNCATED_PARAMETER, index, JdbcTypeNameMapper.getTypeName(this.getSqlDescriptor().getSqlType()), unwrappedValue));
            }

            st.setString(index, unwrappedValue);
        }

        protected void doBind(CallableStatement st, X value, String name, WrapperOptions options) throws SQLException {
            st.setString(name, (String) javaTypeDescriptor.unwrap(value, String.class, options));
        }
    };
}}

Be aware of that this solution may cause unwanted results. For example: You are searching "ABCD", but you have a field with length 3. It will return a result with that field has "ABC" in it.

Great that you found a workaround. I'm still looking if there is some way to force a cast to be generated, but so far it doesn't seem that there is something like that in JPA. If you use Firebird 3, you could try to define a stored function that takes a varchar with a certain max length and just return that value; then in your criteria builder, you could wrap the value in a call to that function. It is a bit of a kludge, but it might just work. I'll see if I can find the time for a more complete writeup. — Mark Rotteveel, Jul 19 '18 at 14:42
A suggestion for your current solution: if you know your like pattern is longer then the defined field, you could also consider binding `null` instead, that way you avoid problem ABC being consider like ABCD in a three character field. — Mark Rotteveel, Jul 19 '18 at 14:47

java.sql.DataTruncation: Data truncation exception and LIKE operator relation

2 Answers2