Madhu Madhu - 26 days ago 7
Java Question

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

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

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