如何将指定SQL转换为Java 8 CriteriaQuery？执行报错求助

原始可运行SQL #

SELECT *
FROM SOLICITUDES
ORDER BY CASE WHEN TECNICO = 'test' THEN 0 ELSE 1 END, 
         CASE WHEN TECNICO = 'test' THEN CREACION_DATE END DESC,
         CREACION_DATE DESC;

当前编写的CriteriaQuery代码 #

final EntityManager em = getEntityManager();
final Class<Solicitud> cl = Solicitud.class;
final CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery<Solicitud> cq = cb.createQuery(cl);
final Root<Solicitud> r = cq.from(cl);

cq.select(r);
cq.orderBy(
           cb.asc(
              cb.selectCase()
              .when(cb.equal(r.get("tecnico"), "test"), 0)
                  .otherwise(1)
           ),
           cb.desc(
               cb.selectCase()
               .when(cb.equal(r.get("tecnico"), "test"), r.get("creacionDate"))
           ),
           cb.desc(r.get("creacionDate"))
);

TypedQuery<Solicitud> query = em.createQuery(cq);
return query.getResultList();

报错日志 #

[dpv-web] 2024-03-19 10:05 : [ERROR] {es.juntadeandalucia.cfv.dpv.web.webapp.bean.datamodel.LazyFiltrableDataModelv3.load()} es.juntadeandalucia.cfv.dpv.web.webapp.bean.datamodel.LazyFiltrableDataModelv3 () - Error en la carga de la rejilla
es.juntadeandalucia.cfv.dpv.negocio.exception.DPVNegocioException: Error no especificado.
  at es.juntadeandalucia.cfv.dpv.negocio.bo.impl.SolicitudManagerImpl.consultarElementoByFiltro(SolicitudManagerImpl.java:1891)
  at es.juntadeandalucia.cfv.dpv.negocio.bo.impl.SolicitudManagerImpl.consultarElementoByFiltro(SolicitudManagerImpl.java:248)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:498)
  at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
  at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
  at com.sun.proxy.$Proxy146.consultarElementoByFiltro(Unknown Source)
  at es.juntadeandalucia.cfv.dpv.web.webapp.bean.datamodel.LazySolicitudVODataModel.cargar(LazySolicitudVODataModel.java:263)
  at es.juntadeandalucia.cfv.dpv.web.webapp.bean.datamodel.LazySolicitudVODataModel.cargar(LazySolicitudVODataModel.java:203)
  at es.juntadeandalucia.cfv.dpv.web.webapp.bean.datamodel.LazyFiltrableDataModelv3.load(LazyFiltrableDataModelv3.java:352)
  at org.primefaces.component.datatable.DataTable.loadLazyData(DataTable.java:731)
  at org.primefaces.component.datatable.DataTableRenderer.encodeEnd(DataTableRenderer.java:79)
  at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:875)
  at com.sun.faces.renderkit.html_basic.HtmlBasicRenderer.encodeRecursive(HtmlBasicRenderer.java:312)
  at com.sun.faces.renderkit.html_basic.HtmlBasicRenderer.encodeRecursive(HtmlBasicRenderer.java:309)
  at com.sun.faces.renderkit.html_basic.GroupRenderer.encodeChildren(GroupRenderer.java:105)
  at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:845)
  at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1756)
  at org.primefaces.component.panelgrid.PanelGridRenderer.encodeDynamicBody(PanelGridRenderer.java:92)
  at org.primefaces.component.panelgrid.PanelGridRenderer.encodeBody(PanelGridRenderer.java:60)
  at org.primefaces.component.panelgrid.PanelGridRenderer.encodeEnd(PanelGridRenderer.java:49)
  at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:875)
  at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1763)
  at javax.faces.render.Renderer.encodeChildren(Renderer.java:168)
  at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:845)
  at com.sun.faces.renderkit.html_basic.HtmlBasicRenderer.encodeRecursive(HtmlBasicRenderer.java:304)
  at com.sun.faces.renderkit.html_basic.GroupRenderer.encodeChildren(GroupRenderer.java:105)
  at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:845)
  at com.sun.faces.renderkit.html_basic.HtmlBasicRenderer.encodeRecursive(HtmlBasicRenderer.java:304)
  at com.sun.faces.renderkit.html_basic.GridRenderer.renderRow(GridRenderer.java:185)
  at com.sun.faces.renderkit.html_basic.GridRenderer.encodeChildren(GridRenderer.java:129)
  at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:845)
  at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1756)
  at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1759)
  at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1759)
  at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(FaceletViewHandlingStrategy.java:401)
  at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:131)
  at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:121)
  at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
  at com.sun.faces.life

对应的Entity类 #

@Entity
@Table(name = "SOLICITUDES")
public class Solicitud implements Serializable, IAuditoriaDTO, IDTO {

   private static final long serialVersionUID = -4355851440179505416L;

   @Id
   @SequenceGenerator(name = "QSOLICITUD_GENERATOR", sequenceName = "SEQ_SOLICITUD",
      initialValue = 1, allocationSize = 1)
   @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "QSOLICITUD_GENERATOR")
   @Column(name = "Q_SOLICITUD")
   private Long codigo;

   @Column(name = "TECNICO")
   private String tecnico;

   @Temporal(TemporalType.DATE)
   @Column(name = "CREACION_DATE")
   private Date creacionDate;

   public final String getTecnico() {
      return tecnico;
   }
   public final void setTecnico(final String tecnico) {
      this.tecnico = tecnico;
   }

   public final Date getCreacionDate() {
      return this.creacionDate = (this.creacionDate == null) ? null : new Date(this.creacionDate.getTime());
   }

   public final void setCreacionDate(final Date creacionDate) {
      this.creacionDate = (creacionDate == null) ? null : (Date) creacionDate.clone();
   }
}

解决建议 #

1. 修复Case表达式的类型一致性
   第二个selectCase未指定otherwise分支，JPA类型推断可能出错。补充otherwise分支，保持类型和creacionDate一致：

   cb.desc(
       cb.selectCase()
       .when(cb.equal(r.get("tecnico"), "test"), r.get("creacionDate"))
       .otherwise(r.get("creacionDate"))
   )
   

   原始SQL中不满足条件时返回NULL，排序时NULL通常排在末尾，和上述写法效果一致。

2. 使用JPA元模型替代字符串属性名
   避免字符串属性名的拼写错误，改用编译期检查的元模型类：

   // 假设生成的元模型类为Solicitud_
   cb.equal(r.get(Solicitud_.tecnico), "test")
   

3. 查看底层SQL和详细异常
   开启JPA SQL日志，对比生成的SQL与原始SQL是否一致；在consultarElementoByFiltro方法中捕获PersistenceException，打印其根源异常，定位具体错误。

4. 检查JPA版本兼容性
   确保使用的JPA版本（如Hibernate 5.x及以上）支持Java 8 CriteriaQuery的selectCase语法，避免版本兼容问题。

内容的提问来源于stack exchange，提问作者Alejandro Verano García