Set list parameter to native query

Question

I would like to set parameter to a native query,

javax.persistence.EntityManager.createNativeQuery

Something like that

Query query = em.createNativeQuery("SELECT * FROM TABLE_A a WHERE a.name IN ?");
List<String> paramList = new ArrayList<String>();
paramList.add("firstValue");
paramList.add("secondValue");
query.setParameter(1, paramList);

Trying this query result in Exception:

Caused by: org.eclipse.persistence.exceptions.DatabaseException:
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
You have  an error in your SQL syntax; check the manual that corresponds to your MySQL server 
version for the right syntax to use near
'_binary'??\0♣sr\0‼java.util.ArrayListx??↔??a?♥\0☺I\0♦sizexp\0\0\0☻w♦\0\0\0t\0
f' at line 1
Error Code: 1064
Call: SELECT * FROM Client a WHERE a.name IN ?
        bind => [[firstValue, secondValue]]
Query: ReadAllQuery(referenceClass=TABLE_A sql="SELECT * FROM TABLE_A a WHERE a.name IN ?")

Is it any way to set list parameter for native query, without cast to string and append it to sql query?

P.S. I'm use EclipseLink 2.5.0 and MySQL server 5.6.13

Thanks

Answer 1

I believe you can only set list parameters to JPQL queries, not native queries.

Either use JPQL, or construct the SQL dynamically with the list.

Answer 2

It works if you name the parameter:

Query query = em.createNativeQuery("SELECT * FROM TABLE_A a WHERE a.name IN (:names)");
List<String> paramList = new ArrayList<String>();
paramList.add("firstValue");
paramList.add("secondValue");
query.setParameter("names", paramList);

Answer 3

Not a solution but more of a workaround.

 Query query = em.createNativeQuery("SELECT * FROM TABLE_A a WHERE a.name IN ?");
    List<String> paramList = new ArrayList<String>();
    String queryParams = null;
    paramList.add("firstValue");
    paramList.add("secondValue");
    query.setParameter(1, paramList);

    Iterator<String> iter = paramList.iterator();
int i =0;

while(iter.hasNext(){
    if(i != paramList.size()){

    queryParams = queryParams+ iter.next() + ","; 

    }else{

    queryParams = queryParams+ iter.next();

   }
   i++;
}

query.setParameter(1, queryParams );

Answer 4

You can add multiple values like this example:

TypedQuery<Employee> query = entityManager.createQuery(
"SELECT e FROM Employee e WHERE e.empNumber IN (?1)" , Employee.class);
List<String> empNumbers = Arrays.asList("A123", "A124");
List<Employee> employees = query.setParameter(1, empNumbers).getResultList();

Source: PRAGT E., 2020. JPA Query Parameters Usage. Retrieved from: https://www.baeldung.com/jpa-query-parameters