64

I want to execute a simple native query, but it does not work:

@Autowired
private EntityManager em;

Query q = em.createNativeQuery("SELECT count(*) FROM mytable where username = :username");
em.setProperty("username", "test");
(int) q.getSingleResult();

Why am I getting this exception?

org.hibernate.QueryException: Not all named parameters have been set: [username]
oberlies
  • 11,503
  • 4
  • 63
  • 110
membersound
  • 81,582
  • 193
  • 585
  • 1,120

6 Answers6

112

Named parameters are not supported by JPA in native queries, only for JPQL. You must use positional parameters.

Named parameters follow the rules for identifiers defined in Section 4.4.1. The use of named parameters applies to the Java Persistence query language, and is not defined for native queries. Only positional parameter binding may be portably used for native queries.

So, use this

Query q = em.createNativeQuery("SELECT count(*) FROM mytable where username = ?1");
q.setParameter(1, "test");

While JPA specification doesn't support named parameters in native queries, some JPA implementations (like Hibernate) may support it

Native SQL queries support positional as well as named parameters

However, this couples your application to specific JPA implementation, and thus makes it unportable.

Predrag Maric
  • 23,938
  • 5
  • 52
  • 68
  • 1
    yes but i forgot it ...and test it on my example. please mark this answer as right. – pL4Gu33 Mar 03 '15 at 10:58
  • 18
    I know is an old thread, but JPA does allow named parameters, the problem on the original question was this: em.setProperty("username", "test"); and should be like this q.setParameter("username", "test"); – vicmac Sep 14 '18 at 18:58
18

You are calling setProperty instead of setParameter. Change your code to

Query q = em.createNativeQuery("SELECT count(*) FROM mytable where username = :username");
em.setParameter("username", "test");
(int) q.getSingleResult();

and it should work.

oberlies
  • 11,503
  • 4
  • 63
  • 110
17

After many tries I found that you should use createNativeQuery And you can send parameters using # replacement

In my example

String UPDATE_lOGIN_TABLE_QUERY = "UPDATE OMFX.USER_LOGIN SET LOGOUT_TIME = SYSDATE WHERE LOGIN_ID = #loginId AND USER_ID = #userId";


Query query = em.createNativeQuery(logQuery);

            query.setParameter("userId", logDataDto.getUserId());
            query.setParameter("loginId", logDataDto.getLoginId());

            query.executeUpdate();
Ahmed Salem
  • 1,687
  • 22
  • 26
16

I use EclipseLink. This JPA allows the following way for the native queries:

Query q = em.createNativeQuery("SELECT * FROM mytable where username = ?username");
q.setParameter("username", "test");
q.getResultList();
Jeff_Alieffson
  • 2,672
  • 29
  • 34
8

Use set Parameter from query.

Query q = (Query) em.createNativeQuery("SELECT count(*) FROM mytable where username = ?1");
q.setParameter(1, "test");
pL4Gu33
  • 2,045
  • 16
  • 38
  • no that is the right import. But you should use integer parameters. NativeQuery doesnt works with string parameters. I edit my answer. – pL4Gu33 Mar 03 '15 at 10:55
  • By the way, you might want to revert your edit. It DOES work with named param (`... username = :username`). – membersound Mar 03 '15 at 11:13
  • 1
    normally you should get Named parameter "username" is used in native SQL query "SELECT count(*) FROM Persons where username = :username". But named parameter is not allowed in native SQL query, use integer parameter – pL4Gu33 Mar 03 '15 at 11:16
  • 2
    @pL4Gu33 Wihle JPA doesn't support this, some implementations do, like Hibernate. That's the reason it works for the OP. I've edited my answer with this info. – Predrag Maric Mar 03 '15 at 11:29
6

This was a bug fixed in version 4.3.11 https://hibernate.atlassian.net/browse/HHH-2851

EDIT: Best way to execute a native query is still to use NamedParameterJdbcTemplate It allows you need to retrieve a result that is not a managed entity ; you can use a RowMapper and even a Map of named parameters!

private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

@Autowired
public void setDataSource(DataSource dataSource) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}

final List<Long> resultList = namedParameterJdbcTemplate.query(query, 
            mapOfNamedParamters, 
            new RowMapper<Long>() {
        @Override
        public Long mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs.getLong(1);
        }
    });
lekant
  • 992
  • 9
  • 14