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

Question

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

User · Answer

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

User · Answer

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

User · Answer

Use set Parameter from query   Query q    Query  em createNativeQuery  SELECT count    FROM mytable where username    1    q setParameter 1   test

User · Answer

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 lt Long gt  resultList   namedParameterJdbcTemplate query query               mapOfNamedParamters               new RowMapper lt Long gt               Override         public Long mapRow ResultSet rs  int rowNum  throws SQLException               return rs getLong 1

User · Answer

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

User · Answer

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

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

Examples related to java

Examples related to hibernate

Examples related to jpa