org.postgresql.util.PSQLException: ERROR: relation "sequence-gen" does not exist

Question

I am new to Hibernate and now I am trying to use @CollectionId to generate an identifier for my class HobbyDetails using 'sequence-gen' hibernate sequence generator. Below is my StudentDetails class which contains a collection of HobbyDetails.

@Entity
public class StudentDetails {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int studentId;

    private String studentName;

    @ElementCollection
    @JoinTable(name = "STUDENT_HOBBIES", // customised table name
             joinColumns = @JoinColumn(name = "STUDENT_ID"))
    @GenericGenerator(name = "sequence-gen", strategy = "sequence")
    @CollectionId(columns = { @Column(name="HOBBY_ID") }, generator = "sequence-gen", type = @Type(type="long"))
    private Collection<HobbyDetails> hobbies = new ArrayList<HobbyDetails>();

    public int getStudentId() {
        return studentId;
    }

    public void setStudentId(int studentId) {
        this.studentId = studentId;
    }

    public String getStudentName() {
        return studentName;
    }

    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }

    public Collection<HobbyDetails> getHobbies() {
        return hobbies;
    }

    public void setHobbies(Collection<HobbyDetails> hobbies) {
        this.hobbies = hobbies;
    }

}

But when I run the test I get error,

ERROR: ERROR: relation "sequence-gen" does not exist
  Position: 17
Jul 01, 2018 6:31:47 PM org.hibernate.internal.ExceptionMapperStandardImpl mapManagedFlushFailure
ERROR: HHH000346: Error during managed flush [org.hibernate.exception.SQLGrammarException: could not extract ResultSet]
Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    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.doFlush(SessionImpl.java:1460)
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:511)
    at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3278)
    at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2474)
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:473)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:178)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:39)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:271)
    at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:98)
    at org.hibernate.test.HibernateTest.main(HibernateTest.java:82)
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    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.extract(ResultSetReturnImpl.java:69)
    at org.hibernate.id.enhanced.SequenceStructure$1.getNextValue(SequenceStructure.java:95)
    at org.hibernate.id.enhanced.NoopOptimizer.generate(NoopOptimizer.java:40)
    at org.hibernate.id.enhanced.SequenceStyleGenerator.generate(SequenceStyleGenerator.java:482)
    at org.hibernate.collection.internal.PersistentIdentifierBag.preInsert(PersistentIdentifierBag.java:376)
    at org.hibernate.persister.collection.AbstractCollectionPersister.recreate(AbstractCollectionPersister.java:1291)
    at org.hibernate.action.internal.CollectionRecreateAction.execute(CollectionRecreateAction.java:50)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:478)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:356)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1454)
    ... 9 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "sequence-gen" does not exist
  Position: 17
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)

I am using hibernate version 5.3.1. Can anyone help?

Answer 1

This is because sequence generator named "sequence-gen" does not exist in PostgreSQL database. You need to explicitly create the sequence by running the following command

CREATE SEQUENCE sequence-gen;

hyphen(-) symbol will generate syntax error in PostgreSQL database. So, you need to rename your GenericGenerator.

@GenericGenerator(name = "sequence_gen", strategy = "sequence")

@CollectionId(columns = { @Column(name="HOBBY_ID") }, generator = "sequence_gen", type = @Type(type="long"))

private Collection<HobbyDetails> hobbies = new ArrayList<HobbyDetails>();

Also, you need to run the following command in PostgreSQL database in order to generate generator.

CREATE SEQUENCE sequence_gen;