Title: How to implement where condition in one-to-one relationship
Tags: hibernate;jpa
Question: I am trying to implement the below 'example' query in JPA. It is a ONE-TO-ONE relationship. 

select * from Person person, Age age where person.age between age.minAge and age.maxAge

In my real project, i have multiple Where conditions like this and i want to know the most effective way of achieving the same using annotations. 

Thanks in advance.

```@Entity
@Table(name = "PERSON")
public class Person 
{
    @Column(name = "NAME", nullable = false, precision = 0)
    private String name;

    @Column(name = "ADDRESS", nullable = false, precision = 0)
    private String address;

    @Column(name = "AGE", nullable = false, precision = 0)
    private int age;

    private BMIForAge bmi_age;

    **@OneToOne(optional = false, fetch = FetchType.EAGER)
    public BMIForAge getBmi_age() {
        return bmi_age;
        }

        public void setBmi_age(BMIForAge bmi_age) {
        this.bmi_age = bmi_age;
    }**
}


@Entity
@Table(name = "BMI_FOR_AGE")
public class BMIForAge
{
    @Column(name = "MIN_AGE", nullable = false, precision = 0)
    private int minAge;

    @Column(name = "MAX_AGE", nullable = false, precision = 0)
    private int maxAge;

    @Column(name = "BMI", nullable = false, precision = 0)
    private int bmi;
...
}
```

Comment: Thanks Billy. What if the "WHERE" is needed to achieve the actual mapping, in my case one-to-one. Lets say Person and BMIForAge shares 2 foreign keys but that doesn't make it one to one until there a where condition is added .

   person.id_1 = bmi_age.id_1 and 
   person.id_2 = bmi_age.id_2 and 
   person.age between bmi_age.minAge and bmi_age.maxAge 

Can you please tell how can i achieve this ?

Comment: its not primary key, both entities just happen to share couple columns. I don't have control over the data model at this point. should i treat them as separate entities and not use mapping all together? In short using where or mapping or other mechanisms how do i achieve  person.id_1 = bmi_age.id_1 and    person.id_2 = bmi_age.id_2 and  person.age between bmi_age.minAge and bmi_age.maxAge.

Comment: My actual entities have @Id's (plain sequence no's)

Comment: My named query generates query like:     select person from PERSON person inner join BMI_FOR_AGE age on person.c_1=age.c_1 and person.c_2=age.c_2 where (person.AGE between bmi_age.MIN_AGE and bmi_age.MAX_AGE)  which is correct and executing perfectly in DB2 but i get this error too: HibernateException: More than one row with the given identifier was found

Comment: A query imposes a WHERE condition. A mapping imposes relations. Define what you are asking, because you don't define a WHERE clause "using annotations"

Comment: you mean one of the sides of the relation has a composite PK? Still no need for a "WHERE" condition. Update your post with the EXACT situation you are referring to ... which at the moment it has entities with no `@Id` fields


Here is another answer: With JPQL, you would have something like: 

```select p from Person p join p.bmi_age b where p.age between b.minAge and b.maxAge;
```


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 6, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2018:00:09/06/18', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<hibernate><jpa>', 'Title': 'How to implement where condition in one-to-one relationship', 'ViewCount': 537, 'id': 52210389, 'user_AboutMe': None, 'user_AccountId': 11365845.0, 'user_DisplayName': 'Mjasmin', 'user_Reputation': 47.0, 'user_Views': 31.0, 'user_id': 8332770.0}}