How to connect postgresql in hibernate.cfg.xml

Question

I am trying to insert some data into postgresql through hibernate. However, there are not much tutorial about configurate hibernate with postgresql (I know, it should be similar to mysql =))

src/main/resources/hibernate.cfg.xml

<hibernate-configuration>
<session-factory>
<property name="connection.driver_class">org.postgresql.Driver</property>
<property name="connection.url">jdbc:postgresql://127.0.0.1:5432/myDatabase</property>
<property name="connection.username">myUser</property>
<property name="connection.password">myPassword</property>

<!-- JDBC connection pool (use the build-in) -->
<property name="connection.pool_size">1</property>
<!-- SQL dialect -->
<property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="cache.provider_class">org.hibernate.cache.HashtableCacheProvider</property>
<property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>

<!-- thread is the short name for org.hibernate.context.ThreadLocalSessionContext -->
<property name="current_session_context_class">thread</property>

<!-- Set "true" to show SQL statements -->
<property name="hibernate.show_sql">true</property>

<!-- mapping class using annotation -->
<mapping class="com.hib.entities.Student"></mapping>
</session-factory>

</hibernate-configuration>

src/main/java/

package com.hib.init;

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;

public class Hibernateutil {
private static final SessionFactory sessionF;
private static final ServiceRegistry serviceR;

static {
    Configuration conf = new Configuration();
    conf.configure();
    System.out.println("Begin");
    serviceR = new ServiceRegistryBuilder().applySettings(conf.getProperties()).buildServiceRegistry();
    System.out.println("Ready???");
    try {
        sessionF = conf.buildSessionFactory(serviceR);
        System.out.println("Success??");
    }catch(Exception e) {
        throw new ExceptionInInitializerError(e);
    }
}

public static SessionFactory getSeeionFactory() {
    return sessionF;
}
}

src/main/java package com.hib.entities;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table
public class Student {

@Id
@GeneratedValue
private Integer id;
private String firstName;

private Integer age;

public Student() {}

public Student(Integer id, String firstName, Integer age) {
    super();
    this.id = id;
    this.firstName = firstName;
    this.age = age;
}

public Integer getId() {
    return id;
}

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

public String getFirstName() {
    return firstName;
}

public void setFirstName(String firstName) {
    this.firstName = firstName;
}

public Integer getAge() {
    return age;
}

public void setAge(Integer age) {
    this.age = age;
}   
}

src/main/java

package com.hib.demo;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import com.hib.entities.Student;
import com.hib.init.Hibernateutil;

public class DemoFirst {

public static void main(String[] args) {
    SessionFactory sessionFactory = Hibernateutil.getSeeionFactory();
    Session session = sessionFactory.openSession();
    session.beginTransaction();

    Student student = new Student();
    student.setFirstName("Bob");
    student.setAge(26);

    session.save(student);
    session.getTransaction().commit();

    session.close();
}
}

And this is the error I got :

Success??
Hibernate: select nextval ('hibernate_sequence')
Aug 12, 2014 11:01:10 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 0, SQLState: 42P01
Aug 12, 2014 11:01:10 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ERROR: relation "hibernate_sequence" does not exist
Position: 17
Exception in thread "main" org.hibernate.exception.SQLGrammarException: ERROR: relation        "hibernate_sequence" does not exist
Position: 17
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
at com.sun.proxy.$Proxy9.executeQuery(Unknown Source)
at org.hibernate.id.SequenceGenerator.generateHolder(SequenceGenerator.java:123)
at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:116)
at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:120)
at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:204)
at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:55)
at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:189)
at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:49)
at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90)
at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:642)
at org.hibernate.internal.SessionImpl.save(SessionImpl.java:635)
at org.hibernate.internal.SessionImpl.save(SessionImpl.java:631)
at com.hib.demo.DemoFirst.main(DemoFirst.java:20)


Caused by: org.postgresql.util.PSQLException: ERROR: relation "hibernate_sequence" does not exist
Position: 17
at    org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
... 14 more

pom.xml

 <dependency>
    <groupId>postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>8.4-701.jdbc4</version>
</dependency>

Solution 1

According to this thread you must set your Id annotation as follows:

@GeneratedValue(strategy = GenerationType.IDENTITY)

Solution 2

You forgot to add the DDL auto generation property. You can use any of the following settings:

<property name="hibernate.hbm2ddl.auto" value="update">
<property name="hibernate.hbm2ddl.auto" value="create">
<property name="hibernate.hbm2ddl.auto" value="create-drop">

1. update is going to create and then simply update the DDL schema
2. create is going to create the schema if it doesn't exist
3. create-drop will create the schema when the SessionFactory is initialized and destroy it when the SessionFactory is destroyed. This is useful during integration testing.

Solution 3

You should make mapping beetwen table-entity class, column- field. For example (table and columns should exist) :

@Entity
@Table(name = "student")
public class Student {

@Id
@GeneratedValue
@Column(name = "id")
private Integer id;

@Column(name = "first_name")
private String firstName;

@Column(name = "age")
private Integer age;