hibernate cannot find sequence

Question

I need a simple sequence which would give me incremented integers that I later on use as part of a String.

I made this sequence using postgresql command line:

CREATE SEQUENCE my_seq
INCREMENT BY 1

The sequence exists as I can query it from postgresql command line, But I'm trying to get the values using hibernate:

Query query = session.createSQLQuery("select nextval(:sequence);");
query.setParameter("sequence", "my_seq");
Long nextVal=((BigInteger)query.uniqueResult()).longValue();

And I am getting this exception:

ERROR: relation "my_seq" does not exist

The sequence values do NOT represent the attribute of any entity. I only need them to store the number of logins, but I do not store the logins as entities.

EDIT: I got it working by adding the scheme name to the query:

String query1 = "select nextval(myscheme.my_seq)";
Query query = session.createSQLQuery(query1);

I can't figure out why it needed the scheme though, as myscheme was already default and all other queries worked fine without specifying the scheme. If anyone can shed some light I shall accept its answer.

does this select nextval(my_seq) work if you run it in db client itself? — Elbek
– Elbek, Commented Oct 3, 2014 at 13:43
this link might help you take a look stackoverflow.com/questions/6386888/… — noble_man
– noble_man, Commented Oct 3, 2014 at 14:42

Giovanni · Accepted Answer · 2014-10-03 14:18:33Z

1

You don't need to map a sequence to an entity. Just leave the mapping as follow for the ID column of your entity, and the id of the tntity will be generated from the sequence my_seq. You don't need to call nextval(my_seq) or anything else.

@Entity("myEntity")
public class MyEntity {

   @Id
   @Column(name = "id", unique = true, nullable = false, insertable=false)
   @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "my_seq")
   @SequenceGenerator(name = "my_seq", sequenceName = "my_seq", allocationSize = 1)
   private long id;
}

answered Oct 3, 2014 at 14:18

Giovanni

5432 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alexandru Severin Over a year ago

As I said, the sequence is not an attribute of an entity, I do not have any 'myEntity' nor do I need it. I only want the integer from nextVal so I can use it as part of a code. More specifically I will form a string like: "You are " + nextValOfSequence + "nth user" which I will send to the user, however I do not store the user in the db.

Giovanni Over a year ago

In this case you should just call session.createSQLQuery("select nextval(my_seq)") without any mapping at all.

Alexandru Severin Over a year ago

I did, but hibernate cannot find it.

user4104265 · Accepted Answer · 2014-10-03 15:01:18Z

0

Below is the logic which will use a sequence called "my_seq" from the database and map it to the column "column_name" table "table_name"

Soon after you import the classes the sequence generator should be placed before you begin the class

@Entity
@org.hibernate.annotations.Entity(dynamicInsert = true, dynamicUpdate = true)
@Table(name = "table_name")
@SequenceGenerator(name = "my_seq", sequenceName = "my_seq")

public class ClassName implements Serializable {

private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO, generator = "my_seq")
@Column(name = "column_name")
private Long columnName;

}

Let me know if this is useful.

answered Oct 3, 2014 at 15:01

user4104265

573 silver badges9 bronze badges

1 Comment

Alexandru Severin Over a year ago

Thanks, but I don't want to map it to any table. I got it working though.

Community · Accepted Answer · 2017-05-23 11:50:54Z

0

In order to avoid hardcoding schema to your query, you can set hibernate.default_schema property in your persistence.xml as follows.

<property name="hibernate.default_schema" value="myscheme"/>

This could be also used in spring configuration file as is stated here.

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Nov 25, 2014 at 9:13

Pavel

761 silver badge8 bronze badges

Collectives™ on Stack Overflow

hibernate cannot find sequence

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related