JPA CriteriaBuilder like on double

Question

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%'

maybe see https://stackoverflow.com/questions/5363113/jpql-we-cant-concatstring-integer-eclipselink — Scary Wombat, Apr 25 '17 at 07:29

coladict · Accepted Answer · 2017-04-25T12:23:46.633

8

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%'

edited Apr 25 '17 at 12:23

answered Apr 25 '17 at 07:25

coladict

4,799
1
16
27

Thank you @coladict, but criteriaBuilder.toString() expects Expression< Character> and failed when passed Expression – albert Apr 25 '17 at 12:08
@albert I edited the answer after doing some source back-tracing in Hibernate. This should work regardless what the original expression was. – coladict Apr 25 '17 at 12:25

JPA CriteriaBuilder like on double

1 Answers1

Linked