EclipseLink/TopLink全外连接代替左连接

Question

On模型Card可以有或没有CardHolder(1:1)，并且我希望通过发行人将每个卡片过滤器链接到激活的CardHolder以及没有CardHolder的卡片，因此我需要一个完整的外部连接。尽管下面的查询转换为左连接，只返回cardholder

的卡片

    final ExpressionBuilder builder = new ExpressionBuilder( Card.class );
    Expression queryExp = builder.get( "cardIssuer" ).equal( cardIssuer );
    queryExp = queryExp.and( builder.get( "cardStatus" ).get( "statusType" ).equal( "ACTIVATED" ) );
    queryExp = queryExp.and( builder.getAllowingNull( "cardHolder" )isNull().or(
                  builder.get( "cardHolder" ).get( "status" ).get( "status" ).equal( "ACTIVE" ) ) );

    Expression orderExpression = builder.get( "cardHolder" ).get( "surname" ).descending();

    return getMultiple( queryExp, pageable , Card.class, orderExpression );

翻译查询是

  SELECT COUNT(t0.CARD_ID) FROM CARD t0 LEFT JOIN CARD_HOLDER t3 
 ON (t3.CARD_HOLDER_ID = t0.CARD_HOLDER_ID), CARD_HOLDER_STATUS t2, CARD_STATUS t1 
 WHERE (((((t0.CARD_ISSUER_ID = 10006) AND (t1.STATUS_TYPE = 'ACTIVATED')) AND (t2.STATUS = 'ACTIVE')) 
 AND (t0.CARD_ID IN ('52683','52692'))) 
 AND ((t1.CARD_STATUS_ID = t0.CARD_STATUS_ID) AND (t2.STATUS_ID = t3.STATUS_ID)))

Answer 1

由于JPA版本外部连接没有正确完成，所以我找到了通过本机查询的方法

@NamedNativeQueries( {
@NamedNativeQuery( name = Card.USER_DIRECTORY_BASE,
    query = "select * from card c full outer join card_holder ch on c.card_holder_id = ch.card_holder_id "
      + "where c.CARD_ISSUER_ID = ?1 and c.card_status_id = 1 and ( ch.STATUS_ID = 1 or c.CARD_HOLDER_ID is null) "
      + "order by ch.FORENAME asc", 
    resultClass  = Card.class ),
@NamedNativeQuery( name = Card.USER_DIRECTORY_BASE_COUNT,
    query = "select count(*) from card c full outer join card_holder ch on c.card_holder_id = ch.card_holder_id "
      + "where c.CARD_ISSUER_ID = ?1 and c.card_status_id = 1 and ( ch.STATUS_ID = 1 or c.CARD_HOLDER_ID is null) "
      + "order by ch.FORENAME asc" )
} )

得到结果

Query query = em.createNamedQuery( Card.USER_DIRECTORY_BASE);
query.setParamenter(1,10000);
query.getResultList();