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

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!

CodePudding user response：

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

CodePudding user response：

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.