Searching in Postgres in multiple fields

Question

I am working on a Spring-MVC project using Hibernate as ORM and PostgreSQL as the database. In the project, I would like to search somee products in the database. As you can guess, the products have quite a few parameters like productName, productDescription, productTags, etc. Question : I am looking for a search function in HQL preferably where all the fields are searched and the productList is returned. I have a simmple search function as mentioned below.

Search function :

public List<ProductBasic> listProduct(Productname) {

    if(session == null){
        session = this.sessionFactory.openSession();
    } else{
        session = this.sessionFactory.getCurrentSession();
    }
    Query query = session.createQuery("from ProductBasic as p where p.productName=:productName order by p.ordernumber");
    query.setParameter("productName",productName);
    List<ProductBasic> productBasicList= query.list();
    return productBasicList;
}

Predrag Maric · Accepted Answer · 2014-11-25 15:03:58Z

1

You can simply compare your search string with multiple fields

Query query = session.createQuery("from ProductBasic as p where p.productName=:searchString or p.productDescription = :searchString order by p.ordernumber");

answered Nov 25, 2014 at 15:03

Predrag Maric

24.5k5 gold badges57 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

We are Borg Over a year ago

Simple, thank you. This helps. :-) Do I have to setParameter only once?

Predrag Maric Over a year ago

@Akshay Yes, only once.

We are Borg Over a year ago

Can you inshort tell me how to get only first 50 results?

Predrag Maric Over a year ago

@Akshay query.setMaxResults(50)

We are Borg Over a year ago

Thank you. I can accept your answer in 8 minutes(Stackoverflow says that). I will do it. Just last thing, so do I have to write some additional code to get the next 50 results. Like I searched, and I have a display with Ajax, so the user scrolls down and wants to see the next 50 results, what can I do then?

|

Joe Love · Accepted Answer · 2014-11-25 21:33:24Z

In the long run, the best thing to do is to create a column (or perhaps even another table) for search terms. I personally like full text search using the tsvector datatype..

Basically you have a trigger that appends all of your data together and saves it in a tsvector field (or just a text field, but it's bigger). The trigger rebuilds this on update or delete.

you call your field "search" and you can simply do a "where search like '%term%'"

you can speed this all up using either full text search txvector indexes, or using trigrams-- both of which will also allow queries where no exact match is found, but it'll return similar results. With full text, case doesn't matter and you can even find synonyms, etc.. Let me know if you're interested and I'll post some example code.

Collectives™ on Stack Overflow

Searching in Postgres in multiple fields

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related