Object to DB tables mapping implementation

[abosabry_2005]

Hi there,
I've always been wondering what's the best way to model a one-to-many relation between two Java classes and save them into a DB.

For example,
Imagine we have two classes Person and Address where a person might have more than one address, we know that the best way to model this in a relational DB, would be to have two tables
Person(personID,Name) and Address(addressID,personID,city,country, addrType)

So we save as many addresses as we want and associate them with the person ID.

I want to implement this in objects, for example have a person1 as an instance of a Person class, and whenever I want to add an address I instantiate an address1 of class Address and perhaps have a method

person1.addAddress(address1);

I wonder how this could be coded.I have lots of thoughts about implementing this but all are out of the OO scope, and I don't want to implement it using Entity classes or else, I just want thoughts and perhaps an example or anything that could clear my thoughts about it.

[thines01]

Your Person class will need to have a Vector of addresses.
When/if you call add address, it adds one to the vector,

[abosabry_2005]

First of all thanks thines01 for your answer;
And yes I did think of having an ArrayList to store addresses, that's not my problem, the problem is how do I map the data from objects to be rows in DB tables.
Take for example:

homeAddress = new Address('Washington','USA');
busineesAddress = new Address('California','USA');

Person me = new Person('Mike');
me.addAddress(homeAddress);
me.addAddress(businessAddress);

All I want to know, how do I save the object me of class Person into it's corresponding DB table PersonTable and the two other objects homeAddress and busineessAddress of class Address and still the two be associated with each other in an object oriented way for instance maybe have another method in Person called
commit(), that when called saves to DB. I wanna know the best way to code this.

[thines01]

The database should contain an ID in the user table to tell it which addresses are associated OR the database can have a foreign key back to the user table OR you can have a table that does nothing but contain a foreign-key to the user table mapped to a foreign-key in the address table so you can still map users to addresses.

Here is a technique to save:

Let's say you have an ID field both on the user and the address classes.

1) Open a SQL transaction (if allowed)
2) Update the user, if ID exists and data has changed / Insert user if ID is empty
- If inserted, re-select to get the db generated ID
3) for each address with an address ID, update (if changed)
4) for each address without an address ID, insert
5) Commit the transaction

[abosabry_2005]

My problem is not with the DB implementation, it's with the Java implementation for instance

class Person{
private String name;
private String id;
private ArrayList addresses;

  public Person(String name)
  {
    this.name = name;
    addresses = new ArrayList();
    generateID();
  }
  
  public void addAddress(Address adres)
  {
     addresses.add(adres);
  }
  
  private void generateID()
  {
     //Auto-generated ID
     //id=???;
  }
  
  public void savePerson()
  {
     //Save the person into the DB table Person
     
     // My problem is here how do I take the addresses ArrayList 
     //and in some how I could save it to the DB associated with the Person 
     //Using his ID in an object oriented way
     
     //for(int i=0;i<addresses.size();i++)
     //{
     //   how do I save the address associated with the Person
     //}
  }

  public Address getAddress(String type)
  {
     //Fetch address from the DB and return it as an Address Object
  }
}

And here is also Address

class Address
{
  private String city, country, type;

  public Address(String city, String country, String type)
  {
     //
  }
}

[thines01]

You must know the ID of the person.
If your database has IDs for the user or for the address + user,
you must know the ID and update the data based on that ID.

It really depends on the layout of your database.
I cannot tell you more than that without actually seeing the schema.

To insert, you will use the "INSERT INTO" statement.
to update, you will use the "UPDATE" statement.

Build SQL statements as strings based on the user ID or address ID.

You will need an open connection to the database.
http://en.wikipedia.org/wiki/Java_Database_Connectivity

I'm not sure which part is the problem at this point.
Do you need to know how to connect to the database for the update or insert?

Either way, I would not recommend putting database access code INSIDE the actual person class.
I would create a master as a collection of person objects and an archiver class for controlling the loading and storing of Person objects. That way, you'll have visibility to all necessary records without dealing with the one.

[abosabry_2005]

I would create a master as a collection of person objects and an archiver class for controlling the loading and storing of Person objects. That way, you'll have visibility to all necessary records without dealing with the one.

This part here is what I'm interested in.