Native query with named parameter fails with “Not all named parameters have been set”

56
votes

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]
6
javahibernatejpa
Just a tip, why do you want use native query for this query? You can do the same using Jpa query language, there a lot of benefits to prefer Jpa query. – Rafael Zeffa
@Rafael Zeffa Could I use jpa without having an @Entity class? If so, could you please suggest how to optimize it using jpa? – membersound
you can't do it without a entity, but why you can't map a @entity for that table? – Rafael Zeffa
You call setProperty. It should be setParameter. See also this answer – oberlies
View this link stackoverflow.com/a/50365522/3073945 – Md. Sajedul Karim

6 Answers

101
votes

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.

14
votes

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();
12
votes

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();
12
votes

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.

5
votes

Use set Parameter from query.

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

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);
        }
    });