CODEWITHSUNDEEP
jpaderby
Carmine
Carmine

Reputation: 155

JPA subquery syntax error

I have the problem with a subquery with JPQL, same error occurs with Criteria. The query return a syntax error:

Encountered "," javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement.

JPQL

StringBuilder query = new StringBuilder("SELECT d FROM Document d WHERE NOT EXISTS (SELECT ds FROM Documents ds WHERE d = ds.key.document)"); 
TypedQuery<Documento> tQuery = entityManager.createQuery(query.toString());
List<Document> documents = tQuery.getResultList();

Criteria

    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Document> query = cb.createQuery(Document.class);

    Root<Document> root = query.from(Document.class);
    CriteriaQuery<Document> select = query.select(root);

    Subquery<Documents> sQuery = query.subquery(Documents.class);
    Root<Documents> rootSubquery = sQuery.from(Documents.class);
    Predicate p = cb.equal(rootSubquery.get("key").get("document"), root);
    sQuery.select(rootSubquery);
    sQuery.where(p);
    select.where(cb.not(cb.exists(sQuery)));
    TypedQuery<Documento> tQuery = entityManager.createQuery(query);
    List<Document> documents = tQuery.getResultList();

Upvotes: 0

Views: 253

Answers (2)

Carmine
Carmine

Reputation: 155

I do not know if it's a bug or should that be so, but having a composite key I specified in the columns of the subquery the affected key and it works. This occurs only in subqueries in other select I have not encountered this error

the changes:

JPQL

StringBuilder query = new StringBuilder("SELECT d FROM Document d WHERE NOT EXISTS (SELECT ds.key.document FROM Documents ds WHERE ds.key.document = d)");

Criteria

...
Subquery<Documents> sQuery = query.subquery(Documents.class);
Root<Documents> rootSubquery = sQuery.from(Documents.class);
Predicate p = cb.equal(rootSubquery.get("key").get("document"), root);
sQuery.select(rootSubquery.get("key").get("document"));
sQuery.where(p);
select.where(cb.not(cb.exists(sQuery)));
...

Upvotes: 0

ujulu
ujulu

Reputation: 3309

The query seems to be correct. But the word KEY is a reserved word in Derby. That should be the reason. So you better rename this attribute in the Documents entity.

Upvotes: 1

Related Questions