DntFrgtDSemiCln DntFrgtDSemiCln - 1 year ago 166
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

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-/ Instrument ID 1
11:13:01,704 INFO [stdout] (http-/ Instrument ID 1
11:13:01,704 INFO [stdout] (http-/ 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;

@Table(name = "instrument")
public class Instrument{

@Column(name="User_ID", length=9, unique=true, nullable=false)
String user_ID;

String ID;

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 Source

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! :)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download