sql-server - 如何在 Spring Data JPA 中从 SQL Server 队列中获取消息

Question

我正在使用包含队列即 queueXXX 的 SQL Server 数据库。

我想从 Spring data JPA 运行以下代码并从队列中返回消息列表。

 declare @result table(
    [message_body] varbinary(MAX)
    , [message_type_name] nvarchar(256)
    , [validation] nchar(2)
    , [message_enqueue_time] datetime
    , [conversation_handle] uniqueidentifier
);

waitfor (
    receive top (1) [message_body], [message_type_name], [validation], [message_enqueue_time], [conversation_handle]
    from queueXXX
    into @result
), timeout 10000;

declare @c uniqueidentifier = (select top 1 [conversation_handle] from @result);

-- End the conversation so sending a message back to the sender who will close 
-- their side of the conversation.
--
if @c is not null
    end conversation @c;

-- Only return the first row as we will not have any other messages
select top 1 [message_body], LTRIM(RTRIM([message_type_name])) as [message_type_name], LTRIM(RTRIM([validation])) as [validation], [message_enqueue_time], [conversation_handle] from @result;

我尝试了以下方法来完成这项工作。但它不起作用。

@Repository
public class QueueReaderDao {
    @PersistenceContext 
    EntityManager entityManager;

public void readQueue(){
    
    Query q =entityManager.createNamedQuery("declare @result table( " + 
        "   [message_body] varbinary(MAX) " + 
        "   , [message_type_name] nvarchar(256) " + 
        "   , [validation] nchar(2) " + 
        "   , [message_enqueue_time] datetime " + 
        "   , [conversation_handle] uniqueidentifier " + 
        "); " + 
        " " + 
        "waitfor ( " + 
        "   receive top (1) [message_body], [message_type_name], [validation], [message_enqueue_time], [conversation_handle] " + 
        "   from queueXXX " + 
        "   into @result " + 
        "), timeout 10000; " + 
        " " + 
        "declare @c uniqueidentifier = (select top 1 [conversation_handle] from @result); " + 
        " " + 
        "-- End the conversation so sending a message back to the sender who will close  " + 
        "-- their side of the conversation. " + 
        "-- " + 
        "if @c is not null " + 
        "   end conversation @c; " + 
        " " + 
        "-- Only return the first row as we will not have any other messages " + 
        "select top 1 [message_body], LTRIM(RTRIM([message_type_name])) as [message_type_name], LTRIM(RTRIM([validation])) as [validation], [message_enqueue_time], [conversation_handle] from @result;");
    
    q.getResultList();
    
    System.out.println("query works");
}

任何形式的帮助都将受到高度赞赏。

标签： sql-serverspring-boothibernatespring-data-jpa