JPA/Hibernate Join On Constant Value

Question

I am trying to join to different entities from the same table using a constant value in the join statement. In SQL, I would do something like this...

SELECT *
FROM owner o
JOIN types t on t.owner_id = o.id AND t.type = 'A'
--                                ^^^^^^^^^^^^^^^^ THIS IS WHAT I AM TRYING TO REPLICATE

In Java + JPA/Hibernate, I am trying to do something like this...

@Entity
@Table(name = "OWNER")
public class Owner {

    @Id
    @Column(name="ID")
    private Long id

    @OneToOne(mappedBy = "owner", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumns({
        @JoinColumn(name = "ID", referencedColumnName = "ID"),
        @JoinColumn(constantValue = "A", referencedColumnName="type")})
    //              ^^^^^^^^^^^^^^^^^^^  I AM LOOKING FOR SOMETHING THAT DOES THIS.
    //                                   constantValue IS NOT A VALID ARGUMENT HERE.
    private TypeA inspectionSnapshot;

    @OneToOne(mappedBy = "owner", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumns({
        @JoinColumn(name = "ID", referencedColumnName = "ID"),
        @JoinColumn(constantValue = "B", referencedColumnName="type")})
    private TypeB inspectionSnapshot;

    /* Getters & Setters ... */
}

@Entity
@Table(name = "TYPES")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "TYPE", discriminatorType = DiscriminatorType.STRING)
public abstract class BaseType {

    @Id
    @OneToOne
    @JoinColumn(name = "OWNER_ID", referencedColumnName="ID")
    private Owner id;

    @Id
    @Column(name = "TYPE")
    private char type

    /* Getters & Setters ... */
}

@Entity
@DiscriminatorValue("A")
public class TypeA extends BaseType {
    /* All functionality in BaseType */
}

@Entity
@DiscriminatorValue("B")
public class TypeA extends BaseType {
    /* All functionality in BaseType */
}

Thanks in advance!

Dmitry · Answer 1 · 2018-06-29T03:47:21.993

20

Try to specify a constant as the value of the formula

@JoinColumnsOrFormulas({
@JoinColumnOrFormula(formula=@JoinFormula(value="'A'", referencedColumnName="type")),
@JoinColumnOrFormula(column = @JoinColumn("id", referencedColumnName="id"))
})
private TypeA inspectionSnapshot;

edited Jun 29 '18 at 03:47

answered Jun 29 '18 at 03:32

Dmitry

250
4
6

4

It's good practice on Stack Overflow to add an explanation as to why your solution should work – 4b0 Jun 29 '18 at 03:35
This is the perfect solution to make a join with a fixed value – Alessandro C Sep 30 '19 at 16:44
1

F-anyone's-I, the single quote around the constant value is crucial. Hibernate, without it, tries to map it as a column name not a value. – Jin Kwon Oct 27 '20 at 04:47
Mind the single quotes in `value="'A'"` otherwise Hibernate will produce something like `Unknown column 'type0.A' in 'field list'` – barfoos Feb 17 '23 at 09:20

score 9 · Answer 2 · answered Mar 02 '15 at 21:29

If you don't mind using Hibernate-specific annotations you could try with the @WhereJoinTable annotation, e.g.:

@OneToOne(mappedBy = "owner", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@JoinColumn(name = "ID", referencedColumnName = "ID")
@WhereJoinTable(clause = "TYPE = 'A'")
private TypeA inspectionSnapshot;

Note that the clause attribute must contain SQL, not JPQL, so you need to use the database column name instead of the JPA entity field name.

score 5 · Accepted Answer · answered Mar 02 '15 at 21:00

5

You're looking at non-standard joins. Here's the documentation for treating such a case:

http://docs.oracle.com/cd/E13189_01/kodo/docs40/full/html/ref_guide_mapping_notes_nonstdjoins.html

Hope it helps!

answered Mar 02 '15 at 21:00

Kikin-Sama

425
3
8

Funny story... we ended up remodeling the data so that each of the sub-types had a unique sequence number for a primary key. But, I did try your solution and it totally worked! Thanks for the help! – EvilJoe Mar 03 '15 at 12:24

JPA/Hibernate Join On Constant Value

3 Answers3

Linked