CODEWITHSUNDEEP
springhibernatejpa
VKP
VKP

Reputation: 658

How to do Custom join with Child Entity for a field in JPA?

Hi i have below two entity tables where LOANS is my parent class and DDL Table class is child . The join is custom join . I have other child tables which am able to join correctly. But for this DDL table i want to try a custom join . I am not sure how i can achieve it . The Oracle Query i am trying to achieve is below . How i can do this . Open for Suggestions if this not the correct way of doing it .

Oracle Query

select dt.DESCRIPTION 
from LOANS l,ddl_table dt
where
        dt.table_name='master' and dt.field_name='mc'
        and  SUBSTR(dt.DESCRIPTION, 2, 1) = l.MC_TYPE
        and l.LOAN_ID = :LOAN_ID

Parent Entity

@DynamicUpdate
@Data
@NoArgsConstructor
@ToString
@Table(name = "LOANS")
@Entity
public class Loans {

@Id
@Column(name = "LOAN_ID")
private Long loanId;

@OneToOne(cascade = CascadeType.ALL)
@WhereJoinTable(clause = "table_name='master' and field_name='type'")
private DDLTable  ddlTable;
// Other child tables.
}

DDL Table Entity

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
@Table(name = "DDL_TABLE")
@Entity
public class DDLTable {

@Id
@Column(name = "TABLE_NAME")
private String tableName;
@Id
@Column(name = "FIELD_NAME")
private String fieldName;

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

@Column(name = "FILTER_TYPE")
private String filterType;

}

Upvotes: 0

Views: 1284

Answers (1)

crizzis
crizzis

Reputation: 10716

Do you want to (1) establish an association between Loans and DdlTable, or (2) simply execute the query you posted?

If the former, then only the dt.table_name='master' and dt.field_name='mc' and SUBSTR(dt.DESCRIPTION, 2, 1) = l.MC_TYPE part becomes the join clause. You will then be able to call em.find(Loans.class, loanId) and the Loans.ddlTable will be populated in the result. You'll want sth along the lines of:

@OneToOne
@JoinFormula(formula = "SELECT SUBSTR(dt.DESCRIPTION, 2, 1) FROM ddl_table dt WHERE dt.table_name='master' and dt.field_name='mc'", referencedColumnName = "MC_TYPE")

If the latter, there's no need for the Loans.ddlTable field at all, your query can be translated easily into JPQL:

SELECT dt.description
from Loans l, DdlTable dt
where
        dt.tableName='master' and dt.fieldName='mc'
        and  SUBSTRING(dt.description, 2, 1) = l.mcType
        and l.loanId = :LOAN_ID

As a side note, I'd think about mapping SUBSTRING(dt.description, 2, 1) as a virtual column in your DB to make the entity mapping easier. Perhaps even a dedicated view with dt.tableName='master' and dt.fieldName='mc'. You could index SUBSTRING(dt.description, 2, 1) for faster lookup.

Upvotes: 1

Related Questions