CODEWITHSUNDEEP
javaspring-bootjpa
Ryan S
Ryan S

Reputation: 155

JPARepository & Hibernate return results given ID?

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> {
}

Upvotes: 1

Views: 1149

Answers (2)

Ram
Ram

Reputation: 1803

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.

Upvotes: 1

K. Siva Prasad Reddy
K. Siva Prasad Reddy

Reputation: 12375

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.

Upvotes: 0

Related Questions