I am trying to create a hibernate mapping for a table which doesn't have an Id column as such. Say I have Color table in SQL Server with columns - Nickname and Color. So I am doing it like this It gives mapping error.
Now I uncomment id part - and add id to the bean Color class also.
But when I am trying to do It is giving error as the query is trying to fetch id column from the table Color which doesn't have any column as such.
Enities in a relational database without a primary key are invalid; they are not relational data. This is because in order to participate in a relationship all rows in the entity need to be uniquely identifiable. This is the purpose of the primary key.
Hibernate or an Object Relational Mapping tool. If the data you are trying to map is not relational you can't use Hibernate to map that entity.
There are partial work arounds for legacy data sets, such as mapping every column in the table as a comosite key. But the easiest fix is to add a surrogate key to your table.
Thanks Paul, very clearly made the point. However if a table is on DB2(AS400 machine), has 65K records in it , about 10 logicals defined on this file(table), 100s of programs accessing and manipulating this file with earlier attribute-list. How can we add surrogate key without disturbing any of these? If any link on how to add this surrogate key - will be much helpful. TIA.
The number of records don't matter do they? And DB2 supports auto generation of keys so you should be able to add a surrogate key without being too disruptive. The problem you will possibly find is where the developers of other applications have been sloppy and not named the attributes in insert and select statements, at which point they will fail.
If its too much risk to fix the data model then you will have to use native SQL from Hibernate rather than trying to map an object that is not relational.
Adding primary keys should be in your DB2 documentation.