1

I am querying a view which joins three tables and returns the result like below:

select * from v_project_details Project_ID Repo_Name Branch_Name 100 Repo1 Branch1 100 Repo1 Branch2 101 Repo2 Branch2 @Getter @Setter @Entity @Table(name='v_project_details') public class ProjectDetails{ @Id @Column(name="Project_Id") private int ProjectId } @Column(name="Repo_Name") private String RepoName } @Column(name="Branch_Name") private String BranchName } @Query(select p from v_project_details p) List<ProjectDetails> findAll(); Results: Project_ID Repo_Name Branch_Name 100 Repo1 Branch1 100 Repo1 Branch1 - I am expecting Branch2 here 101 Repo2 Branch2

when i query the table from spring jpa, i see three results but first row is repeated twice.

Looks like hibernate is not reinstantiating the object if @Id value is repeated more than once in the result set.

How do i force it to reinstantiate the object ? I do not have an unique identifier in the view as my view is joined from different tables.

Improve this question
3
  • Why is your Project_ID column not marked as PRIMARY KEY in your SQL table? Commented Aug 2, 2021 at 20:43
  • 1
    how to allow duplicate entries for @Id primary keys are supposed to be unique. Commented Aug 2, 2021 at 20:45
  • You can simply remove the @Id on your project_id field. Consider having a separate @Id annotated field called ProjectDetailsId. Looks like your projectId would be a foreign key reference to a project entity -- not the primary id of your project detail. Commented Aug 2, 2021 at 21:49

2 Answers 2

Reset to default
2

@Id is used by JPA to identify the primary key. In your case, project_id as your primary key, which JPA understands that both row1 and row2 as same records. If project_id is not your unique identifier for your records but instead a combination of multiple fields like (eg: combination of project_id, repo_name and branch_name), you need to define a composite primary key based on those 3 fields. refer to below post by baledung to know more on composite primary keys.

Composite primary keys with JPA

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

JPA defines @Id as primary key field. Here is how w3scools defines primary key. Cite from https://www.w3schools.com/sql/sql_primarykey.ASP

The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

So, primary key is always unique and prohibits null values.

JPA implementations can make use of it and store items effectively with assumption that @Id is always unique.

Your Project_ID can't be @Id since it's not unique, so you should remove this annotation and everything should work as expected. JPA requires entity to have primary key, so you can define composite key or include row number in your view, so you can make row number primary key.

Improve this answer

2 Comments

I get an error with out the @Id for entity : No Identifier specified for the entity.
@Srinivas, I've added a note regarding @Id annotation

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.