Set precision in criteria BigInteger cast

Hibernate ORM
1

Hi,

I have this scenario:

DB Table:

create table accounts
(
id bigint primary key,
description varchar(255),
operationid varchar(30) not null
);

DB Data:

+--+-----------+------------------------------+
|id|description|operationid                   |
+--+-----------+------------------------------+
|1 |First      |2                             |
|2 |Second     |3                             |
|3 |Third      |25                            |
|4 |Fourth     |38                            |
|5 |Fifth      |900123456                     |
|6 |Sixth      |123456789012345678901234567890|
+--+-----------+------------------------------+

DB Query:

select *
from accounts a
where cast (operationid as numeric) >= 123456789012345678901234567890;

DB Query result:

+--+-----------+------------------------------+
|id|description|operationid                   |
+--+-----------+------------------------------+
|6 |Sixth      |123456789012345678901234567890|
+--+-----------+------------------------------+

As I need to implement this query in JPA Criteria, I came up with the following code snippets:
Class:

@Entity
@Table(name = "accounts")
public class Account implements Serializable {
    private static final long serialVersionUID = -1798070786993154676L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String description;
    
    @Column(length = 30, nullable = false)
    private String operationId;
    
    //...constructors, getters, setters, etc.
}

Routine:

CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<Account> cq = cb.createQuery(Account.class);

Root<Account> root = cq.from(Account.class);
List<Predicate> predicates = new ArrayList<>();

Object value = new BigInteger("123456789012345678901234567890");
predicates.add(cb.greaterThanOrEqualTo(root.get("operationId").as(BigInteger.class), cb.literal(value).as(BigInteger.class)));

Query<Account> query = session.createQuery(cq.select(root).where(predicates.toArray(new Predicate[0])));
query.getResultList().forEach(a -> log.info(a.toString()));

But when this code runs, it shows the following exception:
A field with precision 19, scale 2 must round to an absolute value less than 10^17.

And if I see the DB Query generated by Hibernate:
select account0_.id as id1_0_, account0_.description as descript2_0_, account0_.operationId as operatio3_0_ from accounts account0_ where cast(account0_.operationId as numeric(19, 2))>=123456789012345678901234567890

I can see an “arbitrary” precision cast of (19, 2).

The question is: Is there any way to configure/set a precision from the JPA Criteria for the cast operation? Or there is another way I could achieve my objective?

Thank you!

2

You can create a custom SQLFunction which renders the SQL fragment that you want. Other than that, there is no option to do this yet.

3

Hi @beikov thank you for your comment. Could you please help me with a code snippet pointing me in the right direction? I can’t find detailed documentation on this topic.

4

Here is an example: postgresql - HQL - Check if an Array contains a value - Stack Overflow