Java Spring hibernate HQL where 子句不起作用

Question

我有一个带有 JOIN 的 HQL 查询，但连接实体上的 where 子句 (instrPrice.date BETWEEN :dateFrom AND :dateTo ) 不起作用。查询始终返回instrumentPrice的所有记录，而不是通过日期限制结果。

命名查询

@NamedQuery(name = "findAllPrices", 
        query = "SELECT DISTINCT taPat FROM TaPatternInstrument taPat "
                + "LEFT JOIN FETCH taPat.instrument instr "
                + "LEFT JOIN instr.instrumentPriceList instrPrice "
                + "WHERE taPat.id = :taPatternInstrumentId "
                + "AND instrPrice.date BETWEEN :dateFrom AND :dateTo ")

调用查询的服务

public TaPatternInstrument findAllPrices(int taPatternInstrumentId, LocalDate dateFrom,  LocalDate dateTo) {

   TypedQuery<TaPatternInstrument> typedQuery = createNamedQuery("findAllPrices", 
        TaPatternInstrument.class);
   typedQuery.setParameter("taPatternInstrumentId", taPatternInstrumentId);
   typedQuery.setParameter("dateFrom", dateFrom);
   typedQuery.setParameter("dateTo", dateTo);
   return typedQuery.getSingleResult(); 
}

实体

public abstract class BaseEntity implements Serializable {

   @Id  
   @Column(name = "id")     
   @GeneratedValue(strategy =
                GenerationType.IDENTITY)
   protected int id; ... 
}

public class TaPatternInstrument extends BaseEntity {

   @ManyToOne(fetch = FetchType.EAGER)
   @JoinColumn(name = "instrument_id", nullable = false, foreignKey = @ForeignKey(name = 
            "tapatterninstrument_instrument_fk"))
   private Instrument instrument;

}


public class Instrument extends BaseEntity {

  @OneToMany(mappedBy = "instrument", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
  private List<InstrumentPrice> instrumentPriceList;

}

生成的 SQL

SELECT DISTINCT tapatterni0_.id  AS id1_34_0_, 
...
FROM   tapatterninstrument tapatterni0_ 
       LEFT OUTER JOIN instrument instrument1_ 
                    ON tapatterni0_.instrument_id = instrument1_.id 
       LEFT OUTER JOIN instrumentprice instrument2_ 
                    ON instrument1_.id = instrument2_.instrument_id 
WHERE  tapatterni0_.id = ? 
       AND ( instrument2_.date BETWEEN ? AND ? ) 

Answer 1

解决方案是在instrumentPriceList上添加FETCH:LEFT JOIN FETCH instr.instrumentPriceList instrPrice

@NamedQuery(name = "findAllPrices", 
query = "SELECT DISTINCT taPat FROM TaPatternInstrument taPat "
        + "LEFT JOIN FETCH taPat.instrument instr "
        + "LEFT JOIN FETCH instr.instrumentPriceList instrPrice "
        + "LEFT JOIN taPat.taPatternInstrumentPriceList taPatpr "
        + "WHERE taPat.id = :taPatternInstrumentId "
        + "AND instrPrice.date BETWEEN :dateFrom AND :dateTo ")

FETCH 强制 Hibernate 在第一个数据库请求时立即检索实体 (InstrumentPrice)。因此，where 子句被考虑在内。如果没有 FETCH，则仅当调用实体 Instrument 的 getInstrumentPriceList 方法(执行对 DB 的额外调用)时，才会从 DB 检索实体 InstrumentPrice。通过对数据库的额外调用，不再考虑where子句，从而从实体instrumentPrice中检索所有记录。