Hi,
I'm trying to execute the next query using CriteriaBuilder and CriteriaQuery interfaces:
Code:
select DES_EVE_CAT, COUNT(*) from KWXS.TKWXSSAU where ( DES_EVE_CAT in ('asociaServicioMejorado') ) and ( TIM_EVENTO<=TO_DATE('31/10/2014','DD/MM/YYYY') ) group by DES_EVE_CAT;
My code to represent the sql expression is like this:
Code:
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<TkwxssauWrapper> cq = cb.createQuery(TkwxssauWrapper.class);
Root <Tkwxssau> tkwxssau = cq.from(Tkwxssau.class);
List<Predicate> predicates = new ArrayList<Predicate>();
Expression<String> expDesEveCat = tkwxssau.get("desEveCat");
//predicates.add(expDesEveCat.in(consumptionTypes));
predicates.add(cb.equal(expDesEveCat, EventoAplicacion.EVENTOAPLICACION_REGISTER_SOA_SERVICE_CONSUMPTION));
Path<Date> dateTimEventoPath = tkwxssau.get("timEvento");
if (date1 != null && date2 != null){
predicates.add(cb.between(dateTimEventoPath, date1, date2));
} else if (date1 != null) {
predicates.add(cb.greaterThanOrEqualTo(dateTimEventoPath, date1));
} else if (date2 != null) {
predicates.add(cb.lessThanOrEqualTo(dateTimEventoPath, date2));
}
Path<Long> desEveCatPath = tkwxssau.get( "desEveCat" );
// cq.multiselect(desEveCatPath, cb.count(tkwxssau));
cq.select(cb.construct(TkwxssauWrapper.class, desEveCatPath, cb.count(tkwxssau)));
cq.where(cb.and(predicates.toArray(new Predicate[predicates.size()])));
cq.groupBy(tkwxssau.get("desEveCat"));
TypedQuery<TkwxssauWrapper> q = entityManager.createQuery(cq);
List<TkwxssauWrapper> results = q.getResultList();
It seems the code is right, but when I'm debugging it, it finishes with next error :
org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.SQLGrammarException: could not execute query; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:311)
at
Caused by: java.sql.SQLException: ORA-00907: missing right parenthesis
The sentence generated by my code is this :
select new com.bbva.kwxs.gobsoabacksearch.TkwxssauWrapper(generatedAlias0.desEveCat, count(generatedAlias0)) from Tkwxssau as generatedAlias0 where ( generatedAlias0.desEveCat in (:param0) ) and ( generatedAlias0.timEvento<=:param1 ) group by generatedAlias0.desEveCat
if I translate it to sql to execute in the Oracle SQL Database like this,
Code:
select DES_EVE_CAT, COUNT(generatedAlias0) from KWXS.TKWXSSAU as generatedAlias0 where ( generatedAlias0.DES_EVE_CAT in ('asociaServicioMejorado') ) and ( generatedAlias0.TIM_EVENTO<=TO_DATE('31/10/2014','DD/MM/YYYY') ) group by generatedAlias0.DES_EVE_CAT;
shows the followed error :
Error: ORA-00933: SQL command not properly ended
SQLState: 42000
ErrorCode: 933
I have seen that Oracle does not accept the alias clause AS: from KWXS.TKWXSSAU as generatedAlias0, and I think this is my error.
Is there any possibility to remove the alias or generate the alias without AS clause?
I've checked my configurations files and I 've seen that the mapping to Oracle is correctly indicated in the persintence.xml file :
Code:
<property name="hibernate.dialect" value="org.hibernate.dialect.OracleDialect"/>
I have been looking for the answer for other pages and I still haven't found any solution.
Thanks for you help.
Regards.
José Pascual
Login
FAQ
Search
