java—如何将此sql(或类似sql)转换为jpa标准

1rhkuytd  于 2021-07-27  发布在  Java
关注(0)|答案(1)|浏览(453)

我有这个sql,需要解析到jpa标准。我读过一些东西,但我不能在jpa中使用union,所以我需要一个类似的解决方案。
我有3个表(具有相同的字段),需要在datatable中联合打印。
查询是:

SELECT * FROM (
    SELECT id, project_id, start_date, end_date, 'cs' FROM construction_shares
    UNION
    SELECT id, project_id, start_date, end_date, 'ips' FROM intervention_pr_shares
    UNION
    SELECT id, project_id, start_date, end_date, 'is' FROM intervention_shares
) AS t ORDER BY START_DATE ASC;

有人能帮我吗?
谢谢!

kxe2p93d

kxe2p93d1#

如果jpa不支持联合,您可以尝试两种选择。
创建本机查询并从entitymanager执行,结果是POJO列表。
像这样抛出3个查询:
共享职位:

private Integer id;
private Integer projectId;
private Date startDate; 
private Date endDate

public SharesPojo(Integer id, Integer projectId, Date startDate, Date endDate);

道:

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery<SharesPojo> csQuery = cb.createQuery(SharesPojo.class);
Root<ConstructionShares> csRoot = csQuery.from(ConstructionShares.class);
csQuery.multiselect(
    csRoot.get(ConstructionShares_.id)
    csRoot.get(ConstructionShares_.projectId)
    csRoot.get(ConstructionShares_.startDate)
    csRoot.get(ConstructionShares_.endDate)
);

CriteriaQuery<SharesPojo> ipsQuery = cb.createQuery(SharesPojo.class);
Root<InterventionPrShares> ipsRoot = ipsQuery.from(InterventionPrShares.class);
ipsQuery.multiselect(
    ipsRoot.get(InterventionPrShares_.id)
    ipsRoot.get(InterventionPrShares_.projectId)
    ipsRoot.get(InterventionPrShares_.startDate)
    ipsRoot.get(InterventionPrShares_.endDate)
);

CriteriaQuery<SharesPojo> isQuery = cb.createQuery(SharesPojo.class);
Root<InterventionShares> isRoot = isQuery.from(InterventionShares.class);
isQuery.multiselect(
    isRoot.get(InterventionShares_.id)
    isRoot.get(InterventionShares_.projectId)
    isRoot.get(InterventionShares_.startDate)
    isRoot.get(InterventionShares_.endDate)
);

List<SharesPojo> unionList = new ArrayList<>();
unionList.addAll(em.createQuery(csQuery).getResultList());
unionList.addAll(em.createQuery(ipsQuery).getResultList());
unionList.addAll(em.createQuery(isQuery).getResultList());

相关问题