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

Question

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

You can use a POJO with the fields you need to combine results from different tables — nortontgueno
– nortontgueno, Commented Dec 21, 2018 at 13:24
What's the association between a candidate and a PDF file? Can a candidate have many files, or is a single file bound to a single candidate? — Makoto
– Makoto, Commented Dec 21, 2018 at 16:15

medkhelifi · Accepted Answer · 2018-12-21 16:02:04Z

2

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

edited Dec 21, 2018 at 16:02

answered Dec 21, 2018 at 15:48

medkhelifi

1,1413 gold badges19 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gregor vojvoda Over a year ago

This would be useful, but I at the moment it's not possible to do so (some implementation problems). But thanks for your answer.

sham · Accepted Answer · 2020-08-10 16:41:23Z

0

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

edited Aug 10, 2020 at 16:41

answered Dec 22, 2018 at 5:25

sham

4625 silver badges18 bronze badges

6 Comments

gregor vojvoda Over a year ago

I tried your solution and I get the errors from the SQL statement. The error is on the first "." (Obviously I have used my path to the defined POJO). So I have removed the new end the path and than I get the error: No converter found capable of converting from type

sham Over a year ago

Hi, Could you please paste the code snippet and exception here or if possible link to your source. The example I pushed to github repository is in working state, may be some query syntax specific things have been missed with your code.

gregor vojvoda Over a year ago

Ok so my Candidate wit PDF

import lombok.Builder; import lombok.EqualsAndHashCode; import lombok.AllArgsConstructor;;  @Builder @AllArgsConstructor @EqualsAndHashCode public class CandidateWithPdf {  	private String fname; 	private String lname; 	private byte[] pdf_file;

gregor vojvoda Over a year ago

@CrossOrigin(origins = "localhost:4200") @RepositoryRestResource(path = "/candidates", itemResourceRel = "candidates", collectionResourceRel = "candidates") public interface CandidateRepository extends JpaRepository<Candidate, Long> { @Query(nativeQuery = true, value = "SELECT " + " 'com.dinit.hrast.entity.CandidateWithPdf(fname, lname, pdf_file)' " + "FROM hrast.eupass_candidate " + "LEFT OUTER join hrast.eupass_pdffile " + "on (hrast.eupass_candidate.id = hrast.eupass_pdffile.id_candidate)") List<CandidateWithPdf> listAllCandidates(); }

gregor vojvoda Over a year ago

And the last is the Controller @GetMapping("/listCandidates") public ResponseEntity<List<CandidateWithPdf>> listCandidatesWithPdf() { List<CandidateWithPdf> candidatesWithPdf; candidatesWithPdf = candidateRepository.listAllCandidates(); HttpHeaders responseHeaders = new HttpHeaders(); responseHeaders.set("Access-Control-Allow-Origin", "*"); return new ResponseEntity<>(candidatesWithPdf, responseHeaders, HttpStatus.OK); }

|

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related