Hi,
I have two tables/entities - Customer and Address:
Code:
CREATE TABLE CUSTOMER (customerId PRIMARY KEY, name)
CREATE TABLE ADDRESS (customerId PRIMARY KEY REFERENCES CUSTOMER, nr PRIMARY KEY, city)
so technically it's a one-to-many association. But in fact there is only one customer with multiple addresses out of >6000000 entries and we where explicitly told only to use addresses with nr=1 (because nobody knows the semantics of nr!=1 or which application uses or produces nr!=1). So it would be great if I could do something like
Code:
@Entity
class Customer{
@Id
@Column(name="customerId")
String id;
@OneToOne(mappedBy="customer")
@Where(clause="nr=1")
Address address;
}
@Entity
@IdClass(...)
class Address{
@Id
@Column(name="nr")
int nr;
@Id
@OneToOne
@JoinColumn(name="customerId")
Customer customer;
}
At first, I simply used @OneToMany and accepted, that Customer.address is always a single-element list. But now I have to search customers by addresses (exact condition-clauses depends on user-input) similiar to:
Code:
select c from Customer join c.address a where c.deleted=false and a.city like 'F%' and a.nr=1 limit 10;
Because Customer.address is mapped as OneToMany, Hibernate executes the limit in memory after selecting >6000000 addresses from database.
Because this absolutely kills the performance, it is now really necessary, to change this association to OneToOne. I didn't found lots of examples or documentation on how to do this with @Where. I tried lots of different approaches to map this, but all failed with different exceptions.
Can you explain me, how to transform this kind of OneToMany to OneToOne or how to solve this query without selecting so much unnecessary rows?
Thanks in advance,
Stefan
Login
FAQ
Search