Understanding Native Join Queries in Spring Data JPA

Introduction to Spring Data JPA and Native Queries

Spring Data JPA is an excellent library for interacting with databases using Java. It provides a simplified way of accessing data by abstracting the underlying database technology. One of the key features of Spring Data JPA is its support for native queries, which allow you to execute complex queries directly on the database without having to translate them into JPQL (Java Persistence Query Language) syntax.

In this article, we will delve into how to apply native join queries in Spring Data JPA. We will explore the different scenarios where native queries are necessary and provide examples of how to use them effectively.

Entity Classes: JobPost, JobApplication, and Applicant

Before we dive into native queries, let’s take a look at the entity classes that we’ll be working with. Here are the entities that were provided in the question:

@Entity
@Table(name = "job_post")
public class JobPost {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "job_post_id")
    private Long jobPostId;

    @Column(name = "job_title")
    private String jobTitle;

    @Column(name = "job_description")
    private String jobDescription;

    @Column(name = "vacancy")
    private int vacancy;

    @Column(name = "posted_date")
    @JsonFormat(pattern = "yyyy-MM-dd")
    private Date postedDate;

    @Column(name = "total_applicants")
    private int totalApplicants;
    
}

@Entity
@Table(name = "job_application")
public class JobApplication {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "job_application_id")
    private Long jobApplicationId;

    @Column(name = "job_post_id")
    private Long jobPostId;

    @Column(name = "applicant_id")
    private Long applicantId;
}

@Entity
@Table(name = "applicant")
public class Applicant {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "applicant_id")
    private Long applicantId;

    @Column(name = "applicant_name")
    private String applicantName;

    @Column(name = "applicant_mobile_no")
    private String applicantMobileNo;

    @Column(name = "applicant_email")
    private String applicantEmail;

}

Many-To-Many Mapping

In the original question, it was mentioned that we have a many-to-many relationship between JobPost and Applicant. In this scenario, we can use the @JoinTable annotation on the entity class to directly map the relationship.

Here’s an example of how you can do this:

@Entity
@Table(name = "job_post")
public class JobPost {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "job_post_id")
    private Long jobPostId;

    // ...

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(
        name = "job_post_applicant",
        joinColumns = @JoinColumn(name = "job_post_id"),
        inverseJoinColumns = @JoinColumn(name = "applicant_id")
    )
    private List<Applicant> applicants;
}

In this example, we’ve added a @ManyToMany annotation to the JobPost entity and used the @JoinTable annotation to specify the join table name and column names.

One-To-Many Mapping

For one-to-many relationships, you can use the @OneToMany annotation on the entity class.

Here’s an example of how you can do this:

@Entity
@Table(name = "job_application")
public class JobApplication {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "job_application_id")
    private Long jobApplicationId;

    // ...

    @OneToMany(mappedBy = "jobApplication", fetch = FetchType.LAZY)
    private List<Applicant> applicants;
}

In this example, we’ve added an @OneToMany annotation to the JobApplication entity and used the mappedBy attribute to specify the foreign key column name.

Native Join Queries

Now that we have our entities defined, let’s take a look at how we can use native join queries to fetch data.

Native join queries are essentially SQL queries that are executed directly on the database without being translated into JPQL syntax. They provide more flexibility and performance compared to JPQL queries.

To create a native join query in Spring Data JPA, you can use the @Query annotation on an entity class or a service class.

Here’s an example of how you can do this:

public List<String> findAllJobApplicants(Long jobPostId) {
    return em.createNativeQuery("select a from JobPost j inner join j.jobApplicantList ja inner join ja.applicant a where j.jobPostId=:jobPostId")
        .setParameter("jobPostId", jobPostId)
        .getResultList();
}

In this example, we’ve created a native query using the createNativeQuery method and used the setParameter method to specify the parameter values.

Conclusion

In conclusion, Spring Data JPA provides a powerful way of accessing data in databases. Native join queries are an excellent feature that allows you to execute complex queries directly on the database without having to translate them into JPQL syntax.

By following the examples and best practices outlined in this article, you should be able to effectively use native join queries in your Spring Data JPA applications.

Last modified on 2024-02-15