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

score 3 · Accepted Answer · answered Sep 10 '13 at 14:41

3

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.

answered Sep 10 '13 at 14:41

James

17,965
11
91
146

Dynamically means that i must construct my query with same count of arguments and paramList.size()? – Silence Sep 11 '13 at 08:56
16

Would have loved to see some references with this answer instead of "I believe" – Mathieu Castets Aug 28 '15 at 13:13

score 3 · Answer 2 · answered Apr 10 '19 at 09:08

3

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

answered Apr 10 '19 at 09:08

UnixShadow

1,222
8
12

2

It works in Hibernate since forever, but at the time of the question, it did not work in EclipseLink (which the question was about), and I'm fairly certain it still doesn't. – coladict Apr 10 '19 at 11:19

Ryan Marken · Answer 3 · 2016-11-18T18:32:54.483

2

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

edited Nov 18 '16 at 18:32

answered Nov 18 '16 at 18:25

Ryan Marken

34
4

Although this code may help to solve the problem, it doesn't explain why and/or how it answers the question. Providing this additional context would significantly improve its long-term value. Please edit your answer to add explanation, including what limitations and assumptions apply. – jmattheis Nov 18 '16 at 18:49

score 0 · Answer 4 · answered Feb 22 '22 at 12:49

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

Set list parameter to native query

4 Answers4

Linked