Ali Ali - 26 days ago 12
JSON Question

Sql query call fails if no record is found

I am trying to fetch a string from sql db. I have the following query:

select DISTINCT HARDWARE.NAME
from HARDWARE INNER JOIN
HARDWARE_LINKING
on HARDWARE.ID = HARDWARE_LINKING.ID
where HARDWARE_LINKING.EXTERNALID='5528752'.


Now when there is no record against any id the function call fails.
here is my function code that I am calling to fetch data:

public String search(String externalId) {
String SQL = "select DISTINCT HARDWARE.NAME from HARDWARE INNER JOIN HARDWARE_LINKING on HARDWARE.ID = HARDWARE_LINKING.ID where HARDWARE_LINKING.EXTERNALID=?";
Object[] input = new Object[] {externalId};
String name = jdbcTemplate.queryForObject(SQL,input, String.class);
return name;
}


Is there any way I can return a default value? and sometimes there are multiple values return but I want only single string value to be returned, Is that possible? Thankyou in advance

Answer

One method is to put the default into the query itself. Use an aggregation function and COALESCE():

select coalesce(h.NAME, '<default value>') as NAME
from HARDWARE h INNER JOIN
     HARDWARE_LINKING hl
     on h.ID = hl.ID
where hl.EXTERNALID = '5528752';

An aggregation query with no GROUP BY always returns one row, so this would seem to be more what you are looking for.

Also note that I put table aliases into the query. These make the query easier to write and to read.

Comments