DntFrgtDSemiCln DntFrgtDSemiCln - 1 month ago 12
SQL Question

JPA/Hibernate select query returning duplicate records

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.

Answer

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

Thanks for the help guys! :)

Comments