Strange Behavior..

[shri mon]

I have the following code.

List results = session.createCriteria(MyClass.class)
.add(Restrictions.eq("ATTR_ID",attrID))
.addOrder(Order.desc("CODE_LONG_DESC"))
.list();

The size of the results list is 5.

But all the 5 rows are having the same data.
Any idea what is happening?

Thanks in advance.
Shrimon.

[Martin Podrouzek]

Greetings Shrimon

Please note that I'm quite new to Hibernate. Although I am not using criteria queries I experienced simillar problem. I have a legacy table with multiple rows with same primary id. When I run SELECT on the table i get 4 rows with SAME primary id. It seems that hibernate treats them as same row and return 4 rows with same content (?maybe using cache?). In your case I'd check mapping file and if ATTR_ID in your rows have same ID you may have same problem as I.

This is a mere guess but aren't you trying to do one-to-many join?

You can solve the problem by using Java sql (that works fine with this).

I discovered this problem 2 days ago, so I'm at the beginning of investigation.

Hope it helps

Martin

[Steven Bell]

This is probabaly personal preference, but I find writing the queries in HQL much easier than the criteria queries.

[Paul Sturrock]

Although I am not using criteria queries I experienced simillar problem. I have a legacy table with multiple rows with same primary id. When I run SELECT on the table i get 4 rows with SAME primary id. It seems that hibernate treats them as same row and return 4 rows with same content (?maybe using cache?). In your case I'd check mapping file and if ATTR_ID in your rows have same ID you may have same problem as I.

You might like to read the section in the Hibernate documentation which deals with identity in Hibernate. "4 rows with SAME primary id" basically means your relational data is invalid - if one row has the same primary key as another row, then the key is not a key. The criteria for a primary key to be such is if it is a value which:

is unchanging

is never null

is unique

In this case it is the last point which is important. If you are breaking that rule, then it is not suprising that the data returned is as it appears.

When you have a table which has been incorrectly modelled like this table has the best solution is to add a new surrogate key field which is a genuine primary key. This sorts all your problems. If you can't do this, then you need use a composite key, made up of as many fields from the table as are required to fulfil the specification of a primary key. This could include all the fields. This is a more awkward solution to map than a surrogate key though.

[Martin Podrouzek]

I definitely agree Paul, unfortunatelly this is a production DB :roll: .I would never do anything like this. When I saw it for the first time, I was getting sick. I just have to deal with it... somehow.

Martin

[Paul Sturrock]

OK. I understand - and have dealt with equally poorly modelled legacy DBs designed by DBAs who should never have been given a job.

If you are stuck with it, consider defining all fields in the table as a composite key, which might work most of the time. Of course if the table doesn't have a PK constraint defined, which in this case it sounds possible, your only reliable route is to remodel it. If it is defined this way its not just a problem for Hibernate, but anything which relies on the integrity of the data in this table.