Pessoal, tenho o seguinte método usando quary nativa do hibernate:
public List<Workflow> listaPorUsuario(GrupoWorkflow grupoWorkflow, String termo){
String sql = "SELECT distinct t.id_workflow," +
" t.descricao, " +
" t.quantidade_aprovacoes, " +
" t.criterio_inicio, " +
" t.modo_notificacao, " +
" t.tempo_limite, " +
" t.prioridade " +
" FROM( "+
" SELECT w.id_workflow, "+
" w.descricao, "+
" w.quantidade_aprovacoes, "+
" w.criterio_inicio, "+
" w.modo_notificacao, "+
" w.tempo_limite, "+
" w.prioridade "+
" FROM wf_workflow w, "+
" wf_grupo_workflow gw, "+
" wf_tabela_base tb, "+
" wf_workflow_item wi "+
" WHERE w.id_grupo_workflow = :grupo_workflow "+
" AND w.id_workflow = wi.id_workflow "+
" AND w.id_grupo_workflow = gw.id_grupo_workflow "+
" AND w.id_tabela_base = tb.id_tabela_base "+
" AND wi.username = :username "+
" UNION ALL "+
" SELECT w.id_workflow, "+
" w.descricao, "+
" w.quantidade_aprovacoes, "+
" w.criterio_inicio, "+
" w.modo_notificacao, "+
" w.tempo_limite, "+
" w.prioridade "+
" FROM wf_workflow w, "+
" wf_grupo_workflow gw, "+
" wf_tabela_base tb "+
" WHERE w.id_grupo_workflow = :grupo_workflow "+
" AND w.id_grupo_workflow = gw.id_grupo_workflow "+
" AND w.id_tabela_base = tb.id_tabela_base "+
" AND Not Exists (Select * From wf_workflow_item i "+
" Where i.id_workflow = w.id_workflow) "+
" )t ";
return getSession().createSQLQuery(sql)
.addEntity(Workflow.class)
.setLong("grupo_workflow", grupoWorkflow.getId_grupo_workflow())
.setString("username", termo)
.list();
}
Executei esta query diretamente no banco de dados e executou normalmente, mas qdo tento executar pelo sistema, dá o seguinte erro:
16:49:42,252 ERROR [vraptor2]:253 - Servlet.service() for servlet vraptor2 threw exception
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:118)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1684)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
at sensatta.dao.WorkflowDao.listaPorUsuario(WorkflowDao.java:80)
at sensatta.logic.WorkflowLogic.listaPorGrupo(WorkflowLogic.java:71)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.vraptor.component.DefaultLogicMethod.execute(DefaultLogicMethod.java:117)
at org.vraptor.interceptor.ExecuteLogicInterceptor.intercept(ExecuteLogicInterceptor.java:37)
at org.vraptor.core.InterceptorsLogicFlow.execute(InterceptorsLogicFlow.java:72)
at org.vraptor.interceptor.SettingAndValidationInterceptor.intercept(SettingAndValidationInterceptor.java:131)
at org.vraptor.core.InterceptorsLogicFlow.execute(InterceptorsLogicFlow.java:72)
at org.vraptor.interceptor.InjectionInterceptor.intercept(InjectionInterceptor.java:41)
at org.vraptor.core.InterceptorsLogicFlow.execute(InterceptorsLogicFlow.java:72)
at org.vraptor.interceptor.ComponentLookupInterceptor.intercept(ComponentLookupInterceptor.java:58)
at org.vraptor.core.InterceptorsLogicFlow.execute(InterceptorsLogicFlow.java:72)
at sensatta.logic.DaoInterceptor.intercept(DaoInterceptor.java:17)
at org.vraptor.core.InterceptorsLogicFlow.execute(InterceptorsLogicFlow.java:72)
at org.vraptor.interceptor.RegisterAttributesInteceptor.intercept(RegisterAttributesInteceptor.java:38)
at org.vraptor.core.InterceptorsLogicFlow.execute(InterceptorsLogicFlow.java:72)
at org.vraptor.core.VRaptorExecution.execute(VRaptorExecution.java:88)
at org.vraptor.core.DefaultController.execute(DefaultController.java:42)
at org.vraptor.VRaptorServlet.service(VRaptorServlet.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: Nome de coluna inválido
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:5971)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1527)
at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:1540)
at org.hibernate.type.LongType.get(LongType.java:28)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)
at org.hibernate.type.ManyToOneType.hydrate(ManyToOneType.java:95)
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:1983)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1372)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1300)
at org.hibernate.loader.Loader.getRow(Loader.java:1197)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:569)
at org.hibernate.loader.Loader.doQuery(Loader.java:689)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
... 43 more
P.S.: Na entidade Workflow exiete dois relacionamentos:
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name = "id_tabela_base" , referencedColumnName = "id_tabela_base")
private TabelaBase tabelaBase;
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name = "id_grupo_workflow" , referencedColumnName = "id_grupo_workflow")
private GrupoWorkflow grupoWorkflow;
O que pode ser este erro ?
Valew.