Problem with positional parameters in JPA native query

Question

I'm trying to do:

String sql = "SELECT email FROM users WHERE (type like 'B') AND (username like '?1')";
List results = em.createNativeQuery(sql).setParameter(1, username).getResultList();

But I get IllegalArgumentException that tells me that the parameter is out of bounds. What am I doing wrong?

Answer 1

There shoudn't be quotes around the parameters. Try this instead:

String sql = "SELECT email FROM users WHERE (type like 'B') AND (username like ?1)";

You might also want to double-check that you really mean type like 'B' as this probably doesn't do what you think it does.

Answer 2

a) Why would you use native SQL for a simple query like this? Use JPQL.
b) Why use like if you don't use wildcards? Use = instead.

String jpql =
  "SELECT u.email FROM users u WHERE (u.type = 'B') AND (u.username = '?1')";

List results = 
    em.createQuery(jpql)
      .setParameter(1, username)
      .getResultList();