美文网首页
JPA多表分页动态多条件查询

JPA多表分页动态多条件查询

作者: baiyin1115 | 来源:发表于2019-03-27 17:07 被阅读0次

    参考文章:

    项目里面应用了jpa,开发一时爽,到多表联合查询的时候就比较悲剧了。结合上面的文章,自写sql进行查询。代码如下:

    @Component
    public class JoinQueryRepoImpl {
    
      @Autowired
      private EntityManager entityManager;
      /**
       * 客户和账户级联查询
       */
      public Page<TBizAcctPopup> findAcctPopup(AcctVo condition, Pageable pageable) {
    
        StringBuilder countSelectSql = new StringBuilder();
        countSelectSql.append(" SELECT    count(1)                     ");
        countSelectSql.append(" FROM                                   ");
        countSelectSql.append("     t_biz_acct m,                        ");
        countSelectSql.append("     t_biz_customer_info n                ");
        countSelectSql.append(" WHERE                                  ");
        countSelectSql.append("     m.cust_no = n.id                     ");
    
        StringBuilder selectSql = new StringBuilder();
        selectSql.append(" SELECT                                 ");
        selectSql.append("  m.*,                                 ");
        selectSql.append("  n.cert_no AS cust_cert_no,           ");
        selectSql.append("  n.cert_type AS cust_cert_type,       ");
        selectSql.append("  n.NAME AS cust_name,                 ");
        selectSql.append("  n.sex AS cust_sex,                   ");
        selectSql.append("  n.mobile AS cust_mobile,             ");
        selectSql.append("  n.phone AS cust_phone,               ");
        selectSql.append("  n.email AS cust_email,               ");
        selectSql.append("  n.type AS cust_type,                 ");
        selectSql.append("  n.STATUS AS cust_status              ");
        selectSql.append(" FROM                                   ");
        selectSql.append("  t_biz_acct m,                        ");
        selectSql.append("  t_biz_customer_info n                ");
        selectSql.append(" WHERE                                  ");
        selectSql.append("  m.cust_no = n.id                     ");
    
        Map<String, Object> params = new HashMap<>();
        StringBuilder whereSql = new StringBuilder();
        if (!ObjectUtils.isEmpty(condition.getAcctType())) {
          whereSql.append(" AND m.acct_type = :acctType ");
          params.put("acctType", condition.getAcctType());
        }
        if (!StringUtils.isEmpty(condition.getCustCertNo())) {
          whereSql.append(" AND n.cert_no = :custCertNo ");
          params.put("custCertNo", condition.getCustCertNo());
        }
        if (!StringUtils.isEmpty(condition.getCustName())) {
          whereSql.append(" AND n.name like :custName ");
          params.put("custName", condition.getCustName() + "%");
        }
        if (!StringUtils.isEmpty(condition.getCustMobile())) {
          whereSql.append(" AND n.mobile = :custMobile ");
          params.put("custMobile", condition.getCustMobile());
        }
        if (!ObjectUtils.isEmpty(condition.getStatsList())) {
          whereSql.append(" AND n.status in (:status) ");
          params.put("status", condition.getStatsList());
        }
    
        String orderSql = "  order by m.id asc ";
    
        String countSql = new StringBuilder().append(countSelectSql).append(whereSql).toString();
        Query countQuery = entityManager.createNativeQuery(countSql);
    
        for (Map.Entry<String, Object> entry : params.entrySet()) {
          countQuery.setParameter(entry.getKey(), entry.getValue());
        }
        BigInteger totalCount = (BigInteger) countQuery.getSingleResult();
    
        String querySql = new StringBuilder().append(selectSql).append(whereSql).append(orderSql).toString();
    
        // select s.*,c.* 这种,两个表有相同字段的,因为第二个表的对应字段会用第一个表的对应字段,数据信息不对。
        //Query query = this.entityManager.createNativeQuery(querySql,"StudentResults");
        Query query = this.entityManager.createNativeQuery(querySql, TBizAcctPopup.class);
        for (Map.Entry<String, Object> entry : params.entrySet()) {
          query.setParameter(entry.getKey(), entry.getValue());
        }
        query.setFirstResult((int) pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
    
        List<TBizAcctPopup> resultList = query.getResultList();
        Page<TBizAcctPopup> page = new PageImpl<>(resultList, pageable, totalCount.longValue());
    
        return page;
      }
    }
    
    //-----------------------------------
    //定义返回的时候接数据的bean
    @Entity
    @Data
    @Builder
    @AllArgsConstructor
    @NoArgsConstructor
    public class TBizAcctPopup implements Serializable {
    
      private static final long serialVersionUID = -6712496907168087009L;
    
      @Id
      @Column(name = "id")
      private Long id;
    
      @Column(name = "cust_no", nullable = false)
      private Long custNo;
    
      @Column(name = "name", nullable = false)
      private String name;
      
      @Column(name = "available_balance", nullable = false, precision = 2)
      private BigDecimal availableBalance;
    
      @Column(name = "freeze_balance", nullable = false, precision = 2)
      private BigDecimal freezeBalance;
    
    
      @Column(name = "acct_type", nullable = false)
      private Long acctType;
    
    
      @Column(name = "balance_type")
      private Long balanceType;
    
    
      @Column(name = "status")
      private Long status;
    
    
      @Column(name = "version")
      private Long version;
    
      /**
       * 创建人
       */
      @Column(name = "create_by", updatable = false)
      @CreatedBy
      private Long createBy;
    
      /**
       * 修改人
       */
      @Column(name = "modified_by")
      @LastModifiedBy
      private Long modifiedBy;
    
      /**
       * 创建时间
       */
      @CreatedDate
      @Column(name = "create_at", updatable = false)
      private Timestamp createAt;
    
      /**
       * 修改时间
       */
      @LastModifiedDate
      @Column(name = "update_at")
      protected Timestamp updateAt;
    
    
      @Column(name = "cust_cert_no")
      private String custCertNo;
    
    
      @Column(name = "cust_cert_type")
      private Long custCertType;
    
    
      @Column(name = "cust_name")
      private String custName;
    
    
      @Column(name = "cust_sex")
      private Long custSex;
    
    
      @Column(name = "cust_mobile")
      private String custMobile;
    
    
      @Column(name = "cust_phone")
      private String custPhone;
    
    
      @Column(name = "cust_email")
      private String custEmail;
    
      @Column(name = "cust_type")
      private Long custType;
    
      @Column(name = "cust_status")
      private Long custStatus;
    
      @Transient
      private String acctTypeName;
    
      @Transient
      private String statusName;
    
    }
    

    相关文章

      网友评论

          本文标题:JPA多表分页动态多条件查询

          本文链接:https://www.haomeiwen.com/subject/bwqtbqtx.html