7

I have a table ,say, Instrument with ID,State, and User_ID as columns.

So I have this JPA query to return all the instrument records with a matching User_ID.

 query = manager.createQuery("SELECT instrument from Instrument instrument where instrument.User_ID=:User_ID",Instrument.class); query.setParameter("User_ID", User_ID); List<Instrument> instruments= query.getResultList(); for(Instrument instrument:instruments){ System.out.println("Instrument ID "+instrument.getID()); // using sysout as it is not prod code yet }

It is returning only the first record repeated as many times as there are matching records.

11:13:01,703 INFO [stdout] (http-/127.0.0.1:8080-1) Instrument ID 1 11:13:01,704 INFO [stdout] (http-/127.0.0.1:8080-1) Instrument ID 1 11:13:01,704 INFO [stdout] (http-/127.0.0.1:8080-1) Instrument ID 1

I have three records in Db with instrument IDs 1,2, and 3

I enabled show sql query on hibernate and the query runs fine on the Database directly and returns distinct records.

Hibernate Query:

 select instrumentjdo0_.User_ID as member_U1_0_, instrumentjdo0_.ID as ID2_0_, instrumentjdo0_.state as state4_0_ from instrument instrumentjdo0_ where instrumentjdo0_.User_ID=?

Instrument Entity

import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.Table; @Entity @Table(name = "instrument") public class Instrument{ @Id @Column(name="User_ID", length=9, unique=true, nullable=false) String user_ID; @Column(name="ID",nullable=false) String ID; @Column(name="state",nullable=false) String state; public String getID() { return ID; } public void setID(String ID) { this.ID = ID; } public String getUserID() { return user_ID; } public void setUserID(String userID) { this.user_ID = userID; } public String getState() { return state; } public void setState(String state) { this.state = state; } }

Not sure what I am missing.

Improve this question
9
  • looks like you have posted wrong Query in your question, check it once again --> User_ID=: "user_ID", <-- why you have double quotes here? Commented Sep 24, 2014 at 15:41
  • Can you add the query generated by hibernate to your question. Commented Sep 24, 2014 at 15:43
  • I have added the hibernate query too Commented Sep 24, 2014 at 15:52
  • ok, query looks good. Now how can you say that the query is returning same record multiple times? How you are iterating the list, can you please add that code also? Commented Sep 24, 2014 at 15:55
  • I have added the iteration code Commented Sep 24, 2014 at 16:00

4 Answers 4

Reset to default
19

The issue was that the wrong column in the Instrument Entity had the @ID attribute assigned to it.

I removed it from User_ID and Added it to ID and it worked fine.

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

6 Comments

But that will not result in repeated records while running your query right, so whatever records you have in DB has same values for User_ID?
yes all the records that are associated with the same user have the same User_ID.
oh ok, then this statement is misleading in your question -- I enabled show sql query on hibernate and the query runs fine on the Database directly and returns distinct records.
surprisingly that did return distinct records. I think hibernate does not directly return the result set and does some kind of processing over the results based on @ID etc.
Got the same issue but I did not have an alternate column on which to put the @Id thus I created a new column.
|
10

I also had faced same issue. For contact table i marked only firstname column as @Id. And table had multiple rows with same firstname due to which first row record with same firstname was getting duplicated in entire result set. To resolve this issue i made IdClass with first name and last name as id attribute and imported it as id class in my bean. Since firstname and lastname together form unique combination, it resolved my issue.

Idclass as below 

public class ContactKey implements Serializable{ protected String firstName; protected String lastName; public boolean equals(final Object inObject) { if (null != inObject) { if (inObject.getClass().equals(this.getClass())) { CqCamAdminKey siteKey = (CqCamAdminKey) inObject; return (null != this.getFirstName() && this.getFirstName().equals(siteKey.getFirstName()) && null != this.getLastName() && this.getLastName().equals(siteKey.getLastName())); } } return super.equals(inObject); } public int hashCode() { if (this.getFirstName() != null && this.getLastName() != null) { return this.getFirstName().hashCode() + this.getLastName().hashCode(); } return super.hashCode(); } }

bean class as below 

@IdClass(contactKey.class) public abstract class CqCamAdminDataBean implements DataModelConstants{ private static final long serialVersionUID = 7686374823515894764L; @Id @JsonIgnore @XmlTransient @Column(name = FIRST_NAME) protected String firstName; @Id @JsonIgnore @Column(name = LAST_NAME) protected String lastName; }
Improve this answer

1 Comment

in case of composite key, this helps : objectdb.com/java/jpa/entity/id
3

Issue with @Id column, If we check closely, @Id column value is same for all the rows. Hence hibernate/JPA not able to get different records, it just get 1st record with this @Id and return duplicate records of it.

Solution - Use @IdClass with columns which result in unique row instead of duplicate row.

Improve this answer

1 Comment

Hi @shileshsaykar. That is the most significant and useful answer to the issue. I followed the tips and indeed i noticed that in the resultset lots of records had equals Id value, and they could be distinguished for other different fields. So i created a proper composite key which helped JPA to compare and find the different records, and this solved my problem. In my opinion, your answer is the one to be marked as accepted. Thank you very much.
1

Make sure to specify DISTINCT in your JPQL query: "SELECT DISTINCT instrument from Instrument instrument where instrument.User_ID=:User_ID". For scalar queries, DISTINCT essentially removes underlying duplicates from the result set.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.