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!

Petar Bivolarski · Accepted Answer · 2021-10-21 06:07:29Z

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, 2021 at 6:07

Petar Bivolarski

1,76713 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

MyDev2020 Over a year ago

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()); }

Petar Bivolarski Over a year ago

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 Over a year ago

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?

MyDev2020 Over a year ago

I added a null checker and updated my post with recent code as well, but I still encounter this API Error.: null

Petar Bivolarski Over a year ago

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

|

Stefanov.sm · Accepted Answer · 2021-10-21 07:01:50Z

0

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.

edited Oct 21, 2021 at 7:01

answered Oct 21, 2021 at 6:46

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related