I'm not able to select particular column from a JPQL?

Question

here is my query where I'm trying to get id and userNotes from Job Class.

@Query("SELECT j.id, j.userNotes FROM Job j WHERE j.bookingTime BETWEEN :stDate AND :edDate")

List<Job> getDriverCalendar(@Param("stDate") Timestamp stDate, @Param("edDate") Timestamp edDate);

Job.java

  package com.housecar.model;
  import java.math.BigDecimal;
  import java.sql.Timestamp;

  import javax.persistence.Column;
  import javax.persistence.Entity;
  import javax.persistence.EntityListeners;
  import javax.persistence.GeneratedValue;
  import javax.persistence.GenerationType;
  import javax.persistence.Id;
  import javax.persistence.JoinColumn;
  import javax.persistence.OneToOne;
  import javax.persistence.Table;

  import org.springframework.data.jpa.domain.support.AuditingEntityListener;

  @Entity
  @EntityListeners(AuditingEntityListener.class)
  @Table(name = "hc_job")
  public class Job{

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @Column(name = "ride_time")
  private Timestamp rideTime;

  @Column(name = "booking_time")
  private Timestamp bookingTime;

  @Column(name = "guest_id")
  private Long guestId;

  @Column(name = "booked_user_id")
  private Long bookedUserId;

  @Column(name = "car_id")
  private Long carId;

  @Column(name = "pickup_location")
  private String pickupLocation;

  @Column(name = "drop_location")
  private String dropLocation;

  @Column(name = "trip_type")
  private Character tripType;

  @Column(name = "is_private_job")
  private Boolean isPrivateJob;

  @Column(name = "estimated_fare")
  private BigDecimal estimatedFare;

  @Column(name = "actual_fare")
  private BigDecimal actualFare;

  @Column(name = "tip")
  private BigDecimal tip;

  @Column(name = "payment_status")
  private Character paymentStatus;

  @Column(name = "user_notes")
  private String userNotes;

  @Column(name = "cancellation_notes")
  private String cancellationNotes;

  @Column(name = "status")
  private Character status;

  @OneToOne
  @JoinColumn(name = "id", referencedColumnName = "id", insertable = false, updatable = false)

  private JobDriverRating jobDriverRating;

  @OneToOne
  @JoinColumn(name = "id", referencedColumnName = "id", insertable = false, updatable = false)

  private JobCostSplit jobCostSplit;

  public Long getId() {
    return id;
  }

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

  public Long getGuestId() {
    return guestId;
  }

  public void setGuestId(Long guestId) {
    this.guestId = guestId;
  }

  public Long getBookedUserId() {
    return bookedUserId;
  }

  public void setBookedUserId(Long bookedUserId) {
    this.bookedUserId = bookedUserId;
  }

  public Long getCarId() {
    return carId;
  }

  public void setCarId(Long carId) {
    this.carId = carId;
  }

  public String getPickupLocation() {
    return pickupLocation;
  }

  public void setPickupLocation(String pickupLocation) {
    this.pickupLocation = pickupLocation;
  }

  public String getDropLocation() {
    return dropLocation;
  }

  public void setDropLocation(String dropLocation) {
    this.dropLocation = dropLocation;
  }

  public Character getTripType() {
    return tripType;
  }

  public void setTripType(Character tripType) {
    this.tripType = tripType;
  }

  public Boolean getIsPrivateJob() {
    return isPrivateJob;
  }

  public void setIsPrivateJob(Boolean isPrivateJob) {
    this.isPrivateJob = isPrivateJob;
  }

  public BigDecimal getEstimatedFare() {
    return estimatedFare;
  }

  public void setEstimatedFare(BigDecimal estimatedFare) {
    this.estimatedFare = estimatedFare;
  }

  public BigDecimal getActualFare() {
    return actualFare;
  }

  public void setActualFare(BigDecimal actualFare) {
    this.actualFare = actualFare;
  }

  public BigDecimal getTip() {
    return tip;
  }

  public void setTip(BigDecimal tip) {
    this.tip = tip;
  }

  public Character getPaymentStatus() {
    return paymentStatus;
  }

  public void setPaymentStatus(Character paymentStatus) {
    this.paymentStatus = paymentStatus;
  }

  public String getUserNotes() {
    return userNotes;
  }

  public void setUserNotes(String userNotes) {
    this.userNotes = userNotes;
  }

  public String getCancellationNotes() {
    return cancellationNotes;
  }

  public void setCancellationNotes(String cancellationNotes) {
    this.cancellationNotes = cancellationNotes;
  }

  public Character getStatus() {
    return status;
  }

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

  public JobDriverRating getJobDriverRating() {
    return jobDriverRating;
  }

  public void setJobDriverRating(JobDriverRating jobDriverRating) {
    this.jobDriverRating = jobDriverRating;
  }

  public Timestamp getRideTime() {
    return rideTime;
  }

  public void setRideTime(Timestamp rideTime) {
    this.rideTime = rideTime;
  }

  public Timestamp getBookingTime() {
    return bookingTime;
  }

  public void setBookingTime(Timestamp bookingTime) {
    this.bookingTime = bookingTime;
  }

  public JobCostSplit getJobCostSplit() {
    return jobCostSplit;
  }

  public void setJobCostSplit(JobCostSplit jobCostSplit) {
    this.jobCostSplit = jobCostSplit;
  }

}

@Query("SELECT j.id, j.userNotes FROM Job j WHERE j.bookingTime BETWEEN :stDate AND :edDate") this query returned [ ].

@Query("SELECT j FROM Job j WHERE j.bookingTime BETWEEN :stDate AND :edDate") this query returned the complete Job object.

Answer 1

In the above query use the alias name for each value fetched

You will get the result as the list of hashmap so change the return type from List<Job> to List<Map> as shown below,

@Query("SELECT j.id as id , j.userNotes as userNotes  FROM Job j WHERE j.bookingTime BETWEEN :stDate AND :edDate")

List<Map> getDriverCalendar(@Param("stDate") Timestamp stDate, @Param("edDate") Timestamp edDate);

Answer 2

Your query doesn't select a Job. It selects two fields. Such a JPQL query returns a List<Object[]>, where each array of the list has two elements.

The return type of the method should thus be changed to List<Object[]>.