efunbox_audio/src/main/java/cn/efunbox/audio/repository/AlbumRepo.java

package cn.efunbox.audio.repository;

import cn.efunbox.audio.entity.Album;
import cn.efunbox.audio.repository.base.ProjectJpaRepository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.rmi.CORBA.PortableRemoteObjectDelegate;
import java.math.BigInteger;
import java.util.List;

/**
 * Created by yao on 17-9-26.
 */
@Repository
public interface AlbumRepo extends ProjectJpaRepository<Album, BigInteger> {

    @Query(value = "select a.id From album a where a.name like concat('%',?1,'%')", nativeQuery = true)
    List<BigInteger> findIdsByNameLike(String name);

    @Query(value = "select * From album a where a.name like concat('%',?1,'%')", nativeQuery = true)
    List<Album> findByNameLike(String name);


    List<Album> findByIdIn(List<BigInteger> albumIds);

    @Query(value = "" +
            "select DISTINCT al.* FROM  device d\n" +
            "JOIN channel c ON d.id_channel = c.id\n" +
            "JOIN rights r ON c.id = r.id_channel\n" +
            "JOIN audio au ON au.id_group = r.id_group\n" +
            "JOIN album al ON al.id = au.album_id where d.id_device = :idDevice order by al.id", nativeQuery = true)
    List<Album> findAlbum(@Param("idDevice") String idDevice);
}