使用JPA就尽量不要使用原生SQL!!!!!!!

HQL

@Query("select new com.cicjust.idc.entity.VO.BankReceiptVO(ibp.id,ifp.filePath,ibp.receiptDate,ibp.payerName,ibp.receiverName,ibp.amount,ibp.foreignFee,ibp.currency) from IdcBankReceiptPO ibp left join IdcFileBasicsPO ifp on ibp.basicsId=ifp.id " +
            "and (#{#dto.id} is null or ibp.reconciliationBillId=#{#dto.id})" +
            "and (#{#dto.date} is null or ibp.receiptDate=#{#dto.date})" +
            "and (#{#dto.payerName} is null or ibp.payerName like concat('%',#{#dto.payerName},'%') )" +
            "and (#{#dto.receiverName} is null or ibp.receiverName like concat('%',#{#dto.receiverName},'%') )order by ibp.receiptDate asc")
    Page<BankReceiptVO> findAllByReconciliationBillId(@Param("dto") BankReceiptDTO dto, Pageable page);
1
2
3
4
5
6

动态条件(不好用)

 Page<IdcHandoverReceiptPO> result = idcHandoverReceiptDao.findAll((Specification<IdcHandoverReceiptPO>) (root, criteriaQuery, criteriaBuilder) -> {
            Predicate predicate = criteriaBuilder.conjunction();
            String companyNameOrTaxNum = handoverReceiptConditionDto.getCompanyNameOrTaxNum();
            String periodId = handoverReceiptConditionDto.getPeriodId();
            Integer status = handoverReceiptConditionDto.getStatus();


            if (status.intValue() != HandoverReceiptStatusEnum.ALL.getValue()) {
                //查询全部状态
                predicate.getExpressions().add(
                        criteriaBuilder.and(root.<IdcHandoverReceiptPO>get("status").in(status))
                );
            }
            if (!StringUtils.isBlank(companyNameOrTaxNum)) {
                Predicate p1 = criteriaBuilder.like(root.get("taxNumber"), "%"+companyNameOrTaxNum+"%");
                Predicate p2 = criteriaBuilder.like(root.get("companyName"), "%"+companyNameOrTaxNum+"%");
                predicate.getExpressions().add(
                        criteriaBuilder.or(p1, p2)
                );
            }
            if (!StringUtils.isBlank(periodId)) {
                predicate.getExpressions().add(
                        criteriaBuilder.and(root.<IdcHandoverReceiptPO>get("periodId").in(periodId))
                );
            }
            //taxNumber在用户的权限列表内
            predicate.getExpressions().add(
                    criteriaBuilder.and(root.get("taxNumber").in(allLocalUserPassedAccountTaxNumer))
            );


            return predicate;
        },pageable);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

OneToMany (单方维护关系)

private List<IdcVatItemPO> idcVatItemPO;
    
    @Fetch(FetchMode.SUBSELECT)
    @OneToMany(cascade = {CascadeType.REMOVE},fetch = FetchType.LAZY)
    @JoinColumn(name="main_id")
    @JsonIgnore
    public List<IdcVatItemPO> getIdcVatItemPO() {
        return idcVatItemPO;
    }
    public void setIdcVatItemPO(List<IdcVatItemPO> idcVatItemPO) {
        this.idcVatItemPO = idcVatItemPO;
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
Hibernate: update idc_vat_item set main_id=null where main_id=?
1
  • @JoinColum光秃秃一个name时,在由单方级联删除多方时,由于删除单方,会先级联变更多方的外键为null,但如果数据库设置外键不能为null时,就会产生报错。

    org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
    
    1

此时可以添加updatable = false 参数取消单方对多方的级联更新

  • 如果想通过在单方实体的List里面remove元素达到删除多方元素的需求,添加orphanRemoval=true