replace java loop with query in postgresql

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

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.