Spring Data Repository Example

import java.util.Map;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import ru.vtb.poc.service.pd.domain.PaymentDocumentEntity;

@Repository
public interface PaymentDocumentRepository extends PagingAndSortingRepository<PaymentDocumentEntity, Long>
{

    @Query("select count(*) from PaymentDocumentEntity e join e.sourceDocument s where s.documentId = :sourceDocumentId")
    Long countTotalDocument(@Param("sourceDocumentId") String sourceDocumentId);
    
    @Query("select count(*) from PaymentDocumentEntity e join e.sourceDocument s where s.documentId = :sourceDocumentId and e.status = 2")
    Long countSignedDocument(@Param("sourceDocumentId") String sourceDocumentId);

    @Query("select count(*) from PaymentDocumentEntity e join e.sourceDocument s where s.documentId = :sourceDocumentId and e.selected = true and e.status = 0")
    Long countInvalidSignDocument(@Param("sourceDocumentId") String sourceDocumentId);

    @Query("select e from PaymentDocumentEntity e join e.sourceDocument s where s.documentId = :sourceDocumentId and e.selected = true and e.status = 1")
    Page<PaymentDocumentEntity> findSignDocumentList(@Param("sourceDocumentId") String sourceDocumentId,
                                                     Pageable pageable);

    @Query("select e from PaymentDocumentEntity e join e.sourceDocument s where s.documentId = :sourceDocumentId")
    Page<PaymentDocumentEntity> findBySourceDocumentId(@Param("sourceDocumentId") String sourceDocumentId,
                                                       Pageable pageable);

    @Query("select count(e) as count, sum(e.amount) as amount from PaymentDocumentEntity e join e.sourceDocument s where s.documentId = :sourceDocumentId and e.selected = true")
    Map<String, Object> calculateSelectedDocumentsAmmount(@Param("sourceDocumentId") String sourceDocumentId);

    @Query("select count(*) from PaymentDocumentEntity e1 join e1.sourceDocument s1 where e1.entityId <= ("
            + "select min(e.entityId) "
            + "from PaymentDocumentEntity e "
            + "join e.sourceDocument s "
            + "join e.sourceContentList c "
            + "where false in (c.valid) "
            + "and s.documentId = :sourceDocumentId "
            + "and e.entityId > :from) "
            + "and s1.documentId = :sourceDocumentId ")
    Long findNextInvalidDocuement(@Param("sourceDocumentId") String sourceDocumentId,
                                  @Param("from") Long form);

    @Query("select count(*) from PaymentDocumentEntity e1 join e1.sourceDocument s1 where e1.entityId <= ("
            + "select max(e.entityId) "
            + "from PaymentDocumentEntity e "
            + "join e.sourceDocument s "
            + "join e.sourceContentList c "
            + "where false in (c.valid) "
            + "and s.documentId = :sourceDocumentId "
            + "and e.entityId < :from)"
            + "and s1.documentId = :sourceDocumentId ")
    Long findPrevInvalidDocuement(@Param("sourceDocumentId") String sourceDocumentId,
                                  @Param("from") Long form);

}