Pages

10 November, 2023

How to pass multiple values as sql array for a parameter? (Or how can I cirumvent limit of parameters of prepared statement?)

I'm trying to create a query using QueryDSL wich accepts more than 32767 parameters (32767 is the upper limit of parameters the postgresql driver accepts).


The query is currently created like this:
static final QInvoiceEntity invoiceEntity = ...

void queryInvoicesWithId(String... ids) {
JPAQuery query = ...
query.where(invoiceEntity.id.in(ids));
}



This works if no more than 32767 parameters are passed. If you pass more than that (e.g. 38000) the following exception is thrown:
Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 38000
at org.postgresql.core.PGStream.sendInteger2(PGStream.java:275)



One solution according to 1 is to use ANY and pass the parameters as an array. I've tried the following but to no avail:
query.where(Expressions.booleanTemplate("{0} = ANY {1}", invoiceEntity.id, ids);



But this throws the following exception:
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ? near line 3, column 31 [select invoiceEntity
from InvoiceEntity invoiceEntity
where invoiceEntity.id = ANY (?1)



I've even tried to pass the parameters as a string and convert it back in the query:
query.where(Expressions.booleanTemplate("{0} = ANY string_to_array({1}, ',')", invoiceEntity.id, String.join(",", ids));



But this throws the following exception:
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 3, column 45 [select invoiceEntity
from InvoiceEntity invoiceEntity
where invoiceEntity.id = ANY string_to_array(?1, ',')

No comments:

Post a Comment

Thanks