意外的AST节点：查询

Question

SELECT e.EE_ESERVICE_ID,
       CASE
          WHEN EXISTS
                  (SELECT 1
                     FROM WORKFLOWS w
                    WHERE w.WORKFLOW_ID = e.WORKFLOW_ID)
          THEN
             (SELECT i.I18ND_TRANSLATION
                FROM WORKFLOWS w, I18N_DICTIONARY i
               WHERE     WORKFLOW_ID = e.WORKFLOW_ID
                     AND i.I18N_ID = w.WORKFLOW_NAME
                     AND i.I18N_LOCALE_ID = 1)
          ELSE
             COALESCE ('', '')
       END
          AS WORKFLOW_NAME
  FROM ESERVICES e

StringBuffer hql = new StringBuffer("select e.eserviceId as eserviceId,"
                + " (case when exists(select 1 from Workflow w where w.workflowId = e.workflowId)"
                + " then (select i.i18ndTranslation from Workflow w,I18nDictionary i where w.workflowId = e.workflowId and i.i18nId = w.workflowName and i.i18nLocaleId = :localeId)"
                + " else coalesce('', '') end) as workflowName"
                + " from Eservice as e");

我知道我们可以直接从左连接实现它，但由于一些限制，我们使用交叉连接，所以我需要走这条路...

请分享您的想法，欢迎提出建议

更新1我们没有使用关联，这就是为什么我们严格交叉连接。

    @Entity
    @Table(name = "ESERVICES")
    public class Eservice implements java.io.Serializable {

        private static final long serialVersionUID = -4088905661029802626L;

        public Eservice() {}

        @Id
        @SequenceGenerator(name = "escSequence", sequenceName = "SQ_ESERVICES",allocationSize=1)
        @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "escSequence")
        @Column(name = "EE_ESERVICE_ID", unique = true, nullable = false)
        private BigDecimal eserviceId;

        @Column(name = "WORKFLOW_ID")
        private BigDecimal workflowId;

    @Transient
    private String workflowName;

        // setter and getter
    }

@Entity
@Table(name = "WORKFLOWS")
public class Workflow implements java.io.Serializable { 

    private static final long serialVersionUID = -4863237070153860617L;

    public Workflow() {}

    @Id
    @SequenceGenerator(name = "wfSequence", sequenceName="SQ_WORKFLOWS", allocationSize=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE , generator = "wfSequence")
    @Column(name = "WORKFLOW_ID", unique = true, nullable = false)
    private BigDecimal workflowId;

    @Column(name = "I18N_WORKFLOW_NAME")
    private BigDecimal workflowName;

    // setter and getter
}

@Entity
@Table(name="I18N_DICTIONARY")
@Cache(usage=CacheConcurrencyStrategy.READ_WRITE)
public class I18nDictionary implements Serializable{

    /**
     * The serialization runtime associates with each serializable class a version number called serialVersionUID.
     */
    private static final long serialVersionUID = -2587075034303056842L;

    @Id
    @SequenceGenerator(name = "seqDictGenerator", sequenceName = "SQ_I18N_DICTIONARY",allocationSize=1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seqDictGenerator")
    @Column(name="I18N_DICT_ID", unique = true, nullable = false)
    private BigDecimal i18nDictId;

    @Column(name="I18N_ID")
    private BigDecimal i18nId;

    @Column(name="I18ND_TRANSLATION")
    private String i18ndTranslation;

    @Column(name="I18N_LOCALE_ID")
    private BigDecimal i18nLocaleId;

    // setter and getter    
}

Query query = getSession()
                    .createQuery(hql.toString().trim())
                    .setBigDecimal("localeId", i18nLocale.getI18nLocaleId())
                    .setResultTransformer(Transformers.aliasToBean(Eservice.class));

            List<Eservice> resultSet = query.list();

Answer 1

对我来说，你可以试试这个：

select e.eserviceId as eserviceId,
    case
        when e.workflowId is null then ''
        else
        (select i.i18ndTranslation from Workflow w,I18nDictionary i
        where w.workflowId = e.workflowId
        and i.i18nId = w.workflowName and i.i18nLocaleId = :localeId)
    end as workflowName
from Eservice as e

编辑

试试这个：

String hql = 
    "select " + new Eservice.class.getName() + 
    "(e.eserviceId, " +
    "case " +
        " when e.workflowId is null then '' " +
        " else " +
        " (select i.i18ndTranslation from Workflow w,I18nDictionary i " +
        " where w.workflowId = e.workflowId " +
        " and i.i18nId = w.workflowName and i.i18nLocaleId = :localeId) " +
    "end)" +
" from Eservice as e "

public Eservice(BigDecimal eserviceId, String workflowName) {
     this.eserviceId = eserviceId;
     this.workflowName = workflowName;
}

Query query = getSession().createQuery(hql.toString().trim())
                .setBigDecimal("localeId", i18nLocale.getI18nLocaleId());

        List<Eservice> resultSet = (List<Eservice>)query.list();