Complex Inner Joins using Springboot JPA

Question

I'm new to Springboot JPA, I need my code to fetch the below sql command using JPA

**Table Details:**
create table questions (
    id varchar(20) PRIMARY KEY,
    description varchar(200) NOT NULL
);

create table questions_workflow (
    business_id varchar(20),
    parent_id varchar(20),
    child_id varchar(20),
    PRIMARY KEY (business_id, parent_id, child_id),
    FOREIGN KEY (parent_id) REFERENCES questions(id),
    FOREIGN KEY (child_id) REFERENCES questions(id)
);

**Query:**
select a.business_id, a.parent_id, b.description, a.child_id, c.description
from questions_workflow a
inner join questions b on a.parent_id=b.id
inner join questions c on a.child_id=c.id
where a.business_id=?;

I only need the JPA Repository code for this, I think this can be achieved using the @Query annotation, the only doubt I have is what should be the return type since I'm fetching custom columns?

Answer 1

Let me share my approach and solution.

The repository code for the above query is as follows:

1. Let's create two Entities corresponding to our tables.

import javax.persistence.*;

@Entity
@Table(name = "questions")
public class Question {
    @Id
    private String id;
    private String description;

    // Getters and setters...
}

@Entity
@Table(name = "questions_workflow")
public class QuestionWorkflow {
    @Id
    @Column(name = "business_id")
    private String businessId;

    @ManyToOne(targetEntity = Question.class)
    @JoinColumn(name = "parent_id", referencedColumnName = "id")
    private Question parentQuestion;

    @ManyToOne(targetEntity = Question.class)
    @JoinColumn(name = "child_id", referencedColumnName = "id")
    private Question childQuestion;

    // Getters and setters...
}

2. Now, create a class to hold the result of our query.

public class CustomResult {
    private String businessId;
    private String parentId;
    private String parentDescription;
    private String childId;
    private String childDescription;

    public CustomResult(String businessId, String parentId, String parentDescription, String childId, String childDescription) {
        this.businessId = businessId;
        this.parentId = parentId;
        this.parentDescription = parentDescription;
        this.childId = childId;
        this.childDescription = childDescription;
    }

    // getters and setters...
}

3. Now, let's create our JPA repository:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface QuestionWorkflowRepository extends JpaRepository<QuestionWorkflow, String> {

    @Query("SELECT new com.yourpackage.CustomResult(a.businessId, a.parentQuestion.id, a.parentQuestion.description, a.childQuestion.id, a.childQuestion.description) " +
           "FROM QuestionWorkflow a " +
           "WHERE a.businessId = ?1")
    List<CustomResult> findByBusinessId(String businessId);
}

Here, you need to replace com.yourpackage with your actual package name.

Remember that in JPQL, the join operations are performed based on the object model, not the database tables. Hence, we don't need to explicitly mention "INNER JOIN" operations in the @Query annotation for ManyToOne relationships. In the above query, "a.parentQuestion.description" will automatically fetch the description from the Question entity that's joined via the parent_id and similar for childQuestion. The returned results will be wrapped in CustomResult objects and returned as a list.

Remember to mark your foreign key columns with @ManyToOne and @JoinColumn annotations in your entity classes. The @JoinColumn annotation defines the actual physical mapping on the database table.

Hope this answer would be helpful.