22

I making a sample application with Hibernate. My requirement is that there is no primary key on the table. I had to do only select query from application. I know there should be a primary key, but the table I am referring has been made without it.

It has about 50k records. So, modifying the table to add ID column does not see viable option.

Can it possible?

Trygve Laugstøl
  • 7,440
  • 2
  • 36
  • 40
Ankit
  • 425
  • 1
  • 5
  • 21

5 Answers5

45
  1. Hibernate requires that entity tables have primary keys. End of story.
  2. 50k records is simply not that many when you're talking about a database.

My advice: add an autoincrement integer PK column to the table. You'll be surprised at how fast it is.

Matt Ball
  • 354,903
  • 100
  • 647
  • 710
  • 6
    The answer mentioned by awied here [Hibernate and no PK works](http://stackoverflow.com/questions/767277/hibernate-and-no-pk) without touching your database table at all. This is an example of Hibernate dont actually checks duplicacy. – Sankalp Feb 05 '14 at 12:45
  • 3
    so story does not end in point 1. There is way to achieve this. Also pls look at: https://sourcedexter.com/handling-database-tables-with-no-primary-key-in-spring-mvc-and-hibernate/ also. I think the ans need an edit ! – Dexter May 10 '18 at 17:11
9

So, modifying the table to add ID column does not see viable option.

Why's that? Do you just mean, because it already has fifty thousand records? Trust me, that's really not very many.

Even if the table doesn't have a surrogate key, and doesn't have a primary-key constraint, and even if you're not willing to alter the table, it still probably has some sort of candidate key — that is, some set of columns that are never null, and whose values uniquely identify a record. Even without altering the table to enforce their uniqueness and non-nullity, you can tell Hibernate that those columns form a composite ID.

ruakh
  • 175,680
  • 26
  • 273
  • 307
  • 4
    This is a good answer but to back up the OP, there are MANY circumstances when one cannot alter the table and "make it right". For example, customer gives us a view that was created that contains no ID, no primary key, and, in many cases, duplicate rows. Very frustrating but it happens. – cbmeeks Apr 18 '13 at 20:04
  • 1
    Old thread, but im still finding it on google. So worth mentioning that when working in an enterprise environment, you're not going to be able to justify adding an extra column to a table with huge throughput and millions of rows just because you cant query it with your chosen orm framework... you simply have to work round it. – cowls Apr 03 '14 at 08:15
4

I have found solution for tables without primary key and null as values. It will work on oracle DB. Maybe something similar exists for other DBs.

  1. You should create new primary key in the POJO class:

    @Id @Column(name="id") private Integer id;

and use createNativeQuery like this

getEntityManager().createNativeQuery("select rownum as id, .....

The native query will generate primary key and you will get unique results.

Kanaris007
  • 304
  • 2
  • 6
3

Not with Hibernate. It requires a primary key.

Rob Stevenson-Leggett
  • 35,279
  • 21
  • 87
  • 141
1

Using DB2 with a table without primary keys the following works:

Define the primary key field in the Entity similar to:

@Id
@Column(name="id")
private Integer id;

For Select Query add:

String selectQuery = "select ROW_NUMBER() OVER () id, ..."

I haven't tested with updates to the entity but selecting the info from the DB works fine

GT2018
  • 11
  • 1