Hibernate query building based on conditions

Question

I have a form where user can select search criteria.

The criterias are say:

Product Name: Input field
Name Option: Radio button group - begins with (default selected)/ is/ contains
Country: dropdown of country
Status: All, Active, Blocked
Type: All, One, Two, Three

Only Product Name is mandatory. Other dropdowns are optional.

So if country is not given, I should find products for all countries. If active is not given, I should find both active and blocked products. If Type is not given, I should return all the three types products.

I am building hibernate query as below:

String productName = searchCriteria.getValue("productName");
String productNameCriteria = searchCriteria.getValue("productNameCriteria");
String country = searchCriteria.getValue("country");
String status = searchCriteria.getValue("status");
String type = searchCriteria.getValue("type");

Query prodQuery = null;
String prodSql = "select count(*) from Product p where";

// is
if (productNameCriteria.equalsIgnoreCase("IS")){
            prodSql += "p.productName = '"+productName+"'";
        }

// begins with
else if (productNameCriteria.equalsIgnoreCase("BEGINS WITH")){
            prodSql += "p.productName = '"+productName+"%'";
        }

// contains
else (productNameCriteria.equalsIgnoreCase("BEGINS WITH")){
            prodSql += "p.productName = '%"+productName+"%'";

        }

if(!country.equalsIgnoreCase("0")){
         prodSql += " and p.country = '"+country+"'";
     }

if(!status.equalsIgnoreCase("ALL")){
      if(status.equalsIgnoreCase("active"))
          prodSql += " and p.status = 'active'";

      else
          prodSql += " and p.status = 'blocked'";
 }

 if(!type.equalsIgnoreCase("ALL")){
      if(type.equalsIgnoreCase("one"))
          prodSql += " and p.type = 'one'";

      else if(type.equalsIgnoreCase("two"))
          prodSql += " and p.type = 'two'";

      else 
         prodSql += " and p.type = 'three'";
}

prodQuery = this.em.createQuery(prodSql);
List<Object[]> results = prodQuery.getResultList();

Am I doing query building the right way ? Or is there any other efficient method ???

Thanks for reading!!

criteria query are now deprecated, take a look here https://stackoverflow.com/questions/40720799/deprecated-createcriteria-method-in-hibernate-5 — Giuseppe, Jan 12 '18 at 10:16

ptomli · Accepted Answer · 2011-11-18T07:57:44.147

Try looking at Criteria Query

Criteria crit = sess.createCriteria(Product.class);
if (productNameCriteria.equalsIgnoreCase("IS"))
    crit.add( Restrictions.eq("productName", productName);
else if (productNameCriteria.equalsIgnoreCase("BEGINS WITH"))
    crit.add( Restrictions.like("productName", productName + "%")
// etc

If you absolutely must build a string query then you should be using a StringBuilder

StringBuilder sb = new StringBuilder();
sb.append("select count(*) from Product p where ");
if (productNameCriteria.equalsIgnoreCase("IS"))
    sb.append("p.productName = '").append(productName).append("'");
// etc
String query = sb.toString();

Using a StringBuilder reduces the number of instances created at runtime.

You could also look into using query parameters, which would reduce some of the query complexity, though I don't know what the runtime query performance effects are.

"select count(*) from Product p where p.productName = :productName"
"select count(*) from Product p where p.productName = ?"

You can then use Query#setParameter (or one of the other variants like setString) to define the values in the query. This is also a much, much better way of building the query because it's going to automatically manage quoting and escaping of values you're receiving from the UI. Use query parameters and not string concatenation, regardless of how you build the query string.

Sorry.. but can not use the newer version of JPA since am working with websphere v6.1 I will have to work with query building in the way I have explained in the question. Is the way I have built it correct ???/ — Vicky, Nov 18 '11 at 07:46

Ken Chan · Answer 2 · 2011-11-18T08:04:59.480

Yes .It will work if you build the query dynamically in this way .But the code will become tedious and noisy as it involves string manipulating of the where-condition clause .

For this kind of query 's use case , which is a search that allows users to specify a range of different property values to be matched by the returned result set , using Query By Example(QBE) is more efficient and elegant.

The idea of QBE is that you provide an instance of the queried class with some properties initialized, and the query will returns the records with matching property values.

Reference

Thanks for the reply. But as mentioned in comment, I can not use Criteria Builder as I have to work with websphere v6.1 — Vicky, Nov 18 '11 at 08:43

Hibernate query building based on conditions

2 Answers2