In a helpdesk application, all entity(s) have an id field well-mapped with an <id> tag in the Schema. Being this a table-per-entity mapping structure, each register of, in principle, any table should have a distinct value at the id column.
On the other hand, we aren't properly deleting registers when an entity is deleted at the application as we want to keep them for the history of the application itself. So what we do is to set a column value to 'D'. This way, when a register of a table (an entity at the end of the road) is active or exists, then, this column that all tables do have is set as an 'A' and changed when deleting.

Problem is when trying to retrive objects from a table where more than one register with the same id column have been inserted although there's only one column with the active value set to 'A'.
Why all this ?
The thing is that we get a ScrollableResult, scrollRes, with all the objects we need at a time and, of course, we have added the "where bla.active = 'A' " at the end of the query, hoping that this would have been enough in case of having duplicated keys/id's from old entries.
However, hibernate doesn't instantiate the objects of this collection until you actually ask for them. Hibernate only keeps the id's of these selected objects. So when doing the scrollRes.get(arg0) method, hibernate throws a plane query like "from Bla bla where bla.id = 'id' " to get the actuall object , and of course, an HibernateException ocurrs. Taking into account that we may have duplicated keys or id's, only discriminated by the active column by an A/D (active-deleted), but only one will be set to 'A' (active). How could we easly manage this persitance problem?

Thing is that we could have a real unique key, but we are very interested in not to. So what we want is basically have one sort-of-key, let's say 'code', and then another column called 'active'. At first, 'code' is being used as a key field. But whenever a persisten object/register is trying to be deleted, then, what we do is to change the value of the 'active' column from 'A' into 'D', in order to keep historical information. Now, this is what deleting is about in our application. But then, of course, we control that in case a 'code' column value appears more than once, only one of these entries will have the 'active' column value set to 'A'. This way, we pretend to retrive active registers/objects by querying with a "where active='A' " clause so we make sure that we are only getting objects with distinct values at 'code' field. And we do get the net.sf.hibernate.ScrollableResults, scrollRes, filled correctly. Problem is that what this scrollRes is really holind are only the 'code' values of this objects, and when calling the scrollRes.get(arg0) method, an hibernate query like "from Bla bla where bla.code = 'code' " is called, which in sql terms comes to be something like, "select * from table Bla where code = 'code' ". This throws an HibernateException when finding more than one 'code' colum with the same value; being the problem that in this automatical query, the clause "where active='A' " hasn't been attached.

So, i want to think that there must be a simple solution, such as, manipulating the mapping file or perhaps the configuration file; or maybe it's possible to twist this automaticla querys when needed or desired with some hibernate object. I don't know. I'm not an hibernate guru, but let me say that somehow i'm sure there must be a way. Problem is that i don't know what is this way....snif.

Well, if anyone have a solution, pleeeeeeeeease help.

Thanks a lot!

First off, this is not a good relational design, from what I have heard. It sounds like you are saying that you are duplicating primary keys (actually not even a primary key, since it then is not unique). Primary keys, by definition, should be unique. If you tried to define this column as a primary key every RDBMS out there is going to complain about non-unique values. This is nothing to do with Hibernate. A relational model should always, always, always utilize unique keys. Even making the combination of "id" and your active column as unique won't work because it sounds like you might have multiple id and acitve=false combinations.

You could try to perform what you are looking for by using sub-classing. Define an ActiveEntity version of your Entity class. The map both in Hibernate, using the active column as the discriminator. This is doable, but by far the better way is to redesign your relational model to be more relational.

As you have not given any details about this, I don't know for sure, but another option might be to introduce an intermediary table which links the current active version of a record. Your entities could then link to this intermediary table. I have an entity which is sort of similiar in my domain model. It models the relation between a Course and a CourseRevision. The way I have set it up is that CourseRevision has a many-to-one relation to its Course (could also be bi-directional such that Course knows about its collection of revisions). Then Course maintains a manual many-to-one link to its currently active revision. Of course all CourseRevisions in my database table have a completely unique id.

Hibernate 2.6

With all requisite disclaimers acknowledging the silliness of
non-unique keys...

I am stuck with a legacy table with non-unique keys. (The mapping follows).
The table is keyed with a composite id which is not unique. Basically, there
may be multiple time-stamped entries that server as descriptive lines for
a transaction.

When I do a query the size of results List is correct. However, when I
look at the records the one field that is not part of the composite id
is the same in all the records.

Originally, I was depending on default auto-generated (hbm2java) equals()
implementation which used only the fields within the id. It occurred to me
that this may not work because hibernate would not be able to
distinguish between cached entities that are different though they have the
same key.

I then added the 'description' field (the only non-key field) to the equals()
implementation (using use-in-equals) but this did not change a thing.

I have tried querying with both HQL within the mapping file as below
(ie. getCashHistoryDetailDescriptionsById) and using a Criteria object
which mimics the HQL below.

Am I doing something wrong? Given that I am stuck with table,
is there are workaround?

Thanks
Rob

Hibernate 2.6

With all requisite disclaimers acknowledging the silliness of
non-unique keys...

I am stuck with a legacy table with non-unique keys. (The mapping follows).
The table is keyed with a composite id which is not unique. Basically, there
may be multiple time-stamped entries that serve as descriptive lines for
a transaction detailed in another table.

When I do a query the size of results List is correct. However, when I
look at the records the one field (ie. description) that is not part of the composite id
is the same in all the records.

Originally, I was depending on default auto-generated (hbm2java) equals()
implementation which used only the fields within the id. It occurred to me
that this may not work because hibernate would not be able to
distinguish between cached entities that are different though they have the
same key.

I then added the 'description' field (the only non-key field) to the equals()
implementation (using use-in-equals) but this did not change a thing.

I have tried querying with both HQL within the mapping file as below
(ie. getCashHistoryDetailDescriptionsById) and using a Criteria object
which mimics the HQL below.

Am I doing something wrong? Given that I am stuck with table,
is there are workaround?

Thanks
Rob