Nested database objects (compound objects)?

[Rick Flower]

John..

If I want to have a class that has address information and use that in a number of other classes via a foreign key scheme, is that possible and what is the best general way to do it?  Currently I've got my user class having the address info directly in the class which is not really what I want to do -- I'd rather break it out into a separate address class and put associated functionality there so it's not duplicated N times (depending on the number of other classes that contain address info)..  Overall, if that's possible, what's the best way to write the SQL to make sure the class data is put into the correct tables,etc.?   Also, suggestions on how the classes relate to each other would be great as well (e.g. one class subclassing the address class or ??) I hope this makes sense!

Thx!

[John O'Hanley]

Hi Rick,

Yes, if there is Address logic that's used in more than one place, it
makes sense to create an Address class.

Let's use the example of
Person
Company

Each of the above has an associated address. There are 2 separate
ideas:
1. how to treat that in Java (the object model)
2. how to treat that in the database (where to store the Address info)

For #1, I would simply make an Address class. It would not subclass
anything special (it would be a subclass of Object). It would be final
and immutable. The Address class would implement all of the
validations related to addresses.
References :
http://www.javapractices.com/topic/TopicAction.do?Id=29
http://www.javapractices.com/topic/TopicAction.do?Id=72


The Person and Company classes would use an Address field. To
construct a Person from a SELECT statement, just create a constructor
in the Person class which takes all the necessary data : first Person
data, and then all the pieces of the Address. At this level, whether
or not the Address data is in a separate table or not only affects the
SELECT statement - it doesn't affect the Person constructor.

The Person constructor accepts all the pieces of the Address, and
simply forwards the data to an Address constructor. You need to pay
attention to error handling here. The Person class needs to be ready
to handle any ModelCtorExceptions that may be thrown by Address.
Typically, you would simply need to append or add the error messages
generated by Address onto the ModelCtorExeption being managed by
Person.

http://www.web4j.com/web4j/javadoc/hirondelle/web4j/model/ModelCtorException.html
http://www.web4j.com/web4j/javadoc/hirondelle/web4j/model/AppException.html

For #2, the database model, you could model that according to your
taste. You could have a separate Address table, or you could
assimilate it into other existing tables.

Your Data Access Objects can talk to either style. When the Address is
separated into its own table, then you will need to double the
operations, and you should do that in a transaction. For example, one
INSERT for Person, and one for Address.

- John

[Rick Flower]

On Sunday, February 17, 2013 6:59:35 AM UTC-8, John O'Hanley wrote:

... 

The Person and Company classes would use an Address field. To
construct a Person from a SELECT statement, just create a constructor
in the Person class which takes all the necessary data : first Person
data, and then all the pieces of the Address. At this level, whether
or not the Address data is in a separate table or not only affects the
SELECT statement - it doesn't affect the Person constructor.

The Person constructor accepts all the pieces of the Address, and
simply forwards the data to an Address constructor. 

John.. Thanks for the info!  Much appreciated.. One question if you dont mind

In a situation like this with a separate Address class, what do I do with the ID's

(e.g. fId) for each of the Person class and Address class -- likely they'll have 

different ID's for each table.. Right now my address class constructor looks like:

    public Address(Id       aId,

                   SafeText aStreetAddress1,

                   SafeText aStreetAddress2,

                   SafeText aCity,

                   SafeText aState,

                   SafeText aZipCode) throws ModelCtorException {

        fId             = aId;

        fStreetAddress1 = aStreetAddress1;

        fStreetAddress2 = aStreetAddress2;

        fCity           = aCity;

        fState          = aState;

        fZipCode        = aZipCode;

        validateState();

    }

and my sample Person class constructor looks more like :

    public Person(Id       aId,

                        SafeText aName,

                        SafeText aPhone,

                        SafeText aStreetAddress1,

                        SafeText aStreetAddress2,

                        SafeText aCity,

                        SafeText aState,

                        SafeText aZipCode

    ) throws ModelCtorException {

        fId             = aId;

        fName        = aName;

        fPhone          = aPhone;

        fAddress        = new Address(aId,aStreetAddress1,aStreetAddress2,aCity,aState,aZipCode);

        validateState();

    } 

I'm specifically concerned about the use of "aId" in both the Person constructor and then the same value being handed to the Address constructor as I'm sure those values won't be the same most of the time.. Should I assume it will do the right thing or do I need to change something here?

Regarding the exceptions, I realize I need to put some logic in the Person constructor to catch any exceptions thrown by the Address class but haven't done that yet.. Thanks again!

--Rick

[Rick F.]

Also John -- since I've got a separate Address class, do I also need :

AddressAction class

AddressDAO class

?

Just wanted to make sure I'm barking up the right tree..  Thanks!

[John O'Hanley]

Hi Rick,

So, you have chosen to use two tables, one for Person and one for
Address. That's fine.

The id's passed to the 2 constructors aren't going to be the same.
They represent keys from 2 distinct tables, and their values aren't
related.

The one SQL statement used when creating a Person thing will return
fields from both the Person and Address tables.
The SQL will need to reference two *different* id's; one id for the
Person, one id for the Address.
The Person constructor will be passed *both* of the id's, the first id
for the Person, and the second id for the Address. The id for the
Address is simply forwarded to the Address constructor.

Does this make sense?

-John

[John O'Hanley]

Hi Rick,

I don't think you would need an AddressAction class, no.

But it probably makes sense to have an AddressDAO class.

As usual, the goal is to have no code repetition. So if you need to
talk to the Address table in separate areas, all in the same way, then
it makes sense to define those operations in one place.

The most robust design would use transactions. For example, when
creating a new Person by doing database INSERTs, the PersonDAO would
use a corresponding method of AddressDAO in its implementation. And,
the two INSERTs would be wrapped in a single transaction.

-John

[John O'Hanley]

Some documentation about transactions is here:

http://www.web4j.com/UserGuide.jsp#Transactions

[Rick F.]

On Saturday, March 2, 2013 9:16:19 AM UTC-8, John O'Hanley wrote:

 

The Person constructor will be passed *both* of the id's, the first id
for the Person, and the second id for the Address. The id for the
Address is simply forwarded to the Address constructor.

Thanks for the replies John.. Regarding the constructor for the Person

class, would it then look something like the following?  I'm just trying to

wrap my brain around this..  Thanks again!  I got the idea on the transaction

as that makes perfect sense to ensure an all-or-nothing approach..

 public Person(Id       aPersonId,

                      Id      anAddressId,

[John O'Hanley]

No, not quite. You have the two different id's being passed to the
constructor, but they aren't being referenced correctly later on.

More like this:

public Person(Id aPersonId,
SafeText aName,
SafeText aPhone,
/* the remaining items relate to the address
*/
Id aAddressId,

SafeText aStreetAddress1,
SafeText aStreetAddress2,
SafeText aCity,
SafeText aState,
SafeText aZipCode
) throws ModelCtorException {

fId = aPersonId;

fName = aName;
fPhone = aPhone;
fAddress = new

Address(aAddressId,aStreetAddress1,aStreetAddress2,aCity,aState,aZipCode);
validateState();
}

[Rick F.]

Thanks John.. By the way.. I guess I need to dig thru the code a bit more but who exactly calls this constructor -- is it something in the DAO or somewhere within web4j directly?  I'll do some more playing around this evening...

[Rick F.]

On Saturday, March 2, 2013 6:59:13 PM UTC-8, Rick F. wrote:

Thanks John.. By the way.. I guess I need to dig thru the code a bit more but who exactly calls this constructor -- is it something in the DAO or somewhere within web4j directly?  I'll do some more playing around this evening...

To answer my own question I think the constructor for the Person class would be called in the PersonAction.java's validateUserInput() method that calls builder.build() to construct an object which is used later by the PersonAction::attemptAdd() method.. Does that sound about right? 

[Rick F.]

One more question for you John.. 

In the the PersonAction class I changed the ID reference so it could support two ID's as needed for the construction of the People and Address classes which looks similar to the example below.. but that causes the error shown further below..

    public static final RequestParameter PERSON_ID = RequestParameter.withLengthCheck("PersonId");

    public static final RequestParameter NAME = RequestParameter.withLengthCheck("Name");

    public static final RequestParameter ADDR_ID  = RequestParameter.withLengthCheck("AddrId");

    public static final RequestParameter ADDRESS = RequestParameter.withLengthCheck("StreetAddress");

...

    @Override protected void validateUserInput() {

        fLogger.fine("Validating user input.");

        ModelFromRequest builder = new ModelFromRequest(getRequestParser());

        try {

            fModel = builder.build(Person.class, PERSON_ID, NAME, PHONE, ADDR_ID, ADDRESS, CITY, STATE, ZIP_CODE);

        }

        catch (ModelCtorException ex){

            addError(ex);

        }

    }

SEVERE: *** Unknown Parameter *** : 'Id'. Please add public static final RequestParameter field for this item to your Action.

I think I'm stuck.. Hopefully you can steer me back on track!

Thanks again!

[John O'Hanley]

Yes, it's called within the Action class.

Please refer to the documentation on the web site. These questions are
all answered clearly in the docs, and in the 3 example apps.

[John O'Hanley]

I think that has to do with an anti-hacker aspect of web4j.

All of the request parameters seen by an Action are "white-listed". If
a request parameter comes in from a web form, and it's not known to
the Action, in its list of RequestParameter fields, then an error is
thrown.

Your form still refers to "Id", and not "PersonId" and "AddrId".

[Rick F.]

Thanks John.. That clears things up -- I didn't even think about looking in the form.. Duh!  I'll also RTFM too.. Thx!

[Rick F.]

John.. I've got things close to working but ran into an issue related to the address_id that resides in the Organization table (aka Person table in your example).. It turns out to be null and it causes the problem below.. I guess I was thinking that the address-id autogenerated field created by the first insert would be available for use in the 2nd insert.. For now I've disabled transactions but saw this with transactions enabled as well.. I'm guessing I might be going about something wrong perhaps.... I'll look at it some more tomorrow...

FINE: Adding record, and returning autogenerated id : ADD_ADDRESS {

 fSqlText = INSERT INTO Address (Street_Address1, Street_Address2, City, State, Zipcode) values (?,?,?,?,?)

 Params = [1234 First St, null, Los Angeles, CA, 90067]

}

Mar 3, 2013 10:53:08 PM hirondelle.web4j.database.SqlEditor addRecord

FINE: Adding record, and returning autogenerated id : ADD_ORGANIZATION {

 fSqlText = INSERT INTO Organization (address_id, name, phone, admin_name, admin_phone, admin_email) VALUES (?,?,?,?,?,?)

 Params = [null, MyOrganization, 310-123-9409, Joe Blow, 310-123-0239, foo@bar.com]

}

Mar 3, 2013 10:53:08 PM hirondelle.web4j.Controller logAndEmailSeriousProblem

SEVERE: TOP LEVEL CATCHING Throwable

Mar 3, 2013 10:53:08 PM hirondelle.web4j.Controller logAndEmailSeriousProblem

SEVERE: 

Error for web application Electricity Tracker/4.8.0.0.

*** Messages : + [Cannot execute edit. Error Id code : 0 org.postgresql.util.PSQLException: ERROR: column "address_id" is of type bigint but expression is of type character varying

  Hint: You will need to rewrite or cast the expression.

  Position: 98 ADD_ORGANIZATION {

 fSqlText = INSERT INTO Organization (address_id, name, phone, admin_name, admin_phone, admin_email) VALUES (?,?,?,?,?,?)

 Params = [null, MyOrganization, 310-123-9409, Joe Blow, 310-123-0239, foo@bar.com]

}

] Has Been Displayed  : false ***

--------------------------------------------------------

[John O'Hanley]

Are the database and its driver configured to actually return the id
after the insert?

web4j has the HasAutoGeneratedKeys setting in web.xml (see User Guide)
to flag whether or not this is turned on.

-John

[Rick F.]

Thanks John.. I hadn't even thought about that.. I am using PostgreSQL and did

notice last night that I hadn't set the stringtype="unspecified" in the context.xml

inside the META-INF for that driver but it didn't change the outcome.. I will try this

tonight.. I had assumed I had a code issue but perhaps not .. Of course it's too

early to tell .. 8-)

[Rick F.]

Ok.. I checked my web.xml and its turned on:

    <init-param>  

      <description>

        Value : (true | false), ignores case.

        Indicates if the database and driver support the method

        Connection.prepareStatement(SqlText, Statement.RETURN_GENERATED_KEYS).

      </description>

      <param-name>HasAutoGeneratedKeys</param-name>

      <param-value>true</param-value> 

    </init-param> 

Any other suggestions to look into?

[John O'Hanley]

Have you verified that the database/driver is indeed returning the id
of the inserted item?

- John

[Rick F.]

On Wednesday, March 6, 2013 2:17:33 PM UTC-8, John O'Hanley wrote:

Have you verified that the database/driver is indeed returning the id
of the inserted item?

 

No.. Is there a good way to verify that?  I'll check the logs again of a run and see.. I recall seeing the insert statements but don't recall seeing anything showing a returned ID.

I'll look at the docs again to see if there's a web.xml setting to control the output of that or perhaps it's related to log verbosity..

[Rick F.]

John.. I'm currently using the Postgres JDBC driver.. Is it possible that Web4J is not sending the correct command to the JDBC driver?  In poking around elsewhere, it appears that the method is different for the generic JDBC driver vs the Postgres one.. Below is what is currently logged when I run my app... I could try the generic JDBC driver....?  

Mar 6, 2013 8:57:01 PM hirondelle.web4j.database.DbConfig logDatabaseAndDriverNames

CONFIG: 

  Database Id passed to ConnectionSource: DEFAULT

  Database name: PostgreSQL/9.2.1

  Database driver name: PostgreSQL Native Driver/PostgreSQL 9.1 JDBC4 (build 901)

  Database URL:  jdbc:postgresql://localhost:5432/mydatabase?useServerPrepStmts=false

  Supports scrollable ResultSets (TYPE_SCROLL_INSENSITIVE, CONCUR_READ_ONLY): true

Mar 6, 2013 8:57:01 PM hirondelle.web4j.database.DbConfig initDataLayer

CONFIG: Success : Database named 'DEFAULT' detected OK.

[Rick F.]

I don't know if I posted the transcript of the inserts but here it is if it will shed some light on the issue :

Mar 6, 2013 8:57:25 PM hirondelle.web4j.database.SqlEditor addRecord

FINE: Adding record, and returning autogenerated id : ADD_ADDRESS {

 fSqlText = INSERT INTO Address (Street_Address1, Street_Address2, City, State, Zipcode) values (?,?,?,?,?)

 Params = [1234 First Ave, null, Los Angeles, CA, 90067]

}

Mar 6, 2013 8:57:25 PM hirondelle.web4j.database.SqlEditor addRecord

FINE: Adding record, and returning autogenerated id : ADD_ORGANIZATION {

 fSqlText = INSERT INTO Organization (address_id, name, phone, admin_name, admin_phone, admin_email) VALUES (?,?,?,?,?,?)

 Params = [null, Organization, 310&#045;123&#045;9409, Joe Blow, 310&#045;123&#045;0239, foo&#064;bar&#046;com]

}

Mar 6, 2013 8:57:25 PM hirondelle.web4j.Controller logAndEmailSeriousProblem

[John O'Hanley]

The second part says that the address_id is null.

This isn't really a web4j question. This is about knowing your
database and your driver, how to use it and configure it properly.

[Rick F.]

Is it safe to assume that if an auto-generated ID is to be returned that it would show up in the log provided?  I'm going to assume that's the case unless I hear otherwise... I will continue to look into the issue as I can't proceed without solving this.. I'll post a reply with the ultimate solution as it may be of interest to someone else perhaps..

[John O'Hanley]

Correct, yes.

[Rick F.]

Ok.. finally had some time to look into the problem.. Loaded up the web4j code as part of my project so I could debug into it.. Verified that the address id of the newly inserted address record was returned ok.  Anyway, I changed things a bit after I figured out what was going wrong.. The main thing I had wrong was that the ID of the newly inserted address record was being returned by Db.add() but being tossed out.. I did not realize it returns the ID of the record.. So I had to add some logic to squirrel away the ID into the organization class as shown below so that when it was time to do the organization add that the address ID was present :

void add(Address anAddress) throws DAOException {

        Id addressId = Db.add(ADD_ADDRESS, baseParamsFrom(anAddress));

        anAddress.setId(addressId);

    }

I then had to adjust the baseParamsFrom() method in the Organization class to put the above saved ID into the right format (an integer -- not a string) like that shown below: 

    // PRIVATE

    private Object[] baseParamsFrom(Organization aOrganization){

        return new Object[]{

                aOrganization.getAddress().getId().asInteger(),

                aOrganization.getOrgName(),

                aOrganization.getPhone(), aOrganization.getAdminName(), aOrganization.getAdminPhone(),

                aOrganization.getAdminEmail()

        };

    }

That was more or less the main issues.. I then tried to follow what you suggested and changed the OrganizationDAO to use the AddressDAO as follows (as opposed to using both DAO's inside the Action class) :

    void add(AddressDAO anAddressDAO, Organization anOrganization) throws DAOException {

        anAddressDAO.add(anOrganization.getAddress());

        Db.add(ADD_ORGANIZATION, baseParamsFrom(anOrganization));

    }

I am currently adding the wrappers in the above method for transaction processing which is currently missing.. Anyway, works like a charm!  Thanks for the help and being patient with me.. I'm sure I'll have other dumb questions.. 

[Rick F.]

By the way.. If you have an idea of how to do a transaction on this sort of setup, let me know .. I'm getting a null pointer exception because I do not have an address id to use when it's time to do the 2nd insert for the organization.. I'm thinking that I could use DbTx but am not sure what to use for the first arg -- a connection.. Is there a way to get it from the Db class reference already used in that area or ??

 

 

[Rick F.]

Nevermind.. Found it.. 

BuildImpl.forConnectionSource().getConnection()

Seems to work ok..