closure table save 전략

categoryClosure.kt

    @Transactional
    fun saveCategory(dto:CategorySaveReq, idAncestor: Long?): Category? {
        val category = categoryRepository.saveCategory(dto.toEntity())

        categoryRepository.saveCategoryClosure(category?.id!!, idAncestor )

        return category
    }
    
    override fun saveCategoryClosure(idDescendant: Long, idAncestor: Long?) {
        // JPQL 로는 insert into Select 구문을 지원하지 않는 것 같은데..
        val insertSql = """                      
            INSERT INTO category_closure
            (`id_ancestor`, `id_descendant`, `depth`, `updated_at`, `created_at`)
            VALUES 
            ($idDescendant, $idDescendant, 0, now(), now())
         
            """.trimIndent()
        em.createNativeQuery(insertSql).executeUpdate()


        if (idAncestor != null) {
            em.createNativeQuery(
                """
					INSERT INTO category_closure
                    (`id_ancestor`, `id_descendant`, `depth`, `updated_at`, `created_at`)
                    SELECT
                    p.id_ancestor,
                    c.id_descendant,
                    p.depth + c.depth + 1,
                    c.updated_at,
                    c.created_at
                    FROM category_closure as p, category_closure as c
                    WHERE p.id_descendant = $idAncestor and c.id_ancestor = $idDescendant
                """.trimIndent()
            ).executeUpdate()
        }

    }