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

Question

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

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

查询:

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

API:

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 
    }

ProductPhoto 实体

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