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, Oct 03 '14 at 13:43
this link might help you take a look http://stackoverflow.com/questions/6386888/get-next-sequence-value-from-database-using-hibernate — noble_man, Oct 03 '14 at 14:42

score 1 · Answer 1 · answered Oct 03 '14 at 14:18

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 03 '14 at 14:18

Giovanni

543
2
11

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. – Alexandru Severin Oct 03 '14 at 14:28
In this case you should just call session.createSQLQuery("select nextval(my_seq)") without any mapping at all. – Giovanni Oct 03 '14 at 14:35
I did, but hibernate cannot find it. – Alexandru Severin Oct 03 '14 at 14:36

score 0 · Answer 2 · answered Oct 03 '14 at 15:01

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.

Thanks, but I don't want to map it to any table. I got it working though. — Alexandru Severin, Oct 03 '14 at 20:27

score 0 · Answer 3 · edited May 23 '17 at 11:50

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 '17 at 11:50

Community

1
1

answered Nov 25 '14 at 09:13

Pavel

76
1
8

hibernate cannot find sequence

3 Answers3