Spring 存储库中的 PostgreSQL 参数化查询

Question

我正在使用Spring框架和PostgreSQL数据库。在下面的查询中，我想选择那些产品名称与参数"关键字"匹配的行当我运行以下查询时，它给了我以下错误

有人告诉我如何编写正确的查询吗？

查询：

@Query(value="Select pp from product_photo pp join products p ON pp.product_id=p.id Where p.name ~* ?Keyword",nativeQuery = true)
    List<ProductPhoto> trail(@Param("keyword")String Keyword);

应用程序接口：

http://localhost:8080/productPhotos/search/trail?keyword=Bottles

错误：

could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

产品实体

@Entity
@Table(name="products")
public class Product implements Serializable{
    private static final long serialVersionUID = 1L;
    @Id
    @SequenceGenerator(name="SEQ_PRODUCTS", sequenceName="PRODUCTS_SEQ", allocationSize=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQ_PRODUCTS")
    private Long id;
    @NotEmpty
    @Column(name="NAME", nullable=false)
    private String name;
    @Column(name="DESCRIPTION",length=1000)
    private String description;
    @Column(name="PRICE", nullable=false)
    private Double price;
    private String SKU;
    private String supplierCode;
    private String readerOfferCode;
    private float productWeight;
    private Boolean statusEnabled=true;
    private float quantity;
    public String barcode;
    private boolean featuredProduct;
    private Double meanRating;
    private Double discountedPrice;
    @Enumerated(EnumType.STRING)
    private stockStatus stockStatus;
    @ManyToOne 
    private Supplier supplier;
    @ManyToOne (fetch = FetchType.EAGER)
    private ProductCategory productCategory;
    @ManyToOne
    private ProductDepartment productDepartment;
    @JsonIgnore
    @OneToMany(mappedBy="product",targetEntity=ProductPhoto.class,fetch = FetchType.EAGER)
    private List<ProductPhoto> pictures;
    ..// Getters and setters 
    }

产品照片实体

@Entity
public class ProductPhoto implements Serializable {
    private static final long serialVersionUID = 1L;
        @Id
        @GeneratedValue(strategy=GenerationType.IDENTITY)
        private Long id;
        //@JsonIgnore
        @ManyToOne 
        private Product product;
        private String path;
        private Boolean primaryPhoto=false;
        @Column( nullable = false, updatable = false, insertable = false, columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
        @Temporal(TemporalType.TIMESTAMP)
        private Date uploadDate ;
        //..getters and setters }

Answer 1

好的，

在上述评论的帮助下，我能够运行正确的查询

@Query("Select pp from ProductPhoto pp join pp.product p where pp.product.id=p.id "
        + "AND p.statusEnabled='true' AND pp.primaryPhoto='true' "
        + "AND (UPPER(p.name) LIKE UPPER(%:keyword%) OR p.description LIKE %:keyword%)")
List<ProductPhoto> sqlLike(@Param("keyword") String Keyword);