Bind array param to native query

Question

I have table product_spec_entry with following columns:

• product_spec_id
• commodity_spec_id

for one product_spec_id may be several commodity_spec_id, for example:

|product_spec_id | commodity_spec_id|
|----------------|------------------|
|1683            |1681              |
|1692            |1693              |
|1692            |1681              |
|1692            |1687              |
|1692            |1864              |
|1860            |1681              |
|1868            |1681              |
|1868            |1864              |

I want get all product_spec_id that have all commodity_spec_id are passed as parameter.

I wrote next query:

SELECT ps.product_spec_id, commodities
FROM (
       SELECT
         product_spec_id,
         array_agg(commodity_spec_id) AS commodities
       FROM system.product_spec_entry
       GROUP BY product_spec_id) ps
WHERE Cast(ARRAY [1681, 1864] as BIGINT[]) <@ Cast(ps.commodities as BIGINT[]);

It's work fine, and return expected result:

product_spec_id = 1692, 1868

I try use this query for JPA native query:

String query = "SELECT ps.product_spec_id " +
                "FROM ( " +
                "       SELECT " +
                "         product_spec_id, " +
                "         array_agg(commodity_spec_id) AS commodities " +
                "       FROM system.product_spec_entry " +
                "       GROUP BY product_spec_id) ps " +
                "WHERE CAST(ARRAY[:commoditySpecIds] AS BIGINT[]) <@ CAST(ps.commodities AS BIGINT[])";
List<Long> commoditySpecsIds = commoditySpecs.stream().map(Spec::getId).collect(Collectors.toList());

List<BigInteger> productSpecIds = em.createNativeQuery(query).setParameter("commoditySpecIds", commoditySpecsIds)
                .getResultList();

It does not work because I get array of record (ARRAY[(1692, 1868)]) instead array of bigint (ARRAY[1692, 1868])

How I should bind array param to my query? May be I can use more simple query for it.

Colwin

In plain JDBC you can pass an array through PreparedStatement.setArray() but I don't know if your obfuscation layer allows that – Banky

I can not use JDBC, I can create query only with EntityManager – Colwin

Isn't there a setArray() equivalent in your obfuscation layer? – Banky

Answer 1

Leave out the array[...] from your SQL:

WHERE CAST(:commoditySpecIds AS BIGINT[])

and then pass the list of IDs as a string that looks like this:

"{1,2,3,4}"

The default toString() for Lists usually returns something like: "[1,2,3]", so you could do something like this:

String literal = commoditySpecsIds.toString();
literal = "{" + literal.substring(1,literal.length() - 1) + "};

and then pass that to your obfuscation layer:

setParameter("commoditySpecIds", literal)

Banky

It threw PersistenceException, because syntax error on CAST ({ 1,2,3,4}) – Colwin

@Colwin Then try to add single quotes to the literal: literal = "'{" + literal.substring(1,literal.length() - 1) + "}'"; – Banky

It's work from console but not from java.. (org.hibernate.exception.GenericJDBCException: could not prepare statement) and nothing more info.. it's work with following construction CAST(string_to_array(:params, ',') AS BIGINT []) where :commoditySPecIds = "1,2,3,4". But it's "hot fix".. if I found normal fix way I write it here – Colwin

Why did you add that string_to_array()? This is not what I put in my answer. It's not needed if you use the literal I have show and the cast. – Banky

I alrady wrote why.. beacase it's work only when I call query from console but not work when I use it in java code.. – Colwin

Answer 2

I'm exactly in the same situation. Hope @VladMihalcea can help us

Edit

I figure it out to do it with JPA. After reading the impementation of setParameter, i discovered something similar to UserType, the TypedParameterValue.

When you use

setParameter("commoditySpecIds", new TypedParameterValue(IntArrayType.INSTANCE, commoditySpecsIds))

Where IntArrayType.INSTANCE come from "hibernate-types" librairy provided by Vlad Mihalcea. Be carefull, the "commoditySpecsIds" must be an array, not a Collection.

Hope that helps

Aeolis

Perfect - spent a couple of hours before I found this - works perfectly! vladmihalcea.com/… – Playroom

Answer 3

Actually the answer is very simple - you should be using Array instead of List here.

final var commoditySpecs = List.of(new Spec(1681), new Spec(1864));
final Long[] commoditySpecsIds = commoditySpecs.stream().map(Spec::getId).toArray(Long[]::new);
final List<Integer> resultList = entityManager
        .createNativeQuery("""
                           SELECT ps.product_spec_id
                           FROM (
                                    SELECT
                                        product_spec_id,
                                        array_agg(commodity_spec_id) AS commodities
                                    FROM product_spec_entry
                                    GROUP BY product_spec_id) ps
                           WHERE Cast(:commoditySpecIds as BIGINT[]) <@ Cast(ps.commodities as BIGINT[]);
                           """)
        .setParameter("commoditySpecIds", commoditySpecsIds)
        .getResultList();
for (final var o : resultList) {
    System.out.println(o);
}

prints

1692
1868

Copenhagen

Answer 4

Other approcah for your case is unwrap the JPA provider session and use some methods form the JPA provider API.

If you are using hibernate, you can do the follow:

// unwrap hibenate session
final Session hibernateSession = em.unwrap(Session.class);

// create you SQL query in hibernate style
final SQLQuery sqlQuery = hibernateSession.createSQLQuery(sql);

And then set the parameter also using hibernate API

final Type customType = new CustomType(new ArrayTypeUser());
sqlQuery.setParameter("commoditySpecIds", value, customType);

Where "ArrayTypeUser" is a custom type that maps PostgresSQL array type to a Java array type.

This is not the best solution, but as you is are already using native queries, maybe for this particular case the best solution is skip the JPA standard API and use the JPA provide API.

Sharronsharyl