This is all about Hibernate 3 and annotations along the lines of EJB 3 persistence. Oracle 10g is my database.

I've got Item class that mapped to 2 tables (ITEM and SKU). Both tables have primary key constaints defined. I use a sequence to provide item_id that satisfies the primary key constraint for ITEM table (see code below). However, I couldn't find a way to let Hibernate know that another primary key exists and needs to be provided when an Item object gets persisted. As a result, when I try to persist it, Hibernate doesn't supply anything for sku_id and I get a constraint violation exception on the database side.

Here are some questions that come to mind:
1. Is it at all possible to persist an object that spans multiple tables?
2. If so, what are the most important restrictions, if any, such as support for sequences, primary keys in the secondary tables, and the like?

Here are a few code fragments from Item.java:
@Entity
@Table(name = "item")
@SequenceGenerator(name = "ItemSeq", sequenceName = "sq_item")
@SecondaryTable(name = "sku", join = @JoinColumn(name = "item_sysid", referencedColumnName = "item_sysid"))
public class Item implements Serializable {

@Id(generate = GeneratorType.SEQUENCE, generator = "ItemSeq")
@Column(name = "item_sysid")
public Integer getItemId() {
return this.itemId;
}
public void setItemId(Integer itemSysid) {
this.itemId = itemSysid;
}

You could use a trigger. That's what I found myself having to do. Unfortunately, I cannot find an annotation way to do this. As a result, we have 2 object models. One which resembles our database, and one which denormalizes the data across 3 tables into one class. All of this is due to the limitations of either @SecondaryTable or the documentation not stating how to assign sequences to @SecondaryTable. I'm disappointed in having to design a less efficient system, but fortunately, I use fast enough servers that no one notices. : (


Here's an example of my trigger for Oracle9i

[/code]

the secondary tables share the same primary key of the primary table, hence no additional generation strategy is needed

emmanuel, you're assuming we don't wish to write to the secondary table. Your statement is only valid in a read only situation.

picture the following relationship: A user can have multiple phone numbers.
Imagine a users table and a phone numbers table:

create table users(
user_id int PRIMARY KEY,
username varchar2(50)
....
);

create table phone_numbers(
phone_number_id int PRIMARY KEY,
user_id int references users(user_id),
phone_number varchar2(25),
active_date TIMESTAMP,
...
);

What if I want a class with the following fields:
public class User{
int id;
String username;
String homePhoneNumber;
long activationDate.
....
//getters and setters below.
}

I want to create a User java object and persist it. How do you figure that no additional generation strategy is needed? The phone number row is to be created at the same time as the the user table row. How can I have the secondary table row created with a sequence?


A join table would be very inappropriate because in this example, a phone number cannot be assigned to more than one person (according to the business rules of this contrived example as well as the real-world example I'm using hibernate in). I don't want to create phone number objects since everything logically belongs in a single, easy-to-use class.

Breaking the class apart to mirror the DB contents (a PhoneNumber class attached to the User class) would expose my database schema to my object model, which will provide inappropriate coupling, creating an unnecessary challenge if the underlying schema ever changes.

One of the goals of an ORM solution is to allow us to have a good, normalized database schema mapping to an independent object model optimized for our specific need.

I'd appreciate your insight into this. We have a massive adoption of hibernate that's being hindered by the sequence restrictions.

No I'm not.
There is a reason why your DBA has chose to define the link between user and phone number as 2 entity tables and not one (plus with a FK column and not the same PK value).
The proper way to map is is through an association (actually in your case a onetoMany since there is no unique cosntraint on phone_number.user_id)

Emmanuel,

I understand that I can do a many to one mapping, but doesn't that require creating a phone number class? If I understand you correctly, you're telling us we must change our domain model, however, I think a simplified domain model is much more optimal and I'm trying to accomplish it using SecondaryTables, but I believe I'm limited by the ability to add Schemas to the SecondaryTables.

In this case, we want to decouple our domain model from our database. We want to map one class to many tables. The highly normalized database structure is what the DBA wanted. We're handing an API to the user which greatly simplifies the structure and gives us the freedom to rearrange the schema without modifying the API.

So yes, a user can have more than one phone number, but imagine that adding the 2nd phone number is a separate use case. Let's add an address table. What we wish to do is have a separate object that gathers multiple addresses or phone numbers.

The vast majority of the time, our partners will be importing data using a single class that contains

public class User{
String username;
....
String home_phone;
...
String cell_phone;
String address;

}


We wish to simplify the API since 95% of the time, a user will fit this profile. For the other 5%, they can use a different API. In essesnce, we want to take what would normally be multiple classes in a many to one scenario and combine them to accomodate 95% percent of the users. The database is not flexible and must accomodate 100% of the use cases, but the remaining 5% "special cases" should use an API optimized for their needs.

I think a major trapping of Java is that Sun likes make an API that covers 100% of possible scenarios, often making it extremely difficult for the 98% use cases. For example: the Calendar class, EJBs, String searching.

Can I use SecondaryTables to make a single class write to more than one table with a sequence?

you can't because your "secondary table" does not have a PK, FK column pointing back to the main table, you ahve a separated FK column to do that.

Just because in 99% of my usecase I need the country name, does not mean that I want to set the country name in the user object rather than a separated country object.

actually you can do what you want by mapping a DB view that does the job you want ie joining the 2 tables in one in the view.
But the minute a user has more than one phone number, this will blow up.

Emmanuel. Thank you for your help and your suggestions. They are greatly appreciated, but I'm afraid the view won't work.

In oracle, the instant you join 2 tables, you can no longer write to the view, especially across to both tables. You won't be able to insert new data into both tables.

http://download-east.oracle.com/docs/cd ... sthref2945

Regarding your previous post:



But the problem is we do want to set it on the user object. The problem is that you're forcing us to create 2 domain models: one that we'll actually expose, and another one that is TIGHTLY coupled with our database and designed to get around the weaknesses of the current SecondaryTable specification. We now need adapters so a user can pass a sensible object to our API and then we, behind the scenes, copy all the values from the sensible object into beans designed to work around Hibernate's seemingly strange limitations.

For my employer, I am working on a very large project that is doing that now. Realistically, the performance hit is not perceptible, but it's rather challenging to document as well as explain to others. The bottom line is that this 2 layered adapter approach works and scales decently, but I'd much rather write a complex mapping than maintain 3 times the number of classes needed.

For my case, I need about 10 domain object. I have 35 tables representing these objects. I have 35 classes mapping to these tables. I have 10 adapter classes transforming the sensible objects to the hibernate objects. The users of my API only know about the 10 domain objects.

You seem to be advocating exposing the 35 class layer to the user. Therefore, I am stuck, for the entire life of my API, with the table layout I have now, due to the tight coupling. If I want to change the underlying table structure, I have to either build an adapter layer or completely modify the API.

The ideal would be to have an optimized domain model with a complex mapping and letting hibernate handle all the joins across as many tables as needed. Since I'm working in an enterprise environment, dozens of developers from multiple divisions and companies will be using my API. The simpler I make it, the less I have to troubleshoot it after it's released and the greater the probability it'll be used correctly by our users.

Correct me if I'm wrong, but one of the biggest intentions of a persistence technology, such as Hibernate, is to ensure that our application developers can manipulate data objects that make sense to them and our DBAs can organize their tables in a manner that makes sense to them. The Java developer shouldn't know anything about the structure of the underlying database in an ideal world. This is especially relevant in enterprise scenarios, such as mine, where the database will be used by more than one technology, such as reporting software, or different custom applications.

Yes but the conception has to be shared across layers.
I cannot map a bidding system database on a financial java app just because I use an ORM

@GeekLove, I'm trying to do a very similar thing.

We have a Person table on which the attributes of a single person are stored.

We also have a Contact table where the phone/email/fax etc of people's contactable personas are stored.

I would like to have a ContactPerson which would be a join of these tables.

The Person part would be repeated for each Contact linked to that Person. So if a person had 3 offices (perhaps someone high powered who works in London, Paris and New York!), we want to see 3 ContactPerson objects for him.

So the Contact table has a FK link to the Person's PK.

I just can't get it to work. Obviously the ID of this ContactPerson object cannot be the ID of the Person because there will be several for each person.

But Person has to be the primary table because of the limitation that the secondary must link to the primary's PK.

I think it may be possible if you use an XML class mapping to supplement annotations, but I'm not sure.

Did you find a satisfactory solution?

We have other complexities in that the Contact table also has @ManyToMany links on it, and how to specify those from the ContactPerson is a mystery.

Is it possible now, with hibernate 3.2 to do what I and GeekLove want using the <join> mapping element?

I currently use annotations. but I'm prepared to add XML mapping to my config if this allows properties to be PERSISTED TO more than one table.

The mapping DTD has



This really implies that it's possible.

So, I cave a Contact which has a "person_id" column in its table.

How to I specify this relationship in a mapping file:



That's not right. From the VERY few examples I can find in "Java Persistence with Hibernate", and "Pro Hibernate 3", I can see that the key must point "up" to the primary table. How can I have it the other way round? Contact point to Person.

Or I could manipulate the mapping programatically after I've build the mappings and before I create the SessionFactory.

The API docs are "blank". They exist, but with no explanation at all about how to use the classes.

So, I could create an org.hibernate.mapping.Join, but how to I use it to do the join that I need? It is a mystery!

Well, great. Thanks for all the help.