JPARepository & Hibernate return results given ID?

Question

I have a one to many relationship between users and tasks (one user has many tasks).

Sql:

SELECT G.accountability, G.title, G.interval, G.description, U.user_name
FROM user U
LEFT OUTER JOIN GOAL G on (G.USER_ID = U.USER_ID)

I insert my data in the database where there is a foreign key association in each task to the user id. Is it possible in JPA to essentially say:

Given the user ID here are all the tasks

Here is my simple repo

import com.habicus.core.model.Goal;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface GoalRepository extends JpaRepository<Goal, Long> {
}

Ram · Answer 1 · 2018-04-22T09:15:40.960

1

You can select the tasks using below query which will join the task using the userId.

@Query("select task from User user join user.task task where user.id = :userId")
List<Task> getTasksByUserId(@Param("userId") Integer userId)

If you want to select specific columns as your query, it would be something like below.

@Query("select task.accountability, task.title, task.interval, task.description, user.user_name from User user join user.task task where user.id = :userId")
List<Object[]> getTasksByUserId(@Param("userId") Integer userId)

You can as well use Projections if going with second option. Please refer here for more details.

edited Apr 22 '18 at 09:15

answered Apr 22 '18 at 03:27

Ram

1,743
2
18
40

Please make this a full answer, not just a code answer with no explanation. – petezurich Apr 22 '18 at 06:56
1

@petezurich Added relevant details. – Ram Apr 22 '18 at 09:08

score 0 · Answer 2 · answered Apr 22 '18 at 03:27

Assuming your Goal and User entities are created as follows:

@Entity
class User {
   @Id
   private Long id;
   @OneToMany
   private List<Goal> tasks;
   ...
}

@Entity
class Goal {
   @Id
   private Long id;
   @ManyToOne
   private User user;
   ...
}

One option is loading user by id and return tasks:

userRepository.findById(userId).getTasks()

Another option is to add the following method to GoalRepository:

List<Goal> findByUserId(Long userId);

Spring Data JPA strip off findBy prefix and build WHERE clause with the rest of the part. So, first, it looks whether there is userId property in Goal. If not, then it checks for user property which in-turn contains id property.

PS: Assuming you are referring to goal and task to the same thing.

JPARepository & Hibernate return results given ID?

Sql:

2 Answers2