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?

Answer 1

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.