art-examination/src/main/java/com/izouma/awesomeAdmin/repo/MenuRepo.java

package com.izouma.awesomeAdmin.repo;

import com.izouma.awesomeAdmin.domain.Menu;
import com.izouma.awesomeAdmin.dto.MenuDTO;
import org.hibernate.annotations.Where;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.List;

public interface MenuRepo extends JpaRepository<Menu, Long>, JpaSpecificationExecutor<Menu> {
    @Query(nativeQuery = true, value = "SELECT ifnull(max(sort + 1),1) FROM menu")
    int nextSort();

    @Query("select m.category from Menu m group by m.category")
    List<String> categories();

    @Transactional
    @Modifying
    @Query(value = "delete from menu_authority where authority = ?1", nativeQuery = true)
    int clearAuthority(String name);

    @Transactional
    @Modifying
    @Query(value = "insert into menu_authority value (?2, ?1)", nativeQuery = true)
    int saveAuthority(String name, Long id);

    @Query(value = "select * " +
            "from menu " +
            "    join menu_authority on menu.id = menu_authority.menu_id " +
            "where authority in ?1 and active = 1 group by id", nativeQuery = true)
    List<MenuDTO> authorityMenus(Iterable<String> authority);
}