Home > database >  How to retrieve ONE column from another table with Foreign key using Hibernate / JPA
How to retrieve ONE column from another table with Foreign key using Hibernate / JPA

Time:04-19

I would like to use the Foreign key "MODEL_ID" to retrieve just one column "MODEL_NAME" from the TT_CARS table, I tried the following code, that works but it returns the whole CARS object.

@JoinColumn(name = "MODEL_ID", referencedColumnName = "ID")
@ManyToOne(fetch = FetchType.EAGER)
private CARS cars;

Also I tried the code below, its also not working

@SecondaryTable(name = "TT_CARS", pkJoinColumns = @PrimaryKeyJoinColumn(name = "ID", referencedColumnName="MODEL_ID"))

Is there other way to retieve just the column (MODEL_NAME) using hibernate and JPA??

remarks: The modelName should be part of the Options class.

my code

import javax.persistence.*;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;

@Entity
@Table(name = "TT_OPTIONS")
public class Options  {

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

    @NotNull
    @Column(name = "DESCRIPTION", nullable = false)
    private String description;

    @Column(name = "MODEL_ID") // Foreign key
    private Long modelId;

    @Column(name = "MODEL_NAME", table = "TT_CARS") // this is the column name I would like to retrieve from the TT_CARS table
    private String modelName;

    // getters and setters
}

CodePudding user response:

You can use @Formula. It is read-only calculated column that can be retrieved by the custom subquery. It does not present in the target table.

Defines a formula (derived value) which is a SQL fragment that acts as a @Column alternative in most cases. Represents read-only state.

Example:

@Entity
@Table(name = "TT_OPTIONS")
public class Options  {

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

    @Column(name = "DESCRIPTION", nullable = false)
    private String description;

    @Column(name = "MODEL_ID") 
    private Long modelId;

    @Formula("(select TT_CARS.MODEL_NAME from TT_CARS where TT_CARS.ID = MODEL_ID)")
    private String modelNameFormula;
}

@Entity
@Table(name = "TT_CARS")
public class Cars {
    @Id
    @Column(name = "ID")
    private Long id;

    @Column(name = "MODEL_NAME")
    private String modelName;
}

Hibernate generated native query:

    select
        options0_.id as id1_4_0_,
        options0_.description as descript2_4_0_,
        options0_.model_id as model_id3_4_0_,
        (select
            TT_CARS.MODEL_NAME 
        from
            TT_CARS 
        where
            TT_CARS.ID = options0_.MODEL_ID) as formula1_0_ 
    from
        tt_options options0_ 
    where
        options0_.id=?

@SecondaryTable designed for @OneToOne relationship to map multiple tables to the same entity. It will not work for the @ManyToOne relationship.

  • Related