replace java loop with query in postgresql

Question

I used postgresql 10 and spring boot

I try with success to load tree using loop in java.

in the loop I call each time the query

but it consumes cpu in application server and cpu in database server and takes time to load the tree which contain 5000 unit.

I want using only one query to load the tree without loop in java. the result from java is ResponseEntity<List<UnitDTO>>

this is my code :

  @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());
    }

the java code which contains the loop is :

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;
    }

and the code which call query is :

  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;
  }

Updated :

I try to use the recursive query but the problem is that the tree does not display correctly. all the unit are in same level. I think the problem in this line WHERE id = :idParent I comment it because I didn't find how can I send it

  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;
      }
      

Answer 1

The usual way to fetch a tree in SQL is to use a recursive Common Table Expression (CTE):

WITH RECURSIVE un_id AS (
    SELECT id
    FROM unit
    WHERE id = :idParent -- or parentid if you want to exclude the parent itself

    UNION

    SELECT unit.id
    FROM unit
    JOIN un_id
        ON unit.parentid = un_id.id
)
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'
                then cast(1 as Boolean)
            else cast(0 as BOOLEAN) end) as disabled
        ,
       cast(0 as varchar(10))            as favoriteNbr,
       cast(null as varchar(10))         as favoriteId
FROM un_id -- reference to the CTE
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

Recursively fetch all the IDs and then join the rest of the needed data.