H2 Trigger, Notifier not called in Automatic Mixed Mode (AUTO_SERVER=TRUE) on remote client

Question

I'm trying to use H2 Trigger facility to let clients connected to a H2 database in automatic mixed mode (AUTO_SERVER=TRUE) receive notification when something changes in the database table

test(id INTEGER NOT NULL AUTO_INCREMENT, message varchar(1024))

So far only the H2 server receives the TRIGGER notification, while clients cannot receive any notification therefore their only way to check for changes to the database is to poll with queries to the table, but this way the TRIGGER itself is useless, I could just simply all clients and server poll the database for changes!.

Is there some way to let a trigger notify all clients connected or call a method inside each client so that they realize the table has been modified with an insertion (doesn't bother me the delete or update cases)?

I post my code below which is based on this answer by Thomas Mueller (H2 database creator):

import java.sql.*;
import java.util.concurrent.atomic.AtomicLong;
import org.h2.api.Trigger;

public class TestSimpleDb
{
    public static void main(String[] args) throws Exception
    {
        final String url = "jdbc:h2:test;create=true;AUTO_SERVER=TRUE;multi_threaded=true";

        boolean isSender = false;
        for (String arg : args)
        {
            if (arg.contains("receiver"))
            {
                System.out.println("receiver starting");
                isSender = false;
            }
            else if (arg.contains("sender"))
            {
                System.out.println("sender starting");
                isSender = true;
            }
        }

        if (isSender)
        {
            Connection conn = DriverManager.getConnection(url);
            Statement stat = conn.createStatement();
            stat.execute("create table test(id INTEGER NOT NULL AUTO_INCREMENT, message varchar(1024))");
            stat.execute("create trigger notifier "
                    + "before insert, update, delete, rollback "
                    + "on test FOR EACH ROW call \""
                    + TestSimpleDb.Notifier.class.getName() + "\"");

            Thread.sleep(500);

            for (int i = 0; i < 10; i++) {
                System.out.println("Sender: I change something...");
                stat.execute("insert into test(message) values('my message')");
                Thread.sleep(1000);
            }
            conn.close();
        }
        else 
        {
            new Thread() {
                public void run() {
                    try {
                        Connection conn = DriverManager.getConnection(url);
                        while (true) {
                            ;
                            //this loop is just to keep the thread alive..
                        }
                    }
                    catch (Exception e)
                    {
                        e.printStackTrace();
                    }
                }
            }.start();
        }
    }

    public static class Notifier implements Trigger
    {
        @Override
        public void init(Connection cnctn, String string, String string1, String string2, boolean bln, int i) throws SQLException {
            // Initializing trigger
        }

        @Override
        public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
            if (newRow != null) {
                System.out.println("Received: " + (String) newRow[1]);
            }
        }

        @Override
        public void close() {
            // ignore
        }

        @Override
        public void remove() {
            // ignore
        }
    }
}

Answer 1

Like all triggers, this trigger is called on the server, that is, when using the automatic mixed mode (like you do) in the process that opened the database first. Therefore, if I first start the "sender", then I get the following output there:

sender starting
Sender: I change something...
Received: my message
Sender: I change something...
Received: my message

and if I then start the "receiver", I get the following messages there:

Receiver: event received
Receiver: event received
Receiver: event received

If you want that the "receiver" can display what rows were changed, you would need a different architecture. For example, you could add a timestamp column to the table (and an index for this column), and then, on the receiver side, query for the rows where the timestamp is new. This will only work for added and changed rows; for removed rows, you might need to add a new table that contains the removed rows since time x. This table would need to be garbage collected from time to time so that it doesn't grow forever.