How to execute a JPA Bulk Update statement which takes a List as a parameter value

Question

I have an Update Query that looks like this

UPDATE 
    table_name 
SET 
    column_name = ? 
WHERE
    column_name = ? AND id in (?)

So the JPA transaction is

em.createNativeQuery(Update_QUERY)
   .setParameter(1, updatedStatus)
   .setParameter(2, currentStatus)
   .setParameter(3, ids)
   .executeUpdate();

The Input to the method is List id, currentStatus, and updatedStatus

How do I pass the List as a single parameter, if I convert the List to a comma-separated String I get the error Specified text is not number as strings is not allowed in the In clause.

Answer 1

How do I pass the List as a single parameter

An example approach:

String jpql = "UPDATE NameEntity ne " + 
              "SET ne.valstring = :updated_status " +
              "WHERE ne.valstring = :current_status AND ne.id IN :ids";
em.createQuery(jqpl)
  .setParameter("updated_status", updatedStatus)
  .setParameter("current_status", currentstatus)
  .setParameter("ids", Arrays.asList(ids))
  .executeUpdate();

Three simple rules:

1. Use native SQL for bulk update / delete on tables that are not mapped to entities.

   Native SQL queries work directly on database tables bypassing the persistence context (a set of managed entities), so it is safe to use such queries if a given database table has no corresponding entity.

2. Use JPQL for bulk update / delete on tables that are mapped to entities

   In case of a given database table is mapped by an entity, using a SQL update / delete will lead to inconsistency between persistence context and the underlying database, so use JQPL counterparts instead and the persistence provider will take care of consistency.

3. Bulk update / delete should be executed as the first operation within the transaction or ideally in its own transaction.