Return column name in a JpaRepository query in Spring Boot using PostgreSQL

Question

I have a table Job_Applicants, which in it has a column named reviewed which holds a boolean value and also a status column which holds a string value

When doing the following querie

@Query("select a.applicant, a.reviewed, a.status from JobApplicants a where
a.job.id = ?1")
List<JobApplicants> getOnlyApplicants(UUID jobId);

I get the following:

"jobApplicantList": {
        "bca4ed5c-b81a-420d-8b8b-9130d815d5f1":[
            {
            "id": "109ca778-8526-40c6-a9dd-1f4e6ef2fef7",
            "createdDate": "2018-05-20T11:22:50.607",
            "lastModifiedDate": "2018-05-20T11:22:50.607",
            "address": null,
            "firstName": "Xxxx",
            "lastName": "Xxx",
            "email": "xxxx@gmail.com",
            "phoneNumber": "xxxxxx",
            "resume": "xxx.pdf",
            "resumeUrl": "https://",
            "coverLetter": "xxx.pdf",
            "coverLetterUrl": "https:/",
            "website": "sdfasdfasdf"
        },
        null,
        null
    ],
}

All the XXX added by me to remove personal info.

But as you can see the reviewed and status columns come out without the column names, is there a way I can adjust my querie to include the column name?

So it doesn't read "null, null" in the end?

I would like for it to read "reviewed":null, "status":null

Here's my Entitiy if will help

import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;

import javax.persistence.*;
import java.time.LocalDateTime;
import java.util.UUID;

@Entity
@Table(name = "JOB_APPLICANTS")
public class JobApplicants {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @org.hibernate.annotations.Type(type="org.hibernate.type.PostgresUUIDType")
    private UUID id;

    private String status;
    private Boolean reviewed;
    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "APPLICANT_ID")
    private Applicant applicant;
    @ManyToOne()
    @JoinColumn(name = "JOB_ID")
    private Job job;

    @CreationTimestamp
    private LocalDateTime appliedOnDate;
    @UpdateTimestamp
    private LocalDateTime lastModifiedDate;


    public JobApplicants(){}

    public UUID getId() {
        return id;
    }

    public void setId(UUID id) {
        this.id = id;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public Boolean getReviewed() {
        return reviewed;
    }

    public void setReviewed(Boolean reviewed) {
        this.reviewed = reviewed;
    }

    public Applicant getApplicant() {
        return applicant;
    }

    public void setApplicant(Applicant applicant) {
        this.applicant = applicant;
    }

    public Job getJob() {
        return job;
    }

    public void setJob(Job job) {
        this.job = job;
    }

    public LocalDateTime getAppliedOnDate() {
        return appliedOnDate;
    }

    public void setAppliedOnDate(LocalDateTime appliedOnDate) {
        this.appliedOnDate = appliedOnDate;
    }

    public LocalDateTime getLastModifiedDate() {
        return lastModifiedDate;
    }

    public void setLastModifiedDate(LocalDateTime lastModifiedDate) {
        this.lastModifiedDate = lastModifiedDate;
    }

    @Override
    public String toString() {
        return "JobApplicants{" +
                "id=" + id +
                ", status='" + status + '\'' +
                ", reviewed=" + reviewed +
                ", applicant=" + applicant +
                ", job=" + job +
                ", appliedOnDate=" + appliedOnDate +
                ", lastModifiedDate=" + lastModifiedDate +
                '}';
    }
}

*But as you can see the reviewed and status columns come out with the column names,* - what do you mean? — Scary Wombat, May 21 '18 at 00:45
At the end of the querie it just has "null, null" I would like for it to read "status: null, reviewed: null" to be able to display properly in the front-end. @ScaryWombat — SpaceOso, May 21 '18 at 00:47
@SpaceOso I think you mean "comes out **without** the column names" — UsamaAmjad, May 21 '18 at 00:49
Hint: `"address": null,` is working OK, so check that code to see how it is done — Scary Wombat, May 21 '18 at 00:54
@SpaceOso Try to add `@Column(name="yourFeildName")` this annotation of feilds. — UsamaAmjad, May 21 '18 at 01:12
@UsamaAmjad didn't seem to work. `List findAllByJob_Id(UUID uuid);` doing a regular querie brings everything with it. — SpaceOso, May 21 '18 at 01:29
`@Query("select a.applicant AS applicant, a.reviewed AS reviewed, a.status AS status from JobApplicants a where a.job.id = ?1") List getOnlyApplicants(UUID jobId);` Did you check adding `AS`? — UsamaAmjad, May 21 '18 at 15:21
@UsamaAmjad thanks a lot for your input, unfortunately that didn't solve it either. I did however figure it out. I'll post my answer. Thanks again bud! — SpaceOso, May 21 '18 at 23:33

score 1 · Accepted Answer · answered May 21 '18 at 23:41

I found the answer. The correct query to get the result I want is:

@Query("select new JobApplicants (a.status, a.reviewed, a.applicant, a.appliedOnDate, a.lastModifiedDate) from JobApplicants a where a.job.id = :jobId")
    List<JobApplicants> getOnlyApplicants(@Param("jobId") UUID jobId);

that query returns:

"bca4ed5c-b81a-420d-8b8b-9130d815d5f1": [
        {
            "id": "ca72dbf5-d20e-482b-a983-909866342c0c",
            "applicant": {
                "id": "109ca778-8526-40c6-a9dd-1f4e6ef2fef7",
                "createdDate": "2018-05-20T11:22:50.607",
                "lastModifiedDate": "2018-05-20T11:22:50.607",
                "address": null,
                "firstName": "Xxxx",
                "lastName": "xxx",
                "email": "xxx@gmail.com",
                "phoneNumber": "xxx",
                "resume": "xxx.pdf",
                "resumeUrl": "https://xxx.pdf",
                "coverLetter": "xxx.pdf",
                "coverLetterUrl": "https://xxx.pdf",
                "website": "sdfasdfasdf"
            },
            "job": null,
            "appliedOnDate": "2018-05-20T11:22:50.611",
            "lastModifiedDate": "2018-05-20T11:22:50.611",
            "status": "approved",
            "reviewed": null
        },

These two threads help me figure it out in case you run into the same issue:

How to return a custom object from a Spring Data JPA GROUP BY query

jpa constructor expressions with multiple SELECT NEW statements

Return column name in a JpaRepository query in Spring Boot using PostgreSQL

1 Answers1