Missing right parenthesis in CriteriaQuery expression

[Jose Pascual Gimeno]

Hi,
I'm trying to execute the next query using CriteriaBuilder and CriteriaQuery interfaces:

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:

List<String> consumptionTypes = Arrays.asList(EventoAplicacion.EVENTOAPLICACION_REGISTER_SOA_SERVICE_CONSUMPTION);

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,

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?

Thanks for you help.

José Pascual

[Martin Vashko]

Welcome to the Ranch, Jose!

I'm new to Hibernate myself at this time, so I may be wrong, but I'd say that the problem might be that Hibernate is not configured to use Oracle's dialect, thus generating SQL which is not valid in Oracle. I've found some information about configuring the dialect here: http://www.tutorialspoint.com/hibernate/hibernate_configuration.htm.

[Jose Pascual Gimeno]

Martin Vajsar wrote:Welcome to the Ranch, Jose!

I'm new to Hibernate myself at this time, so I may be wrong, but I'd say that the problem might be that Hibernate is not configured to use Oracle's dialect, thus generating SQL which is not valid in Oracle. I've found some information about configuring the dialect here: http://www.tutorialspoint.com/hibernate/hibernate_configuration.htm.

Hi Martin,
thanks for your answer.

I've checked my configurations files and I 've seen that the mapping to Oracle is correctly indicated in the persintence.xml file :

<property name="hibernate.dialect" value="org.hibernate.dialect.OracleDialect"/>

I have been looking for the answer for other pages and I still havent found any solution.

Thanks for you help.

Regards.

[Martin Vashko]

Jose Pascual Gimeno wrote:I've checked my configurations files and I 've seen that the mapping to Oracle is correctly indicated in the persintence.xml file :

<property name="hibernate.dialect" value="org.hibernate.dialect.OracleDialect"/>

Is this syntax supported? I've only seen this one:
<property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>That is, the property value comes as the text of the property tag, not as a value attribute. This might explain that the expected dialect isn't used.

[Jose Pascual Gimeno]

Martin Vajsar wrote:

Jose Pascual Gimeno wrote:I've checked my configurations files and I 've seen that the mapping to Oracle is correctly indicated in the persintence.xml file :

<property name="hibernate.dialect" value="org.hibernate.dialect.OracleDialect"/>

Is this syntax supported? I've only seen this one:
<property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>That is, the property value comes as the text of the property tag, not as a value attribute. This might explain that the expected dialect isn't used.

Hi Martin,
in this case I think is right, the persistence.xml file is like this :


<?xml version="1.0" encoding="UTF-8" standalone="no"?> <persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"> <persistence-unit name="persistenceUnit" transaction-type="RESOURCE_LOCAL"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <properties> <property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property> <!-- value="create" to build a new database on each run; value="update" to modify an existing database; value="create-drop" means the same as "create" but also drops tables when Hibernate closes; value="validate" makes no changes to the database --> <property name="hibernate.hbm2ddl.auto" value="validate"/> <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.DefaultNamingStrategy"/> <property name="hibernate.connection.charSet" value="UTF-8"/> <!-- Uncomment the following two properties for JBoss only --> <!-- property name="hibernate.validator.apply_to_ddl" value="false" /--> <!-- property name="hibernate.validator.autoregister_listeners" value="false" /--> </properties> </persistence-unit> </persistence>


and if I try to write the hibernate.dialect as you have said me, it shows the next error

Element 'property' must have no character or element information item [children], because the type's content type is

[Martin Vashko]

Looks like I was wrong about the syntax. The tutorial I've googled up is probably dated. Sorry about that, please revert to your earlier syntax.

When I start my Hibernate project, it prints out the configuration (including the database dialect in use) to the console. Can you see that in your program output too? Does it print out the dialect you've configured?

[Jose Pascual Gimeno]

Martin Vajsar wrote:Looks like I was wrong about the syntax. The tutorial I've googled up is probably dated. Sorry about that, please revert to your earlier syntax.

When I start my Hibernate project, it prints out the configuration (including the database dialect in use) to the console. Can you see that in your program output too? Does it print out the dialect you've configured?

Hi Martin,
I have been seeing the logs when my server is uploading and I have saw this warning:

27/2/2014 12:51:13.165 INFO [Dialect.?:?] Using dialect: org.hibernate.dialect.OracleDialect
27/2/2014 12:51:13.415 WARN [Oracle9Dialect.?:?] The Oracle9Dialect dialect has been deprecated; use either Oracle9iDialect or Oracle10gDialect instead
27/2/2014 12:51:13.415 WARN [OracleDialect.?:?] The OracleDialect dialect has been deprecated; use Oracle8iDialect instead

Is it possible that here is the error?

Should I change the dialect to Oracle8iDialect ?

Thanks for your help

Best regards

José Pascual

[Martin Vashko]

I guess that using the dialect corresponding to the exact version of your database would be the best option. Which version of Oracle are you using?

[Jose Pascual Gimeno]

Martin Vajsar wrote:I guess that using the dialect corresponding to the exact version of your database would be the best option. Which version of Oracle are you using?

Is the Oracle Database 11g Release 2 (11.2)

[Martin Vashko]

For 11g, you should use the org.hibernate.dialect.Oracle10gDialect dialect.

I'd also suggest to make sure you're using the latest version of Oracle's JDBC driver, and probably also the latest version of Hibernate itself.