Need help resolving JPQL syntax error

Support
#1

I am fairly new to JPQL and coming from SQL there are some things I still need to get familiar with, so the problem might be caused by that fact.

Working with the CUBA-Framework, I am trying to create a new entity with a JPQL like a form of projection in SQL and I already have succeeded in doing so but now with another case, I got a syntax error. So here is my JPQL:

SELECT NEW
  com.example.vet.entity.vet_AnimalInformation(a.cage,0,0,'test',0)
  FROM vet_Animals a

This gets transformed internally into what you can see in the exception below and gives me an error:

    An exception occurred while creating a query in EntityManager:
    Exception Description: Syntax error parsing 
    [SELECT NEWcom.example.vet.entity.vet_AnimalInformation(tempEntityAlias,0,0,'test',0) FROM vet_Cage tempEntityAlias, vet_Animals a where tempEntityAlias.id = a.cage.id].
    [71, 72] The SELECT clause has 'NEWcom.example.vet.entity.vet_AnimalInformation' and '(tempEntityAlias, 0, 0, 'test', 0)' that are not separated by a comma.

Whyever this restructuring occurs, I can live with that, although in comparison to SQL I feel a lack of influence on the statement here :slight_smile: somehow the constructor is not recognized as such.
What am I doing wrong here?

Thanks in advance!

#2

So I tried to rewrite my original SELECT statement in the exact way that was shown in the previous error message. Funny enough, now I get another error, that the constructor with those types of parameters could not be found. BUT: the constructor is recognized.
I assumend maybe there could be a bug when my original SELECT statement got restructured that the blank between NEW and the constructor gets lost? But then again, when I left that blank out in my statement, I got an error message in the log but not the exception mentioned in the post above.

#3

Hi Oliver,

You should be able to execute JPQL with new through the EntityManager in the core module.
For example, if you have a class:

package com.company.sales.entity;

public class CustomerProjection {

    private Customer customer;
    private String name;
    private String email;

    public CustomerProjection(Customer customer, String name, String email) {
        this.customer = customer;
        this.name = name;
        this.email = email;
    }

    public Customer getCustomer() {
        return customer;
    }

    public String getName() {
        return name;
    }

    public String getEmail() {
        return email;
    }
}

you can execute the query:

select new com.company.sales.entity.CustomerProjection(c, c.name, c.email) 
from sales_Customer c

Your exception message makes me think that there is no space between NEW and the class name. Please check it.

#4

Thank you very much for the fast reply and the helpful example.

In fact I got a space between NEW and the class name but the JPQL gets restructured internally into the form given in the error message. I think the reason is, that I use “a.cage” where cage is an association, so the framework joins the associated table. In that process, the space after NEW gets lost.

Sometimes this restructuring also gives me headaches when using LEFT JOIN because the SQL that is generated does not work because of the order of the resulting TABLES. This is a different problem for sure but is there a guideline or FAQ on how to use JPQL and how it gets transformed into SQL?

#5

I have checked your hypothesis about association attribute, and it worked for me without any issues.

It seems that you have a test project with the data model, could you attach it here? If the problem is reproduced, we will be able to fix it.

#6

test.rar (1009.5 KB)

Thank you for testing it. I was able to recreate the problem with a test project that I am attaching. I had to omit the tomcat-folder from the deploy-folder because it would have been too big in size. But it had nothing special in it, just like it was after creating the project.

To trigger the error, you have to go to “Application->Animals per special cages” and click on the “Calculate”-button. Then a service gets called where the JPQL is executed.

#7

Thank you for the test project, I was able to reproduce the problem. Perhaps it is caused by the combined usage of NEW and SUM. I’ve created an issue.

As a workaround, you can rewrite the query without NEW and use some Java code to map result set to new entity instances:

public void CalculateAnimalsPerSpecialCage() {
    Transaction tx = persistence.createTransaction();
    try {
        EntityManager em = persistence.getEntityManager();

        TypedQuery<Object[]> queryAnimals = em.createQuery(
                "SELECT ca.cage,SUM(ca.count) " +
                        "FROM test_CagedAnimal ca JOIN test_SpecialCages sc ON sc.cage=ca.cage " +
                        "GROUP BY ca.cage", Object[].class);

        queryAnimals.getResultList().stream()
                .map(row -> {
                    Cage cage = (Cage) row[0];
                    Long count = (Long) row[1];
                    AnimalsPerSpecialCage entity = metadata.create(AnimalsPerSpecialCage.class);
                    entity.setCageNr(cage.getNr());
                    entity.setAnimalCount(Math.toIntExact(count));
                    return entity;
                })
                .forEach(em::persist);

        tx.commit();
    } finally {
        tx.end();
    }
}
#8

Hi Konstantin,

Thank you very much for the reply and the superb workaround. I wasn’t aware that you could return Object-Arrays as type/entity in JPQL, I have only used this with native queries.
Also a very nice expression for the result handling :slight_smile:

I can add, that I have working examples of NEW and SUM e.g. if I JOIN test_cage c and then use c instead of ca.cage in the cinstructor. So I will be watching this issue curiously. But for now you helped me out, thanks a lot!