CODEWITHSUNDEEP
javahibernatepostgresql
Lakshmi
Lakshmi

Reputation: 2294

Missing sequence or table: hibernate_sequence

I am new to hibernate and postgres. Actually I am trying to map potgres database using Hibernate. This is my table stucture in postgresql

CREATE TABLE employee
(
id serial NOT NULL,
firstname character varying(20),
lastname character varying(20),
birth_date date,
cell_phone character varying(15),
CONSTRAINT employee_pkey PRIMARY KEY (id )
)

I am trying to add a record to the database using the following code

 System.out.println("******* WRITE *******");
    Employee empl = new Employee("Jack", "Bauer", new Date(System.currentTimeMillis()), "911");
    empl = save(empl);



 //This is the save function

    private static Employee save(Employee employee) {
    SessionFactory sf = HibernateUtil.getSessionFactory();
    Session session = sf.openSession();

    session.beginTransaction();


    int id = (Integer) session.save(employee);
    employee.setId(id);

    session.getTransaction().commit();

    session.close();

    return employee;
}

When I execute the code I am getting the following error

org.hibernate.HibernateException: Missing sequence or table: hibernate_sequence
Exception in thread "main" java.lang.ExceptionInInitializerError
at org.tcs.com.Hibernate.HibernateUtil.buildSessionFactory(HibernateUtil.java:18)
at org.tcs.com.Hibernate.HibernateUtil.<clinit>(HibernateUtil.java:8)
at org.tcs.com.Hibernate.MainApp.list(MainApp.java:51)
at org.tcs.com.Hibernate.MainApp.main(MainApp.java:17)
Caused by: org.hibernate.HibernateException: Missing sequence or table: hibernate_sequence
at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1282)
at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:155)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:498)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1740)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1778)
at org.tcs.com.Hibernate.HibernateUtil.buildSessionFactory(HibernateUtil.java:15)
... 3 more

I have the sequence called "employee_id_seq" in my database. But I dont know why the database is looking for hibernate_seq. Could someone explain the error and the reason.

Thanks in advance!

Added info

This is my employee class

import java.sql.Date;

public class Employee {

private int id;

private String firstname;

private String lastname;

private Date birthDate;

private String cellphone;

public Employee() {

}

public Employee(String firstname, String lastname, Date birthdate, String phone) {
    this.firstname = firstname;
    this.lastname = lastname;
    this.birthDate = birthdate;
    this.cellphone = phone;

}

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getFirstname() {
    return firstname;
}

public void setFirstname(String firstname) {
    this.firstname = firstname;
}

public String getLastname() {
    return lastname;
}

public void setLastname(String lastname) {
    this.lastname = lastname;
}

public Date getBirthDate() {
    return birthDate;
}

public void setBirthDate(Date birthDate) {
    this.birthDate = birthDate;
}

public String getCellphone() {
    return cellphone;
}

public void setCellphone(String cellphone) {
    this.cellphone = cellphone;
}



}

Upvotes: 44

Views: 94903

Answers (8)

T.Thamilvaanan
T.Thamilvaanan

Reputation: 133

If you encounter this with Spring Boot or Spring boot/Hibernate Migration then potentially you can try the following

Quote:

By default, Hibernate generates key from hibernate_sequence table, we can disable it by setting this hibernate.use-new-id-generator-mappings to false.

application.properties


spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=mkyong
spring.datasource.password=password
spring.jpa.hibernate.use-new-id-generator-mappings=false

Upvotes: 1

Saleh
Saleh

Reputation: 57

Apart from creating the table hibernate_sequence which has a column next_val you can also set quarkus.hibernate-orm.database.generation = drop-and-create. Note this will delete all the record in you database.

Upvotes: -1

user1419261
user1419261

Reputation: 944

In your domain or Model object annotate the id field as below and it should work. For me the GenerationType.AUTO failed

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

Upvotes: 43

Riddhi Gohil
Riddhi Gohil

Reputation: 1818

Simple solution :

create hibernate_sequence table as :

"create sequence <schema>.hibernate_sequence"

Upvotes: 14

Chipo Hamayobe
Chipo Hamayobe

Reputation: 1057

For me, what was causing this error was the wrong version of the MySql.Data library.

I had a version 6.9.6.0 defined in the web.config and yet the actual referenced version was older.

I just commented out :

     <system.data>
      <DbProviderFactories>
      <remove invariant="MySql.Data.MySqlClient" />
        <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
     </DbProviderFactories>
   </system.data>

Upvotes: -2

Daniel
Daniel

Reputation: 37

You can do two things. One is to just manually create a blank hibernate_sequence table postgresql. Two, most likely there is a conflict with the user account permissions not allowing grails to create that table.

Upvotes: -1

oleh.kovaliuk
oleh.kovaliuk

Reputation: 157

If you don't use annotation you should change YourClass.hbm.xml file.

Your ID section should be:

<id name="id" type="int" column="id">
     <generator class="sequence">
         <param name="sequence">employee_id_seq</param>
     </generator>
</id>

File sample:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
 "-//Hibernate/Hibernate Mapping DTD//EN"
 "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> 

<hibernate-mapping>
   <class name="Employee" table="EMPLOYEE">
      <meta attribute="class-description">
         This class contains the employee detail. 
      </meta>
      <id name="id" type="int" column="id">
         <generator class="sequence">
             <param name="sequence">employee_id_seq</param>
         </generator>
      </id>
      <property name="firstName" column="first_name" type="string"/>
      <property name="lastName" column="last_name" type="string"/>
      <property name="salary" column="salary" type="int"/>
   </class>
</hibernate-mapping>

Upvotes: 0

David Lavender
David Lavender

Reputation: 8311

You haven't posted the important bit: the Employee class.

But my guess is that your Employee class is using @GeneratedValue() without specifying the sequence to use. So, Hibernate uses its default name: hibernate_sequence.

You can supply a sequence name as part of the GeneratedValue annotation. eg. 

@GeneratedValue(strategy=SEQUENCE, generator="employee_id_seq")

Upvotes: 32

Related Questions