英文:
Spring boot fetch database data for multiple IDs using single call
问题
在Spring Boot项目中，我有以下代码，其中在循环内部发生了多次数据库调用，这会导致性能下降，这是一个不良的实践。
userList.forEach(user -> {
List<Project> userProjectsList = ProjectRepository.findByUser_UserIdOrderByDatecompletedDesc(user.getUserId());
}
注意： userList 是从存储过程返回的列表。
Project 是一个实体。与该实体相关的存储库是 ProjectRepository。
User 也是一个实体。
Project 和 User 实体之间有 多对一 的关系。
Project 实体如下所示：
@Entity
@Table(name = "Project")
public class Project {
@Id
@GeneratedValue
private Integer projectid;
private Calendar datecompleted;
@ManyToOne
@JoinColumn(name = "userid", referencedColumnName = "userid")
private User user;
// getters and setters
}
我需要修改这段代码以提高效率。作为解决方案，我计划从数据库中一次性获取所有用户所属的所有项目。（使用 MySQL 数据库）
我尝试了以下代码，但似乎不正确。
List<Integer> userIdList = UserList.stream().map(User::getUserId).collect(Collectors.toList());
List<List<Project>> userProjectsList = ProjectRepository.findByUser_UserIdInOrderByDatecompletedDesc(userIdList);
以下方法位于 ProjectRepository 中：
List<List<Project>> findByUser_UserIdInOrderByDatecompletedDesc(List<Integer> userIdList);
请帮助我找到更好的解决方案。提前感谢！
更新：
上述代码未返回正确的结果。我传递了两个用户ID。其中一个用户有2个项目，另一个用户有1个项目。
预期结果是：
[0]->[0]:project1, [1]:project2
[1]->[0]:project3
但实际返回的结果是：
[0]->[0]:project1
[1]->[0]:project2
[2]->[0]:project3
英文:
In spring boot project I have following code which cause low performance as several database calls happen inside a loop which is a bad practice.
userList.forEach(user -> {
List<Project> userProjectsList = ProjectRepository.findByUser_UserIdOrderByDatecompletedDesc(user.getUserId());
}
Note: userList is a List returned from a stored procedure.
Project is an entity. Relevant repository for that entity is ProjectRepository.
User is also an entity.
Project and User entity has many to one relationship.
Project Entity is like below;
@Entity
@Table(name = "Project")
public class Project {
@Id
@GeneratedValue
private Integer projectid;
private Calendar datecompleted;
@ManyToOne
@JoinColumn(name = "userid", referencedColumnName = "userid")
private User user;
// getters and setters
}
I need to modify this code to be more efficient. As a solution I'm planning to fetch all projects belongs to all users at once from database. (Mysql database is using)
I tried with below code but seems it's not correct.
List<Integer> userIdList = UserList.stream().map(User::getUserId).collect(Collectors.toList());
List<List<Project>> userProjectsList = ProjectRepository.findByUser_UserIdInOrderByDatecompletedDesc(userIdList);
Following method is in ProjectRepository.
List<List<Project>> findByUser_UserIdInOrderByDatecompletedDesc(List<Integer> userIdList);
Kindly support me to find a better solution for this. Thanks in advance
Update:
Above code is not returning the correct result. I passed 2 userIds. Out of them one user has 2 projects and the other user has 1 project.
Expected result is:
[0]->[0]:project1, [1]:project2
[1]->[0]:project3
But, returned result is:
[0]->[0]:project1
[1]->[0]:project2
[2]->[0]:project3
答案1
得分: 1
你可以使用 IN 查询来实现这个。你需要将方法签名声明如下：
findByNamesIn(Collection<String> names);
更多细节和其他查询，请参考 https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation。
英文:
You can use IN query for this. You need to declare you method signature as below:
findByNamesIn(Collection<String> names);
For more details and other queries, refer https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论