CODEWITHSUNDEEP
playframeworkhql
Thang Nguyen
Thang Nguyen

Reputation: 1151

SQL in Play!Framework

I have following SQL which run perfectly in SQL software (HeidiSQL)

SELECT gl2.documentNumber, gl2.debitAmount, gl2.creditAmount, 
 gl2.account, gl2.description FROM GeneralLedger AS gl1 
LEFT JOIN GeneralLedger AS gl2 
ON gl1.documentNumber = gl2.documentNumber 
WHERE gl1.account='911' AND gl2.account <> '911';

When put it into Play! controller, I use this:

     String queryPL = "SELECT gl2.documentNumber, gl2.debitAmount, gl2.creditAmount, "
            + "gl2.account, gl2.description FROM GeneralLedger AS gl1 "
            + "LEFT JOIN GeneralLedger AS gl2 "
            + "ON gl1.documentNumber = gl2.documentNumber "
            + "WHERE gl1.account='911' AND gl2.account <> '911'";
    Query query = JPA.em().createQuery(queryPL);
    List<Object[]> profitAndLoss = query.getResultList();

and received error:

IllegalArgumentException occured : org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ON near line 1, column 156 [SELECT gl2.documentNumber, gl2.debitAmount, gl2.creditAmount, gl2.account, gl2.description FROM models.GeneralLedger AS gl1 LEFT JOIN GeneralLedger AS gl2 ON gl1.documentNumber = gl2.documentNumber WHERE gl1.account='911' AND gl2.account <> '911']

I revised the HQL to 

String queryPL = "SELECT gl2.documentNumber, gl2.debitAmount, gl2.creditAmount, "
                + "gl2.account, gl2.description FROM models.GeneralLedger AS gl1 "
                + "LEFT OUTER JOIN models.GeneralLedger AS gl2 "
                + "WITH gl1.documentNumber = gl2.documentNumber "
                + "WHERE gl1.account='911' AND gl2.account <> '911'";
        Query query = JPA.em().createQuery(queryPL);
        List<Object[]> profitAndLoss = query.getResultList();

and still receive an error:

IllegalStateException occured : DOT node with no left-hand-side!

So how can I fix this problem?

Upvotes: 0

Views: 1696

Answers (1)

JB Nizet
JB Nizet

Reputation: 691943

To be able to make a join between two entities in HQL, you need to have an association between these two entities. You can't make a join between two unrelated entities on any condition like you can in SQL. HQL and its join capabilities are described in great details in the Hibernate documentation.

If you really need this type of join, use a native query.

Upvotes: 1

Related Questions