Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » [SOLVED] Transform instruction SQL (PostgreSQL) to JPQL (@NamedQuery)(Transform instruction SQL (PostgreSQL) to JPQL (@NamedQuery))
[SOLVED] Transform instruction SQL (PostgreSQL) to JPQL (@NamedQuery) [message #765889] Wed, 14 December 2011 21:02 Go to next message
Olavo Jr. is currently offline Olavo Jr.Friend
Messages: 4
Registered: December 2011
Junior Member
Hy,

Tools:
Eclipselink 2.3.2.v20111125-r10461
JPA 2.0.3.v201010191057


I'm trying to convert this SQL statement written for PostgreSQL 9.1.1 for JPQL, but without success:

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) = 2011


This was my last attempt:

@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")


This is the error I get:

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)


Any help is always welcome

Olavo Jr.

[Updated on: Thu, 15 December 2011 13:57]

Report message to a moderator

[SOLVED] Transform instruction SQL (PostgreSQL) to JPQL (@NamedQuery) [message #766284 is a reply to message #765889] Thu, 15 December 2011 13:56 Go to previous messageGo to next message
Olavo Jr. is currently offline Olavo Jr.Friend
Messages: 4
Registered: December 2011
Junior Member
Hy,

With the grace of God I made it the solution, using the @ NamedQuery, see the solution that ran flawlessly:

@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")


Hugs to all and good code ...

Olavo Jr.

Report message to a moderator

Re: [SOLVED] Transform instruction SQL (PostgreSQL) to JPQL (@NamedQuery) [message #809136 is a reply to message #766284] Tue, 28 February 2012 14:14 Go to previous messageGo to next message
FiruzzZ Mising name is currently offline FiruzzZ Mising nameFriend
Messages: 19
Registered: October 2011
Junior Member
I think that u really could help me with my problem..
Im trying to run a JPQL with some Date field:

Example: 
"SELECT o FROM AfiliacionDetalle o
 WHERE o.documentoFecha >= '" +aDateObject+ "'"


but! result that Eclikpse doesn't allow this kind of JPQL.

I don't know what to do, I got too many queries with Date conditions.

now Im trying with this, without success
"SELECT o FROM AfiliacionDetalle o WHERE o.documentoFecha <= FUNC('date_trunc','hour', timestamp '" + new Date() + "')"

[Updated on: Tue, 28 February 2012 14:40]

Report message to a moderator

Re: [SOLVED] Transform instruction SQL (PostgreSQL) to JPQL (@NamedQuery) [message #810789 is a reply to message #809136] Thu, 01 March 2012 15:04 Go to previous message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member
Use a parameter for the date,

"SELECT o FROM AfiliacionDetalle o WHERE o.documentoFecha >= :aDate"

Then set the date parameter when you execute the query.

If you really wanted to inline the date, dates are printed in JPQL using the escape clause, {d'yyyy-mm-dd'}

See,
http://en.wikibooks.org/wiki/Java_Persistence/JPQL_BNF#Literals

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Previous Topic:strange Persistence exception with a List type
Next Topic:How to share EntityManagerFactory when different MetaDataSource per tanant
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Sep 25 13:39:31 GMT 2024

Powered by FUDForum. Page generated in 0.06372 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly