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!!

Answer 1

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.