File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Hibernate weird behaviour Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate weird behaviour " Watch "Hibernate weird behaviour " New topic
Author

Hibernate weird behaviour

Maciej Opala
Ranch Hand

Joined: Jul 18, 2011
Posts: 38
Hi,
I've very strange problem with hibernate and just can't work it out. The details below.

There are two tables CE and CP.

CE {
id pk
imp_date not null
cid not null
//other fields
unique(imp_date, cid)
}

CP {
id pk
cid not null unique
//other fields
}

The CE has ManyToOne relationship with CP (the purpose is to maintain common state of CE among various data imports - CP holds editable state of CE) but entry in CP for a CE might be null, so CP is nullable. There are no links on DB level (FK) between two tables. When I fetch CE from DB I'd also like to fetch the editable part (CP) so I need data from both tables. I have the following entry in CE entity:


The query looks like:

And is mapped to:

As far as I know the result of the query is enough to get all the data I need, but after this query there are multiple following queries:

The second query is repeated exactly as many times as there empty records of CP (all nullable). E.g. there are 1000 CE and only 10 has state in CP, the second query will be executed 990 times, which slows down the application.
How to prevent the second query to be executed? I've no idea what to do. Tried unidirectional as well as bidirectional relations, FK on DB level and many, many else.
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1680
    
    7

There are no links on DB level (FK) between two tables.


Why not? It sounds like there is a relationship. Why not this?



Where cp_id is a foreign key into the CP table.



[How To Ask Questions][Read before you PM me]
Maciej Opala
Ranch Hand

Joined: Jul 18, 2011
Posts: 38
Thanks for the response.
The relation is established only in entities. There's no relation on DB level (FK) because of unique(imp_date, cid) in CE, and there's no such field (imp_date) in CP - so I can't add a FK constraint. How to resolve this? I need this unique constraint in CE, but I also want to establish many-to-one relation in DB for CE (many) to CP (one).
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Maciej Opala wrote:There's no relation on DB level (FK) because of unique(imp_date, cid) in CE, and there's no such field (imp_date) in CP - so I can't add a FK constraint. How to resolve this? I need this unique constraint in CE, but I also want to establish many-to-one relation in DB for CE (many) to CP (one).

The only requirement for a ref constraint is that the parent key must be unique. In your case, it is the CP table. Unique constraint on CE(imp_date, cid) doesn't prevent the foreign key to be created. You only need unique constraint on CP(cid), which you already have.
Maciej Opala
Ranch Hand

Joined: Jul 18, 2011
Posts: 38
Martin Vajsar wrote:Unique constraint on CE(imp_date, cid) doesn't prevent the foreign key to be created.


Well, as far as I see this constraint prevents me from creating foreing key from CP(cid) to CE(cid). I get ORA-02270 error. Am I doing something wrong?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

This error indicates you're missing the primary key or unique constraint on the parent table/column.

Check (or post here) the DDL statement that creates the foreign key and fails with that error. Also make sure that the CP table does contain the unique constraint over the cid column (by using SQL Developer, for example, or data dictionary views).
Maciej Opala
Ranch Hand

Joined: Jul 18, 2011
Posts: 38
Here's my DDL statement:

I still get the same error mentioned before. I need the unique constraint over cid in CP table (many to one relation).
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

You're creating the constraint backward. The foreign key constrain must be created on the foreign table (the "many" side).

Swap the CP and CE tables (so that CP is created before CE) and move the constraint to the CE table.

And while at it, you might also consider choosing more descriptive names of your tables.
Maciej Opala
Ranch Hand

Joined: Jul 18, 2011
Posts: 38
The real names are descriptive enough ;] But thanks.

Thanks also for the advice, but I just can't switch the tables (constraints). CE is as master table where the data is imported, so entries appear first in CE table (CE to CP foreign key abuses this). As I mentioned at the beginning there may be many entries in CE with same CID and different import_date. When users edits entry from CE new recored is inserted to CP (one for all entries in CE with same CID and different import_date). CP is used to keep common state for all entries in CE with same CID. So as you see I need this constraint on CP side. Weird. I know. I see that this is not mapping problem but DB design problem.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Yes, it does look like data model problem. There might be several approaches to solve this issue:

1) You can make constraints deferrable in Oracle, so if the CE and CP table are populated by one transaction, a deferred constraint would allow populate CE first and CP later. (Beware, however, I've witnessed the FK constraint to be violated when manipulating the tables in deferred constrains mode in concurrent operations; it is a bug in Oracle. I believe that if only one process manipulates the data in deferred constraints mode, it won't happen.)

2) Perhaps you should have another table that would define the CIDs. Both CE and CP would have foreign key to this table.

3) If the "data import" is an ETL process, you might load the data to some staging tables first and then write them to the destination tables, which might allow you to fill the tables in the correct order.

Or perhaps something else. We don't know enough about your processes and requirements at this point.
Maciej Opala
Ranch Hand

Joined: Jul 18, 2011
Posts: 38
Ok, will take it into consideration and thanks for useful advice.
But apart from the design of DB schema my main question was why hibernate executes so many selects? As I mentioned CP part might be nullable. It looks like the selects are executed to ensure that the result is really null :/ Any help/idea on this topic?
Maciej Opala
Ranch Hand

Joined: Jul 18, 2011
Posts: 38
Finally (after 4 days of googling) I've (probalby) found a solution to my problem. No idea how to resolve this. I need to store CID.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Hibernate weird behaviour