CODEWITHSUNDEEP
javasqlhibernate
Edster
Edster

Reputation: 153

Hibernate entity join substitute value2 when value1 NULL

I am using Hibernate and trying to build the next logic in my entity for SELECT query. Creating a join column where if value of professor's name = NULL, then select value of teacher's name.

Code for Teacher table:

@Entity
@Table(name = "teacher")
public class Teacher {

@Id
@Column(name = "id_number)
private String id;

@OneToOne
@JoinColumn(name = "t_name")
private Professor name;

// Getters and Setters ...
}

Code for Professor table:

 @Entity
    @Table(name = "professor")
    public class Professor{

    @Id
    @Column(name = "id_number)
    private String id;

    @Column(name = "p_name")
    private String name;

    // Getters and Setters ...
    }

Working SQL query example:

select 
    t.id_number                 as  "Identification Number",
    isnull(p.p_name, t.t_name)  as  "Name"
from teacher t
left join professor p
on t.t_name = p.p_name
where id_number in (23, 24, 25, 26, 27)

What should I change in my entities to replicate logic of the SQL query above? Will really appreciate for any help provided.

Upvotes: 2

Views: 1689

Answers (1)

Kishore Bandi
Kishore Bandi

Reputation: 5701

I'm not sure if you can provide an annotation at field (name) level to achieve this.
My guess is, if something like that is present then it might cause the update also to behave the same way. (override teacher's name with professor's)

Couple of other solutions:

  1. Hibernate's Formula annotation:
    Create another variable say actualName and provide Formula Annotation with Coalesce ( I used it before to return another field when one field was null).

    @Formula("COALESCE(nullableField, backupField)")

    I'm not sure if you can use a mapped entity in it, if not you've to make use of JoinColumnOrFormula annotation and write a query for this.

  2. Create a getter for this new field actualName which will check if professor's name is present then return it. else return teacher's name. This will eliminate the need to write another query.

  3. You could also modify the getter of name field in teacher class to return another field that you would want. NOTE: This will also cause your update operation on teacher's table to replace teacher's name with professor's if professor's name is present. Not Recommended at all

Upvotes: 3

Related Questions