Example Schema
// +------------------------------------+    +------------------------------------+
// |PUBLISHER                           |    |BOOK                                |
// +------------------------------------+    +------------------------------------+
// |ID          CHAR(2)      PRIMARY KEY----->PUBLISHER   CHAR(2)      PRIMARY KEY|
// |NAME        VARCHAR(256) NOT NULL   |    |ID          CHAR(3)      PRIMARY KEY|
// |VOLUMES     INTEGER      NOT NULL   |    |TITLE       VARCHAR(256) NOT NULL   |
// |UPDATE_DATE TIMESTAMP    NOT NULL   |    |AUTHOR      VARCHAR(256) NULL       |
// |CREATE_DATE TIMESTAMP    NOT NULL   |    |ISSUE_DATE  DATE         NULL       |
// +------------------------------------+    |UPDATE_DATE TIMESTAMP    NOT NULL   |
//                                           |CREATE_DATE TIMESTAMP    NOT NULL   |
//                                           +------------------------------------+
public class Main {

    public static void main(String[] args) {
        connect();

        Runner runner = new Runner(Main.class, "sample_");
        runner.run();

        disconnect();
    }

    /////////////////////////////////////////////////////////////////

    private static void connect() {
        DatabaseManager.setConnector(new Connector() {
            public TransactedDatabase connect() throws SQLRuntimeException {
                RubbishDatabase dbh = new RubbishDatabase(new HsqldbExceptionFactory());
                dbh.setLogging(true);

                dbh.connect("jdbc:hsqldb:hsql://localhost", "sa", "");
                dbh.setAutoCommit(false);

                return dbh;
            }
        });

        Publisher.addTrigger(new BeforeCreate() {
            public void execute(Timing timing, Event event, ActiveRecord row, Database dbh) {
                System.out.println(">>>>> BEFORE PUBLISHER CREATE => " + row);
            }
        });
    }

    private static void disconnect() {
        DatabaseManager.rollback();
        DatabaseManager.disconnect();
    }

    private void puts(Object x) {
        System.out.println(x);
    }

    private void puts_table(Object[] rows) {
        System.out.println(new TabularFormat(rows));
    }

    //---------------------------------------------------------------

    public void sample_find() throws Exception {
        // SELECT PUBLISHER.CREATE_DATE, PUBLISHER.ID, PUBLISHER.NAME, PUBLISHER.UPDATE_DATE, PUBLISHER.VOLUMES FROM PUBLISHER WHERE ID = ? [01]
        Publisher publisher = Publisher.find("ID = ?", "01");
        puts(publisher);

        // SELECT BOOK.AUTHOR, BOOK.CREATE_DATE, BOOK.ID, BOOK.ISSUE_DATE, BOOK.PUBLISHER, BOOK.TITLE, BOOK.UPDATE_DATE FROM BOOK WHERE ( BOOK.PUBLISHER = ? ) [01]
        Book[] books = Book.findAny(Book.PUBLISHER.eq("01"));

        for (int i = 0; i < books.length; i++)
            puts(books[i]);

        // SELECT PUBLISHER.CREATE_DATE, PUBLISHER.ID, PUBLISHER.NAME, PUBLISHER.UPDATE_DATE, PUBLISHER.VOLUMES FROM PUBLISHER
        Publisher.findAny(new ObjectHandler() {
            public void handle(Object row) {
                Publisher publisher = (Publisher) row;
                puts(publisher.getId() + ": " + publisher.getName());
            };
        });

        // SELECT PUBLISHER.CREATE_DATE, PUBLISHER.ID, PUBLISHER.NAME, PUBLISHER.UPDATE_DATE, PUBLISHER.VOLUMES FROM PUBLISHER
        Publisher.findAny(new Handler() {
            public void handle(Publisher publisher) {
                puts(publisher.getId() + ": " + publisher.getName());
            };
        });

        // SELECT BOOK.AUTHOR, BOOK.CREATE_DATE, BOOK.ID, BOOK.ISSUE_DATE, BOOK.PUBLISHER, BOOK.TITLE, BOOK.UPDATE_DATE FROM BOOK WHERE ( BOOK.PUBLISHER >= ? ) AND ( BOOK.ID BETWEEN ? AND ? ) ORDER BY BOOK.TITLE [04, 001, 003]
        Book.findAny(Book.PUBLISHER.ge("04").and(Book.ID.between("001", "003")).order_by(Book.TITLE), new ObjectHandler() {
            public void handle(Object row) {
                Book book = (Book) row;
                puts(book.getPublisher() + ": " + book.getTitle());
            }
        });

        // SELECT PUBLISHER.CREATE_DATE, PUBLISHER.ID, PUBLISHER.NAME, PUBLISHER.UPDATE_DATE, PUBLISHER.VOLUMES FROM PUBLISHER WHERE ( VOLUMES = ? ) [4]
        Publisher[] publishers = Publisher.findAny(new MapHandler() {
            public void handle(Map conditions) {
                conditions.put(Publisher.VOLUMES, new Integer(4));
            };
        });

        for (int i = 0; i < publishers.length; i++)
            puts(publishers[i]);

        DatabaseManager.rollback();
    }

    //---------------------------------------------------------------

    public void sample_save_update() throws Exception {
        puts_table(Publisher.findAny(Publisher.ID.order_by()));

        // UPDATE PUBLISHER SET NAME = ? WHERE ( VOLUMES = ? ) AND ( UPDATE_DATE = ? ) AND ( NAME = ? ) AND ( ID = ? ) AND ( CREATE_DATE = ? ) [XXXXX, 4, 2005-07-10 17:10:00.000, Eigakukan, 01, 1999-07-10 17:10:00.000]
        Publisher publisher = Publisher.findByPK("01");
        publisher.setName("XXXXX");
        publisher.save();

        puts_table(Publisher.findAny(Publisher.ID.order_by()));

        DatabaseManager.rollback();
    }

    //---------------------------------------------------------------

    public void sample_save_insert() throws Exception {
        puts_table(Publisher.findAny(Publisher.ID.order_by()));

        // INSERT INTO PUBLISHER (VOLUMES, UPDATE_DATE, NAME, CREATE_DATE, ID) VALUES (?, ?, ?, ?, ?) [2, 2007-01-05 14:21:04.350, O'Reilly Media, Inc, 2007-01-05 14:21:04.350, 07]
        Publisher publisher = new Publisher("07", "O'Reilly Media, Inc", new Integer(2), new Date(), new Date());
        publisher.save();

        puts_table(Publisher.findAny(Publisher.ID.order_by()));

        DatabaseManager.rollback();
    }

    //---------------------------------------------------------------

    public void sample_destory() throws Exception {
        puts_table(Book.findAny(Book.PUBLISHER.eq("01").order_by(Book.ID)));

        // DELETE FROM BOOK WHERE ISSUE_DATE IS NULL AND ( UPDATE_DATE = ? ) AND ( TITLE = ? ) AND ( PUBLISHER = ? ) AND ( ID = ? ) AND ( CREATE_DATE = ? ) AND ( AUTHOR = ? ) [2005-06-10 17:10:00.000, I Know Why the Caged Bird Sings, 01, 001, 1999-06-10 17:10:00.000, Maya Angelou]
        Book book = Book.find(Book.PUBLISHER.eq("01").and(Book.ID.eq("001")));
        puts(book);
        book.destroy();

        puts_table(Book.findAny(Book.PUBLISHER.eq("01").order_by(Book.ID)));

        DatabaseManager.rollback();
    }

    //---------------------------------------------------------------

    public void sample_update() throws Exception {
        puts_table(Book.findAny(Book.PUBLISHER.eq("01").order_by(Book.ID)));

        // UPDATE BOOK SET TITLE = ? WHERE ( BOOK.PUBLISHER = ? ) AND ( BOOK.ID > ? ) [XXXXX, 01, 001]
        Book.update(Book.PUBLISHER.eq("01").and(Book.ID.gt("001")), new Handler() {
            public void handle(Book row) {
                row.setTitle("XXXXX");
            }
        });

        puts_table(Book.findAny(Book.PUBLISHER.eq("01").order_by(Book.ID)));

        DatabaseManager.rollback();
    }

    //---------------------------------------------------------------

    public void sample_delete() throws Exception {
        puts_table(Book.findAny(Book.PUBLISHER.eq("01").order_by(Book.ID)));

        // DELETE FROM BOOK WHERE ( BOOK.PUBLISHER = ? ) AND ( BOOK.ID > ? ) [01, 001]
        Book.delete(Book.PUBLISHER.eq("01").and(Book.ID.gt("001")));

        puts_table(Book.findAny(Book.PUBLISHER.eq("01").order_by(Book.ID)));

        DatabaseManager.rollback();
    }

    //---------------------------------------------------------------

    public void sample_find_joined() throws Exception {
        // SELECT PUBLISHER.CREATE_DATE, PUBLISHER.ID, PUBLISHER.NAME, PUBLISHER.UPDATE_DATE, PUBLISHER.VOLUMES, BOOK.AUTHOR, BOOK.CREATE_DATE, BOOK.ID, BOOK.ISSUE_DATE, BOOK.PUBLISHER, BOOK.TITLE, BOOK.UPDATE_DATE FROM PUBLISHER, BOOK WHERE ( PUBLISHER.ID = BOOK.PUBLISHER ) AND ( PUBLISHER.ID = ? ) AND ( BOOK.ID = ? ) [01, 001]
        Map row = Publisher.join(Book.class).find(Publisher.ID.eq("01").and(Book.ID.eq("001")));
        puts(row.get(Publisher.class));
        puts(row.get(Book.class));

        // SELECT BOOK.AUTHOR, BOOK.CREATE_DATE, BOOK.ID, BOOK.ISSUE_DATE, BOOK.PUBLISHER, BOOK.TITLE, BOOK.UPDATE_DATE, PUBLISHER.CREATE_DATE, PUBLISHER.ID, PUBLISHER.NAME, PUBLISHER.UPDATE_DATE, PUBLISHER.VOLUMES FROM BOOK, PUBLISHER WHERE ( PUBLISHER.ID = BOOK.PUBLISHER ) AND ( PUBLISHER.ID = ? ) [02]
        Map[] rows01 = Book.join(Publisher.class).findAny(Publisher.ID.eq("02"));

        for (int i = 0; i < rows01.length; i++) {
            Publisher publisher = (Publisher) rows01[i].get(Publisher.class);
            Book book = (Book) rows01[i].get(Book.class);
            puts(publisher.getName() + ": " + book.getTitle());
        }

        // SELECT BO.AUTHOR, BO.CREATE_DATE, BO.ID, BO.ISSUE_DATE, BO.PUBLISHER, BO.TITLE, BO.UPDATE_DATE, PU.CREATE_DATE, PU.ID, PU.NAME, PU.UPDATE_DATE, PU.VOLUMES FROM BOOK BO, PUBLISHER PU WHERE PU.ID = BO.PUBLISHER AND PU.ID = ? [01]
        Map[] rows02 = Publisher.join("PU", Book.class, "BO").findAny("PU.ID = BO.PUBLISHER AND PU.ID = ?", "01");

        for (int i = 0; i < rows02.length; i++) {
            Publisher publisher = (Publisher) rows02[i].get(Publisher.class);
            Book book = (Book) rows02[i].get(Book.class);
            puts(publisher.getName() + ": " + book.getTitle());
        }

        // SELECT BOOK.AUTHOR, BOOK.CREATE_DATE, BOOK.ID, BOOK.ISSUE_DATE, BOOK.PUBLISHER, BOOK.TITLE, BOOK.UPDATE_DATE, PUBLISHER.CREATE_DATE, PUBLISHER.ID, PUBLISHER.NAME, PUBLISHER.UPDATE_DATE, PUBLISHER.VOLUMES FROM BOOK, PUBLISHER WHERE ( PUBLISHER.ID = BOOK.PUBLISHER ) AND ( PUBLISHER.ID = ? ) [02]
        Map[] rows03 = Book.join(Publisher.class).findAny(Publisher.ID.eq("02"));

        for (int i = 0; i < rows03.length; i++) {
            Publisher publisher = (Publisher) rows03[i].get(Publisher.class);
            Book book = (Book) rows03[i].get(Book.class);
            puts(publisher.getName() + ": " + book.getTitle());
        }

        // SELECT BOOK.AUTHOR, BOOK.CREATE_DATE, BOOK.ID, BOOK.ISSUE_DATE, BOOK.PUBLISHER, BOOK.TITLE, BOOK.UPDATE_DATE, PUBLISHER.CREATE_DATE, PUBLISHER.ID, PUBLISHER.NAME, PUBLISHER.UPDATE_DATE, PUBLISHER.VOLUMES FROM BOOK, PUBLISHER WHERE ( PUBLISHER.ID = BOOK.PUBLISHER ) AND ( PUBLISHER.ID = ? ) [01]
        Book.join(Publisher.class).findAny(Publisher.ID.eq("01"), new MapHandler() {
            public void handle(Map row) {
                Publisher publisher = (Publisher) row.get(Publisher.class);
                Book book = (Book) row.get(Book.class);
                puts(publisher.getName() + ": " + book.getTitle());
                book.setTitle("XXXXX");
                book.save();
            }
        });

        // SELECT BOOK.AUTHOR, BOOK.CREATE_DATE, BOOK.ID, BOOK.ISSUE_DATE, BOOK.PUBLISHER, BOOK.TITLE, BOOK.UPDATE_DATE, PUBLISHER.CREATE_DATE, PUBLISHER.ID, PUBLISHER.NAME, PUBLISHER.UPDATE_DATE, PUBLISHER.VOLUMES FROM BOOK, PUBLISHER WHERE ( PUBLISHER.ID = BOOK.PUBLISHER ) AND ( PUBLISHER.ID = ? ) [01]
        Book.join(Publisher.class).findAny(Publisher.ID.eq("01"), new Handler() {
            public void handle(Publisher publisher, Book book) {
                puts(publisher.getName() + ": " + book.getTitle());
            }
        });

        DatabaseManager.rollback();
    }

    //---------------------------------------------------------------

    public void sample_find_joined_2() throws Exception {
        // SELECT BOOK.AUTHOR, BOOK.CREATE_DATE, BOOK.ID, BOOK.ISSUE_DATE, BOOK.PUBLISHER, BOOK.TITLE, BOOK.UPDATE_DATE FROM BOOK , PUBLISHER WHERE ( PUBLISHER.ID = BOOK.PUBLISHER ) AND ( PUBLISHER.NAME LIKE ? ESCAPE '\' ) [%Nippon%]
        puts_table(Book.findAny(Publisher.NAME.like("%Nippon%")));

        DatabaseManager.rollback();
    }

    //---------------------------------------------------------------

    public void sample_destory_cascade() throws Exception {
        puts_table(Publisher.findAny(Publisher.ID.order_by()));
        puts_table(Book.findAny(Book.PUBLISHER.order_by().order_by(Book.ID)));

        // DELETE FROM BOOK WHERE ( PUBLISHER = ? ) [01]
        // DELETE FROM PUBLISHER WHERE ( VOLUMES = ? ) AND ( UPDATE_DATE = ? ) AND ( NAME = ? ) AND ( ID = ? ) AND ( CREATE_DATE = ? ) [4, 2005-07-10 17:10:00.000, Eigakukan, 01, 1999-07-10 17:10:00.000]
        puts("rows=" + Publisher.findByPK("01").destroyCascade());

        puts_table(Publisher.findAny(Publisher.ID.order_by()));
        puts_table(Book.findAny(Book.PUBLISHER.order_by().order_by(Book.ID)));

        DatabaseManager.rollback();
    }

    //---------------------------------------------------------------

    public void sample_get_parent() throws Exception {
        Book book = Book.find(Book.PUBLISHER.eq("01").and(Book.ID.eq("001")));

        // SELECT PUBLISHER.CREATE_DATE, PUBLISHER.ID, PUBLISHER.NAME, PUBLISHER.UPDATE_DATE, PUBLISHER.VOLUMES FROM PUBLISHER WHERE ( ID = ? ) [01]
        puts(book.toPublisher());

        DatabaseManager.rollback();
    }

    //---------------------------------------------------------------

    public void sample_get_children() throws Exception {
        puts("rows=" + Publisher.findByPK("01").bookList().length);

        // SELECT BOOK.AUTHOR, BOOK.CREATE_DATE, BOOK.ID, BOOK.ISSUE_DATE, BOOK.PUBLISHER, BOOK.TITLE, BOOK.UPDATE_DATE FROM BOOK WHERE ( PUBLISHER = ? ) [01]
        puts(ArrayUtils.toString(Publisher.findByPK("01").bookList()));

        DatabaseManager.rollback();
    }

    //---------------------------------------------------------------

    public void sample_add() throws Exception {
        puts_table(Publisher.findByPK("01").bookList());

        Book book = new Book();
        book.setId("999");
        book.setTitle("My Title");
        book.setAuthor("My Author");
        book.setCreate_date(new Date());
        book.setUpdate_date(new Date());

        // INSERT INTO BOOK (UPDATE_DATE, TITLE, PUBLISHER, CREATE_DATE, AUTHOR, ID) VALUES (?, ?, ?, ?, ?, ?) [2007-01-05 14:21:04.866, My Title, 01, 2007-01-05 14:21:04.866, My Author, 999]
        Publisher.findByPK("01").add(book);

        puts_table(Publisher.findByPK("01").bookList());

        DatabaseManager.rollback();
    }

    //---------------------------------------------------------------

    // +-----------------------------------+
    // |IDENTITY_TABLE                     |
    // +-----------------------------------+
    // |ID          INTEGER PRIMARY KEY(ID)|
    // |INSERT_DATE DATE    NULL           |
    // +-----------------------------------+
    public void sample_identity_table() throws Exception {
        Identity_table.addTrigger(new AfterCreate() {
            public void execute(Timing timing, Event event, ActiveRecord row, Database dbh) {
                Identity_table identity_table = (Identity_table) row;
                identity_table.setId((Integer) dbh.getOne("CALL IDENTITY()"));
            }
        });

        // INSERT INTO IDENTITY_TABLE (INSERT_DATE) VALUES (?) [2007-01-05 14:21:04.897]
        // CALL IDENTITY()
        Identity_table row1 = new Identity_table();
        row1.setInsert_date(new Date());
        row1.save();
        puts(row1);

        // INSERT INTO IDENTITY_TABLE (INSERT_DATE) VALUES (?) [2007-01-05 14:21:04.913]
        // CALL IDENTITY()
        Identity_table row2 = new Identity_table();
        row2.setInsert_date(new Date());
        row2.save();
        puts(row2);

        puts_table(Identity_table.findAny());

        DatabaseManager.rollback();
    }

    //---------------------------------------------------------------

    // +---------------------------------+
    // |ARTIST                           |
    // +---------------------------------+      +----------------------+
    // |ID   INTEGER      PRIMARY KEY(ID)---+   |PAINTING              |
    // |NAME VARCHAR(256) NOT NULL       |  |   +----------------------+
    // +---------------------------------+  +--->ARTIST_ID  PRIMARY KEY|
    //                                       +-->GALLERY_ID PRIMARY KEY|
    // +---------------------------------+   |  +----------------------+
    // |GALLERY                          |   |
    // +---------------------------------+   |
    // |ID   INTEGER      PRIMARY KEY(ID)----+
    // |NAME VARCHAR(256) NOT NULL       |
    // +---------------------------------+
    public void sample_associate() throws Exception {
        Artist.addTrigger(new AfterCreate() {
            public void execute(Timing timing, Event event, ActiveRecord row, Database dbh) {
                Artist artist = (Artist) row;
                artist.setId((Integer) dbh.getOne("CALL IDENTITY()"));
            }
        });

        Gallery.addTrigger(new AfterCreate() {
            public void execute(Timing timing, Event event, ActiveRecord row, Database dbh) {
                Gallery gallery = (Gallery) row;
                gallery.setId((Integer) dbh.getOne("CALL IDENTITY()"));
            }
        });

        Artist lassen = new Artist();
        lassen.setName("Christian Riese Lassen");
        lassen.save();

        Artist yamagata = new Artist();
        yamagata.setName("Hiro Yamagata");
        yamagata.save();

        Gallery red = new Gallery();
        red.setName("Red Gallery");
        red.save();

        Gallery blue = new Gallery();
        blue.setName("Blue Gallery");
        blue.save();

        Gallery green = new Gallery();
        green.setName("Green Gallery");
        green.save();

        puts("\n===== initial state =====");
        // +--+----------------------+
        // |id|name                  |
        // +--+----------------------+
        // |0 |Christian Riese Lassen|
        // |1 |Hiro Yamagata         |
        // +--+----------------------+
        puts_table(Artist.findAny());

        // +--+-------------+
        // |id|name         |
        // +--+-------------+
        // |0 |Red Gallery  |
        // |1 |Blue Gallery |
        // |2 |Green Gallery|
        // +--+-------------+
        puts_table(Gallery.findAny());

        // Empty set
        puts_table(Painting.findAny());
        puts("");

        puts("\n===== associated =====");
        // INSERT INTO PAINTING (GALLERY_ID, ARTIST_ID) VALUES (?, ?) [1, 0]
        lassen.associate(blue);
        // INSERT INTO PAINTING (GALLERY_ID, ARTIST_ID) VALUES (?, ?) [2, 0]
        lassen.associate(green);

        // INSERT INTO PAINTING (GALLERY_ID, ARTIST_ID) VALUES (?, ?) [0, 1]
        yamagata.associate(red);
        // INSERT INTO PAINTING (GALLERY_ID, ARTIST_ID) VALUES (?, ?) [1, 1]
        yamagata.associate(blue);
        // INSERT INTO PAINTING (GALLERY_ID, ARTIST_ID) VALUES (?, ?) [2, 1]
        yamagata.associate(green);
        puts("");

        puts_table(Painting.findAny());

        puts("- lassen <- Gallery");
        // SELECT GALLERY.ID, GALLERY.NAME FROM GALLERY , PAINTING WHERE ( PAINTING.GALLERY_ID = GALLERY.ID ) AND ( PAINTING.ARTIST_ID = ? ) [0]
        puts_table(lassen.pull(Gallery.class));
        puts("- yamagata <- Gallery");
        // SELECT GALLERY.ID, GALLERY.NAME FROM GALLERY , PAINTING WHERE ( PAINTING.GALLERY_ID = GALLERY.ID ) AND ( PAINTING.ARTIST_ID = ? ) [1]
        puts_table(yamagata.pull(Gallery.class));

        puts("- red <- Artist");
        // SELECT ARTIST.ID, ARTIST.NAME FROM ARTIST , PAINTING WHERE ( PAINTING.ARTIST_ID = ARTIST.ID ) AND ( PAINTING.GALLERY_ID = ? ) [0]
        puts_table(red.pull(Artist.class));
        puts("- blue <- Artist");
        // SELECT ARTIST.ID, ARTIST.NAME FROM ARTIST , PAINTING WHERE ( PAINTING.ARTIST_ID = ARTIST.ID ) AND ( PAINTING.GALLERY_ID = ? ) [1]
        puts_table(blue.pull(Artist.class));
        puts("- green <- Artist");
        // SELECT ARTIST.ID, ARTIST.NAME FROM ARTIST , PAINTING WHERE ( PAINTING.ARTIST_ID = ARTIST.ID ) AND ( PAINTING.GALLERY_ID = ? ) [1]
        puts_table(green.pull(Artist.class));
        puts("");

        puts("\n===== unassociated green<->yamagata, lassen<->blue =====");
        // DELETE FROM PAINTING WHERE ( GALLERY_ID = ? ) AND ( ARTIST_ID = ? ) [2, 1]'
        green.unassociate(yamagata);
        // DELETE FROM PAINTING WHERE ( GALLERY_ID = ? ) AND ( ARTIST_ID = ? ) [1, 0]'
        lassen.unassociate(blue);
        puts("");

        puts_table(Painting.findAny());

        puts("- lassen <- Gallery");
        puts_table(lassen.pull(Gallery.class));
        puts("- yamagata <- Gallery");
        puts_table(yamagata.pull(Gallery.class));

        puts("- red <- Artist");
        puts_table(red.pull(Artist.class));
        puts("- blue <- Artist");
        puts_table(blue.pull(Artist.class));
        puts("- green <- Artist");
        puts_table(green.pull(Artist.class));
        puts_table(green.pull("artist"));
        puts("");

        DatabaseManager.rollback();
    }

}