Utiliser les séquences avec postgresql

**Emplyst** · 14/01/2009, 22h22

Bonjour à tous,

Je n'ai jamais vraiment utilisé Hibernate ou PostgreSQL avant hier après-midi. Après avoir trouver les 36 librairies nécessaires à faire fonctionner hibernate je me suis lancer dans une petite conception d'une bibliothèque pour tester comment fonctionne l'héritage sous hibernate.

Grosso-modo voici ma conception

Parcontre, lorsque j'arrive pour insérer un auteur de la base de donnée j'arrive avec l'erreure suivante:

63 [main] INFO org.hibernate.cfg.Environment - Hibernate 3.3.1.GA
110 [main] INFO org.hibernate.cfg.Environment - hibernate.properties not found
125 [main] INFO org.hibernate.cfg.Environment - Bytecode provider name : javassist
156 [main] INFO org.hibernate.cfg.Environment - using JDK 1.4 java.sql.Timestamp handling
391 [main] INFO org.hibernate.cfg.Configuration - configuring from resource: /hibernate.cfg.xml
391 [main] INFO org.hibernate.cfg.Configuration - Configuration resource: /hibernate.cfg.xml
953 [main] INFO org.hibernate.cfg.Configuration - Reading mappings from resource : Author.hbm.xml
1281 [main] INFO org.hibernate.cfg.HbmBinder - Mapping class: Author -> authors
1375 [main] INFO org.hibernate.cfg.Configuration - Reading mappings from resource : Book.hbm.xml
1485 [main] INFO org.hibernate.cfg.HbmBinder - Mapping class: Book -> books
1906 [main] INFO org.hibernate.cfg.Configuration - Reading mappings from resource : Novel.hbm.xml
2031 [main] INFO org.hibernate.cfg.HbmBinder - Mapping class: Novel -> novels
2047 [main] INFO org.hibernate.cfg.Configuration - Reading mappings from resource : Comic.hbm.xml
2141 [main] INFO org.hibernate.cfg.HbmBinder - Mapping class: Comic -> comics
2156 [main] INFO org.hibernate.cfg.Configuration - Configured SessionFactory: null
2172 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - Using Hibernate built-in connection pool (not for production use!)
2172 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - Hibernate connection pool size: 1
2188 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - autocommit mode: false
2313 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - using driver: org.postgresql.Driver at URL: jdbc:postgresql://localhost/TestHibernate
2313 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - connection properties: {user=postgres, password=****}
2641 [main] INFO org.hibernate.cfg.SettingsFactory - RDBMS: PostgreSQL, version: 8.2.4
2641 [main] INFO org.hibernate.cfg.SettingsFactory - JDBC driver: PostgreSQL Native Driver, version: PostgreSQL 8.3 JDBC4 with SSL (build 604)
2719 [main] INFO org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.HSQLDialect
2735 [main] INFO org.hibernate.transaction.TransactionFactoryFactory - Using default transaction strategy (direct JDBC transactions)
2735 [main] INFO org.hibernate.transaction.TransactionManagerLookupFactory - No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
2735 [main] INFO org.hibernate.cfg.SettingsFactory - Automatic flush during beforeCompletion(): disabled
2735 [main] INFO org.hibernate.cfg.SettingsFactory - Automatic session close at end of transaction: disabled
2735 [main] INFO org.hibernate.cfg.SettingsFactory - JDBC batch size: 15
2735 [main] INFO org.hibernate.cfg.SettingsFactory - JDBC batch updates for versioned data: disabled
2750 [main] INFO org.hibernate.cfg.SettingsFactory - Scrollable result sets: enabled
2750 [main] INFO org.hibernate.cfg.SettingsFactory - JDBC3 getGeneratedKeys(): disabled
2750 [main] INFO org.hibernate.cfg.SettingsFactory - Connection release mode: auto
2766 [main] INFO org.hibernate.cfg.SettingsFactory - Default batch fetch size: 1
2766 [main] INFO org.hibernate.cfg.SettingsFactory - Generate SQL with comments: disabled
2766 [main] INFO org.hibernate.cfg.SettingsFactory - Order SQL updates by primary key: disabled
2766 [main] INFO org.hibernate.cfg.SettingsFactory - Order SQL inserts for batching: disabled
2766 [main] INFO org.hibernate.cfg.SettingsFactory - Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
2766 [main] INFO org.hibernate.hql.ast.ASTQueryTranslatorFactory - Using ASTQueryTranslatorFactory
2766 [main] INFO org.hibernate.cfg.SettingsFactory - Query language substitutions: {}
2766 [main] INFO org.hibernate.cfg.SettingsFactory - JPA-QL strict compliance: disabled
2766 [main] INFO org.hibernate.cfg.SettingsFactory - Second-level cache: enabled
2781 [main] INFO org.hibernate.cfg.SettingsFactory - Query cache: disabled
2797 [main] INFO org.hibernate.cfg.SettingsFactory - Cache region factory : org.hibernate.cache.impl.bridge.RegionFactoryCacheProviderBridge
2797 [main] INFO org.hibernate.cache.impl.bridge.RegionFactoryCacheProviderBridge - Cache provider: org.hibernate.cache.NoCacheProvider
Hibernate: call next value for authors_id_seq
2797 [main] INFO org.hibernate.cfg.SettingsFactory - Optimize cache for minimal puts: disabled
2797 [main] INFO org.hibernate.cfg.SettingsFactory - Structured second-level cache entries: disabled
2813 [main] INFO org.hibernate.cfg.SettingsFactory - Echoing all SQL to stdout
2813 [main] INFO org.hibernate.cfg.SettingsFactory - Statistics: disabled
2813 [main] INFO org.hibernate.cfg.SettingsFactory - Deleted entity synthetic identifier rollback: disabled
2813 [main] INFO org.hibernate.cfg.SettingsFactory - Default entity-mode: pojo
2828 [main] INFO org.hibernate.cfg.SettingsFactory - Named query checking : enabled
3047 [main] INFO org.hibernate.impl.SessionFactoryImpl - building session factory
3891 [main] INFO org.hibernate.impl.SessionFactoryObjectFactory - Not binding factory to JNDI, no JNDI name configured
4360 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 42601
4375 [main] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: syntax error at or near "call"
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not get next sequence value
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:119)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:122)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:210)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:117)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93)
at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:534)
at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:526)
at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:522)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:342)
at $Proxy0.saveOrUpdate(Unknown Source)
at TestHibernate.main(TestHibernate.java:13)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "call"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1608)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1343)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:194)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:98)
... 15 more

SQL

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
CREATE DATABASE "TestHibernate"
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default;
 
CREATE TABLE authors
(
  id bigserial NOT NULL,
  name character varying(150) NOT NULL,
  CONSTRAINT authors_pkey PRIMARY KEY (id)
) 
WITHOUT OIDS;
ALTER TABLE authors OWNER TO postgres;
 
CREATE SEQUENCE authors_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE authors_id_seq OWNER TO postgres;
 
 
 
CREATE TABLE books
(
  id bigserial NOT NULL,
  isbn character varying(25) NOT NULL,
  title character varying(250) NOT NULL,
  author bigint,
  price money,
  CONSTRAINT books_pkey PRIMARY KEY (id),
  CONSTRAINT books_author_fkey FOREIGN KEY (author)
      REFERENCES authors (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT books_isbn_key UNIQUE (isbn)
) 
WITHOUT OIDS;
ALTER TABLE books OWNER TO postgres;
 
CREATE SEQUENCE books_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE books_id_seq OWNER TO postgres;
 
CREATE TABLE comics
(
  id bigint NOT NULL,
  drawer bigint,
  CONSTRAINT comics_pkey PRIMARY KEY (id),
  CONSTRAINT comics_drawer_fkey FOREIGN KEY (drawer)
      REFERENCES authors (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT comics_id_fkey FOREIGN KEY (id)
      REFERENCES books (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
) 
WITHOUT OIDS;
ALTER TABLE comics OWNER TO postgres;
 
CREATE TABLE novels
(
  id bigint NOT NULL,
  "pageCount" integer,
  CONSTRAINT novels_pkey PRIMARY KEY (id),
  CONSTRAINT novels_id_fkey FOREIGN KEY (id)
      REFERENCES books (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
) 
WITHOUT OIDS;
ALTER TABLE novels OWNER TO postgres;

hibernate.cfg.xml

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
	<session-factory>
		<property name="connection.driver_class">org.postgresql.Driver</property>
		<property name="connection.url">jdbc:postgresql://localhost/TestHibernate</property>
		<property name="connection.username">postgres</property>
		<property name="connection.password">qwerty</property>
 
		<property name="connection.pool_size">1</property>
		<property name="dialect">org.hibernate.dialect.HSQLDialect</property>
		<property name="current_session_context_class">thread</property>
		<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
		<property name="show_sql">true</property>
 
		<mapping resource="Author.hbm.xml"/>
		<mapping resource="Book.hbm.xml"/>
		<mapping resource="Novel.hbm.xml"/>
		<mapping resource="Comic.hbm.xml"/>
	</session-factory>
</hibernate-configuration>

Author.hbm.xml

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
	<class name="Author" table="authors">
		<id name="id" unsaved-value="null">
			<generator class="sequence">
				<param name="sequence">authors_id_seq</param>
			</generator>
		</id>
		<property name="name" />
	</class>
</hibernate-mapping>

TestHibernate.java

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
 
public class TestHibernate {
	public static void main(String[] args) {
		SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
		Session session = sessionFactory.getCurrentSession();
		session.beginTransaction();
 
		Author a1 = new Author("Maurice");
		session.saveOrUpdate(a1);
 
		session.getTransaction().commit();
		sessionFactory.close();
 
		/*Book b1 = new Roman("1321465-65", "Maurice au pays des merveilles", a1, 455, 65.22);
		session.update(b1);
 
		Author a2 = new Author("Linda");
		session.update(a2);
 
		Book b2 = new Roman("735r461-32", "Les aventures de Linda", a2, 45, 25.39);
		session.update(b2);
 
		session.getTransaction().commit();
		sessionFactory.close();*/
	}
}

Author.java

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
public class Author {
	private Integer id;
	private String name;
 
	public Author(String name) {
		this(null, name);
	}
 
	public Author() {
		this(null);
	}
 
	private Author(Integer id, String name) {
		this.id = id;
		this.name = name;
	}
 
	public void setName(String name) {
		this.name = name;
	}
 
	public String getName() {
		return name;
	}
 
	private void setId(Integer id) {
		this.id = id;
	}
 
	public Integer getId() {
		return id;
	}
}

Donc je me demandais, comment puis-je faire fonctionner ma séquence?

J'ai pas vraiment d'idée sur comment je pourrais résoudre ce problème.

**Alphonse87** · 15/01/2009, 10h40

Bonjour, alors petite remarque concernant la séquence, ce n'est pas la peine d'en créer une nouvelle car tu as défini ton id comme BIGSERIAL, les codes suivants sont équivalents :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE TABLE tablename (colname BIGSERIAL);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename( colname BIGINT DEFAULT nextval('tablename_colname_seq') NOT NULL);

Pour la configuration XML de Hibernate, je ne peux pas trop t'aider, j'ai réalisé mon mapping via les annotations. Voici comment elles sont, des fois que ça te donne des pistes pour ton problème :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
@Id
	@GeneratedValue(strategy = AUTO, generator = "administrator_sequence_name")
	@SequenceGenerator (name = "administrator_sequence_name", sequenceName = "administrator_id_administrator_seq", allocationSize = 1)

Edit : Et ici pour la doc : http://www.hibernate.org/hib_docs/v3...n-id-generator

**Emplyst** · 15/01/2009, 18h00

Merci de ta réponse Alphonse, néanmoins j'ai trouvé où était mon problème ce matin

C'était de "dialect" de Hibernate qui était définit à HSQL (Hibernate SQL) plutôt que PostgreSQL.

Dans hibernate.cfg.xml:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
<property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>

Utiliser les séquences avec postgresql

Hibernate Java

Discussions similaires

Partager

Partager