Hibernate @ManyToMany, error : relation does not exists

Question

I'm trying to implement a many-to-many relation with 2 entities: Book and Authors.

A book can be written by multiples authors, and authors can have written multiple books.

But when I try to persist a book with his authors, I got an error: relation "book" does not exist.

Bellow some more pieces of information:

Entities

BookEntity

package orm.model;

import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;

@Entity
public class Livre {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="livre_id")
    Long livre_id;


    @ManyToMany
    @JoinTable(
            name="livre_auteur",
            joinColumns = {@JoinColumn(referencedColumnName = "livre_id")},
            inverseJoinColumns = {@JoinColumn(referencedColumnName = "auteur_id")}
    )
    private List<Auteur> auteurs;

    private String title;


    public Livre() {

    }   


    public Long getLivre_id() {
        return livre_id;
    }

    public void setLivre_id(Long livre_id) {
        this.livre_id = livre_id;
    }

    public List<Auteur> getAuteurs() {
        return auteurs;
    }

    public void setAuteurs(List<Auteur> auteurs) {
        this.auteurs = auteurs;
    }

    public String getTitle() {
        return title;
    }


    public void setTitle(String title) {
        this.title = title;
    }

}

AuthorEntity

package orm.model;

import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.Table;

@Entity(name="Auteur")
@Table(name="auteur")
public class Auteur {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="auteur_id")
    private Long auteur_id;
    private String name;
    private String surName;

    @ManyToMany(mappedBy="auteurs")
    private List<Livre> livres;


    public Auteur() {

    }


    public List<Livre> getLivres() {
        return livres;
    }

    public void setLivres(List<Livre> livres) {
        this.livres = livres;
    }

    public Long getAuteur_id() {
        return auteur_id;
    }


    public void setAuteur_id(Long auteur_id) {
        this.auteur_id = auteur_id;
    }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getSurName() {
        return surName;
    }
    public void setSurName(String surName) {
        this.surName = surName;
    }


}

DAO

Generic Dao :

package orm.dao;

import javax.persistence.EntityManager;

import orm.helper.Helper;

public class GenericDao {

    private EntityManager entityManager;

    public GenericDao() {
        this.entityManager = Helper.createEntityManager();
    }


    protected EntityManager getEntityManager() {

        if(entityManager!=null || !entityManager.isOpen()) {
            entityManager = Helper.createEntityManager();
        }

        return entityManager;
    }

}

Author Dao :

package orm.dao;

import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.TypedQuery;

import orm.exception.AlreadyExistsException;
import orm.helper.Helper;
import orm.model.Auteur;

public class AuteurDao extends GenericDao {

    public Auteur insert(Auteur auteur) throws AlreadyExistsException {
        EntityManager entityManager = getEntityManager();

        //check if Auteur exists
        Auteur existingAuteur = findById(auteur.getAuteur_id());

        if(existingAuteur!=null) {
            throw new AlreadyExistsException("l'auteur existe déjà dans la base de donnée, id : " + auteur.getAuteur_id());
        }

        Helper.beginTransaction(entityManager);
        entityManager.persist(auteur);
        Helper.commitTransactionAndClose(entityManager);
        return auteur;  
    }

    private Auteur findById(Long id) {
        TypedQuery<Auteur> query = getEntityManager().createQuery("from Auteur a where a.auteur_id = :id", Auteur.class);
        query.setParameter("id", id);

        try {
            return query.getSingleResult();
        } catch (NoResultException e) {
            return null;
        }

    }
}

Book Dao :

package orm.dao;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;

import orm.exception.AlreadyExistsException;
import orm.helper.Helper;
import orm.model.Livre;

public class LivreDao extends GenericDao{

    public Livre insert(Livre livre) throws AlreadyExistsException{

        EntityManager entityManager = getEntityManager();

        //test if livre already exist
        Livre existingLivre = findByNumber(livre.getLivre_id());

        if(existingLivre!=null) {
            throw new AlreadyExistsException("livre already in database with id : " + livre.getLivre_id());
        }

        Helper.beginTransaction(entityManager);
        entityManager.persist(livre);
        Helper.commitTransactionAndClose(entityManager);
        return livre;

    }


    private Livre findByNumber(Long id) {

        TypedQuery<Livre> query = getEntityManager().createQuery("from Livre l where l.livre_id = :id", Livre.class);
        query.setParameter("id", id);

        try {
            return query.getSingleResult();
        } catch (Exception e) {
            return null;
        }


    }
}

Main

package orm;

import java.util.ArrayList;
import java.util.List;

import orm.dao.LivreDao;
import orm.exception.AlreadyExistsException;
import orm.model.Auteur;
import orm.model.Livre;

public class Main {

    public static void main(String[] args) throws AlreadyExistsException {

        //1st author
        Auteur jSlater = new Auteur();
        jSlater.setName("Jack");
        jSlater.setSurName("Slater");

        //2nd author
        Auteur jBegood = new Auteur();
        jBegood.setName("Begood");
        jBegood.setSurName("Johnny");

        //author list
        List<Auteur> auteurs = new ArrayList<Auteur>();
        auteurs.add(jSlater);
        auteurs.add(jBegood);

        //book
        Livre livre = new Livre();
        //set multiple authors to book
        livre.setTitle("la chèvre enchantée");
        livre.setAuteurs(auteurs);

        // create Livre lists
        List<Livre> livreList1 = new ArrayList<>();
        livreList1.add(livre);

        List<Livre> livreList2 = new ArrayList<>();
        livreList2.add(livre);

        // populate to previously created Acteurs 
        jSlater.setLivres(livreList1);
        jBegood.setLivres(livreList2); 

        // then save
        LivreDao livreDao = new LivreDao();
        livreDao.insert(livre);

    }

}

error stack trace :

avr. 18, 2019 7:59:30 PM org.hibernate.jpa.internal.util.LogHelper logPersistenceUnitInformation
INFO: HHH000204: Processing PersistenceUnitInfo [
    name: entrainement
    ...]
avr. 18, 2019 7:59:30 PM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate Core {5.2.12.Final}
avr. 18, 2019 7:59:30 PM org.hibernate.cfg.Environment <clinit>
INFO: HHH000206: hibernate.properties not found
avr. 18, 2019 7:59:30 PM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>
INFO: HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
avr. 18, 2019 7:59:30 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
WARN: HHH10001002: Using Hibernate built-in connection pool (not for production use!)
avr. 18, 2019 7:59:30 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001005: using driver [org.postgresql.Driver] at URL [jdbc:postgresql://localhost:5432/entrainement-orm]
avr. 18, 2019 7:59:30 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001001: Connection properties: {user=postgres, password=****}
avr. 18, 2019 7:59:30 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001003: Autocommit mode: false
avr. 18, 2019 7:59:30 PM org.hibernate.engine.jdbc.connections.internal.PooledConnections <init>
INFO: HHH000115: Hibernate connection pool size: 20 (min=1)
avr. 18, 2019 7:59:30 PM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.PostgreSQL81Dialect
avr. 18, 2019 7:59:31 PM org.hibernate.engine.jdbc.env.internal.LobCreatorBuilderImpl useContextualLobCreation
INFO: HHH000424: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException
avr. 18, 2019 7:59:32 PM org.hibernate.hql.internal.QueryTranslatorFactoryInitiator initiateService
INFO: HHH000397: Using ASTQueryTranslatorFactory
avr. 18, 2019 7:59:32 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 0, SQLState: 42P01
avr. 18, 2019 7:59:32 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ERREUR: la relation « livre » n'existe pas
  Position : 74
avr. 18, 2019 7:59:32 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 0, SQLState: 42P01
avr. 18, 2019 7:59:32 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ERREUR: la relation « livre » n'existe pas
  Position : 13
Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:164)
    at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:789)
    at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:767)
    at orm.dao.LivreDao.insert(LivreDao.java:24)
    at orm.Main.main(Main.java:32)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
    at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:45)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2919)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3490)
    at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:626)
    at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:280)
    at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:261)
    at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:306)
    at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:318)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:275)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:182)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:113)
    at org.hibernate.jpa.event.internal.core.JpaPersistEventListener.saveWithGeneratedId(JpaPersistEventListener.java:67)
    at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:189)
    at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:132)
    at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:58)
    at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:782)
    ... 3 more
Caused by: org.postgresql.util.PSQLException: ERREUR: la relation « livre » n'existe pas
  Position : 13
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:562)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:366)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
    ... 20 more

Some help would be highly appreciated, Kind regards, Alexis

score 3 · Answer 1 · edited May 26 '20 at 15:30

3

I had the same problem with using postgres. Just specify the schema name in @JoinTable

@ManyToMany
@JoinTable(
        name="**schemaName**.livre_auteur",
        joinColumns = {@JoinColumn(referencedColumnName = "livre_id")},
        inverseJoinColumns = {@JoinColumn(referencedColumnName = "auteur_id")}
)
private List<Auteur> auteurs;

It should be work.

edited May 26 '20 at 15:30

Alessio

3,404
19
35
48

answered May 26 '20 at 15:05

Qualifier

31
2

2

Should be `schema = "schemaName",name = "livre_auteur",...` – M at Mar 14 '21 at 07:02

score 0 · Answer 2 · answered Apr 18 '19 at 18:16

As your @ManyToMany is bidirectional, for each of the Autors you need to fill in the list of Livre which would contain one entry in the above example:

    //book
    Livre livre = new Livre();
    //set multiple authors to book
    livre.setAuteurs(auteurs);
    livre.setTitle("la chèvre enchantée");

    // create Livre lists
    List<Livre> livreList1 = new ArrayList<>();
    livreList1.add(livre);

    List<Livre> livreList2 = new ArrayList<>();
    livreList2.add(livre);

    // populate to previously created Acteurs 
    jSlater.setLivres(livreList1);
    jBegood.setLivres(livreList2); 

    // then save
    LivreDao livreDao = new LivreDao();
    livreDao.insert(livre);

thanks for your reply, altough your reply was pertinent, unfortunatly it didn't solve the error, I still have the same error showing up. I edited my answer to include the DAO classes — Alexis Brunet, Apr 19 '19 at 09:37

score 0 · Answer 3 · answered May 26 '20 at 15:19

I think to solve it you have to configure cascade in ManyToMany annotation, because you miss to save the parent entities. @ManyToMany(..., cascade = [CascadeType.PERSIST]) cascade help you to insert and update using entity foreign key. It will insert the relation before persist the prime entity.

reference baeldung to understand all cases types with its purpose.

hope that will help.

score 0 · Answer 4 · answered Jun 09 '22 at 05:58

I've had same problem, and all the answers listed here did not help me. And I find out that postrgres creates tables in case sensitive manner, if you add double quotes when creating tables.

"auteur" and auteur are two different names.

I don’t know exactly how it works, but apparently Hibernate cannot find the @JoinTable for the @ManyToMany relation, because, as I wrote above, this is a different table for postgres. Knowing this solved my problem. Maybe this will help someone else.

Hibernate @ManyToMany, error : relation does not exists

4 Answers4