Java 从数据库中获取事件

Getting events from a database

提问by mainstringargs

I am not very familiar with databases and what they offer outside of the CRUD operations.

我对数据库以及它们在 CRUD 操作之外提供的内容不是很熟悉。

My research has led me to triggers. Basically it looks like triggers offer this type of functionality:

我的研究使我找到了触发器。基本上看起来触发器提供了这种类型的功能：

(from Wikipedia)

There are typically three triggering events that cause triggers to "fire":

• INSERT event (as a new record is being inserted into the database).
• UPDATE event (as a record is being changed).
• DELETE event (as a record is being deleted).

（来自维基百科）

通常有三个触发事件会导致触发器“触发”：

• INSERT 事件（因为新记录被插入到数据库中）。
• UPDATE 事件（因为正在更改记录）。
• DELETE 事件（因为正在删除记录）。

My question is: is there some way I can be notified in Java (preferably including the data that changed) by the database when a record is Updated/Deleted/Inserted using some sort of trigger semantics?

我的问题是：当使用某种触发器语义更新/删除/插入记录时，是否可以通过某种方式在 Java 中通知我（最好包括更改的数据）？

What might be some alternate solutions to this problem? How can I listen to database events?

这个问题可能有哪些替代解决方案？如何监听数据库事件？

The main reason I want to do this is a scenario like this:

我想这样做的主要原因是这样的场景：

I have 5 client applications all in different processes/existing across different PCs. They all share a common database (Postgres in this case).

我有 5 个客户端应用程序，它们都在不同的进程中/存在于不同的 PC 上。它们都共享一个公共数据库（在本例中为 Postgres）。

Lets say one client changes a record in the DB that all 5 of the clients are "interested" in. I am trying to think of ways for the clients to be "notified" of the change (preferably with the affected data attached) instead of them querying for the data at some interval.

假设一个客户更改了数据库中所有 5 个客户都“感兴趣”的记录。我试图想办法让客户“通知”更改（最好附上受影响的数据）而不是他们以某个时间间隔查询数据。

采纳答案by Javamann

Using Oracle you can setup a Trigger on a table and then have the trigger send a JMS message. Oracle has two different JMS implementations. You can then have a process that will 'listen' for the message using the JDBC Driver. I have used this method to push changes out to my application vs. polling. If you are using a Java database (H2) you have additional options. In my current application (SIEM) I have triggers in H2 that publish change events using JMX.

使用 Oracle，您可以在表上设置触发器，然后让触发器发送 JMS 消息。Oracle 有两种不同的 JMS 实现。然后，您可以拥有一个使用 JDBC 驱动程序“侦听”消息的进程。我已经使用这种方法将更改推送到我的应用程序与轮询。如果您使用的是 Java 数据库 (H2)，您还有其他选择。在我当前的应用程序 (SIEM) 中，我在 H2 中有使用 JMX 发布更改事件的触发器。

回答by JosephStyons

The simplest thing to do is to have the insert/update/delete triggers make an entry in some log table, and have your java program monitor that table. Good columns to have in your log table would be things like EVENT_CODE, LOG_DATETIME, and LOG_MSG.

最简单的做法是让插入/更新/删除触发器在某个日志表中创建一个条目，并让您的 Java 程序监视该表。日志表中的好列应该是 EVENT_CODE、LOG_DATETIME 和 LOG_MSG 之类的内容。

Unless you require very high performance or need to handle 100Ks of records, that is probably sufficient.

除非您需要非常高的性能或需要处理 100K 的记录，否则这可能就足够了。

回答by Kevin

What you're asking completely depends on both the database you're using and the framework you're using to communicate with your database.

你问什么完全取决于你使用的数据库和你用来与数据库通信的框架。

If you're using something like Hibernate as your persistence layer, it has a set of listeners and interceptors that you can use to monitor records going in and out of the database.

如果您使用 Hibernate 之类的东西作为持久层，它有一组侦听器和拦截器，您可以使用它们来监视进出数据库的记录。

回答by Scott Anderson

There are a few different techniques here depending on the database you're using. One idea is to poll the database (which I'm sure you're trying to avoid). Basically you could check for changes every so often.

根据您使用的数据库，这里有几种不同的技术。一种想法是轮询数据库（我确定您正在尝试避免这种情况）。基本上，您可以每隔一段时间检查一次更改。

Another solution (if you're using SQL Server 2005) is to use Notification Services, although this techonology is supposedly being replaced in SQL 2008 (we haven't seen a pure replacement yet, but Microsoft has talked about it publicly).

另一种解决方案（如果您使用的是 SQL Server 2005）是使用通知服务，尽管该技术据说会在 SQL 2008 中被取代（我们还没有看到纯粹的替代品，但微软已经公开讨论过）。

回答by tpdi

Calling external processes from the database is very vendor specific.

从数据库调用外部进程是非常特定于供应商的。

Just off the top of my head:

就在我的头顶：

• SQLServer can call CLR programs from triggers,

• postgresql can call arbitrary C functions loaded dynamically,

• MySQL can call arbitrary C functions, but they must be compiled in,

• Sybase can make system calls if set up to do so.

• SQLServer 可以从触发器调用 CLR 程序，

• postgresql 可以调用动态加载的任意 C 函数，

• MySQL 可以调用任意的 C 函数，但是它们必须被编译进去，

• 如果设置为这样做，Sybase 可以进行系统调用。

回答by Chris Thornhill

If you are using Oracle, check out this previous post.

如果您使用的是 Oracle，请查看上一篇博文。

回答by easel

I think you're confusing two things. They are both highly db vendor specific.

我认为你混淆了两件事。它们都是高度特定于数据库供应商的。

The first I shall call "triggers". I am sure there is at least one DB vendor who thinks triggers are different than this, but bear with me. A trigger is a server-side piece of code that can be attached to table. For instance, you could run a PSQL stored procedure on every update in table X. Some databases allow you to write these in real programming languages, others only in their variant of SQL. Triggers are typically reasonably fast and scalable.

第一个我将称之为“触发器”。我确信至少有一个 DB 供应商认为触发器与此不同，但请耐心等待。触发器是可以附加到表的服务器端代码段。例如，您可以在表 X 中的每次更新时运行 PSQL 存储过程。一些数据库允许您使用真实的编程语言编写这些存储过程，而其他数据库则只允许使用它们的 SQL 变体。触发器通常相当快且可扩展。

The other I shall call "events". These are triggers that fire in the database that allow you to define an event handler in your client program. IE, any time there are updates to the clients database, fire updateClientsList in your program. For instance, using python and firebird see http://www.firebirdsql.org/devel/python/docs/3.3.0/beyond-python-db-api.html#database-event-notification

另一个我将称之为“事件”。这些是在数据库中触发的触发器，允许您在客户端程序中定义事件处理程序。IE，任何时候客户端数据库有更新，在你的程序中触发 updateClientsList。例如，使用 python 和 firebird 参见http://www.firebirdsql.org/devel/python/docs/3.3.0/beyond-python-db-api.html#database-event-notification

I believe the previous suggestion to use a monitor is an equivalent way to implement this using some other database. Maybe oracle? MSSQL Notification services, mentioned in another answer is another implementation of this as well.

我相信之前使用监视器的建议是使用其他数据库实现这一点的等效方法。也许甲骨文？另一个答案中提到的 MSSQL 通知服务也是另一个实现。

I would go so far as to say you'd better REALLY know why you want the database to notify your client program, otherwise you should stick with server side triggers.

我什至会说你最好真的知道为什么你想让数据库通知你的客户端程序，否则你应该坚持使用服务器端触发器。

回答by Gary Kephart

This is usually what the standard client/server application is for. If all inserts/updates/deletes go through the server application, which then modifies the database, then client applications can find out much easier what changes were made.

这通常是标准客户端/服务器应用程序的用途。如果所有的插入/更新/删除都经过服务器应用程序，然后服务器应用程序修改数据库，那么客户端应用程序可以更容易地找出进行了哪些更改。

回答by time4tea

Don't mix up the database (which contains the data), and events on that data.

不要混淆数据库（包含数据）和该数据上的事件。

Triggers are one way, but normally you will have a persistence layer in your application. This layer can choose to fire off events when certain things happen - say to a JMS topic.

触发器是一种方式，但通常您的应用程序中会有一个持久层。当某些事情发生时，该层可以选择触发事件 - 例如 JMS 主题。

Triggers are a last ditch thing, as you're operating on relational items then, rather than "events" on the data. (For example, an "update", could in reality map to a "company changed legal name" event) If you rely on the db, you'll have to map the inserts & updates back to real life events.... which you already knew about!

触发器是最后一搏，因为那时您正在对关系项目进行操作，而不是对数据进行“事件”。（例如，“更新”实际上可以映射到“公司更改法定名称”事件）如果您依赖数据库，则必须将插入和更新映射回现实生活中的事件......你已经知道了！

You can then layer other stuff on top of these notifications - like event stream processing - to find events that others are interested in.

然后，您可以在这些通知之上分层其他内容（例如事件流处理）以查找其他人感兴趣的事件。

James

詹姆士

回答by time4tea

Hmm. So you're using PostgreSQL and you want to "listen" for events and be "notified" when they occur?

唔。因此，您正在使用 PostgreSQL 并且想要“监听”事件并在事件发生时“得到通知”？

http://www.postgresql.org/docs/8.3/static/sql-listen.htmlhttp://www.postgresql.org/docs/8.3/static/sql-notify.html

http://www.postgresql.org/docs/8.3/static/sql-listen.html http://www.postgresql.org/docs/8.3/static/sql-notify.html

Hope this helps!

希望这可以帮助！