java - 使用相关表中的参数从表中选择

Question

我有 3 个表(实体)(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;
    .....

我还有抽象类 User 和子类 Customer，它在数据库中有自己的表。

@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;
    .....

用户和客户表具有多对一的相关表位置，通过 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;
    ....

我尝试使用(字符串)关键字和locality_id(长)位置通过应用程序实现搜索。

第一种方法:

@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);

第二种方法(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);

两者都不起作用。结果我需要 List <Application> ，其中在 Application.Customer.locality_id 中描述字段和位置时具有“keyWord”。请帮忙

Answer 1

这是您要找的吗？

SELECT a.*
FROM application a
JOIN (SELECT * FROM customer c UNION ALL SELECT * FROM executor e) as u on a.customer_id=u.id
JOIN locality l on u.locality_id=l.id
WHERE a.description like '%<keyWord>%'
AND l.region = <region>;

我想我要问你的一个问题是:如何区分客户 ID 和执行者 ID？