CODEWITHSUNDEEP
javapostgresqljpajdbceclipselink
user10832358
user10832358

Reputation: 1

EclipseLink: createNativeQuery exeption - org.postgresql.util.PSQLException

I'm running into a weird issue that hasn't been reported by others from what I could tell, there may be a bug when using createNativeQuery. I have tried so many things to understand why to no avail.

Current Environment:

When executing:

Long total = (Long) em.createNativeQuery("Project.count").getSingleResult();

It's throwing the following:

    javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.v20180807-4be1041): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: syntax error at or near "Project"
  Position: 1
Error Code: 0
Call: Project.count
Query: DataReadQuery(sql="Project.count")
    at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:391)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:264)
    at org.eclipse.persistence.internal.jpa.QueryImpl.getSingleResult(QueryImpl.java:530)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:404)
    at com.bbandt.util.testers.ProjectTester.ProjectVersionMapping(ProjectTester.java:43)
    at com.bbandt.util.testers.ProjectTester.main(ProjectTester.java:21)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.v20180807-4be1041): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: syntax error at or near "Project"
  Position: 1
Error Code: 0
Call: Project.count
Query: DataReadQuery(sql="Project.count")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:342)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:691)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:567)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2096)
    at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:603)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:275)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:261)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:332)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelect(DatasourceCallQueryMechanism.java:314)
    at org.eclipse.persistence.queries.DataReadQuery.executeNonCursor(DataReadQuery.java:199)
    at org.eclipse.persistence.queries.DataReadQuery.executeDatabaseQuery(DataReadQuery.java:154)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:914)
    at org.eclipse.persistence.queries.DataReadQuery.execute(DataReadQuery.java:139)
    at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:813)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2981)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1895)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1877)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1842)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:262)
    ... 4 more
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "Project"
  Position: 1
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:106)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1015)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:651)
    ... 21 more

Persistence.xml (truncated purposely )

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="LocalPostgreSQL" transaction-type="RESOURCE_LOCAL">

        <mapping-file>META-INF/orm.xml</mapping-file>
        <mapping-file>META-INF/Queries.xml</mapping-file>
<!--        <mapping-file>META-INF/ResultsetMappings.xml</mapping-file> 
-->

        <class>com.bbandt.jpa.converters.UUIDAttributeConverter</class>
        <class>model.Component</class>
        <class>model.ComponentVulnerability</class>
        <class>model.ComponentVulnerabilityPK</class>
        <class>model.Project</class>
        <class>model.ProjectVersion</class>
        <class>model.ProjectVersionPK</class>

ORM.xml contents:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings version="2.1"
    xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm http://xmlns.jcp.org/xml/ns/persistence/orm_2_1.xsd">

    <named-native-query name="Project.count">
        <query>Select count(*) as total from public.project</query>
    </named-native-query>

</entity-mappings>

From what I could tell, the named native query is not getting resolved back to the actual SQL contents.

Executing this works (and it resolves the named-native-query name "Project.count"):

Long total = (Long) em.createNamedQuery("Project.count").getSingleResult();

This also works:

Long total = (Long) em.createNativeQuery("Select count(*) from project").getSingleResult();

I didn't want to resort to JPQL for other reasons unimportant to this thread and I also need to implement SQL Resultset Mappings.

Any insight on this would be greatly appreciated. I truly ran out of ideas.

Upvotes: 0

Views: 277

Answers (1)

Indrakumara
Indrakumara

Reputation: 1645

createNamedQuery: Used to define queries wthe ith name in mapping file or annotation createNativeQuery: Used to execute native/pure SQL queries

Since you use createNativeQuery method with a named query you are getting above error

Upvotes: 1

Related Questions