在postgresql中用查询替换java循环

我使用了postgresql 10和spring boot

我尝试在java中使用循环加载树,并取得了成功。

在循环中,每次查询时我都会调用

但是它消耗了应用服务器和数据库服务器中的cpu,并且加载包含5000个单元的树需要时间。

我想在java中只使用一个查询来加载树,而不使用循环。 java的结果是 ResponseEntity<List<UnitDTO>>

这是我的代码:

  @GetMapping("/unitsBook")
    @Timed
    public ResponseEntity<List<UnitDTO>> getAllUnitsBook(Pageable pageable, @RequestParam(name="lang", required=false) String lang,
    @RequestParam(name="emp", required=false) String empID) {
        log.debug("REST request to get a page of Units");
        Page<UnitDTO> page = unitService.findAllUnitBook(pageable, lang,empID);
        HttpHeaders headers = PaginationUtil.generatePaginationHttpHeaders(page, "/api/unitsBook");
        return ResponseEntity.ok().headers(headers).body(page.getContent());
    }

包含循环的java代码是:

public List<UnitDTO> getUnitBookList(Pageable pageable, String lang,String empID) {

        List<UnitDTO> list=unitRepository.findUnitList(pageable, lang,empID);
        List<UnitDTO> unitChildList=getChildrenUnitList(list,lang,pageable,empID);

        return unitChildList;
 }

private List<UnitDTO> getChildrenUnitList(
        List<UnitDTO> unitList, String lang,Pageable pageable,String empID) {

     for(UnitDTO UnitDTO : unitList) {

         List<UnitDTO> childrenListEntity = unitRepository.findUnitByParentId(pageable, lang,UnitDTO.getValue(),empID);
         UnitDTO.setChildren(getChildrenUnitList(childrenListEntity,lang,pageable,empID));

     }

        return unitList;
    }

调用查询的代码为:

  public List<UnitDTO> findUnitList(Pageable pageable, String lang,String empID) {
      
    String querystr = "SELECT ";
    querystr += " unl.name AS text ,";
    querystr += " un.id AS value ,";
    querystr += " ,cast( 0 as varchar(10) ) as favoriteNbr,cast( null as varchar(10) ) as favoriteId ";
    querystr += " FROM public.unit un ";
    querystr += " LEFT OUTER JOIN public.unitlang unl ON unl.unit_id = un.id ";
    querystr += " Where unl.lang = :lang  parentid is null  order by app_order asc";

    log.debug("-- Query:" + querystr);

    Query query = em.createNativeQuery(querystr, "UnitDTOMap");
    query.setParameter("lang", lang);

    List<UnitDTO> unitDTOs = query.getResultList();

    if (pageable.isUnpaged()) {
      return unitDTOs;
    }

    return unitDTOs;
  }





  @Override
  public List<UnitDTO> findUnitByParentId(Pageable pageable, String lang, String idParent,String empID) {
    log.debug("-- pageable:" + pageable.getPageNumber() + ", Size:" + pageable.getPageSize() + ", isUnpaged:" + pageable.isUnpaged() + ", lang:" + lang);
    lang = lang.toUpperCase();
    String querystr = "SELECT ";

    querystr += " unl.name AS text ,";
    querystr += " un.id AS value ,"; 
    querystr += " (case when cast((select count(*) from employee where employee.unit_id = un.id) as varchar(10)) != '0'  then cast(1 as Boolean)  else cast(0 as BOOLEAN) end) as disabled";
    querystr += " ,cast( 0 as varchar(10) ) as favoriteNbr,cast( null as varchar(10) ) as favoriteId ";
    querystr += " FROM unit un ";
    querystr += " LEFT OUTER JOIN unitlang unl ON unl.unit_id = un.id ";
    querystr += " Where unl.lang = :lang  and un.parentid = :idParent order by app_order asc ";

    log.debug("-- Query:" + querystr);

    Query query = em.createNativeQuery(querystr, "UnitBookDTOMap");
    query.setParameter("lang", lang);
    query.setParameter("idParent", idParent);
    List<UnitDTO> unitDTOs = query.getResultList();

    log.debug("-- unitDTOs Size:" + unitDTOs.size());

    if (pageable.isUnpaged()) {
      return unitDTOs;
    }

    return unitDTOs;
  }

更新:

我尝试使用递归查询,但问题是树显示不正确。 所有单元都在同一层。 我认为这行WHERE id=:id中的问题家长我评论它是因为我找不到如何发送它

  public List<UnitDTO> getUnitBookList(Pageable pageable, String lang) {
    
                List<UnitDTO> list=unitRepository.findUnitList(pageable, lang);
              //  List<UnitBookDTO> unitChildList=getChildrenUnitList(list,lang,pageable);
    
                return list;
         }
         
    
     @Override
      public List<UnitDTO> findUnitList(Pageable pageable, String lang) {
        log.debug("-- pageable:" + pageable.getPageNumber() + ", Size:" + pageable.getPageSize() + ", isUnpaged:" + pageable.isUnpaged() + ", lang:" + lang);
        lang = lang.toUpperCase();
        String querystr = "WITH RECURSIVE un_id AS ( ";
    
        querystr += " SELECT id";
        querystr += "  FROM unit ";
  //  querystr += " WHERE id = :idParent";
    querystr += " UNION";
        querystr += " SELECT unit.id";
   
        querystr += " FROM unit JOIN un_id ON unit.parentid = un_id.id )  ";
        querystr += " SELECT unl.name                          AS text, un.id                             AS value, (case   when cast((select count(*) from employee where employee.unit_id = un.id) as varchar(10)) != '0' ";
        querystr += " then cast(1 as Boolean)   else cast(0 as BOOLEAN) end) as disabled , cast(0 as varchar(10))            as favoriteNbr,";
        querystr += " cast(null as varchar(10))         as favoriteId FROM un_id JOIN unit un USING (id) LEFT OUTER JOIN unitlang unl ON unl.unit_id = un.id Where unl.lang = :lang order by app_order asc";
    
        log.debug("-- Query:" + querystr);
    
        Query query = em.createNativeQuery(querystr, "UnitDTOMap");
        query.setParameter("lang", lang);
    
        List<UnitDTO> unitDTOs = query.getResultList();
    
        log.debug("-- unitDTOs Size:" + unitDTOs.size());
    
        if (pageable.isUnpaged()) {
          return unitDTOs;
        }
    
        return unitDTOs;
      }