Set list parameter for custom query

Question

I would like to set a parameter for my own request,

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

Attempt to execute this request 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 possible to set the list parameter for my own query without casting to a string and add it to the sql query?

PS I am using EclipseLink 2.5.0 and MySQL server 5.6.13

thanks

Answer 1

I believe that you can set list parameters only for JPQL queries, and not for your own queries.

Either use JPQL or dynamically create a SQL file with a list.

Answer 2

Not a solution, but a more 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 3

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