Home > database >  Is that possible in spring boot that join column (foreign key) with id
Is that possible in spring boot that join column (foreign key) with id

Time:03-08

I want to join column without object reference. is that possible?

I want to do foreign key without object reference like that

    @Data
    @Entity
    @Table(name = "HRM_EMPLOYEE_SALARY_INCREMENT")
    public class EmployeeSalaryIncrement  implements Serializable {
    
    
    
    private static final long serialVersionUID = 9132875688068247271L;
        
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name="ID")
        private Integer id;
    
        @Column(name = "REFERENCE_NO")
        private String referenceNo;
    
        @ManyToOne
        @JoinColumn(name = "AUTHORITY", referencedColumnName = "id")
        private Integer authority;
    
        @ManyToOne
        @JoinColumn(name = "PART_TWO_REGISTER_ID")
        private Integer partTwoRegisterId;
    
        @Column(name = "PART_TWO_ORDER_NO")
        private String partTwoOrderNo;
    
        @Column(name = "REMARKS")
        private String remarks;
    
        @Column(name = "HRM_TYPE")
        private Integer hrmType;
    }

If I found solve this problem, it will helpful for me.

CodePudding user response:

Joining is not needed in this case. If you only need the foreign key value, then simply add the column as a @Column like any other:

@Data
@Entity
@Table(name = "HRM_EMPLOYEE_SALARY_INCREMENT")
public class EmployeeSalaryIncrement  implements Serializable {

    private static final long serialVersionUID = 9132875688068247271L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="ID")
    private Integer id;

    @Column(name = "AUTHORITY")
    private Integer authority;

    // other fields
    // ...
}

CodePudding user response:

No, I don't think that you can join columns between two entities without adding the reference of one to the related entity. You will have to create one entity class corresponding to each of your relational database table and add the reference of one to the other to establish relation.

However, I understand that you may not need all the attributes from your related table based upon your use case, and only wish to select one column from it. You can do that either by only adding required attributes in your joined table entity class (if you are sure you won't need other attributes for that table anywhere else).

Or you can use custom queries using JPQL in your repository class which selects only the required attributes from the tables that you have joined.

I will show you an example of the second way:

//Say, this is your entity class where you wish to join other table to fetch only one attribute from the joined table-    

@Entity
@Table(name = "TABLE1", schema = "SCHEMA1")
public class Table1 {
    
    @Id
    @Column(name = "ID")
    private String id;
    
    @Column(name = "TABLE2_COLUMN")
    private String table2Column;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "TABLE2_COLUMN1")
    private Table2 table2; //refrence of the joined table entity object 
}

// And this is the joined table entity class

    @Entity
    @Table(name = "TABLE2", schema = "SCHEMA1")
    public class Table2 {
        
        @Id
        @Column(name = "ID")
        private String id;
        
        @Column(name = "TABLE2_COLUMN1")
        private String table2Column1;
    
        @Column(name = "TABLE2_COLUMN2")
        private String table2Column2; // The column which we want to select from the joined table
    
    }

In your repository class -

@Repository
public interface Table1Repository extends JpaRepository<Table1, String> {

    @Query("SELECT t1 FROM Table1 t1 WHERE t1.id = :id")
    public List<Table1> getTable1Rows(@Param("id") String id);
    
    @Query("SELECT t1.table2.table2Column2 FROM Table1 t1 WHERE t1.id = :id")
    public String getTable2Column2(@Param("id") String id);
}

Based upon the response from Markus Pscheidt below, I agree when he said there's no need to join the entities if you only need the attribute which is a foreign key. As foreign key is already present as an attribute in your entity (or table) you are working with.

If you need to fetch any other column apart from foreign key, then you may use JPQL to fetch the exact column that you wish to select.

  • Related