4

I try to delete a list of rows from a table using this Native Query:

@NamedNativeQuery(name="WebGroup.DeleteIn",
query="DELETE FROM WebGroup WHERE
WebGroup.GROUP_ID IN (:IDsList)"

getEm().createNamedQuery("WebGroup.DeleteIn")
              .setParameter("IDsList", groupToDeleteIDs)
              .executeUpdate();

and this is the SQL that MySQL executes:

DELETE FROM WebGroup WHERE WebGroup.GROUP_ID IN (:IDsList)

SO, JPA doesn't replace the variable IDsList...

Some one could help me please?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Fabio B.
  • 970
  • 2
  • 14
  • 29

2 Answers2

4

native queries do not support collection expansion neither named parameters.

you should write:

@NamedNativeQuery(name="WebGroup.DeleteIn", query="DELETE FROM WebGroup WHERE WebGroup.GROUP_ID IN (?,?,?,?)"

Query query = getEm().createNamedQuery("WebGroup.DeleteIn");
for(int i = 0; i < 4; i++) query.setParameter(i + 1, groupToDeleteIDs.get(i));
query.executeUpdate();

but it is horrible

on eclipselink + mysql this one works:

@NamedNativeQuery(name="WebGroup.DeleteIn", query="DELETE FROM WebGroup WHERE WebGroup.GROUP_ID IN (?)"

Query query = getEm().createNamedQuery("WebGroup.DeleteIn");
query.setParameter(1, StringUtils.join(groupToDeleteIDs, ",");
query.executeUpdate();

however it is not very nice... but there isn't any other solution using a named query.

Michele Mariotti
  • 7,372
  • 5
  • 41
  • 73
4

One way that works is if you not use the id value like you tried, but instead use the entity and let JPA handle the identification of it like this:

HashSet<Transaction> transactions = new HashSet<Transaction>();
... 
entityManager.createQuery(
  "DELETE FROM Transaction e WHERE e IN (:transactions)").
  setParameter("transactions", new ArrayList<Transaction>(
  transactions)).executeUpdate();

Hope it helps you in the right direction.

jhoglin
  • 61
  • 6