Spring boot using foreign key in crudRepository

Question

I have 3 entities

• CarWash (Wash set)
• Wash (car_wash_id FK to CarWash)
• WashComment(wash_id FK to Wash)

Is there any way to write this query

   @Query(value="select * from wash_comment where wash_comment.wash_id=(select wash_id from wash where wash.car_wash_id=2", nativeQuery=true))
List<WashComment> findAllByCarWashId(CarWash carWash)

without using nativeQuery?

Answer 1

An advice to dealing with JPA: look away from tables, columns, and all the RDBMS objects you have, and focus on the Entities, their properties and relations.

If I understood your problem correctly, you can let Spring Boot solve it automatically, by using a

List<WashComment> findByWash_CarWash_Id($Parameter(name="id") int id) 

method signature - where the _ has the meaning of the . between and their properties, a traversal point - to specify lookup based on wash.carWash.id. So this would translate to something like this:

select * 
from WashComment wc
where wc.wash.carWash.id=:id

(Which would of course be perfectly valid to put into the @Query annotation)

This assumes, your WashComment and Wash objects looks like this:

public class WashComment {
  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private long id;

  @OneToMany
  private Wash wash;

  //... left out for brevity
}

public class Wash {
  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private long id;

  @OneToMany
  private CarWash carWash;

  //... left out for brevity
}

And the @Id field of the Wash class is named id.

More information about this kind of expression here: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-methods.query-property-expressions

Advice 2: if you ever need to use an inner select - try to rewrite it with a JOIN. 99 times out of 100, it will be possible and that is a lot more readable, and usually significantly more performant:

select wc.* 
from wash_comment wc
join wash w on wc.wash_id=w.wash_id
where wash.car_wash_id=2

(Disclaimer: I can't try any of this now, no JRE anywhere near to play with...)