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?

Solution 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.

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

Comments

[Bozho]

I think they are 1-based (contrary to logic)

[Mark Byers]

@Bozho: Sure? Contrary to JDBC, Hibernate numbers parameters from zero. docs.jboss.org/hibernate/core/3.6/reference/en-US/html/…

[Bozho]

Ah, correct. I've always used named parameters, so this has eluded me :)

[Eldad Mor]

Hibernate params are 0-based. JPA are 1-based. Your syntax is good for Hibernate but not according to the JPA spec. I'll try your syntax and we'll see in a second.

[Eldad Mor]

What's the problem with "type like 'B'"?

[Sean Patrick Floyd]

like matches wildcard patterns. 'B' is a string with no wildcard. use = instead of like

[Eldad Mor]

It was indeed the quotes, thanks - but with the number, so it's "username like ?1".

[Eldad Mor]

Thanks for the = comment as well!

[Eldad Mor]

I don't have an object representation for the users, so especially because it's a simple query I chose Native. I actually have a big DB with lots of entities, all nicely done with JPA, but I have this external DB which I use for 2-3 queries (like this one), so I opted to keep it simple with native queries.