如何在Java的JPA Criteria API的FROM和SELECT子句中使用别名？

当前生成的原生SQL #

select ebillorder0_.ID as id1_3_, 
from COMORC_NUTE_EBILL_ORDERS ebillorder0_ 
inner join COMORC_NUTE_EBILL_CUSTOMER ebillcusto1_ on ebillorder0_.EBILL_CUSTOMER=ebillcusto1_.ID 
where ebillorder0_.ORDER_TYPE=? and ebillorder0_.ORDER_STATUS=? and ebillcusto1_.CHANNEL=? and (ebillorder0_.LANGUAGE not in  (?))

期望生成的SQL #

select myOrder.ID as id1_3_, 
from COMORC_NUTE_EBILL_ORDERS myOrder
inner join COMORC_NUTE_EBILL_CUSTOMER ebillcusto1_ on myOrder.EBILL_CUSTOMER=ebillcusto1_.ID 
where myOrder.ORDER_TYPE=? and myOrder.ORDER_STATUS=? and ebillcusto1_.CHANNEL=? and (myOrder.LANGUAGE not in  (?))

当前实现代码 #

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<EbillOrder> criteriaQuery = criteriaBuilder.createQuery(EbillOrder.class);
Root<EbillOrder> ebillOrderRoot = (Root<EbillOrder>) criteriaQuery.from(EbillOrder.class);
Join<EbillOrder, EbillCustomer> customerJoin = ebillOrderRoot.join("ebillCustomer");

criteriaQuery = criteriaQuery.select(ebillOrderRoot.get("id")).where(predicates.toArray(new Predicate[0]));

Query innerQuery = entityManager.createQuery(criteriaQuery);
String innerQuerySql = com.vladmihalcea.hibernate.type.util.SQLExtractor.from(innerQuery);

尝试过的方法 #

criteriaQuery = criteriaQuery.select((Selection) ebillOrderRoot.get("id").alias("myOrder")).where(predicates.toArray(new Predicate[0]));

解决方案 #

要生成指定表别名的SQL，核心是给Root对象设置别名，而非单个字段。修改后的代码如下：

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<EbillOrder> criteriaQuery = criteriaBuilder.createQuery(EbillOrder.class);
// 给Root对象设置表别名"myOrder"
Root<EbillOrder> ebillOrderRoot = criteriaQuery.from(EbillOrder.class).alias("myOrder");
Join<EbillOrder, EbillCustomer> customerJoin = ebillOrderRoot.join("ebillCustomer");

// 构建查询条件（示例，需根据实际逻辑补充完整参数）
List<Predicate> predicates = new ArrayList<>();
predicates.add(criteriaBuilder.equal(ebillOrderRoot.get("orderType"), /* 参数值 */));
predicates.add(criteriaBuilder.equal(ebillOrderRoot.get("orderStatus"), /* 参数值 */));
predicates.add(criteriaBuilder.equal(customerJoin.get("channel"), /* 参数值 */));
predicates.add(criteriaBuilder.not(ebillOrderRoot.get("language").in(/* 参数值 */)));

criteriaQuery.select(ebillOrderRoot.get("id")).where(predicates.toArray(new Predicate[0]));

Query innerQuery = entityManager.createQuery(criteriaQuery);
String innerQuerySql = com.vladmihalcea.hibernate.type.util.SQLExtractor.from(innerQuery);

关键说明 #

• Root.alias("myOrder")会将SQL中COMORC_NUTE_EBILL_ORDERS表的别名设置为myOrder，后续SELECT、WHERE子句中引用该表字段时，都会自动使用这个别名。
• 此前给ebillOrderRoot.get("id")设置别名的操作，仅会修改查询结果列的别名（即as后的内容），无法改变表别名，因此达不到预期效果。

内容的提问来源于stack exchange，提问作者Wrapper