Search code examples
javajpajoinnativequery

Java/JPA: Named Native Query with Join throws Exception

In my Benutzer-Entity I have following OneToMany Relationship:

@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval=true)
@JoinColumn(name="BENUTZER_ID")
private List<Dokument> dokumente = new ArrayList<>();

In my Dokument-Entity I'd now like to get a specific Dokument by searching it by its hash-value and its foreign-key (BenutzerID) - I'm trying to do this by NamedNativeQuery:

@NamedNativeQueries({
  @NamedNativeQuery(
    name="findDokumentByHashAndBenutzerID",
    query="SELECT d.* FROM Dokument d "
            + "JOIN Benutzer b ON d.Benutzer_id = b.Id_Benutzer WHERE d.Benutzer_id = ? AND d.Hash = ?")
})

When I use this Query in my Database (H2) it works, but in Java i get following exception:

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "FINDDOKUMENTBYHASHANDBENUTZERID[] "; erwartet "FROM, {" Syntax error in SQL statement "FINDDOKUMENTBYHASHANDBENUTZERID[] "; expected "FROM, {"; SQL statement: findDokumentByHashAndBenutzerID [42001-197] Error Code: 42001 Call: findDokumentByHashAndBenutzerID Query: ReadAllQuery(referenceClass=Dokument sql="findDokumentByHashAndBenutzerID") at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:377) at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260) at org.eclipse.persistence.internal.jpa.QueryImpl.getSingleResult(QueryImpl.java:516) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:400) at ch.insidebfh.persistence.repository.Repository.findByHash(Repository.java:91) at ch.insidebfh.application.service.ServiceHelper.findByHash(ServiceHelper.java:18) at ch.insidebfh.application.demo.Sandbox$1.lambda$0(Sandbox.java:92) at java.util.ArrayList.forEach(Unknown Source) at ch.insidebfh.application.demo.Sandbox$1.run(Sandbox.java:91) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException

Solution

  • Based on the exception I would guess that you call

     entityManager.createQuery("findDokumentByHashAndBenutzerID");

    instead of

     entityManager.createNamedQuery("findDokumentByHashAndBenutzerID");