Amigos,
Estou levando uma surra da HQL.
Tenho minha classe AccountToPay (Entity) e quero fazer a soma (SUM) de todos os valores (AP.amount) agrupados fornecedor (SP.supplier) se a a conta esta paga ou nao (AP.paid) mas estou tendo a seguinte Exception. Seguem meus codigos. Qualquer ajuda ou comentario sao bem-vindos.
EXCEPTION:
[code]14:33:50,830 ERROR JDBCExceptionReporter:101 - ORA-00979: not a GROUP BY expression
Exception in thread “main” org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2231)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at uk.co.rangel.hibernate.dao.AccountToPayDAO.getAmoutAccountToPayGroupedBySupplierHQL(AccountToPayDAO.java:109)
at uk.co.rangel.hibernate.simulation.accountsupplier.TestAccountToPayByHQL.main(TestAccountToPayByHQL.java:32)
Caused by: java.sql.SQLException: ORA-00979: not a GROUP BY expression[/code]
ENTITY:
[code]import java.util.Calendar;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Lob;
import javax.persistence.ManyToOne;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import org.hibernate.annotations.Cascade;
import org.hibernate.annotations.CascadeType;
@Entity
public class AccountToPay {
@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE)
private Long id;
@Lob
private String description;
private Double amount;
@Temporal(TemporalType.DATE)
private Calendar paymentDate;
private boolean paid;
@ManyToOne
@Cascade(CascadeType.SAVE_UPDATE)
private Supplier supplier;
//gets() and sets()
}[/code]
DAO:
@SuppressWarnings("unchecked")
public List<Object[]> getAmoutAccountToPayGroupedBySupplierHQL(int max){
Query query = this.getDAO().getSession().createQuery("SELECT AP.supplier, AP.paid, SUM(AP.amount) " +
"FROM AccountToPay AP " +
"GROUP BY AP.supplier, AP.paid");
query.setMaxResults(max);
return query.list();
}
TESTE:
[code]public static void main(String[] args) {
AccountToPayDAO accountToPayDAO = new AccountToPayDAO(HibernateUtil.getSession());
List<Object[]> listObject = accountToPayDAO.getAmoutAccountToPayGroupedBySupplierHQL(2);
for(Object[] eachObject : listObject){
Supplier supplier = (Supplier) eachObject[0];
String situation;
if((Boolean) eachObject[1]){
situation = "PAID";
} else {
situation = "NOT PAID";
}
System.out.println(supplier.getName() + " - " + situation);
System.out.println("Amount to Pay: " + eachObject[2]);
System.out.println();
}
}[/code]
Valeu pessoal!