Java EE (Java Enterprise Edition) General Discussion

Hi,

I just started using JPA (with TopLink Essentials) in a new web project, which heavily relies on a relational database at the back end. In one case I used a named native query and run into trouble binding a parameter in the query. The parameter is in an IN clause:

@NamedNativeQuery(
name="thename",
query=
"SELECT ... FROM ... WHERE ... IN ( :keys )"
)

In reality this piece of SQL would be:

SELECT ..FROM..WHERE...IN ('key1', 'key2',...,'keyn')

the key1, key2,..., keyn are know only in runtime. So in my code I construct the keys in a Java String, something like:

String $keys; // eventually gets the value of
// " 'key1','key2','key3' "

I put up all other necessary annotations before come to:

Query q = em.createNamedQuery("thename");
q.setParameter("keys", $keys);
return q.getResultList();

But the parameter never got the value through the getParameter() binding, as soon the getResultList() method was called, the application server reported SQL syntax error. If I hard code the key values in the @NamedNativeQuery() annotation, then it worked fine.

It is a frustrating that JPA Query does not have a getSQLString() method like Hibernate does, this makes debugging a hard task, I don't seem to have a way to know what is going on in the Query object.

Any thoughts/suggestions? many thanks!