2

I need to be able to get a sequential number (I don't mind if there are holes in the generation) and I think a SQL Sequence would be the perfect match for this.

I am working with the Play framework and it uses JPA2 backed by hibernate.

My problem right now is that I can't seem to get Hibernate to generate the Sequences as part of the automatic ddl update on start of the application.

Every documentation I can find about using @SequenceGenerator seems to be related to the Entity Id, and not how to define a sequence independent of the one used for the entity id.

Thanks.

EDIT: Just to give more context for the question, what I am doing is a implementing a service for keeping highscores, the first time a player starts the game it will ask the server for a guest account.

Guest accounts have a username in the form of player + number like "player123123" where the number would be generated from the sequence that I am trying to use.

When the user registers he will be able to change the username to something custom, but not in the form of player + anything, so that the namespace player + number remains free to use for guest accounts.

EDIT2: For now I got it to work by defining an extra useless entity that will never be used and just use the Sequence from that entity, but it is an ugly hack.

Ignoring possible solutions (hacks) for my concrete problem, I would like to know if it is possible with hibernate and jpa2 to declare extra (not the one used for id generation) sequences asociated to an entity or independent from any entity and have hibernate create them in the db automatically.

Improve this question
6
  • IMHO, the automatic ddl update is useful when starting a quick n' dirty project. Once it has matured, you should have SQL scripts to automate the schema creation before starting the app. Put this sequence creation instruction in this script. Commented Feb 8, 2012 at 8:19
  • Right now I am in the quick and dirty stage of the project, so I don't want to have to maintain extra SQL scripts by hand for now, when it is time to put this in production I will go that way. Commented Feb 8, 2012 at 15:52
  • Create the sequence explicitely in the database. Hibernate won't know about it, so its automatic DDL generation won't delete it. Commented Feb 8, 2012 at 17:06
  • At this point in time, I am using an in-memory db for most tests and in development. Commented Feb 8, 2012 at 18:28
  • Then open a JDBC connection to the database at startup, and create the sequence yourself. Commented Feb 8, 2012 at 18:31

2 Answers 2

Reset to default
1

Sequence generators (defined using @SequenceGenerator) should be unique across the entire persistence unit, so you could re-use them. This question deals with this (more or less) specific issue.

A couple of things to keep in mind:

  • Sequences are not supported by all database vendors, I believe Oracle and PostgreSQL support them but I'm not sure about the others
  • Sequence generator can only be defined on entities, so you can't use a @MappedSuperclass
  • Play framework will automatically add and ID field to your entities if you extend Model, so keep that in mind in case you want to override that behaviour
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I am using PostgreSQL so support should not be a problem. What I need is a second sequence associated to this entity, not the one used for generating the ID but another one.
0

Play adds automatically a numeric id to the class when you extend Model.

Given that you don't mind gaps and you only need the numeric sequence, I would suggest to use the id Play provides as the reference for your sequence.

NOTE: I'm assuming in here that you want that sequence linked to an entity which may contain more data. If that's not the case, this may not work.

Improve this answer

2 Comments

I can't just use the generated Id because to get that value, I need to first save the entity, but the problem with that is that I want to use the sequence to generate the data for a field that has to be unique. So in order to save the entity I already need to know a unique string to use as data for that field, I could use a uuid or something like that to save, and then after I get the id replace the value with the one calculated from the id but if is complicated and difficult to understand why I do that when reading the code.
@Ruben if the field gets it's data only based on the id, generate the data for the field on the 'save' method of the entity, where you'll have the id...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.