Original JPA request to delete

Question

I am trying to remove 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 executes MySQL:

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

SO, JPA does not replace IDsList ...

Can anybody help me?

Answer 1

Native queries do not support collection extension nor 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 awful

on eclipselink + mysql this 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, this is not very nice ... but there is no other solution using a named query.

Answer 2

One way: if you do not use the id value, as you tried, but instead use the object and let the JPA handle its identification as follows:

 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.