Hibernate and no PK

Question

Is it possible to create a table (from a JPA annotated Hibernate @Entity) that does not contain a primary key / Id?

I know this is not a good idea; a table should have a primary key.

Answer 1

Roger's self-answer is correct. To elaborate a bit on what is meant (I wasn't clear on it at first and figured this would help):

Say you have you have a table Foo as such:

TABLE Foo (
bar varchar(20),
bat varchar(20)
)

Normally, you can write a class w/Annotations to work with this table:

// Technically, for this example, the @Table and @Column annotations 
// are not needed, but don't hurt. Use them if your column names 
// are different than the variable names.

@Entity
@Table(name = "FOO")
class Foo {

  private String bar;
  private String bat;


  @Column(name = "bar")
  public String getBar() {
   return bar;    
  }

  public void setBar(String bar) {
   this.bar = bar;    
  }

  @Column(name = "bat")
  public String getBat() {
   return bat;    
  }

  public void setBat(String bat) {
   this.bat = bat;    
  }

}

.. But, darn. This table has nothing we can use as an id, and it's a legacy database that we use for [insert vital business function]. I don't think they'll let me start modifying tables in order for me to use hibernate.

You can, instead, split the object up into a hibernate-workable structure which allows the entire row to be used as the key. (Naturally, this assumes that the row is unique.)

Split the Foo object into two thusly:

@Entity
@Table(name = "FOO")
class Foo {

  @Id
  private FooKey id;

  public void setId(FooKey id) {
    this.id = id;
  }

  public void getId() {
    return id;
  }
}

and

@Embeddable
class FooKey implements Serializable {
  private String bar;
  private String bat;

  @Column(name = "bar")
  public String getBar() {
   return bar;    
  }

  public void setBar(String bar) {
   this.bar = bar;    
  }

  @Column(name = "bat")
  public String getBat() {
   return bat;    
  }

  public void setBat(String bat) {
   this.bat = bat;    
  }

}

.. And that should be it. Hibernate will use the Embeddable key for its required identity and you can make a call as normal:

Query fooQuery = getSession().createQuery("from Foo");

Hope this helps first-timers with getting this working.

Answer 2

Use following code; Hibernate doesn't have its own logic to distinguish duplicate records

Let me know if there are any issues with this approach

@Entity @IdClass(Foo.class)
class Foo implements Serializable {
  @Id private String bar;
  @Id private String bat;

  public String getBar() {
   return bar;    
  }

  public void setBar(String bar) {
   this.bar = bar;
  }


  public String getBat() {
   return bat;    
  }

  public void setBat(String bat) {
   this.bat = bat;    
  }
}

Answer 3

i found that this trick works:

<id column="ROWID" type="string" />

Answer 4

I found that its not possible to do so. So bad luck for those working with legacy systems.

If you reverse engineer (create JPA annotated entities from existing JDBC connection) the table will create two Java classes, one Entity and with one field; id, and one embeddable id containing all the columns from your relation.

Answer 5

Though ROWID is a pseudo-column,yet as ROWID corresponds to the physical address of a ROW, it is the quickest mean to retrieve any row data. As @Id is used to identify the object uniquely and ROWID is unique inside a table, we can exploit it to overcome the issue we are discussing about. Actually, if we don't need any meaningful unique identifier, ROWID is the best column to annotate with @Id annotation as it corresponds to the physical address of the row. The following code worked for me. @Id @Column(name = "ROWID") private String Id;

Answer 6

When it comes to views instead of searching for workarounds in Hibernate it might be easier to add dummy id in your database view. I wrote about it in another question: https://stackoverflow.com/a/44050230/1673775

Answer 7

You don't need to create a separate class to be your @Id or Primary Key. Just use an Integer (or whatever). Also, don't publish the fake key as developers who use it might think it's real and otherwise try to use it. Lastly, this is best used in a VIEW. I agree with earlier posts that in most, if not all cases, tables should have a primary key. For example:

@Entity
@Table(name = "FOO")
class Foo {

  @SuppressWarnings("unused")
  @Id
  private Integer id;

  @Column(name = "REAL_COLUMN")
  private String realColumn;

  public String getRealColumn() {
    return realColumn;    
  }

  public void setRealColumn(String realColumn) {
    this.realColumn= realColumn;    
  }


}

Answer 8

To Create the Pojo from table - use the reverse Engineering method existing in eclipse. For the non- primary key table, eclipse will generate the two Pojo classes.

eclipse generated class and hbm.xml - 
---
Foo.java 
//

public class Foo implements java.io.Serializable {

    private FooId id;

    public Foo() {
    }

    public Foo(FooId id) {
        this.id = id;
    }

    public FooId getId() {
        return this.id;
    }

    public void setId(FooId id) {
        this.id = id;
    }

}
---
FooId.java
//

public class FooId implements java.io.Serializable {

    private String bar;
    private String bat;

    public FooId() {
    }

    public FooId(String bar, String bat) {
        this.bar = bar;
        this.bat = bat;
    }

    public String getBar() {
        return this.bar;
    }

    public void setBar(String bar) {
        this.bar = bar;
    }

    public String getBat() {
        return this.bat;
    }

    public void setBat(String bat) {
        this.bat = bat;
    }

    public boolean equals(Object other) {
        if ((this == other))
            return true;
        if ((other == null))
            return false;
        if (!(other instanceof FooId))
            return false;
        FooId castOther = (FooId) other;

        return ((this.getBar() == castOther.getBar()) || (this.getBar() != null
                && castOther.getBar() != null && this.getBar().equals(
                castOther.getBar())))
                && ((this.getBat() == castOther.getBat()) || (this.getBat() != null
                        && castOther.getBat() != null && this.getBat().equals(
                        castOther.getBat())));
    }

    public int hashCode() {
        int result = 17;

        result = 37 * result
                + (getBar() == null ? 0 : this.getBar().hashCode());
        result = 37 * result
                + (getBat() == null ? 0 : this.getBat().hashCode());
        return result;
    }

}
---
Foo.hbm.xml

<hibernate-mapping>
    <class name="com.Foo" table="foo" schema="public" catalog="hibernate_poc">
        <composite-id name="id" class="com.FooId">
            <key-property name="bar" type="string">
                <column name="bar" length="20" />
            </key-property>
            <key-property name="bat" type="string">
                <column name="bat" length="20" />
            </key-property>
        </composite-id>
    </class>
</hibernate-mapping>

---
entry in the Hibernate.cfg.xml - 

<mapping class="com.poc.Foo" resource="Foo.hbm.xml"/>

---
Fetch the Data from table -
FooDataFetch.java
//

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class FooDataFetch {
        private static Session session = null;

   public static void main(String[] args) {
          try{            
            Configuration cfg = new Configuration();
            cfg.configure("/hibernate.cfg.xml");
            SessionFactory sf = cfg.buildSessionFactory();
            session = sf.openSession();

            session.beginTransaction();
              queryPerson(session);
            session.close(); 

          }catch (Throwable ex) { 
             System.err.println("Failed to create sessionFactory object." + ex);
            ex.printStackTrace();
             throw new ExceptionInInitializerError(ex); 
          }       
       }    

       private static void queryPerson(Session session) {
            Query query = session.createQuery("from Foo");                 
            List <Foo>list = query.list();
            java.util.Iterator<Foo> iter = list.iterator();
            while (iter.hasNext()) {
                Foo foo = iter.next();
                System.out.println("Foo Details: \"" + foo.getId().getBar() +"\", " + foo.getId().getBat());
            }
        }      
}

Answer 9

Adding to Awied's comment. If then you want to search for a bar, use following HQL.

Query fooQuery = getSession().createQuery("from Foo.id.bar = '<barName>'");

Answer 10

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.