Skip to content
Print

An Easier Java ORM: Relations

31
Jul
2007

Per request in a comment on the previous article, I’ve decided to devote this post to the subject of relations and how they work in ActiveObjects.

There are two types of relations in database design: one-to-many and many-to-many. In a one-to-many relation, multiple rows in one table usually have a reference to a single row in another table (or quite possibly the same table, depending on the design). As a simple example, assume with me that all tapeworms reproduce solely asexually (which isn’t exactly true, but that’s beside the point). Thus, a single tapeworm would have dozens of offspring. The proud parent tapeworm would have a one-to-many relation to its children (if you could refer to them as such). Another, less parasitic example would be people living in a city. The city would have a one-to-many relation to the thousands of people living within its borders.

One-to-many relations are expressed in ActiveObjects about as simply as the concept can be expressed in words:

public interface City extends Entity {
    public String getName();
    public void setName(String name);
 
    @OneToMany
    public Person[] getOccupants();
}
 
public interface Person extends Entity {
    public String getFirstName();
    public void setFirstName(String firstName);
 
    public String getLastName();
    public void setLastName(String lastName);
 
    public City getCity();
    public void setCity(City city);
}

The “people” table (assuming we’re using the pluralizing name converter) would contain fields “id”, “firstName”, “lastName”, and “cityID”, with a foreign key constraint on cityID ensuring it matches an existing “cities.id” value. The “cities” table would only contain “id” and “name” fields.

The real magic occurs when the dynamic proxy handler attempts to field a call to the getOccupants() method. One of the first thing the method does (after checking for a defined implementation) is look for a relation tagging annotation. In this case, it finds @OneToMany. The handler then looks at the return type for the method, extracts the table name for the Person entity class, and constructs a query to return any people which have a cityID equaling the appropriate id.

If there were more than one field in Person which returned a City, each field would be checked for the relation. Thus, minimally the SQL executed would look like this:

SELECT DISTINCT OUTER.ID FROM (SELECT ID FROM people WHERE cityID = ?) OUTER

The outer/inner query construct is used to allow the UNIONing of multiple SELECT results corresponding with different fields in the people table. With a trivial sub-query like the one given, most database engines will optimize the SQL down to a single, highly performant query.

Many-To-Many

Many-to-many relations are even cooler than one-to-many(s). This is where you begin to see complex mappings such as the (usually standard) two-parent to many children relations in families. Another example might be user permissions. There are several user permission types, each of which could map to an arbitrary number of users. A straight one-to-many mapping doesn’t work here, since neither “end” of the relation refers to a single entity. Thus, we have a many-to-many relation.

In database design, many-to-many relations are almost always expressed using an intermediary table. Thus, if we were to map authors to books (allowing for co-authorships), we would have to create a design with three tables: authors, books and authorships, which solely handles the mapping between author and book. Here’s how this would look in ActiveObjects:

public interface Author extends Entity {
    public String getName();
    public void setName(String name);
 
    @ManyToMany(Authorship.class)
    public Book[] getBooks();
}
 
public interface Authorship extends Entity {
    public Author getAuthor();
    public void setAuthor(Author author);
 
    public Book getBook();
    public void setBook(Book book);
}
 
public interface Book extends Entity {
    public String getName();
    public void setName(String name);
 
    @ManyToMany(Authorship.class)
    public Author[] getAuthors();
}

The code is very similar to the @OneToMany example, with the exception that here we have to provide the class for the intermediary “mapping table”. Once again, the dynamic proxy will find the @ManyToMany annotation, and using the table name for the intermediary table, derive a query relating books to authors based on any relevant mapping fields within Authorship.

Note: while the example given does use the @ManyToMany annotation on both “ends” of the relation, this isn’t required. The annotation merely informs the proxy handler on how to deal with the method call (as well as ensures the schema generator ignores the method signature).

Comments

  1. When using @OneToMany relations, how one can specify, what is the field with foreign key? I need this when e.g. have Person.class and Event.class and Event.class has fields for 3 persons. So in Event.class I have :

    public Person getUser1();
    public void setUser1(Person person);

    public Person getUser2();
    public void setUser2(Person person);

    public Person getUser3();
    public void setUser3(Person person);

    But not sure how to specify relationship from Person.class(@OneToMany) for each user type.

    Thanks for info.
    David

    David Marko Tuesday, August 7, 2007 at 1:02 am
  2. Each of the user* fields will have a foreign key attached to it. In the person class, you would do something like this:

    @OneToMany
    public Event[] getEvents();

    However, this would pull back *all* related events, matching the userID against user1, user2 *and* user3. I’m not sure this is exactly what you want…

    If you need to do a more complex mapping, you should use the @Ignore annotation and implement the actual query inside a defined implementation. Thusly:

    @Ignore
    public Event[] getEventsForUser1();

    // PersonImpl.java
    // …
    public Event[] getEventsForUser1() throws SQLException {
    return person.getEntityManager().find(Event.class, Query.select().where(“user1ID = ?”, person.getID()));
    }
    // …

    Daniel Spiewak Tuesday, August 7, 2007 at 12:05 pm
  3. Oh, I just committed a slightly easier syntax (in Person):

    @OneToMany(“user1″)
    public Event[] getEventsForUser1();

    @OneToMany(“user2″, “user3″)
    public Event[] getEventsForOtherUsers();

    I haven’t tested it yet, but the changes were minimal to the existing code, so it should work nicely.

    Daniel Spiewak Tuesday, August 7, 2007 at 11:16 pm
  4. AO lazyness behaves very strange when looking at log console with SQL statemes, that AO issues. Loot to following code snippet and see what I can see in log from AO. It seems that SELECT statement is ssued for every accessing each object property. Why it is or do I miss something?

    Person[] p_all=manager.find(Person.class,”id>1″);
    Person p1=p_all[0];
    Person p2=p_all[1];
    Person p3=p_all[2];
    System.out.println(p1.getFirstname());

    Resource r=manager.create(Resource.class);
    r.setName(“Server SARAH”);
    r.save();

    BaseEvent b1=manager.create(BaseEvent.class);
    b1.setResource(r);
    b1.setUser1(p1);
    b1.setUser2(p2);
    b1.setUser3(p3);
    b1.setTitle(“Kontrola běhu Lotus NOtes”);
    b1.save();

    SQL statements from AO log:

    8.8.2007 21:02:21 net.java.ao.EntityManager find
    INFO: SELECT id FROM person WHERE id>1
    8.8.2007 21:02:22 net.java.ao.EntityProxy invokeGetter
    INFO: SELECT firstname FROM person WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.DatabaseProvider executeInsertReturningKeys
    INFO: INSERT INTO resource (id) VALUES (DEFAULT)
    8.8.2007 21:02:22 net.java.ao.EntityProxy invokeGetter
    INFO: SELECT name FROM resource WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.EntityProxy save
    INFO: UPDATE resource SET name = ? WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.DatabaseProvider executeInsertReturningKeys
    INFO: INSERT INTO baseEvent (id) VALUES (DEFAULT)
    8.8.2007 21:02:22 net.java.ao.EntityProxy invokeGetter
    INFO: SELECT resourceID FROM baseEvent WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.EntityProxy invokeGetter
    INFO: SELECT user1ID FROM baseEvent WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.EntityProxy invokeGetter
    INFO: SELECT user2ID FROM baseEvent WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.EntityProxy invokeGetter
    INFO: SELECT user3ID FROM baseEvent WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.EntityProxy invokeGetter
    INFO: SELECT title FROM baseEvent WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.EntityProxy save
    INFO: UPDATE baseEvent SET resourceID = ?,user1ID = ?,user2ID = ?,user3ID = ?,title = ? WHERE id = ?

    David Marko Wednesday, August 8, 2007 at 1:12 pm
  5. The last batch of SELECTs seems really odd. There should be a SELECT statement for the first line, the third line (getFirstName()), an INSERT for the create(), and two UPDATEs. Is there any other (possibly concurrent) code happening here?

    As for the lazy-loading… Yeah, it will execute a SELECT for any non-cached property. By default, it does cache any values loaded on the initial find() eg:

    Person[] people = manager.find(Person.class, Query.select(“*”).where(“id > ?”, 1));
    people[0].getFirstName(); // no SELECT will be executed here since the value is already cached

    You don’t really see the advantages of the constant lazy-loading in a short running application. In fact, for anything which doesn’t hit the properties several times and/or have large result sets, lazy-loading is a bad idea (which is why I added the Query.select(“*”) mechanism). However, when you’ve got a long running application, a webapp for instance, it really starts to save on performance and memory over time. Granted, in the short run it is more query intensive (which is annoying), but it buys you a lot in that it only grabs the values it needs as it needs them, and there’s never any SELECT * FROM blah JOIN blah2.

    Daniel Spiewak Wednesday, August 8, 2007 at 5:03 pm
  6. Possibly, there should be some mechanism to deactivate the lazy loading, or at least override it in a less clumsy fashion than the Query.select(“*”). What do you think?

    Daniel Spiewak Wednesday, August 8, 2007 at 5:10 pm
  7. At the beginning of the code I have:
    Person[] p_all=manager.find(Person.class,”id>1″);

    One would expect that this will load all person objects into memory. I think its very common scenario, that you load e.g. some articles with specific criteria and list all titles in browser. But current mechanism hits database for each article title, what is inefficent. I think, that primary fetched objects should be fetch immediately as one expects. But e.g. when article has relationship with autor, the author can be fetch when needed(lazy-loading).

    Current lazy-loading is very aggressive :-)

    David Marko Wednesday, August 8, 2007 at 11:50 pm
  8. Also to lazy-loading, ROR uses :include clause so developer have a full controll, or Django uses select_related() when fetching objects. Moving the lazy-load decision on developer is better way as developer knows what is the est for current situation.

    David Marko Wednesday, August 8, 2007 at 11:55 pm
  9. What is the right mapping to realize a tree structure of a single entity. Let’s say you have a Category entity. A Category can have a parent category and x-child categories.

    interface Category extends Entity {
    Category getParentCategory();

    Category[] getChildCategries();
    }

    Any thoughts are appreciated.

    johan Monday, October 29, 2007 at 8:29 am
  10. @johan

    That’s right. Though, you’re missing an annotation there to make the getChildCategories() method work:

    public interface Category extends Entity {
    public Category getParent();
    public void setParent(Category parent);

    @OneToMany
    public Category[] getChildren();
    }

    Something like that should work just fine, since a tree structure is really just a recursive one-to-many mapping

    Daniel Spiewak Monday, October 29, 2007 at 10:11 am
  11. very nice approach, but I’m a C# developer so cant really use it, shame!

    web development comp Monday, August 10, 2009 at 9:17 am
  12. How can i delete the SQL log or make it stop appearing whenever i run my program.
    The SQL log sth like above:

    SQL statements from AO log:

    8.8.2007 21:02:21 net.java.ao.EntityManager find
    INFO: SELECT id FROM person WHERE id>1
    8.8.2007 21:02:22 net.java.ao.EntityProxy invokeGetter
    INFO: SELECT firstname FROM person WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.DatabaseProvider executeInsertReturningKeys
    INFO: INSERT INTO resource (id) VALUES (DEFAULT)
    8.8.2007 21:02:22 net.java.ao.EntityProxy invokeGetter
    INFO: SELECT name FROM resource WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.EntityProxy save
    INFO: UPDATE resource SET name = ? WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.DatabaseProvider executeInsertReturningKeys
    INFO: INSERT INTO baseEvent (id) VALUES (DEFAULT)
    8.8.2007 21:02:22 net.java.ao.EntityProxy invokeGetter
    INFO: SELECT resourceID FROM baseEvent WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.EntityProxy invokeGetter
    INFO: SELECT user1ID FROM baseEvent WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.EntityProxy invokeGetter
    INFO: SELECT user2ID FROM baseEvent WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.EntityProxy invokeGetter
    INFO: SELECT user3ID FROM baseEvent WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.EntityProxy invokeGetter
    INFO: SELECT title FROM baseEvent WHERE id = ?
    8.8.2007 21:02:22 net.java.ao.EntityProxy save
    INFO: UPDATE baseEvent SET resourceID = ?,user1ID = ?,user2ID = ?,user3ID = ?,title = ? WHERE id = ?

    nla Wednesday, April 21, 2010 at 9:43 pm

Post a Comment

Comments are automatically formatted. Markup are either stripped or will cause large blocks of text to be eaten, depending on the phase of the moon. Code snippets should be wrapped in <pre>...</pre> tags. Indentation within pre tags will be preserved, and most instances of "<" and ">" will work without a problem.

Please note that first-time commenters are moderated, so don't panic if your comment doesn't appear immediately.

*
*