Using SQL-IN-clause in custom @Query in JPA-/CrudRepository with the list or set as passed parameter?

Question

Hi Spring and Hibernate experts!

Can any one say if it is possible to use SQL IN-clause in custom @Query in CrudRepository while the Arraylist or set of strings is passed as parameter?

I am relatively new to Spring and do not quite figure out why I get the following Spring error:

"java.lang.IllegalArgumentException: Parameter value [d9a873ed-3f15-4af5-ab1b-9486017e5611] did not match expected type [IoTlite.model.Device (n/a)]"

In this post (JPQL IN clause: Java-Arrays (or Lists, Sets...)?) the subject is discussed pretty closely but I cannot make the suggested solution to work in my case with custom @Query.

My demo repository as part of the spring boot restful application is the following:

@Repository
public interface DeviceRepository extends JpaRepository<Device, Long> {        
    @Query("SELECT d FROM Device d WHERE d IN (:uuid)")
    List<Device> fetchUuids(@Param("uuid") Set<String> uuid);
}

And the model-class is the following:

@Entity
@SequenceGenerator(sequenceName = "device_seq", name = "device_seq_gen", allocationSize = 1)
@JsonIgnoreProperties(ignoreUnknown = true)
public class Device implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "device_seq_gen")
    @JsonIgnore
    private Integer id;

    @Column(unique=true, length=36)
    @NotNull
    private String uuid = UUID.randomUUID().toString();

    @Column(name="name")
    private String name;

    @JsonInclude(JsonInclude.Include.NON_NULL)
    private String description;

    @OneToMany(
            mappedBy="device",
            cascade = CascadeType.ALL,
            orphanRemoval = true
    )
    private List<Sensor> sensors = new ArrayList<>();


    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    @JsonIgnore
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDeviceUuid() {
        return uuid;
    }

    public void setDeviceUuid(String deviceUuid) {
        this.uuid = deviceUuid;
    }

    public List<Sensor> getSensors() {
        return sensors;
    }

    public void addSensor(Sensor sensor){
        sensor.setDevice(this);
        sensors.add(sensor);
    }

}

An here is the relevant part of the service calling the fetchUuids-custom-method with set-list of strings as parameter (service naturally being called by the relevant restcontroller):

@Service
public class DeviceService implements IDeviceService {

    @Autowired 
    private DeviceRepository deviceRepository;

...

    @Override
    public List<Device> listDevices(Set<String> clientIds) {
        return deviceRepository.fetchUuids(clientIds);
    }
...
}

Answer 1

Quick fix

You have WHERE d IN (:uuid) in the custom query. You cannot match d, which is an alias for Device entity with :uuid parameter, which is a collection of Strings.

WHERE d.uuid IN (:uuid) would fix the query - it matches a String with Strings.

What you should do instead

It's rather misleading to name the method fetchUuids and return a list of Device instances. It's also unnecessary to write a custom query to do that. You can benefor from repository method name conventions and let Spring Data Jpa framework generate the query for you:

List<Device> findByUuidIn(Set<String> uuids);

Answer 2

You can write in this way

@Query(value = "select name from teams where name in :names", nativeQuery = true)
List<String> getNames(@Param("names") String[] names);

and call the function in service and pass an array of String as arguments.like this

String[] names = {"testing team","development team"};
List<String> teamtest = teamRepository.getNames(names);

Answer 3

Yes is possible to using collection in JPA query parameters. Your query is wrong, it should be like this:

@Query("SELECT d FROM Device d WHERE d.uuid IN :uuid")