JPA: Secondary tables +optimistic locking

[Morten Franorge]

Hi,

This post is a duplicate of a post at: http://forums.java.net/jive/thread.jspa?threadID=22135&tstart=15
As I haven't received any answers there, I'm trying my luck here.

Let's say I have to tables: EMPLOYEE(emp_id, name, version) and ADDRESS(adr_id, street, emp_id, version), both have a version field.

I have the following Employee-object, using the secondarytable annotation to get the address fields as well:

@Entity
@SecondaryTable(name="ADDRESS", pkJoinColumns=@PrimaryKeyJoinColumn(name="EMP_ID"))

public class Employee{
@Id
private String emp_id;

@Column(table="street")
private String street;

private String name;

...
}

QUESTION 1: How do I handle multiple version fields? (one for each table, assuming that the address table might be updated in other ways that through the employee-object)

QUESTION 2: what if the relation was the other way around, with the following database:
EMPLOYEE(emp_id, name, version, adr_id)
ADDRESS(adr_id, street, version)
Is there a way to solve this, without making the Address table the primary table, and the employee the secondary?

QUESTION 3: what if the relation was set up through a mapping table, giving the following database:
EMPLOYEE(emp_id, name, version)
ADDRESS(adr_id, street, version)
EMP_ADR(emp_adr_id, emp_id, adr_id)

[Edvins Reisons]

I would say get the type of relationship clear first: option 3 is for a many-to-many, Option 2 can handle one-to-many and one-to-one, and Option 1 looks like a single entity (which is not much different from one-to-one )

[Morten Franorge]

Originally posted by Edvins Reisons:
I would say get the type of relationship clear first: option 3 is for a many-to-many, Option 2 can handle one-to-many and one-to-one, and Option 1 looks like a single entity (which is not much different from one-to-one )

Aaah. You would think so, but not if your database developer makes the most dynamic database in the world.

In both cases the database SUPPORTS manytomany/one-to-many, but in reality all the relationships are one-to-one.
[ January 29, 2007: Message edited by: Morten Fra Norge ]

[Edvins Reisons]

Check with the spec to be sure, but to me, it only makes sense to have one version field per entity. Two versions - two entities. Plus one-to-one relationship means Option 2. And a secondary table does not fit in here.
[ January 29, 2007: Message edited by: Edvins Reisons ]

[Morten Franorge]

Originally posted by Edvins Reisons:
Check with the spec to be sure, but to me, it only makes sense to have one version field per entity. Two versions - two entities. Plus one-to-one relationship means Option 2. And a secondary table does not fit in here.

[ January 29, 2007: Message edited by: Edvins Reisons ]

Well the problem is that the people making the database has a lot of tables, typically one for person and one for address, joined by a third..etc.

The people making the domain model wants less objects. Since one person only has one address, they want to have ONE object containing both. (Avoid navigating across several objects to retrieve one piece of data).

I'm trying to find a good strategy to bridge this gap. I'm slowly starting to realize that I might have to persuade the database people to make the data model less dynamic, convincing them that we don't have to support all eventualities...

[Mark Spritzler]

So the database people want you to make your Java Object model less OO.

The purpose of ORM of course is so that Database models can model the best way for a database, and Java developers can create classes that are best modeled in OO best practices. So an Orm bridges that gap. It also means that you will find you will have more Java objects than database tables. It is more OO.

Mark

[Morten Franorge]

Originally posted by Mark Spritzler:
So the database people want you to make your Java Object model less OO.

The purpose of ORM of course is so that Database models can model the best way for a database, and Java developers can create classes that are best modeled in OO best practices. So an Orm bridges that gap. It also means that you will find you will have more Java objects than database tables. It is more OO.

Mark

Agree, but the database people have also introduced dates in the join tables.
So: PERSON may have many ADDRESSes at the same time, however not all PERSON-ADDRESS relations in the PERS_ADDR table are valid. The PERS_ADDR-table is defined with valid_from and valid_to.

PERS_ADDR(id (PK), pers_id(fk), addr_id(fk), valid_from, valid_to)

The only way I see to model this, is introducing a PersonAddress-object, which really isn't OO at all.... :-(

[Mark Spritzler]

Originally posted by Morten Fra Norge:


Agree, but the database people have also introduced dates in the join tables.
So: PERSON may have many ADDRESSes at the same time, however not all PERSON-ADDRESS relations in the PERS_ADDR table are valid. The PERS_ADDR-table is defined with valid_from and valid_to.

PERS_ADDR(id (PK), pers_id(fk), addr_id(fk), valid_from, valid_to)

The only way I see to model this, is introducing a PersonAddress-object, which really isn't OO at all.... :-(

You can still map it with a date range. The particulars, I don't recall of the top of my head, but it is possible. And sometimes when a join table has additional information about the join, a PersonAddress object is very OO and necessary.

Mark