// +------------------------------------+ +------------------------------------+
// |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();
}
}
