1

I'm having a problem with named querys, using ordinal parameters and the IN clause.

This is my Named Query:

@NamedQuery(name = "Photo.getByIds", query = "FROM Photo i WHERE i.User.idUser = ?1 AND i.idPhoto IN ?2")

This is the code to get the list of photos:

TypedQuery<Photo> q = entityManager.createNamedQuery("Photo.getByIds", Photo.class);

q.setParameter(1, 50);
q.setParameter(2, Arrays.asList(50, 51));

List<Photo> photoList = q.getResultList();

And that's the error I keep getting:

org.hibernate.QueryException: Named parameter does not appear in Query: 1 [FROM entity.Photo i WHERE i.User.idUser = ?1 AND i.idPhoto IN (:x20_, :x21_)] <java.lang.IllegalArgumentException: org.hibernate.QueryException: Named parameter does not appear in Query: 1 [FROM entity.Photo i WHERE i.User.idUser = ?1 AND i.idPhoto IN (:x20_, :x21_)]>java.lang.IllegalArgumentException: org.hibernate.QueryException: Named parameter does not appear in Query: 1 [FROM br.gov.sus.cadsus.entity.CadsusImagemFoto i WHERE i.cadsusFotografia.coSeqFotografia = ?1 AND i.coSeqImagem IN (:x20_, :x21_)]
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1376)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1317)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:255)

Caused by: org.hibernate.QueryException: Named parameter does not appear in Query: 1 [FROM entity.Photo i WHERE i.User.idUser = ?1 AND i.idPhoto IN (:x20_, :x21_)]
at org.hibernate.hql.classic.QueryTranslatorImpl.getNamedParameterLocs(QueryTranslatorImpl.java:551)
at org.hibernate.loader.Loader.bindNamedParameters(Loader.java:1910)
at org.hibernate.loader.Loader.bindParameterValues(Loader.java:1845)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1716)
at org.hibernate.loader.Loader.doQuery(Loader.java:801)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2542)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:940)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:246)
... 25 more

What's wrong? I checked the syntax of these methods/functions/jpql, and everything seems ok. I also tried using named parameters, and it worked. But that's not a soluting for me. I need to use ordinal parameters.

Someone has a clue?

pedro.lauro
  • 11
  • 2
  • 2
    Is Photo defined with a mapped User class? Which part of the query causes an error? i.User.idUser = ?1 or i.idPhoto IN ?2 or both? – Todd Murray Nov 29 '12 at 13:55
  • Yes, Photo is defined with a mapping to User. The exact query causes the error. I tried with just one of the parameters, and both worked isolated. But not together... – pedro.lauro Nov 29 '12 at 14:17
  • Odd, yes. The query "FROM entity.Photo i WHERE i.User.idUser = ?1 AND i.idPhoto IN (:x20_, :x21_)" has an ordinal param and 2 named params , :x20_ and :x21_. That's why the exception is raised. Try surrounding ?2 with parenths, like (?2). See http://stackoverflow.com/questions/4378824/adding-in-clause-list-to-a-jpa-query – Todd Murray Nov 29 '12 at 14:32
  • I tried doing what you said. Got the same error.... – pedro.lauro Nov 29 '12 at 16:47

1 Answers1

1

Select is missing. Query should be

@NamedQuery(name = "Photo.getByIds", query = "SELECT i FROM Photo i WHERE i.User.idUser = ?1 AND i.idPhoto IN ?2")
chalailama
  • 66
  • 5