JPA/Hibernate select query returning duplicate records

Question

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.

Solution 1

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.

Solution 2

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;

}

Solution 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.