Passing multiple values. getting org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

Question

I'm reading query from a text file and depend upon the filters from the UI, With the string buffer i'm appending the additional queries.. Passing multiple id's like above to get the data

I'm getting nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause java.sql.SQLSyntaxErrorException: ORA-01722: invalid number.

I'm using q.setParameter which is Java persistance Query. So i don't have paramterList.Because of comma between the values i'm getting Invalid number. Can you please help me what i can do here to support multiple values and can get the data.

My code sample:

StringBuffer q = new StringBuffer();

 q.append(queryFromPropertiesFile);

if( model.getId()!=null && ! model.getId().isEmpty())
q.append(" and emp.id IN (:id)");

Query query = entity.createNativeQuery(q.toString());

query.setParameter("id", model.getId());

Answer 1

You need to pass a List object (i.e., split your string using , delimiter) and then pass that list of ids (List object) to the setParameter as shown below:

List<String> idsList = new ArrayList<>();
String[] ids = model.getId().split(",");
for(String id : ids) {
    idsList.add(id);
}

query.setParameterList("id", idsList);