Boa noite,
Tenho uma aplicação que executa querys.
Uma query de exemplo que está dando problema é a seguinte:
SELECT flags.name AS operadora,
region.name AS regiao,
DATE_FORMAT( data_per_region.date, '%M' ) AS mes,
(SUM(transactions_fail) + SUM(transactions_success)) AS transacoes
FROM data_per_region
INNER JOIN flags ON flags.id = data_per_region.flags_id
INNER JOIN region ON region.id = data_per_region.region_id
WHERE data_per_region.date BETWEEN DATE_FORMAT( NOW(), '%Y-%m-01 00:00:00' ) AND CONCAT( LAST_DAY( NOW() ), ' 23:59:59' )
GROUP BY data_per_region.flags_id, data_per_region.region_id
A query está correta e traz dados.
Porém ao executar ela usando o createSQLQuery
SQLQuery query = session.createSQLQuery( queryEscaped );
A seguinte exception ocorre
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.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
at br.com.dpp.components.SQLProcessorComponent.process(SQLProcessorComponent.java:69)
at br.com.dpp.components.SQLProcessorComponent.process(SQLProcessorComponent.java:1)
at br.com.dpp.controller.ComponentController.refreshValue(ComponentController.java:62)
at sun.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at br.com.caelum.vraptor.interceptor.ExecuteMethodInterceptor.intercept(ExecuteMethodInterceptor.java:57)
at br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
at br.com.caelum.vraptor.interceptor.ExceptionHandlerInterceptor.intercept(ExceptionHandlerInterceptor.java:69)
at br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
at br.com.caelum.vraptor.core.LazyInterceptorHandler.execute(LazyInterceptorHandler.java:61)
at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
at br.com.caelum.vraptor.interceptor.ParametersInstantiatorInterceptor.intercept(ParametersInstantiatorInterceptor.java:90)
at br.com.caelum.vraptor.core.LazyInterceptorHandler.execute(LazyInterceptorHandler.java:59)
at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
at br.com.caelum.vraptor.interceptor.InstantiateInterceptor.intercept(InstantiateInterceptor.java:42)
at br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
at br.com.dpp.interceptors.NoCacheInterceptor.intercept(NoCacheInterceptor.java:39)
at br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
at br.com.dpp.interceptors.DaoInterceptor.intercept(DaoInterceptor.java:29)
at br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
at br.com.caelum.vraptor.interceptor.InterceptorListPriorToExecutionExtractor.intercept(InterceptorListPriorToExecutionExtractor.java:44)
at br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
at br.com.caelum.vraptor.interceptor.FlashInterceptor.intercept(FlashInterceptor.java:81)
at br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
at br.com.caelum.vraptor.interceptor.ResourceLookupInterceptor.intercept(ResourceLookupInterceptor.java:67)
at br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:54)
at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
at br.com.caelum.vraptor.core.ToInstantiateInterceptorHandler.execute(ToInstantiateInterceptorHandler.java:56)
at br.com.caelum.vraptor.core.DefaultInterceptorStack.next(DefaultInterceptorStack.java:53)
at br.com.caelum.vraptor.core.DefaultRequestExecution.execute(DefaultRequestExecution.java:70)
at br.com.caelum.vraptor.VRaptor$1.insideRequest(VRaptor.java:92)
at br.com.caelum.vraptor.ioc.spring.SpringProvider.provideForRequest(SpringProvider.java:56)
at br.com.caelum.vraptor.VRaptor.doFilter(VRaptor.java:89)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Column 'name' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1056)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1080)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5474)
at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3342)
at org.hibernate.type.StringType.get(StringType.java:41)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:210)
at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:497)
at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:443)
at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:340)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:629)
at org.hibernate.loader.Loader.doQuery(Loader.java:724)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
Em resumo, parece que ele não está encontrando a coluna ‘name’ que referêncio como flags.name e region.name
Tem alguma configuração que tenha que fazer? Ou o esse método não consegue identificar de qual tabela é essa coluna?
IMPORTANTE: Não tem como eu mapear a query pois é uma query do usuário com acesso a uma base dele, ou seja, é uma aplicação parecida com um Query Browser
Grato,
Brunno Tavares
