0

I am using Spring framework and PostgreSQL database .In the following query I want to select those rows which have product name matched by the parameter `keyword' When I run the following query it gives me the following error

Does anybody tell me how to write right query ?

Query:

@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

Error:

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

Product Entity

@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

@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 }
26
  • can you show us your entities please? Commented Nov 8, 2017 at 6:48
  • @YCF_L Check edited question Commented Nov 8, 2017 at 6:53
  • @ScaryWombat LIKE in PostgreSQL is case sensitive , and I want to match case insensitive keyword , That's why I used this Commented Nov 8, 2017 at 6:53
  • try to use @ManyToOne @JoinColumn(name="id") private Product product; Commented Nov 8, 2017 at 6:53
  • 1
    @YCF_L Sorry I missed nativeQuery = true Commented Nov 8, 2017 at 7:01

1 Answer 1

1

Okay With the help of the above comments I am able to run right query

@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);
Sign up to request clarification or add additional context in comments.

1 Comment

what about p.name ~ :keyword did you try it?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.