I try to select all values from the database, but the selectable values are duplicated. From the tools used, the standard Spring Boot + Hibernate setting.
For example, there are entities: Клиент , Телефон and Заказ , where communication
__Клиент__ __Заказ__ | | | | | | M ----- M | | |__________| |_________| 1 | | M __Телефон__ | | | | |___________| HQL sample query:
`SELECT DISTINCT T FROM Customer T LEFT JOIN FETCH T.orders LEFT JOIN FETCH T.phones` When sampling all the Клиент entities, the duplicated lines of the Телефон entity are drawn (as far as I understood for each Заказ line). If you add grouping by Телефон ID:
SELECT DISTINCT T FROM Customer T LEFT JOIN FETCH T.orders LEFT JOIN FETCH T.phones T1 GROUP BY T1.id
then the number of lines of the entity Телефон will be correct, but then all but the single line of the entity Заказ will disappear.
Help me figure out how to solve this problem, maybe there are solutions built into Spring / Hibernate?
UPDATE
Customer:
@Entity @Table(name = "CUSTOMER") public class Customer { private Integer id; private String name; private Set<Order> orders = new HashSet<>(); private Set<Phone> phones = new HashSet<>(); @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "ID") public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } @Column(name = "NAME") public String getName() { return name; } public void setName(String name) { this.name = name; } @ManyToMany @JoinTable(name = "CUSTOMER_ORDER", joinColumns = @JoinColumn(name = "CUSTOMER_ID"), inverseJoinColumns = @JoinColumn(name = "ORDER_ID")) @OrderBy("id ASC") public Set<Order> getOrders() { return this.orders; } public void setOrders(Set<Order> orders) { this.orders = orders; } @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL, orphanRemoval = true) @OrderBy("id ASC") public Set<Phone> getPhones() { return phones; } public void setPhones(Set<Phone> phones) { this.phones = phones; } public void addPhone(Phone phone) { phone.setCustomer(this); getPhones().add(phone); } }
Клиентas specified in the HQL query, are the sameCustomer. Next, in the essence ofCustomer, using theFETCHkeyword, I ask Hibernate to select all theOrdersandPhoneentities, respectively - AlexeyТелефонvalues and add them to the report? - Alexeyselect distinct T from Customer- you will receive a list of all unique customers ... As soon as you start attaching phones / orders to customers, you will receive customer duplication - this is understandable ... - Barmaley