Silence Silence - 1 year ago 149
Java Question

Set list parameter to native query

I would like to set parameter to a native query,


Something like that

Query query = em.createNativeQuery("SELECT * FROM TABLE_A a WHERE IN ?");
List<String> paramList = new ArrayList<String>();
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
f' at line 1
Error Code: 1064
Call: SELECT * FROM Client a WHERE IN ?
bind => [[firstValue, secondValue]]
Query: ReadAllQuery(referenceClass=TABLE_A sql="SELECT * FROM TABLE_A a WHERE 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


Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download