有没有办法“监听"数据库事件并实时更新页面? [英] Is there a way to 'listen' for a database event and update a page in real time?

问题描述

我正在寻找一种方法来创建一个简单的 HTML 表格，该表格可以在发生数据库更改事件时实时更新；特别是添加了一条新记录.

I'm looking for a way to create a simple HTML table that can be updated in real-time upon a database change event; specifically a new record added.

换句话说，把它想象成一个执行仪表板.如果进行了销售并且在数据库中添加了新行(在我的例子中是 MySQL)，那么网页应该用新行刷新"表格.

In other words, think of it like an executive dashboard. If a sale is made and a new line is added in a database (MySQL in my case) then the web page should "refresh" the table with the new line.

我已经看到了一些关于新使用 EVENT GATEWAY 的信息，但所有示例都使用 Coldfusion 作为推动者"而不是消费者".我想让 Coldfusion 将事件更新/推送到网关并使用响应.

I have seen some information on the new using EVENT GATEWAY but all of the examples use Coldfusion as the "pusher" and not the "consumer". I would like to have Coldfusion both update / push an event to the gateway and also consume the response.

如果这可以使用 AJAX 和 CF 的组合来完成，请告诉我！

If this can be done using a combination of AJAX and CF please let me know!

我真的只是想了解从哪里开始实时更新.

I'm really just looking to understand where to get started with real-time updating.

提前谢谢你！！

编辑/所选答案的解释:

我最终选择了@bpeterson76 的答案，因为目前它最容易小规模实施.我真的很喜欢他的 Datatables 建议，这就是我用来接近实时更新的内容.

I ended up going with @bpeterson76's answer because at the moment it was easiest to implement on a small scale. I really like his Datatables suggestion, and that's what I am using to update in close to real time.

随着我的网站变大(希望如此)，我不确定这是否是一个可扩展的解决方案，因为每个用户都会点击侦听器"页面，然后查询我的数据库.我的查询比较简单，但我还是担心以后的性能.

As my site gets larger though (hopefully), I'm not sure if this will be a scalable solution as every user will be hitting a "listener" page and then subsequently querying my DB. My query is relatively simple, but I'm still worried about performance in the future.

但在我看来，随着 HTML5 开始成为 Web 标准，@iKnowKungFoo 建议的 Web Sockets 方法很可能是最好的方法.Comet 长轮询也是一个好主意，但实现起来有点麻烦/似乎还有一些扩展问题.

In my opinion though, as HTML5 starts to become the web standard, the Web Sockets method suggested by @iKnowKungFoo is most likely the best approach. Comet with long polling is also a great idea, but it's a little cumbersome to implement / also seems to have some scaling issues.

所以，让我们希望网络用户开始采用支持 HTML5 的更现代的浏览器，因为 Web Sockets 是一种接近实时的相对简单且可扩展的方式.

So, let's hope web users start to adopt more modern browsers that support HTML5, because Web Sockets is a relatively easy and scalable way to get close to real time.

如果您觉得我做出了错误的决定，请发表评论.

If you feel that I made the wrong decision please leave a comment.

最后，这里有一些源代码:

Finally, here is some source code for it all:

Javascript:

注意，这是一个非常简单的实现.它只是查看当前数据表中的记录数是否已更改，如果更改则更新表并发出警报.生产代码更长，涉及更多.这只是展示了一种接近实时更新的简单方法.

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.1/jquery.js"></script>
<script type="text/javascript" charset="utf-8">

var originalNumberOfRecsInDatatable = 0;
var oTable;

var setChecker = setInterval(checkIfNewRecordHasBeenAdded,5000); //5 second intervals

function checkIfNewRecordHasBeenAdded() {

        //json object to post to CFM page
        var postData = {
        numberOfRecords:  originalNumberOfRecsInDatatable 
        };

        var ajaxResponse = $.ajax({
        type: "post",
        url: "./tabs/checkIfNewItemIsAvailable.cfm",
        contentType: "application/json",
        data: JSON.stringify( postData )
        })

        // When the response comes back, if update is available
        //then re-draw the datatable and throw an alert to the user
        ajaxResponse.then(
        function( apiResponse ){

         var obj = jQuery.parseJSON(apiResponse);

         if (obj.isUpdateAvail == "Yes")
         {              
            oTable = $('#MY_DATATABLE_ID').dataTable();
            oTable.fnDraw(false);

            originalNumberOfRecsInDatatable = obj.recordcount;

            alert('A new line has been added!');
         }

        }
        );

    }
</script>

冷融合:

<cfset requestBody = toString( getHttpRequestData().content ) />

<!--- Double-check to make sure it's a JSON value. --->
<cfif isJSON( requestBody )>

<cfset deserializedResult = deserializeJSON( requestBody )>

<cfset numberOFRecords = #deserializedResult.originalNumberOfRecsInDatatable#>


<cfquery  name="qCount" datasource="#Application.DBdsn#" username="#Application.DBusername#" password="#Application.DBpw#">
    SELECT COUNT(ID) as total
    FROM myTable
</cfquery>

<cfif #qCount.total# neq #variables.originalNumberOfRecsInDatatable#>
    {"isUpdateAvail": "Yes", "recordcount": <cfoutput>#qCount.total#</cfoutput>}
<cfelse>
    {"isUpdateAvail": "No"}
</cfif>


</cfif>

推荐答案

这并不难.简单的方法是通过 .append 添加:

This isn't too difficult. The simple way would be to add via .append:

$( '#table > tbody:last').append('<tr id="id"><td>stuff</td></tr>');

实时添加元素是不可能的.您必须运行一个循环更新的 Ajax 查询以捕获"更改.所以，不是完全实时的，但非常非常接近它.尽管您的服务器的负载可能会，但您的用户确实不会注意到差异.

Adding elements real-time isn't entirely possible. You'd have to run an Ajax query that updates in a loop to "catch" the change. So, not totally real-time, but very, very close to it. Your user really wouldn't notice the difference, though your server's load might.

但如果您要参与更多，我建议您查看 DataTables.它为您提供了许多新功能，包括排序、分页、过滤、限制、搜索和 ajax 加载.从那里，您可以通过 ajax 添加元素并刷新表格视图，或者只是通过其 API 追加.我已经在我的应用程序中使用 DataTables 有一段时间了，它们一直被认为是使大量数据可用的第一大功能.

But if you're going to get more involved, I'd suggest looking at DataTables. It gives you quite a few new features, including sorting, paging, filtering, limiting, searching, and ajax loading. From there, you could either add an element via ajax and refresh the table view, or simply append on via its API. I've been using DataTables in my app for some time now and they've been consistently cited as the number 1 feature that makes the immense amount of data usable.

--编辑--

因为不明显，要更新您调用的 DataTable，请将您的 Datatables 调用设置为一个变量:

Because it isn't obvious, to update the DataTable you call set your Datatables call to a variable:

var oTable = $('#selector').dataTable();

然后运行这个来进行更新:

Then run this to do the update:

  oTable.fnDraw(false);

更新 -- 5 年后，2016 年 2 月:这在今天比 2011 年更有可能.新的 Javascript 框架(例如 Backbone.js)可以直接连接到数据库并触发 UI 元素的更改，包括更改、更新或删除数据的表格......这是其中之一这些框架的主要好处.此外，可以通过与 Web 服务的套接字连接向 UI 提供实时更新，然后也可以捕获并采取行动.虽然这里描述的技术仍然有效，但今天还有更多活"的做事方式.

UPDATE -- 5 years later, Feb 2016: This is much more possible today than it was in 2011. New Javascript frameworks such as Backbone.js can connect directly to the database and trigger changes on UI elements including tables on change, update, or delete of data....it's one of these framework's primary benefits. Additionally, UI's can be fed real-time updates via socket connections to a web service, which can also then be caught and acted upon. While the technique described here still works, there are far more "live" ways of doing things today.

这篇关于有没有办法“监听"数据库事件并实时更新页面?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！