Hello!

For legacy reasons I want to map a table of a mysql db which has no primary. But using no PK seems to conflict with the expectations from Hibernate. Is a PK always required?

Hibernate version:
3.3

If you ever want to insert or update this table, you must have a primary key.

If you just want to select, you can get around it by using a native SQL portion in your code. For example:



If you select multiple fields, it returns a List<Object[]>, eg.2:



Please note: Hibernate is CRAP at casting correctly, that's why I'm adding all those scalars.

You could, in theory, construct insert and update statements via createSQLQuery as well.

HTH

Thanks for you answer. The big drawback in your solution is the native sql query. But I assume this could be the only solution, if tables whith no PK are only allowed in some databases.

Actually you are not right. Tables without primary keys are allowed in most databases. It is just considered to be a bad practice.
However, people often confuse PRIMARY KEY with the surrogate primary key.
You might not have primary key defined in your DDL but do you have any unique constraint ( or UNIQUE KEY speaking in MySQL terms)?
If so, you can specify <natural-id> or <composite-id> in your HBM file and it is as good as a primary key for what Hibernate is concerned.
You can even use a <formula> in it and have a derived key (I haven't try it myself, though).

Perhaps we have a misunderstanding using some terms. I use the term primary key as one/ a set of colums which identify the entry in a unique way and moreover are marked for the schema as primary keys. If you choose a surrogate attribute as primary key or an natural one is a technical detail, but nonetheless it/they represent the primary key of the table.

So the problem I mentioned is based on a legacy database I did not create but have to work with. Within this mysql database there exists a table declaring no primary key. Mysql allows that leaving the danger of duplicate entries which can not be differed in any ways up to the programmer.

Hibernate does not seem to allow this - this is the basis of my posting. Using a natural-id, a composite-id etc is not the same as declaring no primary key, because there are side effects like indexing, uniqueness etc.

You are completely right to state that using no primary key is bad practice and it is clear to say that each entry should be distinct to another by its nature. But as I said there can be legacy issues which have to be respected (temporary).

It is possible to pretend to hibernate that there is a unique key on a table by writing the hbm files/Annotations yourself, but if it's not guaranteed as unique by the database, it's dangerous, you'll probably get a whole bunch of errors whenever you try to do anything with the table.

There's no real harm in writing native SQL queries, as long as the SQLs are ANSI compliant (ie will work on any ANSI compliant database = all of them).

I believe you can also do sqlQuery.addEntity(yourClass) to avoid adding all those scalars, but you have to be very careful in naming the fields because it requires 100% accuracy (including case, and it does funny things with underscores) for it to cast correctly.

Of course, if you can write ANSI sql which is compatible with all modern databases, it begs the question: What's the point of HQL? Why didn't the hibernate designers just force you to use ANSI standard SQL instead of creating yet another SQL variant?

I would do this only when you are reading data (not writing it). When you have a DB like oracle, you can have statements like



=> and thus, you can add this statement into the Hibernate mapping:



subsequently, you define all other columns as



When you use the reverse engineering Wizard, you can 1.) remove the composite-key tag, 2.) search and replace key-property for property and 3.) insert above line