时间戳

首页 > 解决方案 > 使用 PostgreSQL 在 Spring Boot 中的 JpaRepository 查询中返回列名

java - 使用 PostgreSQL 在 Spring Boot 中的 JpaRepository 查询中返回列名

问题描述

我有一个表 Job_Applicants，其中有一个名为 review 的列，其中包含一个布尔值，还有一个状态列，其中包含一个字符串值

进行以下查询时

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

我得到以下信息：

"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
    ],
}

我添加的所有 XXX 以删除个人信息。

但是正如您所看到的，review 和 status 列没有列名，有没有办法可以调整我的查询以包含列名？

所以它最后不读“null，null”？

我想让它读到 "reviewed":null, "status":null

如果有帮助，这是我的实体

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

标签： javasqlpostgresqlspring-bootjpa

解决方案

我找到了答案。获得我想要的结果的正确查询是：

@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);

该查询返回：

"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
        },

如果您遇到相同的问题，这两个线程可以帮助我弄清楚：

如何从 Spring Data JPA GROUP BY 查询中返回自定义对象

具有多个 SELECT NEW 语句的 jpa 构造函数表达式

推荐阅读