CODEWITHSUNDEEP
hibernatespring-bootjpaspring-data
JAVA_CAT
JAVA_CAT

Reputation: 859

How to match columns of tables using JPA

I am just trying to learn JPA. I have three entity class looks like this. Relations are mentioned in the Entity class

@Entity
@Table(name="EMPLOYEE")
public class Employee {
    @Id
    @Column(name="EMP_ID")
    private Integer empId;
    @Column(name="EMP_NAME")
    private String empName;
    @Column(name="EMP_ADDR")
    private String address;
    @OnetoOne
    @JoinColumn(name="PROJ_ID")
    private Project project;
    @OnetoOne
    @JoinColumn(name="TASK_ID")
    private Task task;

}

@Entity
@Table(name="PROJECT")
public class Project {
    @Id
    @Column(name="PROJ_ID")
    private Integer projectId;
    @Column(name="PROJ_NAME")
    private String projectName;
    @Column(name="PROJ_STATUS")
    private String projectStatus;

}

@Entity
@Table(name="TASK")
public class Task {
    @Id
    @Column(name="TASK_ID")
    private Integer taskId;
    @Column(name="TASK_NAME")
    private String taskName;
    @Column(name="TASK_STATUS")
    private String taskStatus;

}

My expected query is select * from Employee a , Task b where a.proj_id= ? and a.task_id=b.task_id

In my employee repository class I have a method 

findEmployeeByProjectProjectId(int projectId).

But I am getting some excess data and not sure how to handle condition a.task_id=b.task_id .

Not sure I am doing anything wrong. Please suggest a solution or in case if I am doing something wrong Please correct me.

Upvotes: 1

Views: 1221

Answers (2)

Mohd Waseem
Mohd Waseem

Reputation: 1374

You have @OneToOne Mapping Between Employee-Project and Employee-Task.

Default Fetch policy for @OneToOne is EAGER. So whenever you will FETCH Employee entity, The associated Project and Task entities (if not null) will be Fetched too in the same query. You dont need the where clasue e.task_id=b.task_id.

    /* Repository method */
    /*Function 1*/
    @Query("Select E FROM Employee E WHERE E.project.PROJ_ID = :projID")
    Employee findByProject( @Param("projID") Integer projID);
    /*Function 2*/
    Employee findByProject( Project project); // This method can be used by passing a managed object of project.

   /*Usage of repository call*/
   Employee e = employeeRepo.findByProject(projectId); // Function 1
   Task t = e.getTask(); // this will not cause a query because Task was EAGERLY loaded with Employee entity.
   Project p = e.getProject(); // this will not cause a query because Task was EAGERLY loaded with Employee entity.

Note: The second function which takes Project object should not be used like:

     Project p=new Project();
     p.setPROJ_ID(projID);
     employeeRepo.findByProject(p); // Function 2 This will throw Exception stating that p is transient which cannot be used for querying.

To use the second method we can load the project proxy and then use it.

    Project p = entityManager.getReference(Project.class, projId);
    employeeRepo.findByProject(p); // Now this is okk

Upvotes: 2

Muxammed Gafarov
Muxammed Gafarov

Reputation: 66

You have to change your repository method to findEmployeeByProject(Project project).Then add to ProjectRepo findById,and in service layer of Employee find project by id and use this Project as a parameter of your findEmployeeByProject(Project project).

Upvotes: 1

Related Questions