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

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

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 :

/* User class */

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 class, used to load partial data from a user */

import lombok.Data;
import lombok.AllArgsConstructor;

@Data
@AllArgsConstructor
public class UserComposite {

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

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

/* UserDao class */    

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&lt;User, Long&gt; {

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

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

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

Repository query like

  @Modifying
  @Query("UPDATE User u SET u.name = :name, u.surname = :surname  WHERE u.id = :userId")
  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

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

Repository query like

  @Modifying
  @Query(&quot;UPDATE User u SET u.name = :name, u.surname = :surname  WHERE u.id = :userId&quot;)
  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:

确定