CODEWITHSUNDEEP
javahibernateprimary-keyhibernate-annotations
Miguel
Miguel

Reputation:

Hibernate/persistence without @Id

I have a database view that yields a result set that has no true primary key. I want to use Hibernate/Persistence to map this result set onto Java objects. Of course, because there is no PK, I cannot decorate any field with @Id.

When deploying, Hibernate complains about the missing @Id. How can I work around this?

Upvotes: 38

Views: 79749

Answers (9)

Edward Q. Bridges
Edward Q. Bridges

Reputation: 18310

If there's a combination of columns that makes a row unique, model a primary key class around the combination of columns. If there isn't, you're basically out of luck -- but you should reexamine the design of the view since it probably doesn't make sense.

There are a couple different approaches:

@Entity
public class RegionalArticle implements Serializable {

    @Id
    public RegionalArticlePk getPk() { ... }
}

@Embeddable
public class RegionalArticlePk implements Serializable { ... }

Or:

@Entity
public class RegionalArticle implements Serializable {

    @EmbeddedId
    public RegionalArticlePk getPk() { ... }
}

public class RegionalArticlePk implements Serializable { ... }

The details are here: https://docs.jboss.org/ejb3/app-server/HibernateAnnotations/reference/en/html_single/index.html#d0e1517

Here's a posting that describes a similar issue: http://www.theserverside.com/discussions/thread.tss?thread_id=22638

Upvotes: 32

luke
luke

Reputation: 4155

Instead of searching for workarounds in Hibernate, it might be easier to add dummy id in your database view. Let's assume that we have PostgreSQL view with two columns and none of them is unique (and there is no primary key as Postgres doesn't allow to make PK or any other constraints on views) ex.

| employee_id | project_name |
|:------------|:-------------|
| 1           | Stack01      |
| 1           | Jira01       |
| 1           | Github01     |
| 2           | Stack01      |
| 2           | Jira01       |
| 3           | Jira01       |
------------------------------

Which is represented by the following query:

CREATE OR REPLACE VIEW someschema.vw_emp_proj_his AS
    SELECT DISTINCT e.employee_id,
                    pinf.project_name
    FROM someschema.project_info pinf
    JOIN someschema.project_employee pe ON pe.proj_id = pinf.proj_id
    JOIN someschema.employees e ON e.employee_id = pe.emloyee_id

We can add dummy id using row_number():

SELECT row_number() OVER (ORDER BY subquery.employee_id) AS row_id

like in this example:

CREATE OR REPLACE VIEW someschema.vw_emp_proj_his AS
SELECT row_number() OVER (ORDER BY subquery.employee_id) AS row_id,
       subquery.employee_id,
       subquery.project_name
FROM
  (SELECT DISTINCT e.employee_id,
                   pinf.project_name
   FROM someschema.project_info pinf
   JOIN someschema.project_employee pe ON pe.proj_id = pinf.proj_id
   JOIN someschema.employees e ON e.employee_id = pe.emloyee_id ) subquery;

And the table will look like this:

| row_id      | employee_id | project_name |
|:------------|:------------|:-------------|
| 1           | 1           | Stack01      |
| 2           | 1           | Jira01       |
| 3           | 1           | Github01     |
| 4           | 2           | Stack01      |
| 5           | 2           | Jira01       |
| 6           | 3           | Jira01       |
-------------------------------------------

Now we can use row_id as @Id in JPA/Hibernate/Spring Data:

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

Like in the example:

@Entity
@Table(schema = "someschema", name = "vw_emp_proj_his")
public class EmployeeProjectHistory {

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

    @Column(name = "employee_id")
    private Integer employeeId;

    @Column(name = "project_name")
    private String projectName;

//Getters, setters etc.

}

Upvotes: 11

sopehl
sopehl

Reputation: 1129

According to Hibernate user guide, using the pk is recommended so even if you are working with the existing project that doesn't contain the pk in the database side, you can add the pk column to the related database table and map it with the Java entities.

The hibernate recommendation was like that:

We recommend that you declare consistently-named identifier attributes on persistent classes and that you use a wrapper (i.e., non-primitive) type (e.g. Long or Integer).

For more detail about pk and Hibernate mechanism, please visit here

Upvotes: 0

Victoria Serkov
Victoria Serkov

Reputation: 43

you could just use @EmbeddedId as follows:

@EmbeddedId
public Id getId() {
    return id;
}

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

Upvotes: 0

Peter S.
Peter S.

Reputation: 591

Modify your select used for view creation:

SELECT
   ROWNUM ID, -- or use nextval of some sequence
   -- other columns
FROM
  TABLE

and map "ID" as primary key.

Upvotes: 0

Stacy Mobley
Stacy Mobley

Reputation: 1

While not exactly what you're asking for, here's a little trick I use. Have the query select "rownum" and define "rownum" as your ID column in the model. That will effectively make every row unique to Hibernate.

Upvotes: -1

rogerdpack
rogerdpack

Reputation: 66711

Here is an example that has 2 keys as "Id" keys: https://gist.github.com/3796379

Upvotes: 1

Fortega
Fortega

Reputation: 19682

For each entity, you must designate at least one of the following:

  • one @Id
  • multiple @Id and an @IdClass (for a composite primary key)
  • @EmbeddedId

so maybe you can create a composite primary key, containing multiple fields?

Upvotes: 9

rudolfson
rudolfson

Reputation: 4146

You could check if there is logic wise an id and provide mapping information accordingly. Hibernate will not check the database for existence of a defined primary key.

Upvotes: 0

Related Questions