CODEWITHSUNDEEP
javahibernatespring-bootspring-data-jpa
Nisha
Nisha

Reputation: 109

Join Query for unrelated entities in Spring boot JPA

I am trying to write a query like the below on unrelated entities A and B

Select a.x,a.y,a.z FROM A a ,B b Where
b.Parameter_Name='APPLICABLE_RULE_TYPE' And b.Parameter_KEY='MC' AND 
b.Parameter_Value=a.rule_type And a.Rule_Status='ON' Order By Rule_Priority;

I am not able to figure out how this should be written in the Repository class of a Spring boot application.

Can someone please suggest a way do this

Thanks in advance.

Edit:

I have tried it as a native query like 

@Query(value = "Select Rule_Id, Rule_Sql,S_System,S_Entity, s_table_name,Rule_Sql_Type "
        + "FROM rule_master TRM ,T_SYSTEM_CONFIG TSC  Where"
        + " Tsc.Parameter_Name='APPLICABLE_RULE_TYPE' "
        + " And Tsc.Parameter_KEY= :systemType"
        + " AND Tsc.Parameter_Value=trm.rule_type  "
        + " And Rule_Status='ON'"
        + " Order By Rule_Priority", nativeQuery = true)
public List<RuleMaster> findByRuleStatus(@Param("systemType" String systemType);

but getting this error

o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 17006, SQLState: null
o.h.engine.jdbc.spi.SqlExceptionHelper   : Invalid column name
org.springframework.orm.jpa.JpaSystemException: could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query

Upvotes: 2

Views: 2860

Answers (1)

Nisha
Nisha

Reputation: 109

I was able to solve this by using native query itself.

The error was because i was selecting only part of the columns from the table. Instead i have used the below query which works fine:

@Query(value = "Select * "
    + "FROM rule_master TRM ,T_SYSTEM_CONFIG TSC  Where"
    + " Tsc.Parameter_Name='APPLICABLE_RULE_TYPE' "
    + " And Tsc.Parameter_KEY= :systemType"
    + " AND Tsc.Parameter_Value=trm.rule_type  "
    + " And Rule_Status='ON'"
    + " Order By Rule_Priority", nativeQuery = true)
public List<RuleMaster> findByRuleStatus(@Param("systemType" String systemType);

Upvotes: 4

Related Questions