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) {
        Runner runner = new Runner(Main.class, "sample_");
        runner.run();
    }

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

    private RubbishDatabase connect() {
        RubbishDatabase dbh = new RubbishDatabase(new HsqldbExceptionFactory());
        dbh.setLogging(true);

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

        return dbh;
    }

    private void disconnect(TransactedDatabase dbh) {
        dbh.rollback();
        dbh.disconnect();
    }

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

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

    public void sample_query() throws Exception {
        TransactedDatabase dbh = connect();

        Map[] publishers = dbh.query("SELECT ID, NAME FROM PUBLISHER ORDER BY ID");

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

        Map[] books = dbh.query("SELECT TITLE, AUTHOR FROM BOOK WHERE PUBLISHER IN (?, ?) ORDER BY PUBLISHER, ID", "04", "06");

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

        disconnect(dbh);
    }

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

    public void sample_query_strbuf() throws Exception {
        TransactedDatabase dbh = connect();

        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ID, NAME ");
        sql.append("FROM PUBLISHER ORDER BY ID");

        Map[] publishers = dbh.query(sql);

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

        disconnect(dbh);
    }

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

    public void sample_getRow() throws Exception {
        TransactedDatabase dbh = connect();

        Map book = dbh.getRow("SELECT TITLE, AUTHOR FROM BOOK WHERE PUBLISHER=? AND ID=?", "06", "000");
        puts(book);

        disconnect(dbh);
    }

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

    public void sample_getOne() throws Exception {
        TransactedDatabase dbh = connect();

        Integer count = (Integer) dbh.getOne("SELECT COUNT(*) FROM BOOK WHERE PUBLISHER=?", "06");
        puts("count=" + count);

        disconnect(dbh);
    }

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

    public void sample_update() throws Exception {
        TransactedDatabase dbh = connect();

        int rows = dbh.update("UPDATE PUBLISHER SET NAME=?, VOLUMES=?, UPDATE_DATE=?", "XXXXX", new Integer(9990), new Date());
        puts("rows=" + rows);

        Map[] publishers = dbh.query("SELECT * FROM PUBLISHER ORDER BY ID");

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

        disconnect(dbh);
    }

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

    public void sample_select() throws Exception {
        TransactedDatabase dbh = connect();

        Publisher[] publishers = (Publisher[]) dbh.select(Publisher.class, "ORDER BY ID");

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

        Book[] books = (Book[]) dbh.select(Book.class, "PUBLISHER IN (?, ?) ORDER BY PUBLISHER, ID", "04", "06");

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

        disconnect(dbh);
    }

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

    public void sample_selectRow() throws Exception {
        TransactedDatabase dbh = connect();

        Publisher publisher = (Publisher) dbh.selectRow(Publisher.class, "ID=?", "04");
        puts(publisher);

        disconnect(dbh);
    }

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

    public void sample_insert() throws Exception {
        TransactedDatabase dbh = connect();

        Publisher publisher = new Publisher();
        publisher.setId("07");
        publisher.setName("O'Reilly Media, Inc");
        publisher.setVolumes(new Integer(2));
        publisher.setUpdate_date(new Date());
        publisher.setCreate_date(new Date());

        int prows = dbh.insert(publisher);
        puts("prows=" + prows);

        Publisher[] publishers = (Publisher[]) dbh.select(Publisher.class, "ORDER BY ID");

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

        Book book01 = new Book();
        book01.setPublisher("07");
        book01.setId("000");
        book01.setTitle("Eclipse");
        book01.setAuthor("IBM");
        book01.setIssue_date(null);
        book01.setUpdate_date(new Date());
        book01.setCreate_date(new Date());

        Book book02 = new Book();
        book02.setPublisher("07");
        book02.setId("001");
        book02.setTitle("Java");
        book02.setAuthor("James Gosling");
        book02.setIssue_date(new ZDate("2001-06-01"));
        book02.setUpdate_date(new Date());
        book02.setCreate_date(new Date());

        List booklist = new ArrayList();
        booklist.add(book01);
        booklist.add(book02);

        int brows = dbh.insert(booklist);
        puts("brows=" + brows);

        Book[] books = (Book[]) dbh.select(Book.class, "PUBLISHER=? ORDER BY ID", "07");

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

        disconnect(dbh);
    }

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

    public void sample_SQLNotNullException() throws Exception {
        TransactedDatabase dbh = connect();

        Publisher publisher = new Publisher();
        publisher.setId("07");
        publisher.setName("O'Reilly Media, Inc");
        publisher.setVolumes(null);
        publisher.setUpdate_date(new Date());
        publisher.setCreate_date(new Date());

        try {
            dbh.insert(publisher);
        } catch (SQLNotNullException e) {
            puts(e);
        }

        disconnect(dbh);
    }

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

    public void sample_SQLUniqueException() throws Exception {
        TransactedDatabase dbh = connect();

        Publisher publisher = new Publisher();
        publisher.setId("00");
        publisher.setName("O'Reilly Media, Inc");
        publisher.setVolumes(new Integer(2));
        publisher.setUpdate_date(new Date());
        publisher.setCreate_date(new Date());

        try {
            dbh.insert(publisher);
        } catch (SQLUniqueException e) {
            puts(e);
        }

        ((RubbishDatabase) dbh).setRestrainUniqueConstraint(true);
        puts("rows=" + dbh.insert(publisher));

        disconnect(dbh);
    }

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

    public void sample_SQLCheckException() throws Exception {
        TransactedDatabase dbh = connect();

        Publisher publisher = new Publisher();
        publisher.setId("07");
        publisher.setName("O'Reilly Media, Inc");
        publisher.setVolumes(new Integer(-1));
        publisher.setUpdate_date(new Date());
        publisher.setCreate_date(new Date());

        try {
            dbh.insert(publisher);
        } catch (SQLCheckException e) {
            puts(e);
        }

        disconnect(dbh);
    }

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

    public void sample_SQLReferentialException() throws Exception {
        TransactedDatabase dbh = connect();

        Book book = new Book();
        book.setPublisher("07");
        book.setId("000");
        book.setTitle("Eclipse");
        book.setAuthor("IBM");
        book.setIssue_date(null);
        book.setUpdate_date(new Date());
        book.setCreate_date(new Date());

        try {
            dbh.insert(book);
        } catch (SQLReferentialException e) {
            puts(e);
        }

        try {
            dbh.update("DELETE FROM PUBLISHER");
        } catch (SQLReferentialException e) {
            puts(e);
        }

        disconnect(dbh);
    }

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

    public void sample_SQL_I_F() throws Exception {
        TransactedDatabase dbh = connect();

        SQL sql01 = new SearchBookSQL("05", null, null, null);

        Map[] books01 = dbh.query(sql01);

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

        SQL sql02 = new SearchBookSQL("05", null, null, "Charles Silverstein");
        Map[] books02 = dbh.query(sql02);

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

        disconnect(dbh);
    }

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

    public void sample_WHERE_I_F() throws Exception {
        TransactedDatabase dbh = connect();

        WHERE where01 = new SearchBookWHERE("05", null, null, null);

        Book[] books01 = (Book[]) dbh.select(where01);

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

        WHERE where02 = new SearchBookWHERE("05", null, null, "Charles Silverstein");
        Book[] books02 = (Book[]) dbh.select(where02);

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

        disconnect(dbh);
    }

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

    public void sample_query_start_lines() throws Exception {
        TransactedDatabase dbh = connect();

        Map[] publishers = dbh.query("SELECT * FROM PUBLISHER ORDER BY ID", 2, 3);

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

        disconnect(dbh);
    }

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

    public void sample_select_start_lines() throws Exception {
        TransactedDatabase dbh = connect();

        Publisher[] publishers = (Publisher[]) dbh.select(Publisher.class, "ORDER BY ID", 2, 3);

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

        disconnect(dbh);
    }

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

    public void sample_select_joined_tables() throws Exception {
        TransactedDatabase dbh = connect();

        Class[] types01 = { Publisher.class, Book.class };
        Map[] jtabs01 = dbh.select(types01, "PUBLISHER.ID=? ORDER BY BOOK.ID", "04");

        for (int i = 0; i < jtabs01.length; i++) {
            Publisher publisher = (Publisher) jtabs01[i].get(Publisher.class);
            Book book = (Book) jtabs01[i].get(Book.class);
            puts(publisher.getName() + ", " + publisher.getUpdate_date() + " / " + book.getTitle() + ", " + book.getUpdate_date());
            publisher = (Publisher) jtabs01[i].get("PUBLISHER");
            book = (Book) jtabs01[i].get("book");
            puts(publisher.getName() + ", " + publisher.getUpdate_date() + " / " + book.getTitle() + ", " + book.getUpdate_date());
        }

        Map types02 = new HashMap();
        types02.put("PU", Publisher.class);
        types02.put("BO", Book.class);
        Map[] jtabs02 = dbh.select(types02, "PU.ID=BO.PUBLISHER AND PU.ID=? ORDER BY BO.ID", "04");

        for (int i = 0; i < jtabs02.length; i++) {
            Publisher publisher = (Publisher) jtabs02[i].get(Publisher.class);
            Book book = (Book) jtabs02[i].get(Book.class);
            puts(publisher.getName() + ", " + publisher.getUpdate_date() + " / " + book.getTitle() + ", " + book.getUpdate_date());
            publisher = (Publisher) jtabs02[i].get("PU");
            book = (Book) jtabs02[i].get("bo");
            puts(publisher.getName() + ", " + publisher.getUpdate_date() + " / " + book.getTitle() + ", " + book.getUpdate_date());
        }

        disconnect(dbh);
    }

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

    public void sample_selectRow_joined_tables() throws Exception {
        TransactedDatabase dbh = connect();

        Class[] types01 = { Publisher.class, Book.class };
        Map jtabs01 = dbh.selectRow(types01, "PUBLISHER.ID=? AND BOOK.ID=? ORDER BY BOOK.ID", "04", "000");

        Publisher publisher01 = (Publisher) jtabs01.get(Publisher.class);
        Book book01 = (Book) jtabs01.get(Book.class);
        puts(publisher01.getName() + ", " + publisher01.getUpdate_date() + " / " + book01.getTitle() + ", " + book01.getUpdate_date());

        Map types02 = new HashMap();
        types02.put("PU", Publisher.class);
        types02.put("BO", Book.class);
        Map jtabs02 = dbh.selectRow(types02, "PU.ID=BO.PUBLISHER AND PU.ID=? AND BO.ID=? ORDER BY BO.ID", "04", "001");

        Publisher publisher02 = (Publisher) jtabs02.get(Publisher.class);
        Book book02 = (Book) jtabs02.get(Book.class);
        puts(publisher02.getName() + ", " + publisher02.getUpdate_date() + " / " + book02.getTitle() + ", " + book02.getUpdate_date());

        disconnect(dbh);
    }

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

    public void sample_query_logging_particularly() throws Exception {
        RubbishDatabase dbh = new RubbishDatabase(new ParticularLogger(), new HsqldbExceptionFactory());
        dbh.setLogging(true);
        dbh.connect("jdbc:hsqldb:hsql://localhost", "sa", "");
        dbh.setAutoCommit(false);

        Map[] publishers = dbh.query("SELECT ID, NAME FROM PUBLISHER ORDER BY ID");

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

        Map[] books = dbh.query("SELECT TITLE, AUTHOR FROM BOOK WHERE PUBLISHER IN (?, ?) ORDER BY PUBLISHER, ID", "04", "06");

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

        dbh.disconnect();
    }

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

    public void sample_table_view() throws Exception {
        TransactedDatabase dbh = connect();

        Map[] rows = dbh.query("SELECT * FROM PUBLISHER");
        puts(new TabularFormat(rows));

        disconnect(dbh);
    }

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

    public void sample_get_catalogs() {
        RubbishDatabase dbh = connect();

        Map[] rows = dbh.getCatalogs();
        puts(new TabularFormat(rows));

        disconnect(dbh);
    }

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

    public void sample_get_schemas() {
        RubbishDatabase dbh = connect();

        Map[] rows = dbh.getSchemas();
        puts(new TabularFormat(rows));

        disconnect(dbh);
    }

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

    public void sample_get_tables() {
        RubbishDatabase dbh = connect();

        Map[] rows = dbh.getTables("PUBLIC");
        puts(new TabularFormat(rows));

        disconnect(dbh);
    }

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

    public void sample_get_columns() {
        RubbishDatabase dbh = connect();

        Map[] rows = dbh.getColumns("BOOK");
        puts(new TabularFormat(rows));

        disconnect(dbh);
    }

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

    public void sample_ddl() {
        TransactedDatabase dbh = connect();

        dbh.execute("CREATE TABLE HOGE (FOO CHAR(256) NOT NULL, BAR VARCHAR(256) NOT NULL)");
        dbh.execute("ALTER TABLE HOGE ADD CONSTRAINT PK_HOGE PRIMARY KEY (FOO)");
        puts(new TabularFormat(((RubbishDatabase) dbh).getColumns("HOGE")));
        dbh.execute("DROP TABLE HOGE");
        puts(new TabularFormat(((RubbishDatabase) dbh).getColumns("HOGE")));

        disconnect(dbh);
    }

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

    public void sample_export_csv() throws IOException {
        TransactedDatabase dbh = connect();

        Map[] rows = dbh.query("SELECT * FROM PUBLISHER");

        CSV csv = new CSV(rows);
        StringWriter writer = new StringWriter();
        csv.export(writer);
        writer.close();
        puts(writer);

        disconnect(dbh);
    }

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

    public void sample_query_each() throws Exception {
        TransactedDatabase dbh = connect();

        dbh.query("SELECT ID, NAME FROM PUBLISHER ORDER BY ID", new MapHandler() {
            public void handle(Map row) {
                puts(row);
            }
        });

        dbh.query("SELECT TITLE, AUTHOR FROM BOOK WHERE PUBLISHER IN (?, ?) ORDER BY PUBLISHER, ID", "04", "06", new MapHandler() {
            public void handle(Map row) {
                puts(row);
            }
        });

        disconnect(dbh);
    }

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

    public void sample_select_each() throws Exception {
        TransactedDatabase dbh = connect();

        dbh.select(Publisher.class, "ORDER BY ID", new ObjectHandler() {
            public void handle(Object row) {
                puts(row);
            }
        });

        dbh.select(Book.class, "PUBLISHER IN (?, ?) ORDER BY PUBLISHER, ID", "04", "06", new ObjectHandler() {
            public void handle(Object row) {
                puts(row);
            }
        });

        disconnect(dbh);
    }

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

    public void sample_create_vo() throws Exception {
        RubbishDatabase dbh = connect();

        VOGen vogen = new VOGen(dbh);
        Template template01 = new ClassTemplate("sample.hsqldb.bean", "C:/eclipse/workspace/rubbish-db/sample/src");
        vogen.write("PUBLIC", template01);
        Template template02 = new IFTemplate("sample.hsqldb.i_f.bean", "C:/eclipse/workspace/rubbish-db/sample/src");
        vogen.write("PUBLIC", template02);

        disconnect(dbh);
    }

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

    public void sample_count() throws Exception {
        TransactedDatabase dbh = connect();

        int pcnt = dbh.count(Publisher.class);
        puts("PUBLISHER: " + pcnt + "rows");

        int bcnt = dbh.count(Book.class, "PUBLISHER=?", "06");
        puts("BOOK(Minmei Publishing): " + bcnt + " rows");

        disconnect(dbh);
    }

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

    public void sample_set_named_params() throws Exception {
        TransactedDatabase dbh = connect();

        Statement pst = new Statement("SELECT ID, NAME FROM PUBLISHER WHERE ID = :id01 OR ID = :id02");
        pst.put("id01", "04");
        pst.put("id02", "06");
        Map[] publishers = dbh.query(pst);
        puts(new TabularFormat(publishers));

        Statement bst = new Statement("UPDATE BOOK SET AUTHOR = ':author', ISSUE_DATE = !:idt WHERE AUTHOR = :author");
        bst.put("author", null);
        bst.put("idt", null);
        dbh.update(bst);

        Select sel = new Select(Book.class, "AUTHOR = :author");
        sel.put("author", ":author");
        Book[] books = (Book[]) dbh.select(sel);

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

        disconnect(dbh);
    }

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

    public void sample_set_named_params_for_joined_tables() throws Exception {
        TransactedDatabase dbh = connect();

        Class[] types01 = { Publisher.class, Book.class };
        JoinedSel sel01 = new JoinedSel(types01, "PUBLISHER.ID=:id ORDER BY BOOK.ID");
        sel01.set("id", "04");
        Map[] jtabs01 = dbh.select(sel01);

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

        Map types02 = new HashMap();
        types02.put("PU", Publisher.class);
        types02.put("BO", Book.class);
        JoinedSelM sel02 = new JoinedSelM(types02, "PU.ID=BO.PUBLISHER AND PU.ID=:id ORDER BY BO.ID");
        sel02.set("id", "04");
        Map[] jtabs02 = dbh.select(sel02);

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

        disconnect(dbh);
    }

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

    public void sample_convert() throws Exception {
        QueryRunner qr = new QueryRunner(new DefaultFilter() {

            public void setParam(int i, Object param, PreparedStatement stmt) throws SQLException {
                if (param instanceof String && ((String) param).indexOf('X') >= 0)
                    stmt.setString(i, ((String) param).replace('X', '-'));
                else
                    super.setParam(i, param, stmt);
            }

            public ResultSet wrapResultSet(ResultSet rs) {
                return new DefaultResultSetWrapper(rs) {
                    public Object getObject(int columnIndex) throws SQLException {
                        Object obj = super.getObject(columnIndex);
                        return (obj instanceof String) ? ((String) obj).replaceAll("Sex", "XXX") : obj;
                    }
                };
            }

        });

        RubbishDatabase dbh = new RubbishDatabase(new HsqldbExceptionFactory(), qr);
        dbh.setLogging(true);
        dbh.connect("jdbc:hsqldb:hsql://localhost", "sa", "");
        dbh.setAutoCommit(false);

        int rows = dbh.update("UPDATE PUBLISHER SET NAME=?, VOLUMES=?, UPDATE_DATE=?", "XXXXX", new Integer(9990), new Date());
        puts("rows=" + rows);

        Map[] publishers = dbh.query("SELECT * FROM PUBLISHER ORDER BY ID");

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

        Map[] books = dbh.query("SELECT TITLE, AUTHOR FROM BOOK WHERE PUBLISHER IN (?, ?) ORDER BY PUBLISHER, ID", "04", "06");

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

        disconnect(dbh);
    }

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

    public void sample_dao_select() throws Exception {
        TransactedDatabase dbh = connect();

        DAOFactory factory = new DAOFactory(dbh, "C:/eclipse/workspace/rubbish-db/sample/src/sample/hsqldb/dao");

        BookDAO bookDAO = (BookDAO) factory._new(BookDAO.class);

        puts("----------");

        Book book = bookDAO.selectByPK("06", "002");
        puts(book);

        puts("----------");

        Book[] books01 = bookDAO.selectByPublisher("04");

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

        puts("----------");

        List books02 = bookDAO.selectById("001");

        for (int i = 0; i < books02.size(); i++)
            puts(books02.get(i));

        puts("----------");

        Book[] books03 = bookDAO.selectByPublisherAuthorIssue_date("05", "Roald Dahl", null);

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

        puts("----------");

        disconnect(dbh);
    }

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

    public void sample_dao_update() throws Exception {
        TransactedDatabase dbh = connect();

        DAOFactory factory = new DAOFactory(dbh, "C:/eclipse/workspace/rubbish-db/sample/src/sample/hsqldb/dao");

        BookDAO bookDAO = (BookDAO) factory._new(BookDAO.class);

        puts("----------");

        Map set01 = new HashMap();
        set01.put("title", "XXX");
        set01.put("issue_date", null);

        int rows01 = bookDAO.updateByPK("06", "002", set01);

        puts("rows=" + rows01);
        Book book = bookDAO.selectByPK("06", "002");
        puts(book);

        dbh.rollback();

        puts("----------");

        Map set02 = new HashMap();
        set02.put("title", "XXXXX");
        set02.put("create_date", new ZDate("2006-10-10"));

        Integer rows02 = bookDAO.updateByPublisher("04", set02);

        puts("rows=" + rows02);
        Book[] books01 = bookDAO.selectByPublisher("04");

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

        dbh.rollback();

        puts("----------");

        bookDAO.updateById("001", new MapHandler() {
            public void handle(Map row) {
                row.put("author", "Taro Yamada");
                row.put("update_date", new ZDate("2006-10-10"));
            }
        });

        List books02 = bookDAO.selectById("001");

        for (int i = 0; i < books02.size(); i++)
            puts(books02.get(i));

        dbh.rollback();

        puts("----------");

        int rows03 = bookDAO.updateByPublisherAuthorIssue_date("05", "Roald Dahl", null, new MapHandler() {
            public void handle(Map row) {
                row.put("title", "Daimoku");
                row.put("author", null);
            }
        });

        puts("rows=" + rows03);
        Book[] books03 = bookDAO.selectByPublisherAuthorIssue_date("05", null, null);

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

        dbh.rollback();

        puts("----------");

        disconnect(dbh);
    }

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

    public void sample_dao_delete() throws Exception {
        TransactedDatabase dbh = connect();

        DAOFactory factory = new DAOFactory(dbh, "C:/eclipse/workspace/rubbish-db/sample/src/sample/hsqldb/dao");

        BookDAO bookDAO = (BookDAO) factory._new(BookDAO.class);

        puts("----------");

        int rows01 = bookDAO.deleteByPK("06", "002");
        puts("rows=" + rows01);
        Book book = bookDAO.selectByPK("06", "002");
        puts(book);

        dbh.rollback();

        puts("----------");

        Integer rows02 = bookDAO.deleteByPublisher("04");
        puts("rows=" + rows02);
        Book[] books01 = bookDAO.selectByPublisher("04");

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

        dbh.rollback();

        puts("----------");

        bookDAO.deleteById("001");
        List books02 = bookDAO.selectById("001");

        for (int i = 0; i < books02.size(); i++)
            puts(books02.get(i));

        dbh.rollback();

        puts("----------");

        int rows03 = bookDAO.deleteByPublisherAuthorIssue_date("05", "Roald Dahl", null);
        puts("rows=" + rows03);
        Book[] books03 = bookDAO.selectByPublisherAuthorIssue_date("05", "Roald Dahl", null);

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

        dbh.rollback();

        puts("----------");

        disconnect(dbh);
    }

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

    public void sample_dao_insert() throws Exception {
        TransactedDatabase dbh = connect();

        DAOFactory factory = new DAOFactory(dbh, "C:/eclipse/workspace/rubbish-db/sample/src/sample/hsqldb/dao");

        BookDAO bookDAO = (BookDAO) factory._new(BookDAO.class);

        Publisher publisher = new Publisher();
        publisher.setId("07");
        publisher.setName("O'Reilly Media, Inc");
        publisher.setVolumes(new Integer(2));
        publisher.setUpdate_date(new Date());
        publisher.setCreate_date(new Date());

        Book book01 = new Book();
        book01.setPublisher("07");
        book01.setId("000");
        book01.setTitle("Eclipse");
        book01.setAuthor("IBM");
        book01.setIssue_date(null);
        book01.setUpdate_date(new Date());
        book01.setCreate_date(new Date());

        Book book02 = new Book();
        book02.setPublisher("07");
        book02.setId("001");
        book02.setTitle("Java");
        book02.setAuthor("James Gosling");
        book02.setIssue_date(new ZDate("2001-06-01"));
        book02.setUpdate_date(new Date());
        book02.setCreate_date(new Date());

        puts("----------");

        dbh.insert(publisher);

        int rows01 = bookDAO.insert(book01);
        puts("rows=" + rows01);
        dbh.select(Book.class, "PUBLISHER=? ORDER BY ID", "07", new ObjectHandler() {
            public void handle(Object row) {
                puts(row);
            }
        });

        dbh.rollback();

        puts("----------");

        dbh.insert(publisher);

        Book[] books01 = { book01, book02 };

        Integer rows02 = bookDAO.insert(books01);
        puts("rows=" + rows02);
        dbh.select(Book.class, "PUBLISHER=? ORDER BY ID", "07", new ObjectHandler() {
            public void handle(Object row) {
                puts(row);
            }
        });

        dbh.rollback();

        puts("----------");

        dbh.insert(publisher);

        List books02 = new ArrayList();
        books02.add(book01);
        books02.add(book02);

        bookDAO.insert(books02);
        dbh.select(Book.class, "PUBLISHER=? ORDER BY ID", "07", new ObjectHandler() {
            public void handle(Object row) {
                puts(row);
            }
        });

        dbh.rollback();

        puts("----------");

        disconnect(dbh);
    }

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

    public void sample_dao_count() throws Exception {
        TransactedDatabase dbh = connect();

        DAOFactory factory = new DAOFactory(dbh, "C:/eclipse/workspace/rubbish-db/sample/src/sample/hsqldb/dao");

        BookDAO bookDAO = (BookDAO) factory._new(BookDAO.class);

        puts("----------");

        int rows01 = bookDAO.countByPK("06", "002");
        puts("rows=" + rows01);

        puts("----------");

        Integer rows02 = bookDAO.countByPublisher("04");
        puts("rows=" + rows02);

        puts("----------");

        int rows03 = bookDAO.countById("001");
        puts("rows=" + rows03);

        puts("----------");

        int rows04 = bookDAO.countByPublisherAuthorIssue_date("05", "Roald Dahl", null);
        puts("rows=" + rows04);

        puts("----------");

        disconnect(dbh);
    }

}