java - Select from table using parameters from related tables

Question

I have 3 tables (Entities) (postgreSQL):

@Entity
@Table(name = "application", schema = "applications")
public class Application implements Serializable {
    @Id
    @GeneratedValue
    @Column(name = "application_id")
    private long applicationId;

    @Column(length = 400)
    @Size(min=50, max=400)
    private String applicationDescribing;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "customerId")
    private Customer Customer;
    .....

Also I have the abstract class User with child class Customer, which has its own table in DB.

@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class User implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    private Long userId;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "localitiesId")
    private Localities userLocality;

    .....

@Entity
@AttributeOverride(name="userId", column=@Column(name="customerId"))
public class Customer extends User {

    @Column(length = 8)
    private String userType;
    .....

User and also Customer table has many to one related table Locality, connected via id.

@Entity
@Table(name = "localities", schema = "resource")
public class Localities implements Serializable {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private long localitiesId;

    @Column(name = "region", length = 50)
    private String region;
    ....

I try to realize seaching via Application, using (String) keyWord and locality_id (long) locality.

First approach:

@Query(value = "SELECT u FROM Application u WHERE u.applicationDescribing LIKE %:keyWord% " +
            "AND userLocality IN (SELECT c FROM User c WHERE c.userLocality IN " +
            "(SELECT l FROM Localities l WHERE l.localitiesId = :locality))")
List<Application> getApplicationsByKeyWordsAndRegion(@Param("keyWord") String keyWord, @Param("locality") long locality);

Second approach (recomended by richyen):

@Query(value = "SELECT a FROM Application a " +
    "JOIN Customer c ON a.customerid = c.userid " +
    "JOIN Localities L ON c.localitiesid = L.id " +
    "WHERE a.applicationDescribing LIKE %:keyWord% AND L.id = :locality")
List<Application> getApplicationsByKeyWordsAndRegion(@Param("keyWord") String keyWord, @Param("locality") long locality);

Both dont work. As a result I need List <Application>, which have "keyWord" in describing field and locality in Application.Customer.locality_id. Please help

标签： javapostgresqlspring-boot

Answer 1

正确答案：

SELECT app FROM Application app
 INNER JOIN app.Customer customer
 INNER JOIN customer.userLocality
 locality WHERE customer.userLocality.id
 = :locality AND app.applicationDescribing
 LIKE CONCAT('%',:keyWord,'%')