Is the code below optimal for this task?

Question

MySQL

Good day everyone! Now I am redesigning the architecture of the application, or rather its category. Implemented Materialized Path+ Adjacency List. And when adding a new category, you need to add the full path to it. Is it possible to pull out the values ​​after the last dot of the required category group with a sql query? That is, there is such a value in the table: PATH: 1123.3131.33 I need to get 33 , that is, after the last comma. And this operation must be carried out for the entire subgroup, and find the largest one. In the code below, I pull out the group of categories that I need and process them. Is this code optimal? Please rate the sampling method.

public ResponseMessage addCategory(List<Category> newCategoryList, int parentId) {


        if (parentId == 0) {
            List<Category> categoryList = entityManager.createQuery("FROM Category c Where c.parentCategory.id=null", Category.class).getResultList();
            Integer largestIndex = getLargestIndex(categoryList);

            for (Category category : newCategoryList) {
                largestIndex++;
                String newPath = largestIndex.toString();
                category.setPath(newPath);
                entityManager.persist(category);

            }
            return new ResponseMessage(true, " ");

        } 

            Category parentCategory = entityManager.find(Category.class,  parentId);
            List<Category> categoryList = parentCategory.getCategoryList();
            categoryList.size();

            int largestIndex = getLargestIndex(categoryList);

            for (Category category : newCategoryList) {
                largestIndex++;
                StringBuilder newPath = new StringBuilder(parentCategory.getPath());
                newPath.append('.');
                newPath.append(largestIndex);
                parentCategory.addCategory(new Category(category.getName(), newPath));
            }
            return new ResponseMessage(true, " ");
   }

    public int getLargestIndex(List<Category> categoryList) {
        int largestIndex = 0;
        for (Category c : categoryList) {
            String[] sequence = c.getPath().split("\\.");
            int number = Integer.parseInt(sequence[sequence.length - 1]);
            if (number > largestIndex)
                largestIndex = number;
        }
        return largestIndex;
    }

ps And whether division by a condition (parentId==0) is optimum? Because I decided not to make a single ancestor, and thus choose those higher in the hierarchy.

Answer 1

Category parentCategory = entityManager.find(Category.class, 1);

Here, instead of 1, you probably need to put parentId.
And the answer to your question: instead of manually incrementing largestIndex , you need to use the id of the last added entry.
By the way, since you are using hibernate, then look towards Closure Table, this pattern seems to me to look more organically with hibernate.
Here is an example of code with id (be sure to wrap it in a transaction):

public ResponseMessage addCategory(List<Category> newCategoryList, int parentId) {
    Category parentCategory = parentId == 0 ? null : entityManager.find(Category.class, parentId);

    for (Category category : newCategoryList) {
        entityManager.persist(category);
        entityManager.flush();
        if (parentCategory != null) {
            category.setPath(parentCategory.getPath() + "." + category.getId());
            category.setParent(parentCategory);
        } else {
            category.setPath(String.valueOf(category.getId()));
        }
    }
    return new ResponseMessage(true, " ");
}

Answer 2

By the way, you have } else {
superfluous in the first function, the condition is enough. if( ... ) {
return
}
... other code..
select * from category where mpath like '%.33'