JPA / JPQL - bulk update

Question

I have to perform a bulk update on a table. Making a fast example :

 UPDATE Book b SET b.amount = b.amount + 1 WHERE b IN ( :books )

The problem is that b.amount can be or a NULL value or an int, and if there is a NULL value it should behave as b.amount would be equal to 1.

Is there any "cast" in JPA/JPQL or any other way to work-around this problem,

Thank you in advance,

Regards, P

score 7 · Accepted Answer · answered Mar 03 '11 at 08:55

7

You should be able to use COALESCE:

UPDATE Book b SET b.amount = COALESCE(b.amount, 1) + 1 WHERE b IN ( :books )

answered Mar 03 '11 at 08:55

axtavt

239,438
41
511
482

Is this JPA specific or Hibernate specific, since I do not want to be provider dependent ? – redbull Mar 03 '11 at 09:00
1

@redbull: It was introduced in JPA 2.0, before JPA 2.0 it was Hibernate-specific. – axtavt Mar 03 '11 at 09:05
Thank you, just what I needed, up! – redbull Mar 03 '11 at 09:42

score 1 · Answer 2 · answered Mar 03 '11 at 09:02

1

I would go and fix the nulls first with a separate query:

UPDATE Book set b.amount = 0 WHERE b.amount IS NULL

And also make it impossible to insert null, if it is not a legal value for your logic. For example have it @Column(nullable=false)

answered Mar 03 '11 at 09:02

Bozho

588,226
146
1,060
1,140

In longer term this seems to be a better approach ( which I will use later ), but for now I do not have this flexibility – redbull Mar 03 '11 at 09:41

score 0 · Answer 3 · answered Apr 01 '13 at 10:55

I used the following way to get the JDBC connection from the current JPA provider.

SessionImplementor si = (SessionImplementor) em.unwrap(Session.class);
Connection connection = si.getJdbcConnectionAccess().obtainConnection();
PreparedStatement pstmt = connection.prepareStatement("...");
// Do something
pstmt.addBatch();
pstmt.executeBatch();
si.getJdbcConnectionAccess().releaseConnection(connection);

JPA / JPQL - bulk update

3 Answers3

Linked