[FIXED] Strange HQL Query Behavior When Fetching Data

Issue

I call the method below to calculate some values. I supply the agencyID and the integer representation of the month to carry out the calculation.

Map jan = new HashMap();
        String a[] =  shifts.getAgencyActiveAgentsByMonth(agencyID, 1).split(",");
        jan.put("label", "Jan");
        jan.put("active", a[0]);
        jan.put("inactive", a[1]);
    activeInactiveGraphData.add(jan);
    Map feb = new HashMap();
        a =  shifts.getAgencyActiveAgentsByMonth(agencyID, 2).split(",");
        feb.put("label", "Feb");
        feb.put("active", a[0]);
        feb.put("inactive", a[1]);
    activeInactiveGraphData.add(feb);

Etc...till I get to December (value 12). However, the code gives a run time error when I get to May (month value 5). The strange thing however is that this error is thrown

java.sql.SQLException: Column 'shift_dayid' not found.

In my query, I did not include the shift_dayId anywhere in my query, as shown below:

@Query(value = "select distinct userid from shift_days where agencyid = :agencyID and month(created_at) = :givenMonth and shift_status = 1", nativeQuery=true)
List<ShiftDaysModel> getAgencyActiveAgentsCountForMonth(@Param("agencyID") String agencyID, @Param("givenMonth") int givenMonth);

I kindly request help in helping me identify why the givenMonth throws runtime exception when the value is greater than 4.

Thank you

EDIT

Here is my ShiftDaysModel as requested...

import java.io.Serializable;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.Date;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Temporal;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;

/**
 *
 * @author Javalove
 */
@Entity
@Getter
@Setter
@NoArgsConstructor
@Table(name = "shift_days")
public class ShiftDaysModel implements Serializable {
    
    public static enum RequestStatus {
        PENDING, ACCEPTED, COMPLETED, REJECTED, TRANSFERED, IN_PROGRESS, VALIDATED;
    }
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long shiftDayID;
    
    @Column(name = "created_at", updatable=false)
    @CreationTimestamp
    @Temporal(javax.persistence.TemporalType.TIMESTAMP)
    private Date createdAt;
    
    @Column(name = "updated_at")
    @UpdateTimestamp
    @Temporal(javax.persistence.TemporalType.TIMESTAMP)
    private Date updatedAt;
    
   
    @Column(name = "userID")
    private String userID;
    
    @Column(name = "agencyID")
    private String agencyID;
    
    @Basic
    @Column(name = "shiftDate")
    private LocalDate shiftDate;
    
    @Basic
    @Column(name = "startTime")
    private LocalTime startTime;
    
    @Basic
    @Column(name = "endTime")
    private LocalTime endTime;
    
    @Column(name = "shiftOptionID")
    private String shiftOptionID;
    
    @Column(name = "shiftStatus")
    private RequestStatus shiftStatus;
    
    @Column(name = "isTransferred")
    private boolean isTransferred;
    
    @Column(name = "transferredFromID")
    private String transferredFromID;
    
    @Column(name = "shiftID")
    private String shiftID;

    @Column(name = "shiftRate")
    private Double shiftRate;
    
    @Column(name = "siteID")
    private String siteID;

    @Basic
    @Column(name = "actualStartDateTime")
    private LocalDateTime actualStartDateTime;
    
    @Basic
    @Column(name = "actualEndDateTime")
    private LocalDateTime actualEndDateTime;
    
    @Column(name = "shiftRequestGroupID")
    private String shiftGroupID = "0";
}

Solution

When you use the signature:

@Query(..)
List<ShiftDaysModel> getAgencyActiveAgentsCountForMonth(...);

The expectation is that the select clause will contain all the fields that are necessary to create the entity ShiftDaysModel.

In your case, the query only select the column userid. When Hibernate/Spring tries to covert each row into a ShiftDaysModel, it throws the exception because there is no value for the field corresponding to shift_dayid.

This might work if you change the query to select distinct * from shift_days ...

Or, if you only care about the userid:

@Query(..)
List<String> getAgencyActiveAgentsCountForMonth(...)

Answered By - Davide D'Alto
Answer Checked By - Senaida (JavaFixing Volunteer)