Spring Data JPA:使用自定义查询更新实体的列表,使用输入列表更新

huangapple go评论104阅读模式
英文:

Spring Data JPA: update a list of Entity using a list of input in a custom Query

问题

我有以下基本对象:

/* 用户类 */

import lombok.Data;
import lombok.AllArgsConstructor;
import javax.persistence.Entity;
import javax.persistence.Id
import javax.persistence.GeneratedValue;
import javax.persistence.ManyToOne;

@Data
@Entity
@AllArgsConstructor
public class User {

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;

private String name;
private String surname;

@ManyToOne
private Address address;
}

/* UserComposite 类,用于从用户加载部分数据 */

import lombok.Data;
import lombok.AllArgsConstructor;

@Data
@AllArgsConstructor
public class UserComposite {

private Long userId;
private String name;
private String surname;
}

我的目标是使用 UserComposite 列表作为输入来更新 User 列表。这是我的 DAO 的样子:

/* UserDao 类 */

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

public interface UserDao extends JpaRepository<User, Long> {

@Transactional
@Modifying
@Query("update User u set u.name = uc.name , "
+ "u.surname = uc.surname "
+ "where u.id = uc.userId "
+ "and UserComposite uc in (?1)")
void updateUserFromCompositeList(List userCompositeList);
}

然而,这并不起作用。我很难将输入中的数据与我的数据库中保存的数据匹配,特别是因为 UserComposite 不是一个实体。

在这个问题上是否有解决方法?

英文:

I have the following basic objects :

  1. /* User class */
  2. import lombok.Data;
  3. import lombok.AllArgsConstructor;
  4. import javax.persistence.Entity;
  5. import javax.persistence.Id
  6. import javax.persistence.GeneratedValue;
  7. import javax.persistence.ManyToOne;
  8. @Data
  9. @Entity
  10. @AllArgsConstructor
  11. public class User {
  12. @Id
  13. @GeneratedValue(strategy=GenerationType.IDENTITY)
  14. private Long id;
  15. private String name;
  16. private String surname;
  17. @ManyToOne
  18. private Address address;
  19. }
  20. /* UserComposite class, used to load partial data from a user */
  21. import lombok.Data;
  22. import lombok.AllArgsConstructor;
  23. @Data
  24. @AllArgsConstructor
  25. public class UserComposite {
  26. private Long userId;
  27. private String name;
  28. private String surname;
  29. }

My goal here, is to update a list of User using a list of UserComposite as input. Here is what my DAO looks like :

  1. /* UserDao class */
  2. import org.springframework.data.jpa.repository.Modifying;
  3. import org.springframework.data.jpa.repository.Query;
  4. import org.springframework.transaction.annotation.Transactional;
  5. public interface UserDao extends JpaRepository&lt;User, Long&gt; {
  6. @Transactional
  7. @Modifying
  8. @Query(&quot;update User u set u.name = uc.name , &quot;
  9. + &quot;u.surname = uc.surname &quot;
  10. + &quot;where u.id = uc.userId &quot;
  11. + &quot;and UserComposite uc in (?1)&quot;)
  12. void updateUserFromCompositeList(List&lt;UserComposite&gt; userCompositeList);
  13. }

However, this does not work. I have a hard time matching the data from the input with the data saved in my database, especially as UserComposite is not an entity.

Is there a way around this problem ?

答案1

得分: 4

Update multiple rows with different IDs values is not possible in one query.

Update every user by id

  1. for (UserComposite user : List<UserComposite> userCompositeList) {
  2. userDao.updateUser(user.getUserId(), user.getName(), user.getSurname());
  3. }

Repository query like

  1. @Modifying
  2. @Query("UPDATE User u SET u.name = :name, u.surname = :surname WHERE u.id = :userId")
  3. void updateUser(@Param("userId") int userId, @Param("name") String name, @Param("surname") String surname);
英文:

Update multiple rows with different IDs values is not possible in one query.

Update every user by id

  1. for (UserComposite user : List&lt;UserComposite&gt; userCompositeList) {
  2. userDao.updateUser(user.getUserId(), user.getName(), user.getSurname());
  3. }

Repository query like

  1. @Modifying
  2. @Query(&quot;UPDATE User u SET u.name = :name, u.surname = :surname WHERE u.id = :userId&quot;)
  3. void updateUser(@Param(&quot;userId&quot;) int userId, @Param(&quot;name&quot;) String name, @Param(&quot;surname&quot;) String surname);

huangapple
  • 本文由 发表于 2020年4月6日 17:38:19
  • 转载请务必保留本文链接:https://go.coder-hub.com/61056840.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定