Java JPA/Hibernate 选择查询返回重复记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26021050/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JPA/Hibernate select query returning duplicate records
提问by DntFrgtDSemiCln
I have a table ,say, Instrument with ID,State, and User_ID as columns.
我有一个表，比如说，带有 ID、State 和 User_ID 作为列的 Instrument。
So I have this JPA query to return all the instrument records with a matching User_ID.
所以我有这个 JPA 查询来返回所有具有匹配 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
我在 Db 中有三个记录，仪器 ID 分别为 1、2 和 3
I enabled show sql query on hibernate and the query runs fine on the Database directly and returns distinct records.
我在 hibernate 上启用了 show sql 查询，查询直接在数据库上运行良好并返回不同的记录。
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.
不知道我错过了什么。
采纳答案by DntFrgtDSemiCln
The issue was that the wrong column in the Instrument Entity had the @IDattribute assigned to it.
问题是仪器实体中错误的列@ID分配了属性。
I removed it from User_IDand Added it to IDand it worked fine.
我将它从User_ID并添加到ID它，它工作正常。
回答by Tanvi B
I also had faced same issue.
For contact table i marked only firstnamecolumn as @Id.
And table had multiple rows with same firstnamedue to which first row record with same firstnamewas getting duplicated in entire result set.
To resolve this issue i made IdClasswith first name and last name as id attribute and imported it as id class in my bean.
Since firstnameand lastnametogether form unique combination, it resolved my issue.
我也遇到过同样的问题。对于联系表，我只将firstname列标记为@Id. 并且表有多个相同的行，firstname因为firstname在整个结果集中，第一行相同的记录被复制。为了解决这个问题，我IdClass使用名字和姓氏作为 id 属性并将其作为 id 类导入到我的 bean 中。由于firstname和lastname一起形成独特的组合，它解决了我的问题。
Idclassas below
Idclass如下
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
bean类如下
@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;
}
回答by shailesh saykar
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.
@Id 列的问题，如果我们仔细检查，所有行的 @Id 列值都相同。因此，hibernate/JPA 无法获取不同的记录，它只获取带有此 @Id 的第一条记录并返回它的重复记录。
Solution - Use @IdClass with columns which result in unique row instead of duplicate row.
解决方案 - 将 @IdClass 与导致唯一行而不是重复行的列一起使用。
