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]

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, Commented Mar 3, 2015 at 11:33
@Rafael Zeffa Could I use jpa without having an @Entity class? If so, could you please suggest how to optimize it using jpa? — membersound
– membersound, Commented Mar 3, 2015 at 12:13
you can't do it without a entity, but why you can't map a @entity for that table? — Rafael Zeffa
– Rafael Zeffa, Commented Mar 3, 2015 at 12:28
You call setProperty. It should be setParameter. See also this answer — oberlies
– oberlies, Commented Nov 27, 2017 at 13:43

Predrag Maric · Accepted Answer · 2015-03-03 11:28:21Z

119

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.

edited Mar 3, 2015 at 11:28

answered Mar 3, 2015 at 10:53

Predrag Maric

24.5k5 gold badges57 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pL4Gu33 Over a year ago

yes but i forgot it ...and test it on my example. please mark this answer as right.

vicmac Over a year ago

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

suppaGonzalo Over a year ago

@vicmac Does JPA really allow? I am using it with Hibernate. Positional binding and Named binding with # works but not with colon (:)

Jeff_Alieffson · Accepted Answer · 2017-03-03 07:58:39Z

20

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

answered Mar 3, 2017 at 7:58

Jeff_Alieffson

2,8921 gold badge32 silver badges34 bronze badges

1 Comment

Prem P. Over a year ago

Thank you! This worked for me and saved me a ton of time!

oberlies · Accepted Answer · 2017-11-27 13:41:58Z

20

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.

answered Nov 27, 2017 at 13:41

oberlies

11.8k5 gold badges69 silver badges117 bronze badges

Comments

Ahmed Salem · Accepted Answer · 2016-04-14 09:26:01Z

18

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

answered Apr 14, 2016 at 9:26

Ahmed Salem

1,75723 silver badges26 bronze badges

Comments

pL4Gu33 · Accepted Answer · 2015-03-03 10:54:44Z

8

Use set Parameter from query.

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

edited Mar 3, 2015 at 10:54

answered Mar 3, 2015 at 10:48

pL4Gu33

2,12517 silver badges40 bronze badges

4 Comments

pL4Gu33 Over a year ago

no that is the right import. But you should use integer parameters. NativeQuery doesnt works with string parameters. I edit my answer.

membersound Over a year ago

By the way, you might want to revert your edit. It DOES work with named param (... username = :username).

pL4Gu33 Over a year ago

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

Predrag Maric Over a year ago

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

lekant · Accepted Answer · 2016-02-12 17:42:03Z

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

Collectives™ on Stack Overflow

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

6 Answers 6

3 Comments

1 Comment

Comments

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

1 Comment

Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related