CODEWITHSUNDEEP
javaspringhibernatejpa
usr-local-ΕΨΗΕΛΩΝ
usr-local-ΕΨΗΕΛΩΝ

Reputation: 26874

Bulk update with HibernateTemplate and IN clause

I would like to perform a database bulk update using Spring's HibernateTemplate (Hibernate 5.1).

HibernateTemplate offers the following interface: public int bulkUpdate(String,Object...).

My query is UPDATE entity item SET item.attribute.id = ? WHERE item.id in (?.....?).

I had a lot of troubles and want to ask what is the proper way to use HibernateTemplate

Question is: how do I properly formulate a bulk update query using Spring's HibernateTemplate? As correctly reported by Mykong, HibernateTemplate automagically sets query parameters 0-based, but eventually the author got the program working with non-positional parameters without mentioning (or having at all) any warning.

Upvotes: 0

Views: 1674

Answers (1)

Evgeni Dimitrov
Evgeni Dimitrov

Reputation: 22506

I think that the recommended way to do that now a days is with Spring Data JPA. There is a getting started tutorial here.

So if you have an entity, you can add an interface that extends any of the reposiotry interfaces supported in SpringDataJpa and add a modifying query.

public interface CustomerRepository extends CrudRepository<Customer, Long> {
    @Transactional
    @Modifying
    @Query("update Customer c set c.firstName = ?1 where c.id = ?2")
    int updateNameById(String nameToUpdate, long id);
    @Transactional
    @Modifying
    @Query("update Customer c set c.firstName = ?1 where c.id in (?2)")
    int updateNameByIds(String nameToUpdate, List<Long> ids);
}

Then Spring will implement that method and you can use the it as:

customerRepo.updateNameByIds("newName", Arrays.asList(cust.getId()));

This will generate the following sql:

update customer set first_name=? where id in (?)

Here is the project I used to test with

Upvotes: 2

Related Questions