Spring Data JPA查询:根据另一个表中真/假值的总数预填/更新一个表

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

Spring Data JPA Query: Prepopulate/update one table based on total amount of true/false values from another table

问题

I have an issue with populating a table based on the results from another table. Table 1 collects submission data for a user on a given project. The submission data collected consists of tasks that the user carried out on the project and is stored in the table as a boolean value (true if they carried out the task and false if they haven't). The table values can be found in the Entity class below:

此处涉及问题:将一个表格根据另一个表格的结果进行填充。表格1收集用户在给定项目上的提交数据。收集的提交数据包括用户在项目上执行的任务,并以布尔值的形式存储在表格中(如果他们执行了任务则为true,否则为false)。表格的值可以在以下的Entity类中找到:

@Entity
@Table(name = "trackersubmission")
public class TrackerSubmit {
    // ... (其他属性和构造函数)
}

Additionally, Table 2 is an overview of the first which should collect the total number of each task carried out by each user over all the projects they have carried out. This can be seen below:

此外,表格2是表格1的概述,应该汇总每个用户在他们进行的所有项目中执行的每个任务的总数。如下所示:

@Entity
@Table(name = "prooverview")
public class ProjectPerformance {
    // ... (其他属性和构造函数)
}

I have created the following Query statement in an attempt to update Table 2 based on submissions in Table 1.

我已经创建了以下的查询语句,试图根据表格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)")

However, when I attempt to run this query I am flagged with the SQL error:

然而,当我尝试运行此查询时,我收到SQL错误:

2020-07-31 10:22:15.456 ERROR 2452 --- [ main] o.h.hql.internal.ast.ErrorTracker : line 1:22: expecting "set", found 'INNER'
2020-07-31 10:22:15.458 ERROR 2452 --- [ main] o.h.hql.internal.ast.ErrorTracker : line 1:22: expecting "set", found 'INNER'

antlr.MismatchedTokenException: expecting "set", found 'INNER'

Any help is much appreciated!

希望能提供帮助!

英文:

I have an issue with populating a table based on the results from another table. Table 1 collects submission data for a user on a given project. The submission data collected consists of tasks that the user carried out on the project and is stored in the table as a boolean value (true if they carried out the task and false if they haven't). The table values can be found in the Entity class below:

@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

Additionally, Table 2 is an overview of the first which should collect the total number of each task carried out by each user over all the projects they have carried out. This can be seen below:

@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

I have created the following Query statement in an attempt to update Table 2 based on submissions in Table 1.

	@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)")

However, when I attempt to run this query I am flagged with the SQL error:

2020-07-31 10:22:15.456 ERROR 2452 --- [           main] o.h.hql.internal.ast.ErrorTracker        : line 1:22: expecting "set", found 'INNER'
2020-07-31 10:22:15.458 ERROR 2452 --- [           main] o.h.hql.internal.ast.ErrorTracker        : 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]

Any help is much appreciated!

Thanks!!

答案1

得分: 1

Appears to be an issue with your query.

Few imp points

  1. Update needs to be followed by SET
  2. Update can be done on one table at a time. So if you want to make an update, the query will be line

update table set field = (select column from table a join table b on condition where condition`) or something similar.

英文:

Appears to be an issue with your query.

Few imp points

  1. Update needs to be followed by SET
  2. Update can be done on one table at a time. So if you want to make an update, the query will be line

update table set field = (select column from table a join table b on condition where condition`) or something similar.

huangapple
  • 本文由 发表于 2020年7月31日 17:49:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/63189506.html
匿名

发表评论

匿名网友

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

确定