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

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]

Answers


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.


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

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

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.


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

Use set Parameter from query.

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

Need Your Help

How to loop through array in jQuery?

javascript jquery arrays loops iteration

I am trying to loop through an array. I have the following code:

jquery loop to create elements

jquery loops

I have had no luck with this task so far so grateful for any help.