CODEWITHSUNDEEP
spring-data-jpajpql
Priya
Priya

Reputation: 1

How to generate a select query dynamically according to conditions submitted by a user from an HTML form

I want to execute a SELECT query based on various the conditions that a user can specify through an HTML form. Each condition in the query maps to an appropriate field in the HTML form. If no value is specified for a field, the corresponding condition can be skipped in the query.

The fully formed query is shown below:

select
  d
from
  TotalInventoryTransaction d
where
  d.transactionDate between ?1 and ?2
and d.material=?3
and d.shipmentOrder between ?4 and ?5
and d.source = ?6
and d.destination= ?7

The Spring Data JPA repository method is as follows:

@Query(FILTER_DATA_QUERY)
List<TotalInventoryTransaction> getFilteredData(Date dateFrom
                                                , Date dateTo
                                                , Long material
                                                , Long stoFrom
                                                , Long stoTo
                                                , String source
                                                , String destination);

I would like to pass FILTER_DATA_QUERY to this method in such a way that if a method parameter is not specified, its corresponding clause is skipped in the executed query.

Please suggest how this can be achieved.

Upvotes: 0

Views: 1008

Answers (3)

Priya
Priya

Reputation: 1

I am now building a query statement by using string buffer according to condition it is working.. if value is not null then I am appending it into the query else I am not appending..
one section is like this..

StringBuffer queryBuff = new StringBuffer();
        queryBuff
                .append("select * from table_name where");
try {
            if ((dateFromStr != null && dateFromStr != "")
                    && (dateToStr != null && dateToStr != "")) {
                dateFrom = df.parse(dateFromStr);
                dateTo = df.parse(dateToStr);
                queryBuff.append(" transaction_date between " + "\'" + dateFrom
                        + "\'" + "and" + "\'" + dateTo + "\'");
            } else {
                queryBuff.append("");
            }
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
if (materialName != null && materialName != "") {

            if ((dateFromStr == null || dateFromStr == "")
                    && (dateToStr == null || dateToStr == ""))

            {
                queryBuff.append(" material=" + "\'" + materialName + "\'");

            } else {
                queryBuff.append(" and ");
                queryBuff.append(" material=" + "\'" + materialName + "\'");
            }

        }

Upvotes: 0

Grinish Nepal
Grinish Nepal

Reputation: 3063

If you are using Spring-data-Jpa I would like to suggest you to use criteria API. Check out the docs here. You can use predicates to create dynamic where clauses. I created an example to show you the use.

Table:- 

@Entity
public class Test {
    @Id private Long id;    
    String attributeOne;
    String attributeTwo;
    ...
}

The actual query 

//Params from the method
String param1 = "1";
String paramNull = null;

CriteriaBuilder qb = em.getCriteriaBuilder();
CriteriaQuery cq = qb.createQuery();
Root<Test> variableName = cq.from(Test.class);

//Creating Predicates
List<Predicate> predicates = new ArrayList<Predicate>();

//Adding predicates when params are not null
if (param1 != null) {
    predicates.add(
            qb.equal(variableName.get("attributeOne"), param1));
}
if (paramNull != null) {
    predicates.add(
            qb.equal(variableName.get("attributeTwo"), paramNull));
}
//Actual query
cq.select(variableName)
        .where(predicates.toArray(new Predicate[]{}));
//executing the query
em.createQuery(cq).getResultList();

Hope this helps.

Upvotes: 1

manish
manish

Reputation: 20135

You simply need to make sure that when a parameter value is supplied, that value is considered for the query and when it is not specified, the corresponding column value is considered instead.

The following hard-coded JPA query will work:

select
  d
from
  TotalInventoryTransaction d
where
  d.transactionDate >= coalesce(?1, d.transactionDate)
and d.transactionDate <= coalesce(?2, d.transactionDate)
and d.material = coalesce(?3, material)
and d.shipmentOrder >= coalesce(?4, d.shipmentOrder)
and d.shipmentOrder <= coalesce(?5, d.shipmentOrder)
and d.source = coalesce(?6, d.source)
and d.destination = coalesce(?7, d.destination)

COALESCE is a standard ANSI-SQL function that has been adopted into the JPA Query Language as well. Clauses like d.material = coalesce(?3, material) can be understood in plain English as if a (non-null) value is specified, match the specified value against the values in the material column; otherwise, match the column with its own value. This way, whenever a particular value is not specified, the values in the corresponding column will be compared against themselves, always yielding a match for that particular column, as required.

I have replaced the between operator with individual operators with the assumption that the user may end up passing only one of the date pairs.

Upvotes: 1

Related Questions