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

参考文章:

项目里面应用了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;

}
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容

  • 简介: 本文由浅入深地讲述了使用 Spring Data JPA 需要关注的各个方面,为读者了解和使用该框架提供了...
    AiPuff阅读 4,520评论 1 28
  • 2017年8月21日 我原本只想简单记录一下springboot中应用Jpa的简单操作。不想由于hibernate...
    行者N阅读 6,541评论 0 23
  • 一、JPA概述 1. JPA是什么 (1) Java Persistence API:用于对象持久化的 API(2...
    努力努力再努力_y阅读 3,258评论 0 4
  • 前言 由于公司一直在使用 JPA 作为 ORM 框架,因此分配到需要多表复杂查询或动态查询都很头大。很多人对 JP...
    StephenRo阅读 16,703评论 2 8
  • 干燥肌肤补水很重要,睡眠面膜成了很多女性的最爱。但是错误的使用方法会让你的肌肤备受伤害。下面,美白专家给大家列出些...
    智慧居家小南阅读 292评论 1 0