主页 / 问题 / 1035349
Accepted
Wolframm
Asked:2020-10-16 22:11:24 +0000 UTC2020-10-16 22:11:24 +0000 UTC

从三个相关表中选择

  • 772

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

用户和相应的客户有一个 Locality 字段 - 一个单独的类，多对一关系。

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

我正在尝试按应用程序实现搜索，其中将设置短语（字符串）keyWord 和 locality_id（长）位置。此选项在 Application 中找不到 userLocality（尽管代码可以编译）：

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

我也尝试了这个选项（错误。原因：ohhiaQuerySyntaxException：连接的预期路径！）：

@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>与 keyWord 关键字和指定位置区域匹配的输出。

java

1 个回答

  1. Best Answer

    在 1 个请求%:keyWord% 中，它很可能不起作用，您需要使用 % 传递 keyWord 参数，否则我看到了这个 -LIKE CONCAT('%',:keyWord,'%')

    在 2 -JOIN .. ON ..不适用于 JPQL，更准确地说，不需要 ON，请求应该类似于：

     SELECT app FROM Application app
 INNER JOIN app.Customer customer
 INNER JOIN customer.userLocality locality

 WHERE
 locality.localitiesId = :locality
 AND app.applicationDescribing LIKE CONCAT('%',:keyWord,'%')

    在应用程序中的另一个时刻，private Customer Customer;按照惯例，字段名称应该带有一个小写字母，我不确定这是否会影响，但仍然如此。

    • 0

相关问题