参考文章：
项目里面应用了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;
}
