英文:
How to join two tables with a group query using Hibernate (5.4) Criteria?
问题
这是我的项目实体：（Project.java）
-------------------------------------------
@Entity
@Table(name = "project")
public class Project implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
@Column
private String name;
@Column
private String description;
@OneToMany(
mappedBy = "project",
cascade = CascadeType.REMOVE,
orphanRemoval = true,
fetch = FetchType.LAZY
)
private List<Task> tasks = new ArrayList<>();
public Project() {}
public Project(String name, String description) {
this.name = name;
this.description = description;
}
// getter setter here
}
这是我的任务实体：（Task.java）
@Entity
@Table(name = "task")
public class Task implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
@Column(name = "title")
private String title;
@Column(name = "description")
private String description;
@ManyToOne
@JoinColumn(name = "project_id", referencedColumnName="id")
private Project project;
public Task() {}
public Task(String title, String description) {
this.title = title;
this.description = description;
}
// getter setter here
}
期望的数据传输对象：（ProjectWithSumOfTaskDto.java）
----------------------------------------------
public class ProjectWithSumOfTaskDto {
private int projectId;
private String name;
private long totalTasks;
public ProjectWithSumOfTaskDto(int id, String name, long totalTasks) {
this.projectId = id;
this.name = name;
this.totalTasks = totalTasks;
}
// getter setter here
}
数据库中的表结构：
tasks:
- id
- title
- description
- project_id
projects:
- id
- name
- description
主要问题：
现在我需要连接“projects”表和“tasks”表，按“project_id”列分组。并获得List
我已经用HQL做过了，现在我必须学习如何在Hibernate Criteria中做到这一点。
我正在使用Hibernate版本5.4（最新版本）
（感谢您的阅读和对开源社区的支持）
<details>
<summary>英文:</summary>
**This is my Project Entity: ( Project.java )**
-------------------------------------------
@Entity
@Table(name = "project")
public class Project implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
@Column
private String name;
@Column
private String description;
@OneToMany(
mappedBy = "project",
cascade = CascadeType.REMOVE,
orphanRemoval = true,
fetch = FetchType.LAZY
)
private List<Task> tasks = new ArrayList<>();
public Project() {}
public Project(String name, String description) {
this.name = name;
this.description = description;
}
// getter setter here
}
**This is my Task Entity: ( Task.java )**
-------------------------------------
@Entity
@Table(name = "task")
public class Task implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
@Column(name = "title")
private String title;
@Column(name = "description")
private String description;
@ManyToOne
@JoinColumn(name = "project_id", referencedColumnName="id")
private Project project;
public Task() {}
public Task(String title, String description) {
this.title = title;
this.description = description;
}
// getter setter here
}
**Desired DTO: ( ProjectWithSumOfTaskDto.java )**
----------------------------------------------
public class ProjectWithSumOfTaskDto {
private int projectId;
private String name;
private long totalTasks;
public ProjectWithSumOfTaskDto(int id, String name, long totalTasks) {
this.projectId = id;
this.name = name;
this.totalTasks = totalTasks;
}
// getter setter here
}
**Table structure in database:**
----------------------------
tasks:
- id
- title
- description
- project_id
projects:
- id
- name
- description
**The main question:**
------------------
What I need now is to join the "projects" table and "tasks" table grouping by the "project_id" column. And obtain List<ProjectWithSumOfTaskDto> as output.
I have done it with HQL, Now I have to learn how to do it in hibernate criteria.
I'm using hibernate version 5.4 (latest)
*(Thanks for reading and many love for open source community)*
</details>
# 答案1
**得分**: 3
以下是翻译好的部分：
**步骤：**
1. 将Task表设为根表。
```java
Root<Task> task = criteria.from(Task.class);
- 将Project与Task进行连接，并将连接类型设置为左连接。
Join<Task, Project> projectJoin = task.join(Task_.project, JoinType.LEFT);
- 以"tasks"表的"project_id"为标准进行分组。
criteria.groupBy(task.get(Task_.project));
- 使用多选(multi-select)，选择task.project_id、project.name，以及分组后任务行数的计数。
criteria.multiselect(projectJoin.get(Project_.ID).alias("projectId"),
projectJoin.get(Project_.NAME).alias("name"),
builder.count(task).alias("totalTasks"));
- 最终以如下方式返回我所需的DTO列表：
return session.createQuery(criteria).getResultList();
整合后的代码如下：
public List<ProjectWithSumOfTaskDto> projectsWithTaskCount() {
return criteriaBuilderContext((session, builder) -> {
CriteriaQuery<ProjectWithSumOfTaskDto> criteria = builder.createQuery(
ProjectWithSumOfTaskDto.class
);
Root<Task> task = criteria.from(Task.class);
Join<Task, Project> projectJoin = task.join(Task_.project, JoinType.LEFT);
criteria.groupBy(task.get(Task_.project));
criteria.multiselect(
projectJoin.get(Project_.ID).alias("projectId"),
projectJoin.get(Project_.NAME).alias("name"),
builder.count(task).alias("totalTasks")
);
return session.createQuery(criteria).getResultList();
});
}
英文:
After spending countless hours, I came by this solution below:
Steps:
- Made Task table as Root.
Root<Task> task = criteria.from(Task.class);
- Joined Project with Task, and made the JoinType as Left join.
Join<Task, Project> projectJoin = task.join(Task_.project, JoinType.LEFT);
- Grouped by with "project_id" of "tasks" table.
criteria.groupBy(task.get(Task_.project));
- Used multi-select and selected the task.project_id, project.name, and the count of task rows after group by.
criteria.multiselect(projectJoin.get(Project_.ID).alias("projectId"),
projectJoin.get(Project_.NAME).alias("name"),
builder.count(task).alias("totalTasks"));
- And finally returned the list of my desired DTO like this:
return session.createQuery(criteria).getResultList();
Together, the code will look like this:
public List<ProjectWithSumOfTaskDto> projectsWithTaskCount() {
return criteriaBuilderContext((session, builder) -> {
CriteriaQuery<ProjectWithSumOfTaskDto> criteria = builder.createQuery(
ProjectWithSumOfTaskDto.class
);
Root<Task> task = criteria.from(Task.class);
Join<Task, Project> projectJoin = task.join(Task_.project, JoinType.LEFT);
criteria.groupBy(task.get(Task_.project));
criteria.multiselect(
projectJoin.get(Project_.ID).alias("projectId"),
projectJoin.get(Project_.NAME).alias("name"),
builder.count(task).alias("totalTasks")
);
return session.createQuery(criteria).getResultList();
});
}
答案2
得分: 1
我没有使用您的DTO，我使用的是您的基类，而且我也没有运行它，
解决方案是：
Criteria cr = session.createCriteria(Project.class, "p");
cr.createAlias("p.tasks", "t", Criteria.INNER_JOIN);
cr.add(Restrictions.eq("p.id", id));
return cr.list();
希望这对您有帮助。
英文:
i am not using your dto what i am using is your base class and i also dont run it,
the solution is :
Criteria cr = session.createCriteria(Project.class, "p");
cr.createAlias("p.tasks", "t", Criteria.INNER_JOIN);
cr.add(Restrictions.eq("p.id",id);
return cr.list();
i hope this is helpful
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论