Named query with optional parameter not working in mysql

Question

I have a named query below for optional parameter which is flightNumber, departureAirport and arrivalAirport. But this query is not working when I don't give any value for these parameter.

@Query("from CapacityMonitor
  where carrierCode = :carrierCode and
  (:flightNumber IS NULL OR flightNumber = :flightNumber) and 
  (:departureAirport IS NULL OR departureAirport = :departureAirport) and
  (:arrivalAirport IS NULL OR arrivalAirport = :arrivalAirport)

I can change a query but i have to use with @Query annotation only

but in other posts this is the solution they have provided for optional parameters. — Ankit, Oct 15 '13 at 07:40
Of course it is 50% of the solution but you have to set the parameter to `null`. That works under the basis that `null is null` returns `true`, as I explained in my answer. — Mosty Mostacho, Oct 15 '13 at 07:42
tht means wht i have written the query is correct thn wht m i supposed to do to make it working — Ankit, Oct 15 '13 at 08:50
Try to use QueryUpdate instead of NamedQuery, also suggested [here](http://stackoverflow.com/a/44107678/2877012) — Dorin Brage, May 22 '17 at 08:10
Try to use CriteriaUpdate insted of NamedQuery [as mentioned here](http://stackoverflow.com/a/44107678/2877012) — Dorin Brage, May 22 '17 at 08:12

score 1 · Answer 1 · answered Oct 15 '13 at 07:34

1

So you want to keep your query the way it is and make it work with or without parameters. Well, you can't do that. If the query is expecting parameters, then you have to set them.

The best approach would be to leave the query the same way it is and set the parameters to NULL so that :param IS NULL returns TRUE in those cases and return all results. That way you will fake a match.

Anyway, the parameter has to be set always.

answered Oct 15 '13 at 07:34

Mosty Mostacho

42,742
16
96
123

thanks mostacho for ur reply but i am keeping it as ":param IS NULL" but that is not working tht's y i m asking – Ankit Oct 15 '13 at 07:38
As I said, `the parameter has to be set always`. Set it to `NULL` to fake a match. – Mosty Mostacho Oct 15 '13 at 07:41
Thanks, can u tell me how will my query looks like after that, can u help me out in that – Ankit Oct 15 '13 at 07:47
As I said, `leave the query the same way it is`. Then, when you wanted to omit the parameters, instead of omitting them (which is not possible), you should set the parameter to `null`. That way the query would run as if the parameter is not there. Do you get the idea? – Mosty Mostacho Oct 15 '13 at 07:49
tht means wht i have written the query is correct thn wht m i supposed to do to make it working – Ankit Oct 15 '13 at 08:51
1

Is your a button broken? Anyway, do something like `SET param := NULL;` before executing your query. – fancyPants Oct 15 '13 at 11:39

score 1 · Answer 2 · answered Apr 26 '21 at 07:52

I would suggest using a Criteria Query to build a statement with custom WHERE clause.

Based on your example, it could look like this (depending on your data types):

public List<CapacityMonitor> getFlights(String carrierCode, String flightNumber, String departureAirport, String arrivalAirport) {
  CriteriaBuilder builder = em.getCriteriaBuilder();
  CriteriaQuery<CapacityMonitor> query = builder.createQuery(CapacityMonitor.class);

  Root<CapacityMonitor> root = query.from(CapacityMonitor.class);
  query.select(root);

  // Carrier code is mandatory
  query.where(builder.equals(root.get("carrierCode"), carrierCode));

  // Other properties are optional
  if (null != flightNumber && flightNumber.length() > 0) {
    query.where(builder.equals(root.get("flightNumber"), flightNumber));
  }

  // Use LIKE expression to match partially
  if (null != departureAirport && departureAirport.length() > 0) {
    query.where(builder.like(root.get("departureAirport"), "%" + departureAirport + "%"));
  }

  if (null != arrivalAirport && arrivalAirport.length() > 0) {
    query.where(builder.like(root.get("arrivalAirport"), "%" + arrivalAirport + "%"));
  }

  return em.createQuery(query).getResultList();
}

Named query with optional parameter not working in mysql

2 Answers2