带有联接的JPQL更新语句 [英] JPQL Update Statement with Join

问题描述

我以前从未使用过JPQL，我需要使用它来批量更新特定列中的值.该列存在于表中，但是实体类的设置没有变量.我无法使用确切的数据，但是设置过程如下所示:

I've never used JPQL before, and I need to use it to batch update the values in a specific column. The column exists in the table, but the entity class was set up without a variable for it. I can't use the exact data, but the setup would be something like this to give you an idea:

数据库列

Books:
book_id book_title author_id

Authors:
author_id first_name last_name

JPA实体类

Books:
private Integer bookId;
private String bookTitle;
private EAuthor author;

Authors:
private Integer authorId;
private String firstName;
private String lastName;

在Books实体类中，有一个getter&作者_id的设置器，但它是从作者实体调用的.我现在的困境是我需要使用相同的author_id批量更新所有book_titles，但我不确定如何编写JPQL查询语句来做到这一点.我尝试过:

In the Books entity class, there's a getter & setter for the author_id, but it calls it from the author entity. My dilemma now is I need to batch update all book_titles with the same author_id and I'm unsure how to write the JPQL query statement to do this. I tried:

String queryUpdate = "UPDATE books b SET b.book_title = :bookTitle"
                            + " WHERE b.author = :authorId";

和

String queryUpdate = "UPDATE books b SET b.book_title = :bookTitle"
                            + " WHERE b.author.authorId = :authorId";

两个查询都说b.author.authorId和b.author都是无效的标识符.是否可以编写2个单独的查询? 1个查询作为选择语句，我可以在其中使用联接，然后第二个查询更新第一个查询的结果集?

Both queries said both b.author.authorId and b.author were invalid identifiers. Would it make be possible to write 2 separate queries? 1 query as a select statement where I can use joins and then the 2nd to update the result set of the first query?

推荐答案

JPQL对于这项工作来说是一个糟糕的选择.不允许使用b.author.authorId，因为它将转换为SQL JOIN. JPQL也不支持UPDATE语句中的子查询.您可以将author_id映射到Books中的属性:

JPQL is a poor choice for the job. b.author.authorId is not allowed, as it would translate to an SQL JOIN. Neither does JPQL support subqueries in UPDATE statements. You could map the author_id to a property in Books:

@Column(name = "author_id", insertable=false, updatable=false)
private Long authorId;

完成上述操作后，您就可以做到:

With the above in place, you could just do:

UPDATE Books b SET b.book_title = :bookTitle WHERE b.authorId = :authorId

，但是您必须忍受更改域模型的目的，只是为了适应使用UPDATE查询的单个案例.

but you would have to live with altering your domain model just to acommodate a single case with an UPDATE query.

您应该能够将任务分成两个查询，如下所示:

You should be able to split the task into two queries like so:

List<Long> ids = em.createQuery("SELECT b.id FROM Books b WHERE b.author.id = :authorId", Long.class)
    .setParameter("authorId", authorId)
    .getResultList();
em.createQuery("UPDATE Books b SET b.bookTitle = :bookTitle WHERE b.id IN :ids")
    .setParameter("ids", ids)
    .setParameter("bookTitle", bookTitle)
    .executeUpdate();

或者，您可以简单地从代码中更新实体:

Or, you could simply update the entities from code:

em.createQuery("SELECT b.id FROM Books b WHERE b.author.id = :authorId", Book.class)
    .setParameter("authorId", authorId)
    .getResultList()
    .forEach(book -> book.setBookTitle(bookTitle));

这篇关于带有联接的JPQL更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！