Hibernate schema parameter doesn't work in @SequenceGenerator annotation

I have the following code:

@Entity
@Table(name = "my_table", schema = "my_schema")
@SequenceGenerator(name = "my_table_id_seq", sequenceName = "my_table_id_seq", 
                   schema = "my_schema")
public class MyClass {
    @Id
    @GeneratedValue(generator = "my_table_id_seq", 
                    strategy = GenerationType.SEQUENCE)
    private int id;

}

Database: Postgresql 8.4, Hibernate annotations 3.5.0-Final.

When saving the object of MyClass it generates the following SQL query:

select nextval('my_table_id_seq')

So there is no schema prefix and therefore the sequence cannot be found. When I write the sequenceName like

sequenceName = "my_schema.my_table_id_seq"

everything works.

Do I have misunderstandings for meaning of schema paramete开发者_开发百科r or is it a bug? Any ideas how to make schema parameter working?

Same problem here, looks like a bug to me. I´m using hibernate 3.6.7 Looking at the source code i see a method org.hibernate.cfg.annotations.reflection.JPAOverridenAnnotationReader#buildSequenceGeneratorAnnotation(Element element) that seems to copy the values of name, sequence-name, initial-value and allocation-sizeattributes, but I see no reference to catalogor schema

i expected to see something analogous to method getTable(Element tree, XMLContext.Default defaults) (of the same class) which has

annotation.setValue("schema", table.schema());
annotation.setValue("catalog", table.catalog());` 

or buildTableGeneratorAnnotation which has

copyStringAttribute(ad, element, "catalog", false);
copyStringAttribute(ad, element, "schema", false);

So, even if a little hackish, the way around -for this version at least- seems to be prefixing the sequenceName as you say.

My workaround looks like this (JPA 2.1, Hibernate 4.3.8.Final, PostgreSQL 9.4):

@SequenceGenerator(name = "seq_name", sequenceName = "my_schema.seq_name", schema = "my_schema", allocationSize = 1, initialValue = 1)

I solved this problem in postgresql 9.6 by only adding my schema before the sequence name, like this:

(before) sequence name: seq_area_tematica
(after) sequence name: sisbp.seq_area_tematica

And its works. See the code:

@Id
@Column(name="seq_area_tematica")
@GeneratedValue(generator="sequence",strategy=GenerationType.SEQUENCE) 
@SequenceGenerator(name="sequence",sequenceName="sisbp.seq_area_tematica")
private Long id;

See the "sisbp" before the sequenceName. The sequenceName was "seq_area_tematica" and now is "sisbp" (schema) + "seq_area_tematica".

Using Hibernate 4.2.0.Final here and have the same problem. Looks like it is a bug like answered by another users. I wanted to use a dynamic schema for sequences, depending on the schema set for the session, but for some sequences I wanted to use public schema. So for me I had to use the solution you proposed: put the schema name on sequence name where I want to use a specific schema:

@SequenceGenerator(name = "my_table_id_seq", sequenceName="my_schema.my_table_id_seq",
 schema = "my_schema")

For the cases where I wanted to use the schema set for the session I used the sequenceName without the schema prepended.

For those that want the same schema for all sequences you can use hibernate.default_schema property. With that you don't need to change your @SequenceGenerator properties:

<prop key="hibernate.default_schema">my_schema_name</prop>

I am using PostgreSQL DBMS. If you want to change dynamically the name of the sequence when Hibernate calls nextval('my_sequence') you can extend your database dialect class and configure Hibernate to use. You need just to override getSequenceNextValString() method. The only information provided to the method is the sequenceName property defined on @SequenceGenerator:

public class SchemaPostgreSQLDialect extends PostgreSQL82Dialect {
    @Override
    public String getSequenceNextValString(String sequenceName) {
        String seqFinalName = mySequenceNameModifierMethod(sequenceName);       
        return "select nextval('" + seqFinalName + "')";
    }

    private String mySequenceNameModifierMethod(String originalSequenceName) {
        // magic modification here
        return modifiedSequenceName;
    }
}

I didn't use this last way to change the name of the sequences because it seems less appropriated to my case.

This sounds like a bug: the JPA provider should honor the "new" (since Java Persistence 2.0) schema and catalog attributes of the @SequenceGenerator annotation. I suggest to raise a Jira issue (the annotations and entity manager projects are now under core), couldn't find any existing one.

Same problem, using Hibernate 4.3.6.Final, with Spring 4.1.4.RELEASE, on Oracle Database 11g Enterprise Edition Release 11.2.0.1.0.

Looks like this is a bug => https://hibernate.atlassian.net/browse/HHH-7232

We got around the issue by creating a synonym in schema A pointing to the sequence in schema B. This synonym was what we used in the schema attribute of @SequenceGenerator annotation

Hmmm, I don't work with the internals of hibernate much but there is some info here:

https://forum.hibernate.org/viewtopic.php?p=2406761

You can also set the default schema for a user connecting to PostgreSQL via ALTER USER ... SET SEARCH_PATH

Try moving the SequenceGenerator annotation from the POJO's class declaration to id field declaration. With Hibernate 3.6.4 this

@Entity
@Table(name = "my_table", schema = "my_schema")
public class MyClass {
    @Id
    @GeneratedValue(generator = "my_table_id_seq", 
                strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(name = "my_table_id_seq", sequenceName = "my_table_id_seq", schema = "my_schema")
    private int id;

}

produces this for MySQL

create table my_schema.my_table_id_seq (
     next_val bigint 
);

insert into my_schema.my_table_id_seq values ( 1 );

and this for PostgreSQL

create sequence my_schema.my_table_id_seq start 1 increment 50;

In my case, the execution does not go through the location indicated by @jambriz.

Version: Hibernate 3.6.10.Final

The problem occurs when hibernate.id.new_generator_mappings = false in persistence.xml. When set to true or simply remove, the sequence scheme is used by Hibernate to build SQL. Solution:

<property name="hibernate.id.new_generator_mappings" value="true" />
<!-- Or remove this property -->

Where's the problem in the code?

In the AnnotationBinder.java class between lines 445 and 480. In the if block, line 455, you can see the scheme being set. In the else the scheme is not set.

Hope this helps!

when using hibernate with spring please set

<prop key="hibernate.id.new_generator_mappings">true</prop>

I had the same problem in my app which is using Hibernate 5.2.7, with Spring 4.3.6, on Oracle Database 19c. Changing sequenceName = "MY_SEQUENCE" to sequenceName = "MY_SCHEMA.MY_SEQUENCE" worked perfectly.

@Id
@Column(name = "ID")
@SequenceGenerator(schema = "MY_SCHEMA", name = "MY_SEQ_GENERATOR", sequenceName = "MY_SCHEMA.MY_SEQUENCE")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "MY_SEQ_GENERATOR")
private Long id;

Hello i was having same problem

but set your hibernate.hbm2ddl.auto to update and run.

<property name="hibernate.hbm2ddl.auto">update</property>