Tutorial 02

Foreign keys, joins and selects

Here is a new XML example. Note that now we have two tables, and table ARTICLE, has a foreign key, which is MEMBER_ID, and it is targeting the MEMBER_ID from the MEMBER table.
Pretty soon, you will be able to do this between different databases, this will be possible by saying which driver and string connection to use for each query, and as you will see, all the joins are not real joins, so this is possible and nice.
  <table name="MEMBER">
    <column name="MEMBER_ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="LOGIN_NAME" required="true" size="99" type="VARCHAR"/>
    <column name="PASSWORD_VALUE" size="99" type="VARCHAR"/>
    <column name="NAME" required="true" size="255" type="VARCHAR"/>
    <column name="EMAIL" size="255" type="VARCHAR"/>
    <column name="CONFIRM_VALUE" size="99" type="VARCHAR"/>
        <unique-column name="LOGIN_NAME"/>

  <table name="ARTICLE">
    <column name="ARTICLE_ID" required="true" primaryKey="true" type="INTEGER"/>
    <column name="TITLE" required="true" size="255" type="VARCHAR"/>
    <column name="SUBTITLE" required="true" type="TEXT"/>
    <column name="BODY" required="true" type="TEXT"/>
    <column name="DAY" type="TIMESTAMP"/>  
    <column name="MEMBER_ID" required="true" type="INTEGER"/>
    <column name="SPECIAL" type="BOOLEAN"/>

    <foreign-key foreignTable="MEMBER">
      <reference local="MEMBER_ID" foreign="MEMBER_ID"/>

This is probably easy to understand again.
So, after generating the classes for this XML, we can insert rows, with foreign keys:

      Member member = new Member();
      member.setName("Paulo Silveira");
      member.setConfirmValue("stupid field");

      // getting the ID of this member
      // we need to find a better way than doing this:
      Member paulo = MemberBroker.selectByName("Paulo Silveira").iterator().next(); // ok, not safe

      Article article = new Article();

      article.setTitle("What a title!");
      article.setBody("long text goes here");
      article.setMemberId(paulo.getMemberId()); // important line!!! foreign key


Maybe, instead of setMemberId(Integer), we should have setMemberId(Member), what do you think? Should we have both?
Now we can get all articles that "Paulo Silveira" has written:
	Collection c;
	Iterator i;
	c = MemberBroker.selectByName("Paulo Silveira");
	Member paulo = c.iterator().next(); // ok, not safe again

	// now, the "join", if we can call this as a join:
	// so, you can read this line as:
	// get all articles, that has the same member id as paulo member id
	c = ArticleBroker.selectByMemberId(paulo.getMemberId());

	i = c.iterator();
	while (i.hasNext()) {
		Article article = (Article) i.next();

NOTE: in the next tutorial, we will see that we can do any kind of queries, not only the ones that are selectBy[FIELD_NAME]().
As you can see here, or by the generated javadoc, inside each [TABLE+NAME]Broker class, you have one select method for each field, and also a rowCount() method.