ps-message/src/main/java/com/uas/ps/message/dao/MessageDao.java

package com.uas.ps.message.dao;

import com.uas.ps.message.domain.Message;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

/**
 * Created by wangyc on 2018/1/13.
 *
 * @version 2018/1/13 15:45 wangyc
 */
@Repository
public interface MessageDao extends JpaRepository<Message, Long>, JpaSpecificationExecutor<Message> {

    /**
     * 通过接收人和接收应用获取消息
     * @param receiverUu 接收人uu
     * @param receiverEnuu 接收企业uu
     * @param consumerAppId 接收应用id
     * @return
     */
    @Query(nativeQuery = true, value = "select * from messages e where e.ms_receiveruu = :receiverUu and e.ms_receiverenuu = :receiverEnuu and e.ms_id in (select ms_id from message$consumeapp a where a.app_id = :consumerAppId)")
    List<Message> findByReceiverUuAndReceiverEnuuAndConsumerAppId(@Param("receiverUu") Long receiverUu, @Param("receiverEnuu") Long receiverEnuu, @Param("consumerAppId")
        Long consumerAppId);

    /**
     * 通过接收人和接收应用获取消息
     * @param receiverUu 接收人uu
     * @param receiverEnuu 接收企业uu
     * @param isRead 阅读状态
     * @param consumerAppId 接收应用id
     * @return
     */
    @Query(nativeQuery = true, value = "select * from messages e where e.ms_receiveruu = :receiverUu and e.ms_receiverenuu = :receiverEnuu and e.ms_isread = :isRead and e.ms_id in (select ms_id from message$consumeapp a where a.app_id = :consumerAppId)")
    List<Message> findByReceiverUuAndReceiverEnuuAndIsReadAndConsumerAppId(@Param("receiverUu") Long receiverUu, @Param("receiverEnuu") Long receiverEnuu, @Param("isRead") Short isRead, @Param("consumerAppId")
        Long consumerAppId);

    /**
     * 通过接收应用id、阅读状态、发送状获取需要推送消息
     * @param consumerAppId 接收应用id
     * @param isRead 阅读状态
     * @param isSent 发送状态
     * @return
     */
    @Query(nativeQuery = true, value = "select * from messages e where e.ms_smsType <> 'DONT_SEND' and e.ms_isread = :isRead and e.ms_issent = :isSent and e.ms_id in (select ms_id from message$consumeapp a where a.app_id = :consumerAppId) limit 0,500")
    List<Message> findByConsumerAppIdAndIsReadAndIsSentNeedToSend(@Param("consumerAppId") Long consumerAppId, @Param("isRead") Short isRead, @Param("isSent") Short isSent);
}