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.

In plain JDBC you can pass an array through PreparedStatement.setArray() but I don't know if your obfuscation layer allows that — user330315
– user330315, Commented Oct 13, 2016 at 12:37
I can not use JDBC, I can create query only with EntityManager — HAYMbl4
– HAYMbl4, Commented Oct 13, 2016 at 12:43
Isn't there a setArray() equivalent in your obfuscation layer? — user330315
– user330315, Commented Oct 13, 2016 at 12:50

user330315 · Accepted Answer · 2016-10-13 12:49:36Z

13

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)

answered Oct 13, 2016 at 12:49

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

HAYMbl4 Over a year ago

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

user330315 Over a year ago

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

HAYMbl4 Over a year ago

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

user330315 Over a year ago

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.

HAYMbl4 Over a year ago

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..

Jerome Dupont · Accepted Answer · 2018-06-04 18:32:27Z

8

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

edited Jun 4, 2018 at 18:32

answered Jun 4, 2018 at 16:46

Jerome Dupont

2152 silver badges8 bronze badges

1 Comment

bobmarksie Over a year ago

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

Enigo · Accepted Answer · 2023-12-28 18:39:28Z

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

bertol · Accepted Answer · 2018-05-30 12:16:13Z

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.

Collectives™ on Stack Overflow

Bind array param to native query

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related