java - 如何使用一对多关系休眠查询从数据库中获取所有数据
问题描述
我有两个表父表是该表中的Credit,只有一行数据存在,另一个是包含多行数据的子表Debit。如何从必须匹配父类和子类的 id 并且父类不显示重复的两个表中获取数据。
我尝试过(来自贷方,借方),但可以显示重复,并且基于 id 显示的数据不正确。
package com.rojmat.entity;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import org.springframework.core.annotation.Order;
@Entity
@Table(name="credit")
public class Credit extends BaseEntity{
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column
private long cid;
@Column @Order
private long openingbalance;
@Column
private Date date;
@Column @Order
private long debittotal;
@Column @Order
private long drawertotal;
@Column @Order
private long debittotalplusdrawertotal;
@Column @Order
private long todaybusiness;
@OneToMany(cascade={CascadeType.ALL})
@JoinTable(name="credit_debit",
joinColumns=@JoinColumn(name="c_id"),
inverseJoinColumns=@JoinColumn(name="d_id"))
/*@JoinColumn(name="cid", referencedColumnName="cid")*/
private List<Debit> debits = new ArrayList<Debit>(Arrays.asList());
public Credit() {
}
public Credit(long cid, long openingbalance, Date date, long debittotal, long drawertotal,
long debittotalplusdrawertotal, long todaybusiness, List<Debit> debits) {
super();
this.cid = cid;
this.openingbalance = openingbalance;
this.date = date;
this.debittotal = debittotal;
this.drawertotal = drawertotal;
this.debittotalplusdrawertotal = debittotalplusdrawertotal;
this.todaybusiness = todaybusiness;
this.debits = debits;
}
public long getCid() {
return cid;
}
public void setCid(long cid) {
this.cid = cid;
}
public long getOpeningbalance() {
return openingbalance;
}
public void setOpeningbalance(long openingbalance) {
this.openingbalance = openingbalance;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public long getDebittotal() {
return debittotal;
}
public void setDebittotal(long debittotal) {
this.debittotal = debittotal;
}
public long getDrawertotal() {
return drawertotal;
}
public void setDrawertotal(long drawertotal) {
this.drawertotal = drawertotal;
}
public long getDebittotalplusdrawertotal() {
return debittotalplusdrawertotal;
}
public void setDebittotalplusdrawertotal(long debittotalplusdrawertotal) {
this.debittotalplusdrawertotal = debittotalplusdrawertotal;
}
public long getTodaybusiness() {
return todaybusiness;
}
public void setTodaybusiness(long todaybusiness) {
this.todaybusiness = todaybusiness;
}
public List<Debit> getDebit() {
return debits;
}
public void setDebit(List<Debit> debit) {
this.debits = debits;
}
/*@Override
public String toString() {
return "Credit [cid=" + cid + ", openingbalance =" + openingbalance + ", date=" + date + ", debittotal= " + debittotal + ", debittotalplusdrawertotal=" + debittotalplusdrawertotal + ", todaybusiness=" + todaybusiness + "]";
}*/
}
package com.rojmat.entity;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="debit")
public class Debit {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column
private long did;
@Column
private String amount;
@Column
private String description;
public Debit() {
}
public Debit(String amount, String description) {
super();
this.amount = amount;
this.description = description;
}
public long getDid() {
return did;
}
public void setDid(long did) {
this.did = did;
}
public String getAmount() {
return amount;
}
public void setAmount(String amount) {
this.amount = amount;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
@Override
public String toString() {
return "Debit [did=" + did + ", amount =" + amount + ", description=" + description + "]";
}
}
1.CreditDaoImpl.java
package com.rojmat.daoImpl;
import java.util.List;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.rojmat.dao.CreditDao;
import com.rojmat.entity.Credit;
@Repository
public class CreditDaoImpl implements CreditDao{
@Autowired
private SessionFactory sessionFactory;
@Override
public void addCreditDebit(Credit credit) {
try {
sessionFactory.getCurrentSession().saveOrUpdate(credit);
} catch(Exception e) {
e.printStackTrace();
}
}
@Override
public void deleteCreditDebit(int cid) {
/*Credit credit = (Credit)sessionFactory.getCurrentSession().createQuery("from Credit as c LEFT JOIN FETCH c.Debit where c.cid="+cid).uniqueResult();
List<Debit> debits = credit.getDebit();
sessionFactory.getCurrentSession().delete(credit);
debits.forEach((debit) -> {
sessionFactory.getCurrentSession().delete(debit);
});*/
}
@SuppressWarnings("unchecked")
@Override
public List<Credit> getAllCreditDebit() {
List<Credit> credit = sessionFactory.getCurrentSession().createQuery("from Credit,Debit").list();
return credit;
}
}
解决方案
试试这个例子:你把“distinct”放在你不想被复制的属性之前
//SQL query
select distinct credit.idCredit as idCredit from Credit credit Left Join Debit debit on credit.idCredit= debit.idCredit
//HQL query
@Entity(name = "Credit")
@Table(name = "Credit")
public class Credit{
//if you put @Id --> HQL Query "select credit from Credit credit"
@Column(name = "idCredit")
private Long idCredit;
@Column(name = "label")
private String label;
@OneToMany
@JoinColumns({@JoinColumn(name = "idCredit" ,referencedColumnName = "idCredit")})
List<Debit> debits;
...
}
public class Debit{
....
@Column(name = "idCredit")
private Long idCredit;
...
}
Query query = getSession().createQuery("select distinct credit.idCredit as idCredit, credit.label as label, credit.debits as debits from Credit credit ");
query.setResultTransformer(Transformers.aliasToBean(Credit.class));
return query.list();
package com.rojmat.entity;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import org.springframework.core.annotation.Order;
@Entity
@Table(name="credit")
public class Credit extends BaseEntity{
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column
private long cid;
@Column @Order
private long openingbalance;
@Column
private Date date;
@Column @Order
private long debittotal;
@Column @Order
private long drawertotal;
@Column @Order
private long debittotalplusdrawertotal;
@Column @Order
private long todaybusiness;
@OneToMany(cascade={CascadeType.ALL})
@JoinTable(name="credit_debit",
joinColumns=@JoinColumn(name="c_id"),
inverseJoinColumns=@JoinColumn(name="d_id"))
/*@JoinColumn(name="cid", referencedColumnName="cid")*/
private List<Debit> debits = new ArrayList<Debit>(Arrays.asList());
public Credit() {
}
public Credit(long cid, long openingbalance, Date date, long debittotal, long drawertotal,
long debittotalplusdrawertotal, long todaybusiness, List<Debit> debits) {
super();
this.cid = cid;
this.openingbalance = openingbalance;
this.date = date;
this.debittotal = debittotal;
this.drawertotal = drawertotal;
this.debittotalplusdrawertotal = debittotalplusdrawertotal;
this.todaybusiness = todaybusiness;
this.debits = debits;
}
public long getCid() {
return cid;
}
public void setCid(long cid) {
this.cid = cid;
}
public long getOpeningbalance() {
return openingbalance;
}
public void setOpeningbalance(long openingbalance) {
this.openingbalance = openingbalance;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public long getDebittotal() {
return debittotal;
}
public void setDebittotal(long debittotal) {
this.debittotal = debittotal;
}
public long getDrawertotal() {
return drawertotal;
}
public void setDrawertotal(long drawertotal) {
this.drawertotal = drawertotal;
}
public long getDebittotalplusdrawertotal() {
return debittotalplusdrawertotal;
}
public void setDebittotalplusdrawertotal(long debittotalplusdrawertotal) {
this.debittotalplusdrawertotal = debittotalplusdrawertotal;
}
public long getTodaybusiness() {
return todaybusiness;
}
public void setTodaybusiness(long todaybusiness) {
this.todaybusiness = todaybusiness;
}
public List<Debit> getDebit() {
return debits;
}
public void setDebit(List<Debit> debit) {
this.debits = debits;
}
/*@Override
public String toString() {
return "Credit [cid=" + cid + ", openingbalance =" + openingbalance + ", date=" + date + ", debittotal= " + debittotal + ", debittotalplusdrawertotal=" + debittotalplusdrawertotal + ", todaybusiness=" + todaybusiness + "]";
}*/
}
package com.rojmat.entity;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="debit")
public class Debit {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column
private long did;
@Column
private String amount;
@Column
private String description;
public Debit() {
}
public Debit(String amount, String description) {
super();
this.amount = amount;
this.description = description;
}
public long getDid() {
return did;
}
public void setDid(long did) {
this.did = did;
}
public String getAmount() {
return amount;
}
public void setAmount(String amount) {
this.amount = amount;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
/*@Override
public String toString() {
return "Debit [did=" + did + ", amount =" + amount + ", description=" + description + "]";
}*/
}
推荐阅读
- react-native - react-native-router-flux 在本机基本页脚选项卡中导航
- database - 如何在 PostgreSQL 中有效地索引 2000 个值的向量并找到最近的邻居?
- python - 使用带有“sqlite://”错误的 read_sql 使用 pandas 创建表查询
- symfony - Symfony 5:身份验证非持久性
- android - Android 意图:总是得到 resultCode = 0
- reactjs - 反应窗口中是否存在fixedRowCount?
- asp.net - 带有属性的 DropDownButton 条件语句?
- r - 如何制作一个对R中向量的指数求和的函数?
- java - 剥离内部注释
- vue.js - 在 Vue.js 中工作时,VS Code 不再让我折叠代码并导航到定义