Search code examples
javajpaspring-data-jpajpa-criteria

JPA CriteriaBuilder like on double

I'm trying to retrieve data out of a legacy database.

The column in the table is defined as a DECIMAL(13,0) containing account numbers.

The column data type cannot be changed as it will have a major impact on the legacy system. Essentially all programs using the table need to be changed and then recompiled which is not an option.

We have a requirement to find all records where the account number contains a value, for example the user could search for 12345 and all accounts with an account number containing 12345 should be returned.

If this was a CHAR/VARCHAR, I would use:

criteriaBuilder.like(root.<String>get(Record_.accountNumber), searchTerm)

As a result of the column defined as DECIMAL(13,0), the accountNumber property is a double.

Is there a way to perform a like on a DECIMAL/double field?

The SQL would be 

SELECT * FROM ACCOUNTS WHERE accountNumber LIKE '%12345%'
Solution

  • I have not actually tried this, but I believe it should work

    criteriaBuilder.like(
    root.get(Record_.accountNumber).as(String.class),
    searchTerm)

    This should generate a query kind of like this:

    SELECT * FROM ACCOUNTS WHERE CAST(accountNumber AS text) LIKE '%12345%'