Hibernate/persistence without @Id

Question

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: http://docs.jboss.org/ejb3/app-server/HibernateAnnotations/reference/en/html_single/index.html#d0e1517

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

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?

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.

}

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