Split String value from single to multiple values to fetch data using WHERE IN SQL clause

Question

I have a request parameter which can accept single or comma separated values using @RequestParam.

Can you help me to break down or split the String of comma separated values so that it can be used when I pass those comma separated values in WHERE IN query clause? This is what the url looks like:

deviceName parameter with single value:
getDevices?deviceName=Laptop

deviceName parameter with comma separated values:
getDevices?deviceName=Laptop,Smartphone,Camera,Television

Currently my code like this:

private List<Devices> processDisplay(List<String> deviceName) {
  Parameters parameters = new Parameters();
  parameters.setDeviceName(!deviceName.isEmpty() ? deviceName : null); \\deviceName is List<String> of type, EDITED: also added null checker
  List<Devices> devices = devicesRepository.getDevices(parameters);
};

How can I pass this comma separated values to SQL WHERE IN so it accepts the broke-down Strings into 4 records?

This is my Repository where my SQL is placed:

public List<Devices> getDevices (Parameters parameters) {
  StringBuilder sql = new StringBuilder("SELECT * FROM devices d ");
  MapSqlParameterSource parameterSource = new MapSqlParameterSource();

  if (!parameters.getDeviceName().isEmpty())) {
    sql.append(" WHERE d.deviceName IN (:deviceName)");
    parameterSource.addValue("deviceName",parameters.getDeviceName().trim())
}

So that it will produce result like this:

SELECT * 
FROM devices d 
WHERE d.deviceName IN ('Laptop','Smartphone','Camera','Television')

NOT like this:

SELECT * 
FROM devices d 
WHERE d.deviceName IN ('Laptop, Smartphone,Camera,Television')

Help would be very much appreciated!

score 1 · Answer 1 · answered Oct 21 '21 at 06:07

1

You can accept a List<String> of devicesNames, pass them to your setDeviceNames() method, which will also accept a List<String> and you will pass them here:

parameterSource.addValue("deviceName", parameters.getDeviceNames());

answered Oct 21 '21 at 06:07

Petar Bivolarski

1,599
10
19

Thank you! But why do I get null as a response when my `devicesName` parameter has no value being passed? Considering that I created a condition where the sql `WHERE IN` will only append if the `devicesName` has a value, else, will return all devices. I did it like this `if (!parameters.getdevicesName().isEmpty()) { sql.append(" WHERE d.deviceName IN (:deviceName)"); parameterSource.addValue("deviceName", parameters.getDeviceName()); }` – MyDev2020 Oct 21 '21 at 06:30
Probably because an empty list is passed which passes your if-condition check, which you can also debug and verify. So, you can add a !list.isEmpty() check too, before adding the condition to the sql query. – Petar Bivolarski Oct 21 '21 at 06:33
I see that you edited your comment. Can you add a non-null check in addition to the current !isEmpty() check? Also, can you update your post with you most recent code? – Petar Bivolarski Oct 21 '21 at 06:36
I added a null checker and updated my post with recent code as well, but I still encounter this `API Error.: null` – MyDev2020 Oct 21 '21 at 06:45
change this: parameters.setDeviceName(!deviceName.isEmpty() ? deviceName : null); to: if (deviceName != null && !deviceName.isEmpty()) {set....} This way you are guaranteeing that you are only setting a value to your object if it is non-null and valid – Petar Bivolarski Oct 21 '21 at 06:48
But if my `deviceName` is null or empty, it should still be passed in my `Repository` class which contains the SQL but will not pass through the `sql.append(" WHERE d.deviceName IN (:deviceName)");` so that only the `SELECT * FROM devices d ` will be processed if my `deviceName` is null or empty. – MyDev2020 Oct 21 '21 at 06:56
Correct, by doing what I suggested you above, along with using the same if-condition in your Repository class, only this statement will be executed in null or empty-cases: SELECT * FROM devices d – Petar Bivolarski Oct 21 '21 at 07:02

Stefanov.sm · Answer 2 · 2021-10-21T07:01:50.943

Just replace

sql.append(" WHERE d.deviceName IN (:deviceName)")

with

sql.append(" WHERE d.deviceName = any(string_to_array(:deviceName, ','))")

Your comma-separated parameter will be split into an array. IN (<list>) and = any(<array>) are equivalent in this case. The result will be

SELECT * 
FROM devices d 
WHERE 
  d.deviceName = any(string_to_array('Laptop,Smartphone,Camera,Television', ','))

Using any unleashes a lot of power. You may have a look here if interested.

Split String value from single to multiple values to fetch data using WHERE IN SQL clause

2 Answers2