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

Answer 1

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:

1. Gave the persistence context the same name as the project (don't know if that is required)
2. 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.
3. Moved the persistence.xml to the src/META-INF/ folder
4. Added persistence provider
5. Used HSQL DB, so changed the schema name to the name that was created by HSQLDB - MyDB in my case
6. 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.