Java - Spring boot query that return an object made from two different tables

user_8466686 0
Viewed 1542

I have two entities that have references by id.

One entity is a candidate:

@Entity
@Table(name = "eupass_candidate", schema = "hrast")
public class Candidate{

    private long id;
    private String firstName;
    private String lastName;
    private String address;
    private String munic;
    private String postalCode;
    // getters and setters

}

And the other one is 

@Entity
@Table(name = "eupass_pdffile", schema = "hrast")
public class PDFFile implements Serializable {

private static final long serialVersionUID = -753514667628201960L;
private long id;
private byte[] pdfFile;
private Long idCandidate;

Now I need to do a join of this two tables and retrieve the candidate first name, last name and the pdf if it has any.

I managed this with the following query:

SELECT fname, lname, pdf_file 
FROM hrast.eupass_candidate  
LEFT OUTER join hrast.eupass_pdffile 
     ON (hrast.eupass_candidate.id = hrast.eupass_pdffile.id_candidate)

Finally I would like to save the result in an object and send it to the front end application.

I understand that I can write the query annotation in an extended CrudeReopository, but in witch one.

  • Do I need another object
  • Or does java let you create an in place object just for this purpose
spring java
2 Answers

what about this approach?

@Entity
@Table(name = "eupass_pdffile", schema = "hrast")
public class PDFFile implements Serializable {

    private static final long serialVersionUID = -753514667628201960L;
    private long id;
    private byte[] pdfFile;
    private Long idCandidate;
    //@ManyToMany
    @JoinColumn(name="id_candidate") //or whatever your column name is.
    private Candidate candidate ;
}
user_11931890

Do I need another object?

Yes as written by @ngueno in comment you need POJO object which hold result of your join (firstName, lastName, pdfFile)

If your requirement is not to use JPA relationship and on your own if you have to manage it then, you can write query in any of the repository class either in CandidateRepository or PDFFileRespositry.

For Example:

import com.example.customquerywithjpa.dto.CandidatePDFFile;
import com.example.customquerywithjpa.entity.Candidate;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface CandidateRepository extends JpaRepository<Candidate, Long> {

    // Inner Join
    @Query("SELECT new com.example.customquerywithjpa.dto.CandidatePDFFile(c.firstName, c.lastName, p.pdfFile) from Candidate c INNER JOIN PDFFile p ON (c.id=p.idCandidate) WHERE c.id =:candidateId")
    List<CandidatePDFFile> fetchCandidateAndPDFFIleByCandidateId(@Param("candidateId") long candidateId);

    // Left outer join
    @Query("SELECT new com.example.customquerywithjpa.dto.CandidatePDFFile(c.firstName, c.lastName, p.pdfFile) from Candidate c LEFT OUTER JOIN PDFFile p ON (c.id=p.idCandidate) WHERE c.id =:candidateId")
    List<CandidatePDFFile> fetchCandidateFirstNameLastNameAndItsPDFFileIfExists(@Param("candidateId") long candidateId);
}

For more understanding I have created sample project and test considering your use case with the assumption that one candidate can have zero or more pdf files. Please refer below github repository:

spring-data-jpa-query-that-return-an-custom-objectmade-from-two-different-tables

user_20761140
Related