Native Query parameters not working in Java EE

Question

I am using JPA through Java EE and have the following native postgresql query:

@NamedNativeQuery(name = "Player.getStandardDev", query ="SELECT STDDEV(?1) FROM Player WHERE ?2 IS NOT NULL")

(I realise the check for IS NOT NULL is probably not needed, but this is not the source of the problem in this case).

I then have the following code trying to execute the query:

Query query = getEntityManager().createNamedQuery("Player.getStandardDev");
query.setParameter(1, attribute);
query.setParameter(2, attribute);
return (BigDecimal) query.getSingleResult();

When getSingleResult() is called, I receive the following error:

org.postgresql.util.PSQLException: ERROR: function stddev(character varying) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 8

I assume this is because the calls to setParameter are not working correctly and so are not replacing the ?s

Is there any other way to use native queries where you can set parameters.

Community · Accepted Answer · 2017-05-23 12:05:52Z

1

The error occurs because when you set a parameter to the query it is evaluated as a string literal in the generated SQL not as a column name. So it becomes

SELECT STDDEV('column_name') FROM Player ...

instead of

SELECT STDDEV(column_name) FROM Player ...

which causes the error because the function expects column name or numeric value.

You can't dynamically set column name in JPA query. You must either hardcode it somehow or use other ways of constructing the query. See this SO post for more details.

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Dec 24, 2014 at 20:07

Bohuslav Burghardt

35.1k7 gold badges130 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Native Query parameters not working in Java EE

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related