java 使用Java进行数据库轮询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2348416/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
database polling using Java
提问by user262577
I am stuck at some point wherein I need to get database changes in a Java code. Request is to get any record updated, added, deleted in any table of db; should be recognized by Java program. How could it be implemented JMS? or a Java thread?
我被困在需要在 Java 代码中更改数据库的某个时刻。请求是获取db任意表中任意记录的更新、添加、删除；应该被Java程序识别。如何实现 JMS？还是Java线程？
Update:Thanks guys for your support i am actually using Oracle as DB and Weblogic 10.3 workshop. Actually I want to get the updates from a table in which I have only read permission so guys what do you all suggest. I can't update the DB. Only thing I can do is just read the DB and if there is any change in the table I have to get the information/notification that certain data rows has been added/deleted or updated.
更新：感谢大家的支持，我实际上使用 Oracle 作为 DB 和 Weblogic 10.3 研讨会。实际上，我想从我只有读取权限的表中获取更新，所以你们都有什么建议。我无法更新数据库。我唯一能做的就是读取数据库，如果表中有任何更改，我必须获取某些数据行已添加/删除或更新的信息/通知。
回答by duffymo
Unless the database can send a message to Java, you'll have to have a thread that polls.
除非数据库可以向 Java 发送消息，否则您必须有一个轮询线程。
A better, more efficient model would be one that fires events on changes. A database that has Java running inside (e.g., Oracle) could do it.
一种更好、更有效的模型是在更改时触发事件的模型。内部运行 Java 的数据库（例如 Oracle）可以做到这一点。
回答by Enno Shioji
We do it by polling the DB using an EJB timer task. In essence, we have a status filed which we update when we have processed that row.
我们通过使用 EJB 计时器任务轮询 DB 来实现。本质上，我们有一个状态文件，我们在处理该行时更新该文件。
So the EJB timer thread calls a procedure that grabs rows which are flagged "un-treated".
因此，EJB 计时器线程调用一个过程来获取标记为“未处理”的行。
Dirty, but also very simple and robust. Especially, after a crash or something, it can still pick up from where it crashed without too much complexity.
脏，但也非常简单和健壮。特别是，在崩溃或其他事情之后，它仍然可以从崩溃的地方恢复，而不会太复杂。
The disadvantage is the wasted load on the DB, and also response time will be limited (probably requires seconds).
缺点是浪费了数据库上的负载，而且响应时间也会受到限制（可能需要几秒钟）。
回答by Adamski
We have accomplished this in our firm by adding triggers to database tables that call an executable to issue a Tib Rendezvous message, which is received by all interested Java applications.
我们在我们公司通过向数据库表添加触发器来实现这一点，这些触发器调用可执行文件来发出 Tib Rendezvous 消息，所有感兴趣的 Java 应用程序都会收到该消息。
However, the ideal way to do this IMHO is to be in complete control of all database writes at the application level, and to notify any interested parties at this point (via multi-cast, Tib, etc). In reality this isn't always possible where you have a number of disparate systems.
但是，恕我直言，执行此操作的理想方法是完全控制应用程序级别的所有数据库写入，并在此时通知任何相关方（通过多播、Tib 等）。实际上，当您拥有多个不同的系统时，这并不总是可行的。
回答by BalusC
You're indeed dependent on whether the database in question supports it. You'll also need to take the overhead into account. Lot of inserts/updates also means a lot of notifications and your Java code has to handle them consistently, else it will bubble up.
您确实依赖于有问题的数据库是否支持它。您还需要考虑开销。大量的插入/更新也意味着大量的通知，你的 Java 代码必须一致地处理它们，否则它会冒泡。
If the datamodel allows it, just add an extra column which holds a timestamp which get updated on every insert/update. Most major DB's supports an auto-update of the column on every insert/update. I don't know which DB server you're using, so I'll give only a MySQL-targeted example:
如果数据模型允许，只需添加一个额外的列，其中包含在每次插入/更新时更新的时间戳。大多数主要数据库都支持在每次插入/更新时自动更新列。我不知道您使用的是哪个数据库服务器，因此我将仅提供一个以 MySQL 为目标的示例：
CREATE TABLE mytable (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
somevalue VARCHAR(255) NOT NULL,
lastupdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX (lastupdate)
)
This way you don't need to worry about inserting/updating the lastupdateyourself. You can just do an INSERT INTO mytable (somevalue) VALUES (?)or UPDATE mytable SET somevalue = ? WHERE id = ?and the DB will do the magic.
这样您就不必担心自己插入/更新lastupdate。你可以只做一个INSERT INTO mytable (somevalue) VALUES (?)or UPDATE mytable SET somevalue = ? WHERE id = ?，数据库就会发挥作用。
After ensuring that the DB server's time and Java application's time are the same, you can just fire a background thread (using either Timerwith TimerTask, or ScheduledExecutorServicewith Runnableor Callable) which does roughly this:
在确保数据库服务器的时间和 Java 应用程序的时间相同后，您可以触发一个后台线程（使用TimerwithTimerTask或ScheduledExecutorServicewithRunnable或Callable），大致如下：
Date now = new Date();
statement = connection.prepareStatement("SELECT id FROM mytable WHERE lastupdate BETWEEN ? AND ?");
statement.setDate(1, this.lastTimeChecked);
statement.setDate(2, now);
resultSet = statement.executeQuery();
while (resultSet.next()) {
// Handle accordingly.
}
this.lastTimeChecked = now;
Update: as per the question update it turns out that you have no control over the DB. Well, then you don't have much good/efficient options. Either just refresh the entire list in Java memory with entire data from DB without checking/comparing for changes (probably the fastest way), or dynamically generate a SQL query based on the current data which excludes the current data from the results.
更新：根据问题更新，事实证明您无法控制数据库。那么，你没有太多好的/有效的选择。要么只用来自 DB 的整个数据刷新 Java 内存中的整个列表，而不检查/比较更改（可能是最快的方式），要么根据当前数据动态生成 SQL 查询，从结果中排除当前数据。
回答by SOA Nerd
I assume that you're talking about a situation where anything can update a table. If for some reason you're instead talking about a situation where only the Java application will be updating the table that's different. If you're using Java only you can put this code in your DAO or EJB doing the update (it's much cleaner than using a trigger in this case).
我假设您正在谈论任何事情都可以更新表格的情况。如果出于某种原因，您正在谈论一种情况，即只有 Java 应用程序将更新不同的表。如果您只使用 Java，您可以将此代码放在您的 DAO 或 EJB 中进行更新（在这种情况下，它比使用触发器要干净得多）。
回答by extraneon
An alternative way to do this is to funnel all database calls through a web service API, or perhaps a JMS API, which does the actual database calls. Processes could register there to get a notification of a database update.
执行此操作的另一种方法是通过 Web 服务 API 或 JMS API 汇集所有数据库调用，后者执行实际的数据库调用。进程可以在那里注册以获得数据库更新的通知。
回答by Ed.
We have a similar requirement. In our case we have a legacy system that we do not want to adversely impact performance on the existing transaction table.
我们也有类似的需求。在我们的例子中，我们有一个遗留系统，我们不想对现有事务表的性能产生不利影响。
Here's my proposal:
这是我的建议：
- A new work table with pk to transaction and insert timestamp
- A new audit table that has same columns as transaction table + audit columns
- Trigger on transaction table to dump all insert/update/deletes to an audit table
- Java process to poll the work table, join to the audit table, publish the event in question and delete from the work table.
- 带有 pk 到事务和插入时间戳的新工作表
- 与事务表+审计列具有相同列的新审计表
- 在事务表上触发以将所有插入/更新/删除转储到审计表
- Java 进程轮询工作表、加入审计表、发布有问题的事件并从工作表中删除。
Question is: What do you use for polling? Is quartz overkill? How can you scale back the polling frequency based on the current DB load?
问题是：你用什么来投票？石英是不是矫枉过正？如何根据当前数据库负载缩减轮询频率？
