接（https://www.jianshu.com/p/69fa02602904）
在有某天有这么一个简单的需求，一个页面有N种查询方式，这N种参数可能有也可能没有，
并且
·只要查询其中的某几个字段（表和表有关联）
·要有分页
·要查询分页后的统计数据（简单，分页后查出来计算也行）
·要查询出分页之前所有数据的统计数据 （这……）
·需要查询的几个字段和条件有些在一个不关联的第三方表里面……这个表的那个字段有时候是填这个表的id，有时候是其他表的 或者随机字符串数据。
不能直接做关联。
处理方法：拼接原生查询 然后用EntityManager的createNativeQuery 来这行原生查询。
public Query createNativeQuery(String sqlString, Class resultClass);
resultClass是返回的实体类，这个的定义特别重要，由于多个表构成的字段查询，还可能会有sum之类的字段，但又不是真正在数据库有对应的表。
比如下面的sql
String sqlstart = "SELECT " +
" operate.id, " +
" usr.real_name, " +
" usr.identity, " +
" depart.title as depart_title, " +
" capital.change_balance, " +
" capital.change_donation, " +
" operate.deposit as change_deposit, " +
" operate.manager_name, " +
" operate.create_time ";
String sqlend =
"FROM " +
" log_card_operate operate LEFT JOIN log_card_capital capital ON operate.id = capital.trade_no, " +
" user usr, " +
" depart depart " +
"WHERE " +
" depart.id = usr.depart_id " +
" AND usr.id= operate.user_id ";
对应的实体类（注意字段和查询的要一致，下划线会自动对应驼峰，如果有些字段要特殊化命名，可以在sql里起别名）
import lombok.Data;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Transient;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
@Data //lombok
@Entity //这个注解必备
public class StatCardOpen implements Serializable {
@Transient
private static final long serialVersionUID = 1L;
@Id //这个注解必备，必须有个id
private Integer id;
private String realName;
private String identity;
private String departTitle;
private BigDecimal changeBalance;
private BigDecimal changeDonation;
private BigDecimal changeDeposit;
private String managerName;
private Date createTime;
}
然后就可以直接查询出所需要的数据：
Query query = em.createNativeQuery(sqlstart + sqlend + sqlorder, StatCardOpen.class);
List<StatCardOpen> resultList = query.getResultList();
完整例子：
public Map<String, Object> openstat(int pageNum, int pageSize, Map<String, Object> params
) {
Map<String, Object> resultMap = new HashMap<>();
params = MapUtil.filterNullHashMapVal(params);
Integer storeId = params.containsKey("storeId") ? Integer.parseInt(params.get("storeId").toString()) : 0;
//增减原因 资金变动类型：1-充值、2-发放补贴、3-现金取款、4-补贴取款……
Integer type = params.containsKey("type") ? Integer.parseInt(params.get("type").toString()) : 0;
Integer managerId = params.containsKey("managerId") ? Integer.parseInt(params.get("managerId").toString()) : 0;
Integer departId = params.containsKey("departId") ? Integer.parseInt(params.get("departId").toString()) : 0;
Date beginTime = params.containsKey("beginTime") ? DateUtils.String2Date(params.get("beginTime").toString(), "yyyy-MM-dd HH:mm:ss") : null;
Date endTime = params.containsKey("endTime") ? DateUtils.String2Date(params.get("endTime").toString(), "yyyy-MM-dd HH:mm:ss") : null;
String keyword = params.containsKey("keyword") ? params.get("keyword").toString() : null;
String sqlstart = "SELECT " +
" operate.id, " +
" usr.real_name, " +
" usr.identity, " +
" depart.title as depart_title, " +
" capital.change_balance, " +
" capital.change_donation, " +
" operate.deposit as change_deposit, " +
" operate.manager_name, " +
" operate.create_time ";
String sqlstartCount = "SELECT operate.id,count(operate.id) as total," +
" sum(capital.change_balance) as all_change_balance," +
"sum(capital.change_donation) as all_change_donation," +
"sum(operate.deposit) as all_change_deposit ";
String sqlend =
"FROM " +
" log_card_operate operate LEFT JOIN log_card_capital capital ON operate.id = capital.trade_no, " +
" user usr, " +
" depart depart " +
"WHERE " +
" depart.id = usr.depart_id " +
" AND usr.id= operate.user_id ";
String sqlorder = " order by operate.create_time DESC ";
if (storeId != null && storeId > 0) {
sqlend += " AND operate.store_id=:storeId";
}
if (type != null && type > 0) {
sqlend += " AND operate.type=:type";
}
if (managerId != null && managerId > 0) {
sqlend += " AND operate.manager_id=:managerId";
}
if (beginTime != null) {
sqlend += " AND operate.create_time >= :beginTime";
}
if (endTime != null) {
sqlend += " AND operate.create_time <= :endTime";
}
if (departId != null && departId > 0) {
sqlend += " AND depart.id = :departId";
}
if (keyword != null && !keyword.isEmpty()) {
sqlend += " and (usr.real_name like '%:keyword1%' or usr.identity like '%:keyword2%') ";
}
Query query = em.createNativeQuery(sqlstart + sqlend + sqlorder, StatCardOpen.class);
Query queryCount = em.createNativeQuery(sqlstartCount + sqlend, StatCardOpenCount.class);
if (storeId != null && storeId > 0) {
query.setParameter("storeId", storeId);
queryCount.setParameter("storeId", storeId);
}
if (type != null && type > 0) {
query.setParameter("type", type);
queryCount.setParameter("type", type);
}
if (managerId != null && managerId > 0) {
query.setParameter("managerId", managerId);
queryCount.setParameter("managerId", managerId);
}
if (beginTime != null) {
query.setParameter("beginTime", beginTime);
queryCount.setParameter("beginTime", beginTime);
}
if (endTime != null) {
query.setParameter("endTime", endTime);
queryCount.setParameter("endTime", endTime);
}
if (departId != null && departId > 0) {
query.setParameter("departId", departId);
queryCount.setParameter("departId", departId);
}
if (keyword != null && !keyword.isEmpty()) {
query.setParameter("keyword1", keyword);
query.setParameter("keyword2", keyword);
queryCount.setParameter("keyword1", keyword);
queryCount.setParameter("keyword2", keyword);
}
query.setFirstResult(pageNum * pageSize);
query.setMaxResults(pageSize);
List<StatCardOpen> resultList = query.getResultList();
DebugPrint.json("resultList", resultList);
BigDecimal sumChangeBalance = BigDecimal.ZERO;
BigDecimal sumChangeDonation = BigDecimal.ZERO;
BigDecimal sumChangeDeposit = BigDecimal.ZERO;
List<StatCardOpenDTO> dtoList = new ArrayList<>();
for (StatCardOpen statCardOpen :
resultList) {
if (statCardOpen != null) {
DebugPrint.json("statCardOpen", statCardOpen);
StatCardOpenDTO cardOpenDTO = new StatCardOpenDTO();
BeanUtils.copyProperties(statCardOpen, cardOpenDTO);
dtoList.add(cardOpenDTO);
sumChangeBalance = sumChangeBalance.add(statCardOpen.getChangeBalance());
sumChangeDonation = sumChangeDonation.add(statCardOpen.getChangeDonation());
sumChangeDeposit = sumChangeDeposit.add(statCardOpen.getChangeDeposit());
}
}
resultMap.put("sumChangeBalance", sumChangeBalance);
resultMap.put("sumChangeDeposit", sumChangeDeposit);
resultMap.put("sumChangeDonation", sumChangeDonation);
resultMap.put("list", dtoList);
resultMap.put("page", pageNum);
resultMap.put("pageSize", pageSize);
resultMap.put("number", dtoList.size());
List<StatCardOpenCount> countList = queryCount.getResultList();
if (countList != null && countList.size() > 0) {
StatCardOpenCount statCardOpenCount = countList.get(0);
if (statCardOpenCount == null) {
resultMap.put("totle", 0);
resultMap.put("allChangeBalance", 0);
resultMap.put("allChangeDonation", 0);
resultMap.put("allChangeDeposit", 0);
} else {
resultMap.put("totle", statCardOpenCount.getTotal());
resultMap.put("allChangeBalance", statCardOpenCount.getAllChangeBalance());
resultMap.put("allChangeDonation", statCardOpenCount.getAllChangeDonation());
resultMap.put("allChangeDeposit", statCardOpenCount.getAllChangeDeposit());
}
}
DebugPrint.json("resultMap", resultMap);
return resultMap;
}
网友评论