Srs.,
Ferramentas:
Eclipselink 2.3.2.v20111125-r10461
JPA 2.0.3.v201010191057
Eu estou tentando converter esta instrução SQL escrita para PostgreSQL 9.1.1 para JPQL, mas sem sucesso:
SELECT fu.codigo, fu.nome, fu.admissao_data, ps.situacao FROM funcionario fu
INNER JOIN Pessoa ps ON ps.codigo = fu.codigo
WHERE ps.situacao = true
AND date_part('MONTH', admissao_data) = 1
AND date_part('YEAR', admissao_data) = 2011Esta foi a minha última tentativa:
@NamedQuery(name="FuncionarioPorMesAnoAdmissao", query="SELECT fu.codigo, fu.nome FROM Funcionario fu WHERE fu.situacao = :situacao AND FUNC(DATE_PART('MONTH', fu.admissaoData)) = :mes AND FUNC(DATE_PART('YEAR', fu.admissaoData)) = :ano")Este é o erro que recebo:
Exception Description: Syntax error parsing the query [FuncionarioPorMesAnoAdmissao: SELECT fu.codigo, fu.nome FROM Funcionario fu WHERE fu.situacao = :situacao AND FUNC(DATE_PART('MONTH', fu.admissaoData)) = :mes AND FUNC(DATE_PART('YEAR', fu.admissaoData)) = :ano], line 1, column 85: syntax error at [DATE_PART].
Internal Exception: MismatchedTokenException(80!=100)
javax.persistence.PersistenceException: Exception [EclipseLink-8024] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [FuncionarioPorMesAnoAdmissao: SELECT fu.codigo, fu.nome FROM Funcionario fu WHERE fu.situacao = :situacao AND FUNC(DATE_PART('MONTH', fu.admissaoData)) = :mes AND FUNC(DATE_PART('YEAR', fu.admissaoData)) = :ano], line 1, column 85: syntax error at [DATE_PART].
Internal Exception: MismatchedTokenException(80!=100)
at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:517)
at org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.getDatabaseSession(EntityManagerFactoryDelegate.java:188)
at org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.createEntityManagerImpl(EntityManagerFactoryDelegate.java:277)
at org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.createEntityManagerImpl(EntityManagerFactoryImpl.java:294)
at org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:272)
at services.gerais.GnsSvrGrs1a.<init>(GnsSvrGrs1a.java:127)
at services.gerais.GnsSvrGrs1a.getInstancia(GnsSvrGrs1a.java:136)
at view.pessoal.GnsPssI1j.recuperaFuncionarios(GnsPssI1j.java:645)
at view.pessoal.GnsPssI1j.access$10(GnsPssI1j.java:627)
at view.pessoal.GnsPssI1j$7.actionPerformed(GnsPssI1j.java:489)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
at java.awt.Component.processMouseEvent(Component.java:6288)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
at java.awt.Component.processEvent(Component.java:6053)
at java.awt.Container.processEvent(Container.java:2041)
at java.awt.Component.dispatchEventImpl(Component.java:4651)
at java.awt.Container.dispatchEventImpl(Container.java:2099)
at java.awt.Component.dispatchEvent(Component.java:4481)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4577)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4238)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4168)
at java.awt.Container.dispatchEventImpl(Container.java:2085)
at java.awt.Window.dispatchEventImpl(Window.java:2478)
at java.awt.Component.dispatchEvent(Component.java:4481)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:643)
at java.awt.EventQueue.access$000(EventQueue.java:84)
at java.awt.EventQueue$1.run(EventQueue.java:602)
at java.awt.EventQueue$1.run(EventQueue.java:600)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
at java.awt.EventQueue$2.run(EventQueue.java:616)
at java.awt.EventQueue$2.run(EventQueue.java:614)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:613)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
Caused by: Exception [EclipseLink-8024] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [FuncionarioPorMesAnoAdmissao: SELECT fu.codigo, fu.nome FROM Funcionario fu WHERE fu.situacao = :situacao AND FUNC(DATE_PART('MONTH', fu.admissaoData)) = :mes AND FUNC(DATE_PART('YEAR', fu.admissaoData)) = :ano], line 1, column 85: syntax error at [DATE_PART].
Internal Exception: MismatchedTokenException(80!=100)
at org.eclipse.persistence.exceptions.JPQLException.syntaxErrorAt(JPQLException.java:362)
at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.handleRecognitionException(JPQLParser.java:305)
at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.addError(JPQLParser.java:246)
at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.reportError(JPQLParser.java:363)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.func(JPQLParser.java:7884)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.functionsReturningNumerics(JPQLParser.java:6738)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticPrimary(JPQLParser.java:4762)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticFactor(JPQLParser.java:4660)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticTerm(JPQLParser.java:4546)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.simpleArithmeticExpression(JPQLParser.java:4462)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.arithmeticExpression(JPQLParser.java:4402)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.simpleConditionalExpression(JPQLParser.java:3321)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.conditionalPrimary(JPQLParser.java:3275)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.conditionalFactor(JPQLParser.java:3194)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.conditionalTerm(JPQLParser.java:3127)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.conditionalExpression(JPQLParser.java:3029)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.whereClause(JPQLParser.java:2986)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.selectStatement(JPQLParser.java:380)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.document(JPQLParser.java:281)
at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.parse(JPQLParser.java:134)
at org.eclipse.persistence.internal.jpa.parsing.jpql.JPQLParser.buildParseTree(JPQLParser.java:95)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:215)
at org.eclipse.persistence.internal.jpa.JPAQuery.processJPQLQuery(JPAQuery.java:106)
at org.eclipse.persistence.internal.jpa.JPAQuery.prepare(JPAQuery.java:90)
at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:613)
at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:575)
at org.eclipse.persistence.internal.sessions.AbstractSession.processJPAQueries(AbstractSession.java:2161)
at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.initializeDescriptors(DatabaseSessionImpl.java:442)
at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.postConnectDatasource(DatabaseSessionImpl.java:676)
at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.loginAndDetectDatasource(DatabaseSessionImpl.java:621)
at org.eclipse.persistence.internal.jpa.EntityManagerFactoryProvider.login(EntityManagerFactoryProvider.java:206)
at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:488)
... 45 more
Caused by: MismatchedTokenException(80!=100)
at org.eclipse.persistence.internal.libraries.antlr.runtime.BaseRecognizer.recoverFromMismatchedToken(BaseRecognizer.java:610)
at org.eclipse.persistence.internal.libraries.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.func(JPQLParser.java:7839)
... 72 more
Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException
at services.gerais.GnsSvrGrs1a.getRetornaListaporCodigo(GnsSvrGrs1a.java:886)
at view.pessoal.GnsPssI1j.recuperaFuncionarios(GnsPssI1j.java:645)
at view.pessoal.GnsPssI1j.access$10(GnsPssI1j.java:627)
at view.pessoal.GnsPssI1j$7.actionPerformed(GnsPssI1j.java:489)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
at java.awt.Component.processMouseEvent(Component.java:6288)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
at java.awt.Component.processEvent(Component.java:6053)
at java.awt.Container.processEvent(Container.java:2041)
at java.awt.Component.dispatchEventImpl(Component.java:4651)
at java.awt.Container.dispatchEventImpl(Container.java:2099)
at java.awt.Component.dispatchEvent(Component.java:4481)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4577)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4238)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4168)
at java.awt.Container.dispatchEventImpl(Container.java:2085)
at java.awt.Window.dispatchEventImpl(Window.java:2478)
at java.awt.Component.dispatchEvent(Component.java:4481)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:643)
at java.awt.EventQueue.access$000(EventQueue.java:84)
at java.awt.EventQueue$1.run(EventQueue.java:602)
at java.awt.EventQueue$1.run(EventQueue.java:600)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
at java.awt.EventQueue$2.run(EventQueue.java:616)
at java.awt.EventQueue$2.run(EventQueue.java:614)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:613)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)Qualquer ajuda é sempre bem-vinda
Olavo Jr.