Hibernate version: 3.0.5

Name and version of the database you are using: SQL Server 2000

We have used Hibernate 3 in a fairly complex project and it is an excellent tool. Though we had performance issues with large number of objects, we were able to achieve acceptable response time after following the guidance given in reference documentation. I would say that Hibernate is one of the best-documented open-source tool.

The project requires export of transactions between two offices. Each office has an independent database and application server instance. For example, I have to export sales invoice from one instance to the other instance. The sales invoice has many entities that are mapped with many-to-one relationship, such as branch, customer, item, etc.

We are using surrogate keys for PK for most of the entities, as the natural keys are composite for most of the entities. Since these generated keys could be different in destination database, we will be sending only natural keys as part of the sales invoice. However, in the destination instance, the information will not be inserted unless I fill these objects with its surrogate ids, for which I have to use criteria query that uses natural key as the criteria.

The problem is that for each entity that is part of sales invoice, I have to make separate call to the database. This is causing performance issues for the import.

Is there a way where we can achieve the equivalent of:

INSERT INTO salesInvoiceHdr (invoiceNumber, invoiceDate, ..., branchId, customerId)
SELECT ?, ?, ..., branch.id, customer.id
FROM branch, customer
WHERE (branch.comanyCode = ? and branch.branchCode = ?)
and (customer.companyCode = ? and customer.customerCode = ?)

Thanks for your help.

I understood you as following:
you want to write an invoice with a customer, branch and item.
You select a primary key on your target system for each entity.
Then you write your objects to the target system.

So your primary key is not generated by the database.

It you do want to select the primary key you may define a primary key which is unique on each system.
e.g.
composite-id
-String officeName
-Integer increment/serial

you can directly write such an entry to the target system but you must verify that the primary key is not regenerated. I do not know how increment behaves here. With sequences this is possible.

Regards Sebastian

Wherever we had composite natural ids, we are using hibernate to generate the ids. The natural ids have been configured with Unique Constraints in relational table.

These generated ids for the same branch could be different in the destination database.

For example, The "Branch" table:

In DB1:

id=209;ComapanyCode = 'ABC'; BranchCode='B01';

In DB2:

id=111;ComapanyCode = 'ABC'; BranchCode='B01';

Note that the "id" is different for the same branch in two DBs.

Sending id from DB1 to DB2 is incorrect, because that may be pointing to a different branch. So, we send only CompanyCode and BranchCode along with the sales invoice.

In the destination application instance, when we receive the sales invoice, we construct Branch object with only CompanyCode and BranchCode attributes set. Using Criteria Query of the Hibernate, we retrieve this object. At this stage we get the "Id" of the branch in destination database.

This object is set to the SalesInvoice object and then the insert of Sales Invoice is called.

I would like to know is there a way I can insert the sales invoice without retrieving the Id of each many-to-one object.

Thanks again.