Spring Data JPA查询:根据另一个表的true/false值的总量来预填充/更新一个表

Question

我在根据另一个表的结果填充表时遇到问题。表1收集了用户在给定项目上的提交数据。收集的提交数据由用户在项目中执行的任务组成，并以布尔值的形式存储在表中(如果他们执行了任务，则为true，如果没有执行，则为false(。表值可以在下面的Entity类中找到：

@Entity
@Table(name = "trackersubmission")
public class TrackerSubmit {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "ts_id")
private int tsid;

@ManyToOne(targetEntity = User.class, fetch = FetchType.EAGER)
@JoinColumn(name = "user_id", insertable = false, updatable = false)
private User user;

@Column(name = "user_id")
private int user_id;

@ManyToOne(targetEntity = User.class, fetch = FetchType.EAGER)
@JoinColumn(name = "manager_id", insertable = false, updatable = false)
private User manager;

@Column(name = "manager_id")
private int manager_id;

@ManyToOne(targetEntity = Project.class, fetch = FetchType.EAGER)
@JoinColumn(name = "project_id", insertable = false, updatable = false)
private Project project;

@Column(name = "project_id")
private int project_id;

@Column(name = "projectname")
private String projectname;

@Column(name = "enddate")
private Date enddate;

@Column(name = "completiondate")
private Date completiondate;

@Column(name = "osra")
private boolean osra;

@Column(name = "wsra")
private boolean wsra;

@Column(name = "quoting")
private boolean quoting;

@Column(name = "scoping")
private boolean scoping;

@Column(name = "idcopier")
private boolean idcopier;

@Column(name = "componentchecker")
private boolean componentchecker;

@Column(name = "nodetool")
private boolean nodetool;

@Column(name = "review")
private boolean review;

@Column(name = "leadrole")
private boolean leadrole;

@Column(name = "parc")
private boolean parc;

@Column(name = "onsite")
private boolean onsite;

@Column(name = "comment")
private String comment;

@Column(name = "statuscheck")
private boolean statuscheck;

@Column(name = "managercomment")
private String managercomment;
public TrackerSubmit(int tsid, User user, int user_id, User manager, int manager_id, Project project,
int project_id, String projectname, Date enddate, Date completiondate, boolean osra,
boolean wsra, boolean quoting, boolean scoping, boolean idcopier, boolean componentchecker,
boolean nodetool, boolean review, boolean leadrole, boolean parc, boolean onsite, String comment,
boolean statuscheck, String managercomment) {
this.tsid = tsid;
this.user = user;
this.user_id = user_id;
this.manager = manager;
this.manager_id = manager_id;
this.project = project;
this.project_id = project_id;
this.projectname = projectname;
this.enddate = enddate;
this.completiondate = completiondate;
this.osra = osra;
this.wsra = wsra;
this.quoting = quoting;
this.scoping = scoping;
this.idcopier = idcopier;
this.componentchecker = componentchecker;
this.nodetool = nodetool;
this.review = review;
this.leadrole = leadrole;
this.parc = parc;
this.onsite = onsite;
this.comment = comment;
this.statuscheck = statuscheck;
this.managercomment = managercomment;
}
//getters and setters

此外，表2是第一个的概述，它应该收集每个用户在他们执行的所有项目中执行的每个任务的总数。这可以在下面看到：

@Entity
@Table(name = "prooverview")
public class ProjectPerformance {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "po_id")
private int poid;

@ManyToOne(targetEntity = User.class, fetch = FetchType.EAGER)
@JoinColumn(name = "user_id", insertable = false, updatable = false)
private User user;

@Column(name = "user_id")
private int user_id;

@Column(name = "audits")
private int audits;

@Column(name = "osra")
private int osra;

@Column(name = "wsra")
private int wsra;

@Column(name = "quoting")
private int quoting;

@Column(name = "scoping")
private int scoping;

@Column(name = "idcopier")
private int idcopier;

@Column(name = "componentchecker")
private int componentchecker;

@Column(name = "nodetool")
private int nodetool;

@Column(name = "review")
private int review;

@Column(name = "leadrole")
private int leadrole;

@Column(name = "parc")
private int parc;

@Column(name = "onsite")
private int onsite;
public ProjectPerformance(int poid, User user, int user_id, int audits, int osra, int wsra, int quoting, int scoping,
int idcopier, int componentchecker, int nodetool, int review, int leadrole, int parc, int onsite) {
this.poid = poid;
this.user = user;
this.user_id = user_id;
this.audits = audits;
this.osra = osra;
this.wsra = wsra;
this.quoting = quoting;
this.scoping = scoping;
this.idcopier = idcopier;
this.componentchecker = componentchecker;
this.nodetool = nodetool;
this.review = review;
this.leadrole = leadrole;
this.parc = parc;
this.onsite = onsite;
}
//getters and setters

我创建了以下Query语句，试图根据表1中提交的内容更新表2。

@Query("UPDATE ProjectPerformance p INNER JOIN "
+ "(SELECT user_id, osra, wsra, quoting, scoping, idcopier, componentchecker, nodetool, review, leadrole, parc, onsite, statuscheck FROM TrackerSubmit GROUP BY user_id WHERE statuscheck = TRUE ) x"
+ "ON p.user_id = x.user_id"
+ "SET p.osra = COUNT(x.osra = TRUE), p.wsra = COUNT(x.wsra = TRUE). p.quoting = COUNT(x.quoting = TRUE), p.scoping = COUNT(x.scoping = TRUE), p.idcopier = COUNT(x.idcopier = TRUE, ), p.componentchecker = COUNT(x.componentchecker = TRUE), p.nodetool = COUNT(x.nodetool = TRUE), p.review = COUNT(x.review = TRUE), p.leadrole = COUNT(x.leadrole = TRUE), p.parc = COUNT(x.parc = TRUE), p.onsite = COUNT(x.onsite = TRUE)")

然而，当我尝试运行这个查询时，我会被标记为SQL错误：

2020-07-31 10:22:15.456[0;39m [31mERROR[0;39m [35m2452[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.hql.internal.ast.ErrorTracker       [0;39m [2m:[0;39m line 1:22: expecting "set", found 'INNER'
[2m2020-07-31 10:22:15.458[0;39m [31mERROR[0;39m [35m2452[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.hql.internal.ast.ErrorTracker       [0;39m [2m:[0;39m line 1:22: expecting "set", found 'INNER'
antlr.MismatchedTokenException: expecting "set", found 'INNER'
at antlr.Parser.match(Parser.java:211) ~[antlr-2.7.7.jar:na]

非常感谢您的帮助！

谢谢！！

Answer 1

您的查询似乎有问题。

很少imp点

1. 更新后需要SET
2. 一次可以对一个表进行更新。因此，如果你想进行更新，查询将是行

update表set字段=(根据条件where condition`从表a中选择列联接表b(或类似的内容。