14

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.

HAYMbl4
  • 1,450
  • 2
  • 15
  • 29
  • In plain JDBC you can pass an array through `PreparedStatement.setArray()` but I don't know if your obfuscation layer allows that –  Oct 13 '16 at 12:37
  • I can not use JDBC, I can create query only with **EntityManager** – HAYMbl4 Oct 13 '16 at 12:43
  • 1
    Isn't there a `setArray()` equivalent in your obfuscation layer? –  Oct 13 '16 at 12:50

3 Answers3

12

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)
  • It threw **PersistenceException**, because syntax error on CAST **({** 1,2,3,4}) – HAYMbl4 Oct 13 '16 at 13:10
  • @HAYMbl4 Then try to add single quotes to the literal: `literal = "'{" + literal.substring(1,literal.length() - 1) + "}'";` –  Oct 13 '16 at 13:11
  • 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 – HAYMbl4 Oct 13 '16 at 13:59
  • 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. –  Oct 13 '16 at 14:00
  • 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.. – HAYMbl4 Oct 13 '16 at 14:04
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

Jerome Dupont
  • 203
  • 2
  • 7
  • Perfect - spent a couple of hours before I found this - works perfectly! https://vladmihalcea.com/how-to-map-java-and-sql-arrays-with-jpa-and-hibernate/ – bobmarksie Oct 28 '19 at 12:03
0

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.

bertol
  • 1
  • 2