jpql (NamedQuery) subquery not working with parameter

Question

I'm using JPQL and a NamedQuery to query the database in a combined question.

@NamedQuery(name = "Entry.findAllForName", query = "Select p From Entry p Where p.entryId In(Select dp.entry From EntryPart dp Where dp.part IN(Select d From Part d Where d.senderId = :name))"),

When I run this query, using:

Query query = em.createNamedQuery("Entry.findAllForName").setParameter("name", name);
List<Entry> list = query.getResultList();
logger.info("Complete query size: {}", list.size());

This does return a result set, but it is 0.

When I change the Named Query to:

@NamedQuery(name = "Entry.findAllForName", query = "Select p From Entry p Where p.entryId In(Select dp.entry From EntryPart dp Where dp.part IN(Select d From Part d Where d.senderId = 'SenderName'))"),

It works and I get the expected database result.

I'm not that familiar with JPQL and @NamedQueries, but I can't see the difference between having the String vaule and the parameter value, since when I do a diff, they are the same.

logger.info("SenderName == {} ? {}", name, name.equals("SenderName"));

This returns "SenderName == SenderName ? true"...

Best, Henrik

Can you debug and check what is the value of name here -->Query query = em.createNamedQuery("Entry.findAllForName").setParameter("name", name); — Gayathri
– Gayathri, Commented Jun 3, 2014 at 15:07
Yes, I also see that the debugging is saying, found parameter 'name' and setting it to "SenderName". — Hiny
– Hiny, Commented Jun 4, 2014 at 5:18

Teena George · Accepted Answer · 2014-06-05 06:43:04Z

I am not able to reproduce your problem. I got the expected results. Can you please post more code? Here is the code which I tested and working. I got this example from Java2s for a quick set up. But it did not work at first so I made some changes. Java2s named query example The changes I did were:

Gave the persistence context the same name as the project (don't know if that is required)
In the Main class, I changed the name of the persistence unit to EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPATest"); because this is the name I used.
Moved the persistence.xml to the src/META-INF/ folder
Added persistence provider
Used HSQL DB, so changed the schema name to the name that was created by HSQLDB - MyDB in my case
The persistence tag had one wrong url which I removed. The one with ../persistence/persistence

Here is the named queries in professor.java file, I modified to see if I could replicate your problem:

    @NamedQueries({
  @NamedQuery(name="findProfessorsAboveSal",
              query="SELECT e " +
                    "FROM Professor e " +
                    "WHERE e.department = :dept AND " +
                    "      e.salary > :sal AND e.id IN(1000)"),
  @NamedQuery(name="replicateError", query="select e from Professor e where "+
"e.id in(select prj from Project prj where prj.id = ?1)")})

Here is my modified persistence.xml

    <?xml version="1.0" encoding="UTF-8"?> 
<persistence xmlns="http://java.sun.com/xml/ns/persistence" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence 
http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd" version="1.0"> 
  <persistence-unit name="JPATest" transaction-type="RESOURCE_LOCAL">
  <provider>org.hibernate.ejb.HibernatePersistence</provider>
   <class>test.ProfessorService</class>
       <class>test.Professor</class>
       <class>test.Project</class>
       <class>test.Department</class>
       <class>test.JPAUtil</class>
    <properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
      <property name="hibernate.hbm2ddl.auto" value="update"/>
      <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/>
      <property name="hibernate.connection.username" value="sa"/>
      <property name="hibernate.connection.password" value=""/>
      <property name="hibernate.connection.url" value="jdbc:hsqldb:data/MyDB"/>
    </properties>
  </persistence-unit>
</persistence>

Finally, I used these jars:

    antlr-2.7.7.jar
asm-3.3.1.jar
cglib-2.2.jar
commons-collections-3.2.1.jar
commons-logging-1.1.1.jar
dom4j-1.6.1.jar
hibernate-3.2.6.ga.jar
hibernate-annotations-3.4.0.GA.jar
hibernate-commons-annotations-3.2.0.Final.jar
hibernate-core-3.6.5.Final.jar
hibernate-entitymanager-3.6.1.Final.jar
hibernate-jpa-2.0-api-1.0.0.Final.jar
hsqldb-1.8.0.7.jar
javassist.jar
jta-1.1.jar
slf4j-api-1.5.8.jar
slf4j-jdk14-1.5.8.jar

Hope this helps.

Thank you, after redoing it as the example provided, it started working, I'm unsure of what was missing, but it is as expected now. Best, Henrik

Rahul Sharma · Accepted Answer · 2018-11-15 05:55:02Z

0

Have you tried using setString instead of setParameter when creatina g query?

edited Nov 15, 2018 at 5:55

Rahul Sharma

13.7k6 gold badges23 silver badges30 bronze badges

answered Jun 3, 2014 at 13:06

Amir M

3544 silver badges19 bronze badges

Collectives™ on Stack Overflow

jpql (NamedQuery) subquery not working with parameter

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related