Jpa使用union all

前言

Hql目前不支持union all语法，目前都是自定义SQL语句去做的

union all使用

定义实体类

@Data
@Entity
@Table(name = "bank_outbound_work_order")
public class BankOutboundWorkOrder  {

    @Id
    private Long id;

   

    private String applicantRemark;


    private String receiveRemark;


    private Long orgId;

    private LocalDateTime createTime;

}

定义实体类

@Data
@Entity
@FieldNameConstants
@Table(name = "bank_inbound_work_order")
public class BankInboundWorkOrder extends FullAuditedEntity {




    private Long orgId;

    private LocalDateTime appointmentDate;


    private String applicationInformation;

    private String creator;


    @Enumerated(EnumType.STRING)
    private BankInboundWorkOrderStatusEnum workOrderStatus;

    private String remark;
    
      private LocalDateTime createTime;

}

dao层写法

@Query(value = " select id, orgId as orgId, applicationInformation as applicationInformation, workOrderStatus as workOrderStatus, createTime as createTime from (" +
        "select  inBound.id as id, inBound.org_id as orgId, inBound.application_information as applicationInformation, inBound.work_order_status as workOrderStatus, inBound.create_time as createTime " +
        " from bank_inbound_work_order inBound where inBound.work_order_status not in :workOrderList " +
        " union all select outBound.id as id, outBound.org_id as orgId, outBound.applicant_remark as applicationInformation, outBound.work_order_status as workOrderStatus, outBound.create_time as createTime " +
        " from bank_outbound_work_order outBound where outBound.work_order_status not in :workOrderList" +
        ") t",
        countQuery = "select count(*) from (" +
                "select  inBound.id as id " +
                " from bank_inbound_work_order inBound where inBound.work_order_status not in :workOrderList " +
                " union all select outBound.id as id " +
                " from bank_outbound_work_order outBound where outBound.work_order_status not in :workOrderList  " +
        ") t",
        nativeQuery = true)
Page<BankHomePageWorkOrderDto> getWorkOrder(@Param("workOrderList") List<String> workOrderList, Pageable pageable);

这里要注意一下BankHomePageWorkOrderDto接受类的写法，写成字段接收会报有问题

No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [org.luban.module.core.dto.saas.bank.BankHomePageWorkOrderDto]

这个时候要写成

public interface BankHomePageWorkOrderDto {


    Long getId();

    Long getOrgId();

    String getApplicationInformation();

    BankWorkOrderStatusEnum getWorkOrderStatus();

    LocalDateTime getCreateTime();
}

才能转换成实体类

总结

Jpa使用hql不能使用union all，目前只有使用自定义sql，本文使用是Springboot2.3.x以及Springboot2.7.x