获取数据时奇怪的HQL查询行为

Question

我调用下面的方法来计算一些值。我提供agencyID和月份的整数表示来执行计算。

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

等等...直到我到达12月（值12）。然而，当我到达5月（月值5）时，代码给出了一个运行时错误。然而，奇怪的是这个错误被抛出

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

在我的查询中，我没有在查询中的任何位置包含shift\u dayId，如下所示：

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

我恳请帮助我确定为什么givenMonth在值大于4时抛出运行时异常。

谢谢你

编辑

这是我要求的ShiftDaysModel。。。

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

Answer 1

当您使用签名时：

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

预期select子句将包含创建实体ShiftDaysModel所需的所有字段。

在您的例子中，查询只选择列userid。当Hibernate/Spring试图将每一行转换为一个ShiftDaysModel时，它会抛出异常，因为对应于shift\u dayid的字段没有值。

如果您将查询更改为从shift\u days中选择distinct*，这可能会起作用…

或者，如果您只关心用户ID：

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