Hi !

I have to implement a service that saves some generic request to a database.
Important things to know is that a request can have a status: PENDING, FAILED or COMPLETED.
Based on this criteria I have in my database 3 tables: PE_REQ, FA_REQ and CO_REQ. Each table store a request object, but only stores the ones with the status equals to the one the table are interessed on (namely failed requests goes into FA_REQ, pending ones to PE_REQ and completed ones to CO_REQ).
I have to follow the design of 3 different tables for performance reasons for the different types of queries foreseen. But that's not the question.

Now I am facing the following problem: what's the best design to follow to allow Hibernate manage this kind os storing the best way ?

Here follows some constraints or problems for service I am developing:
- When a request is created, this one has a unique ID, and this one will never changed. This one is generated by an Oracle sequence.
- Other modules that call the service knows only one type of object: Request. They don't want to have specialised objects that inherit from Request. In fact internally I think that I have to define 3 classes that inherit from Request to allow Hibernate know to which table he has to map the request...
- Other service can update request content, so status can be modified. this means that for example a pending request can become a completed or failed request. This means that the record in PE_REQ has to be moved to another table. This leads to the problem that an update of request is translated to DB by a delete to one table and an insert into another (WITHOUT changing the ID !). request ID must be unique for a request for all the 3 request tables. But how to preserve the ID of the request on insert in this case? If the request was a real adding, not a move from one table to another, the ID must be generated (Oracle sequence).
- Can the move from one table to another be automated by Hibernate or do I have to use Interceptor context to detect based on whatching the status field of the request ?

Any help is welcome :-)

PS: I am busy reading Hibernate in Action (in chapter 4) and I have found until now an answer to my design problem.

Regards,
Jekkil

Another approach would be "object slicing" or "dto pattern" since you are explicitly defining a service. The DTO would be the Request object your service clients see, while internally you could map this however you wanted and broker to/from the DTO representation.

Thanks for the info.

I have decided to go for the DTO pattern for this problem.
But now I am facing another problem.

To summarise let's suppose I have two tables: COMPLETED_REQ and PENDING_REQ.

Both tables used as key the field ID and in the mapping file, this id is defined like this (based on Oracle sequence):


When I insert a record in PENDING_REQ, the ID is automatically assigned from one value generated by the sequence. The ID was set to the unsaved-value (0).
Now suppose that I want to migrate that request from PENDING_REQ to COMPLETED_REQ while preserving the ID value.
Is this possible ?
I have tried, but it seems that even when the ID value is not equal to the unsaved-value (in this case the COMPLETED_REQ keeps the ID of PENDING_REQ record) Hibernate calls the sequence to set a new ID value to the ID field. How can I prevent that ?
Do I have to consider database triggers or Hibernate Interceptors to solve this ?

Thanks,
Jekkil

I think I find a solution, but I am not sure it's very elegant.
I write my own PersistentIdentifierGenerator class.
I just make one based on net.sf.hibernate.id.Assigned and net.sf.hibernate.id.SequenceGenerator called com.db.util.MySequenceGenerator.

The purpose is to use ID if it's defined in the object to persist or use an ID from sequence if not defined.

Here is the code:


If someone has a more elegant way to solve my problem, he's welcomed :-)

Jekkil

Can entries be made directly into the COMPLETED_REQ table (i.e., without having existed in the PENDING_REQ table previously)? Are these mapped using inheritence? Is there some special reason why these are not in the same table with difference between completed v. pending being a simple column discriminator?

Steve,
normally the workflow is the following:
Requests is created with a status PENDING and goes then to the PENDING_REQUEST. Then they become WORKING and remains in that table. After they can become FAILED (request goes to FAILED_REQUEST) or SUCCESS (request goes to COMPLETED_REQUEST)
Why we divide the requests in several table, based on the status discriminator is due to database performance. They are infact billions of requests in the database (most of them in the COMPLETED_REQUEST) and having to do some queries that need table_scan on so large table is what we want to prevent. The system has to have a high througput for processing requests, and so queries on pending and failed request has to be fast. Some delay can be foreseen when accessing history information (COMPLETED_REQUEST).
Concerning inheritance, in Java, I have in fact defined the folloiwng structure:

* RequestData (has no mapping to DB) - contains all the getters and setters
* PendingRequestData extends RequestData (mapped to PENDING_REQUEST) - empty class (except method hash() and equals())
* FailedRequestData extends RequestData (mapped to PENDING_REQUEST) - empty class (except method hash() and equals())
* CompletedRequestData extends RequestData (mapped to PENDING_REQUEST) - empty class (except method hash() and equals())

Jekkil