Hibernate/persistence without @Id

Question

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?

Copenhaver

Answer 1

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

Fer

But, what happen if such composite set of columns includes a nullable one, I don't have PK but I have defined something like UNIQUE Constraint or Index, How Hibernate handle that? Sounds like a paradox: "@Id" with nullable values cannot exists and cannot save without an "@Id" defined. – Whittemore

> View does not make sense. I concur. Projections returned by stored procedures are be perfectly valid views that do not have identifiers on them. – Rubino

Answer 2

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.

}

Rania

It's hard to see this being a clarity win over the embededId above unless you actually need a unique id. Are there performance considerations as well? – Checkerwork

be aware that rownum and row number() over play very badly with cache (same query, different parameters and then different results will have same ID...) – Cake

There is no rownum function available in MySQL version less than 8.0. But it is possible to use the following approach [https://mcmap.net/q/98260/-row_number-in-mysql/… as an alternative. Just in case anybody is looking for alternative. – Commissioner

Answer 3

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?

Emogeneemollient

Answer 4

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

Plod

Answer 5

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.

Twitt

Answer 6

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.

Jaunty

Answer 7

you could just use @EmbeddedId as follows:

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

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

Immoderation

Answer 8

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

Holle

Answer 9

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.

Warmhearted

this will cause problems if the cache is used – Resting