如何将数据从数据库推送到应用程序？ [英] How to push the data from database to application?

问题描述

我想将数据从数据库推送到应用程序，而不是应用程序拉数据。我已经安装了ms sql server和apache tomcat服务器。我有我的应用程序在apache tomcat，这里我连接到数据库。现在我想要数据库发送数据每当有更新数据。但我所知道的是从数据库获取数据是不是好主意，因为应用程序需要监视数据库的更新数据将导致火的查询每5秒，这是不高效的。

I want to push the data from database to application instead of application pull the data. I have installed ms sql server and apache tomcat server. I have my application in apache tomcat, here I made connection to database. Now I want database send the data whenever there is update in data. But all I know is fetch the data from database is not good idea, because application needs to monitor the database for updated data will lead to fire the query for every 5 sec, this is not efficient as well.

我google了我得到一些答案，他们是查询通知这里，Sql server Agent Job自动调度任务。

I google it I got some answers they are Query Notification here, Sql server Agent Job to schedule the task automatically. If you have any other suggestion please post it.

推荐答案

确实有几种可能性：

• 实施不安全的CLR触发器


• 实施不安全的CLR过程


• 使用xp_cmdshell
• 
  
• 拨打网络服务


• 使用查询通知

• Implement unsafe CLR trigger
• Implement unsafe CLR procedure
• Use xp_cmdshell
• Call web service
• Use Query Notification

可以在这里讨论一下：
序列号，在SQL Server中创建和修改。

You can read a little about them in this discussion: Serial numbers, created and modified in SQL Server.

我个人比其他方法更喜欢Query Notification，因为它已经支持fopr各种情况（例如同步/异步通信），而不必重新发明轮。在您的情况下由Microsoft推荐。

Personally I would prefer Query Notification over other methods, because it already has support fopr various cases (e.g. sync/async communication) and you don't have to reinvent the wheel. And is in your case recommended by Microsoft.

轮询是您提到的另一种方法。这是一个更像传统的方法，可以有一些性能惩罚相关，但你不应该担心他们，如果你足够小心。例如，如果您已经在应用程序中构建了身份验证，则可以在您的 Users 表中创建另一个列，如果有与该用户相关的任何更改，则会设置该列。然后，你的应用程序中可以只有一个线程，每秒对这个表执行一次查询（甚至脏读与NOLOCK不应该是一个问题）和维护一些内存结构（例如线程安全字典）这说明哪个客户端应该推送。另一个线程轮询您的字典，当它发现有一些为客户端，执行一个数据库查询，提取数据并将其发送到客户端。这看起来像很多不必要的工作，但最终你得到两个独立的工人，有点帮助分离关注;第一个只是一个informer执行'轻量级'数据库轮询;第二个提取实际数据并执行服务器推送。你甚至可以优化push-worker的方式，当它运行时，它检查多个客户端是否需要一些数据，然后为所有需要它的人执行选择。

Polling is another method you've mentioned. It's is a more like traditional method and there can be some performance penalties related, but you shouldn't worry about them if you are careful enough. For example, if you already have an authentication built in your application, you can create another column in your Users table that is set if there are any changes related to that user. And then, there can be just a thread in your app that will perform a query every second against this table (even dirty reads with NOLOCK shouldn't be a problem here) and maintain some in-memory structure (e.g. thread-safe dictionary) that says which client should get pushed. Another thread polls your dictionary and when it finds there something for the client, performs a db query that extracts data and sends it to the client. This looks like a lot of unnccessary work, but at the end you get two independent workers which somewhat helps to separate concerns; first one is just an informer which performs 'lightweight' database polling; second one extract real data and performs server push. You can even optimize the push-worker in the way that when it runs, it checks if multiple clients need some data and then executes the select for all of those who need it. You would probably want the second worker to run less frequently than first one.

EDIT

如果您希望使用非.NET技术来实现相同的功能，您必须更多地进入SQL Server服务代理。查询通知是在SQL Server Service Broker之上的.NET中构建的简化层，您必须自己构建该层的至少一部分。这包括在另一侧使用SEND和RECEIVE创建队列，消息类型，服务和存储过程。你必须自己照顾对话/对话。 SB实际上是一个调整为在RDBMS环境中工作的异步消息传递世界，因此您将看到一些新的TSQL表达式。但是，MSDN可以帮助：

If you wish to use non-.NET technology to achieve the same functionality, you will have to get more into SQL Server Service Broker. Query Notification is a simplified layer built in .NET on top of SQL Server Service Broker, and you would have to build at least part of that layer by yourself. This includes creating queue, message type, service and stored procedures with SEND and RECEIVE on the other side. You will have to take care of the conversation/dialog by yourself. SB is actually a async-messaging world adjusted to work in RDBMS environment, so you will see some new TSQL expressions. However, MSDN is here to help:

• http://msdn.microsoft.com/en-us/library/ms166061（v = sql.105）.aspx


• http://msdn.microsoft.com /en-us/library/bb522893.aspx

• http://msdn.microsoft.com/en-us/library/ms166061(v=sql.105).aspx
• http://msdn.microsoft.com/en-us/library/bb522893.aspx

这也有助于：从Service Broker外部激活非.NET应用程序

如何对代码进行编码的示例：

Example on how to code the stuff:

-- First you have to enable SB for your database
USE master
ALTER DATABASE Playground
SET ENABLE_BROKER
GO

USE Playground
GO

-- Then create a message type; usually it will be XML
-- because it's very easy to serialize/deserialize it
CREATE MESSAGE TYPE [//Playground/YourMessageType]
VALIDATION = WELL_FORMED_XML
GO

-- Then create a contract to have a rule for communication
-- Specifies who sends which message type
CREATE CONTRACT [//Playground/YourContract] (
    [//Playground/YourMessageType] SENT BY ANY)
GO

--Creates queues, one for initiator (1) and one for target (2)
CREATE QUEUE MyQueue1
GO
CREATE QUEUE MyQueue2
GO

-- Finally, configure services that 'consume' queues
CREATE SERVICE [//Playground/YourService1]
ON QUEUE MyQueue1 ([//Playground/YourContract])
GO

CREATE SERVICE [//Playground/YourService2] 
ON QUEUE MyQueue2 ([//Playground/YourContract])
GO

-- Now you can send a message from service to service using contract
DECLARE 
    @dHandle uniqueidentifier,
    @Msg nvarchar(max) 

BEGIN DIALOG @dHandle
    FROM SERVICE [//Playground/YourService1]
    TO SERVICE '//Playground/YourService2'
    ON CONTRACT [//Playground/YourContract]
WITH ENCRYPTION = OFF

SELECT @Msg = (
    SELECT TOP 3 *
    FROM Table1
    FOR XML PATH('row'), ROOT('Table1'))

;SEND ON CONVERSATION @dHandle 
MESSAGE TYPE [//Playground/YourMessageType] (@Msg)

PRINT @Msg
GO

-- To get the message on the other end, use RECEIVE
-- Execute this in another query window
DECLARE @dHandle uniqueidentifier
DECLARE @MsgType nvarchar(128)
DECLARE @Msg nvarchar(max)

;RECEIVE TOP(1)
    @dHandle = conversation_handle,
    @Msg = message_body,
    @MsgType = message_type_name
FROM MyQueue2

SELECT @MsgType 
SELECT @Msg

END CONVERSATION @dHandle 
GO

这篇关于如何将数据从数据库推送到应用程序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！