sql语句写法,jpa,em

最新推荐文章于 2024-10-15 09:57:23 发布

qq_27988103

最新推荐文章于 2024-10-15 09:57:23 发布

阅读量964

点赞数

本文详细介绍了如何使用复杂的SQL查询结合动态条件过滤，实现高效的数据检索与分页展示，特别聚焦于处理大规模代理结算数据的场景。通过具体代码示例，展示了如何构建SQL查询语句，包括联表查询、条件拼接、聚合函数使用，以及如何利用自定义工具类进行分页查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

@Override
public Page<AgentCountInfo> findAgent(AgentQuery query, Pageable page) {
  // TODO Auto-generated method stub
  StringBuffer whereCause = new StringBuffer();
  Map<String,Object> params = new HashMap<>();
  if(query.hasBill()) {
   whereCause.append(" AND S.BILL_NO=:bill");
   params.put("bill", query.getBill());
  }
  if(query.hasMobile()) {
   whereCause.append(" AND A.MOBILE=:mobile");
   params.put("mobile", query.getMobile());
  }
  if(query.hasName()) {
   whereCause.append(" AND A.`NAME` LIKE '%' :name '%'");
   params.put("name", query.getName());
  }
  if(query.hasOrg()) {
   whereCause.append(" AND A.ORG_NAME=:org");
   params.put("org", query.getOrg());
  }
  String sql = "SELECT" +
    " A.*," +
    " COUNT(S.BILL_NO) TOTAL," +
    " SUM(B.PRIZE) AMOUNT," +
    " (SELECT IFNULL(SUM(AMOUNT),0) FROM T_ITF_AGENT_SETTLEMENT WHERE CLEAN_FLAG=0 AND ID_CARD=A.ID_CARD) CLEAN," +
    " (SELECT IFNULL(SUM(AMOUNT),0) FROM T_ITF_AGENT_SETTLEMENT WHERE CLEAN_FLAG=1 AND ID_CARD=A.ID_CARD) UNCLEAN," +
    " SUM(S.AMOUNT) POUNDAGE," +
    " SUM(C.CLEAN_AMOUNT) COMMISSION," +
    " U.ACTIVE_STATUS STATUS" +
    " FROM" +
    " T_ITF_AGENT_INFO A," +
    " T_ITF_AGENT_SETTLEMENT S," +
    " T_ITF_AGENT_COMMISSION C," +
    " T_ITF_BILL_INFO B," +
    " T_USR_AGENT U" +
    " WHERE" +
    " A.ID_CARD = S.ID_CARD" +
    " AND A.ID_CARD = C.ID_CARD" +
    " AND S.BILL_NO = B.BILL_NO" +
    " AND A.ID_CARD = U.ID_CARD" + whereCause.toString() +
    " GROUP BY" +
    " A.ID_CARD";

  return util.queryPage(sql, params, AgentCountInfo.class, page);
}

@Repository
public class DaoUtil {
@PersistenceContext
EntityManager em;

    public int getQueryCount(String sql,Map<String,Object> param){
        sql = "select count(*) as size from ("+sql+") as countSize";
        Query nativeQuery = em.createNativeQuery(sql);
        for(String key:param.keySet()){
            nativeQuery.setParameter(key,param.get(key));
        }
       return Integer.parseInt(nativeQuery.getSingleResult().toString()) ;
    }

    public <T> List<T> queryList(String sql, Map<String,Object> param , Class<? extends T> c){
        Query nativeQuery = em.createNativeQuery(sql.toString(),c);
        for(String key:param.keySet()){
            nativeQuery.setParameter(key,param.get(key));
        }
        return nativeQuery.getResultList();
    }

    public <T> Page<T> queryPage(String sql, Map<String,Object> param , Class<? extends T> c, Pageable page){
        Query nativeQuery = em.createNativeQuery(sql.toString(),c);
        for(String key:param.keySet()){
            nativeQuery.setParameter(key,param.get(key));
        }
        int count = getQueryCount(sql.toString(), param);
        int startNum =page.getPageNumber()* page.getPageSize(); //分页从零开始
        nativeQuery.setFirstResult(startNum);
        nativeQuery.setMaxResults( page.getPageSize());
        List<T> resultList = nativeQuery.getResultList();
        return new PageImpl<T>(resultList,page,count);
    }
}