dynamically Search Query in HQL with optional Parameter?

Question

i am developing an application in which i need search functionality, i want to write HQL query that dynamically create according to parameters. Currently i have 4 parameters, while searching all parameters are required or 1 or 2 or 3 parameters required according to how user want to searchs.

public List<Plot> fetchSearchedPlots(int plotType, String plotSize, String min, String max)
    {
        Session session = sessionFactory.getCurrentSession();
        List<Plot> searchedLists = new ArrayList<Plot>();
        String query = "FROM Plot where type = ? and size = ? and price >= ? and price <= ?";
        searchedLists = (List<Plot>)session.createQuery( query )
                .setInteger( 0, plotType )
                .setString( 1, plotSize )
                .setString( 2, min )
                .setString( 3, max ).list();
        return searchedLists;
    }

this is my general query for all 4 parameters, now i have to write a search query in which i use multiple optional parameters, How to make this query with optional parameters? kindly convert my query to dynamically optional parameters query ? Thanks

Maybe you can convert it yourself with this info http://stackoverflow.com/questions/12199433/jpa-criteria-api-with-multiple-parameters — RubioRic, May 11 '16 at 05:07
Are you sure? Have you read the accepted answer? What's your exact problem with that answer? You have to construct a CriteriaQuery adding only the predicates that you need. You can obtain a List invoking the getResultList method once you got your query. — RubioRic, May 11 '16 at 09:18
i didn't get from this, can you convert my query exactly and post answer ? — Ahmad, May 11 '16 at 09:21

score 7 · Accepted Answer · answered May 11 '16 at 10:29

I Converted query by myself like this

Session session = sessionFactory.getCurrentSession();
        List<Plot> searchedLists = new ArrayList<Plot>();
        Map<String, Object> params = new HashMap<String,Object>();
        String hqlQuery = "from Plot where societyBlock.societyBlockId = :societyBlock";
        params.put( "societyBlock", societyId );
        if(plotType != null)
        {
            hqlQuery += " and type.typeId = :type";
            params.put( "type", plotType );
        }
        if(!plotSize.isEmpty() && plotSize != null && !plotSize.equals( "" ))
        {
            hqlQuery += " and size = :size";
            params.put( "size", plotSize );
        }
        if(min != null)
        {
            hqlQuery += " and price >= :pricemin";
            params.put( "pricemin", min );
        }
        if(max != null)
        {
            hqlQuery += " and price <= :pricemax";
            params.put( "pricemax", max );
        }
        Query query = session.createQuery( hqlQuery );

        for (String str : query.getNamedParameters())
        {
            query.setParameter( str, params.get( str ) );
        }
        searchedLists = (List<Plot>) query.list();
        System.out.println( searchedLists.size() );
        return searchedLists;

inigo skimmer · Answer 2 · 2016-05-11T09:52:51.243

4

Another variant for dynamic queries is to use Criteria API:

Criteria crit = session.createCriteria(Plot.class);
if (status != null) {
      crit.add(Restrictions.eq("status", status));
}
// other where clauses

For your variant from the question with dynamic criteria creation based on input:

Criteria criteria = session.createCriteria(Plot.class);;
    if(type != null) {
        criteria.add(Restrictions.eq("type", type));
    }
    if(size != null) {
        criteria.add(Restrictions.eq("size", size));
    }
    if(min != null && max != null) {
        criteria.add(Restrictions.between("price", min, max));
    }
    List<Case> searchedLists  = criteria.list();
    return searchedLists;

edited May 11 '16 at 09:52

answered May 11 '16 at 07:02

inigo skimmer

908
6
12

i didn't get from this, can you convert my query exactly ? – Ahmad May 11 '16 at 07:55
if any parameter is null, for example if search do with only type then others parameters are null so it also can handle this Criteria API ? if only type come then this query will execute with type only ? – Ahmad May 11 '16 at 09:06
and also it shows error because createCriteria doesn't have list method so it cannot be save in list – Ahmad May 11 '16 at 09:10
Updated answer. You need to check whether some input params present and build criteria based on that. – inigo skimmer May 11 '16 at 09:53
ok now it looks fine, but i convert query by myself, i just upvote you, thankss, i 'll post my converted query as answer – Ahmad May 11 '16 at 10:31

score 0 · Answer 3 · answered May 11 '16 at 05:49

You can do something like shown below:

    Map<String, Object> parameters= new HashMap<String,Object>();
    parameters.put("status", status);

    StringBuilder hql = "SELECT employee FROM Employee as employee where 1 = 1";
    if (status != null) {
      hql.append(" and employee.status in :status");
    }


    Query query = session.createQuery(hql.toString());


    for (String p : query.getNamedParameters()) {
      query.setParameter(p, parameters.get(p));
    }

i didn't get from this, can you convert my query exactly ? – Ahmad May 11 '16 at 07:54 — Ahmad, May 11 '16 at 07:54

dynamically Search Query in HQL with optional Parameter?

3 Answers3

Linked