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.

Ruben Garat
  • 21
  • 2
  • 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. – JB Nizet Feb 08 '12 at 08: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. – Ruben Garat Feb 08 '12 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. – JB Nizet Feb 08 '12 at 17:06
  • At this point in time, I am using an in-memory db for most tests and in development. – Ruben Garat Feb 08 '12 at 18:28
  • Then open a JDBC connection to the database at startup, and create the sequence yourself. – JB Nizet Feb 08 '12 at 18:31
  • @JBNizet thanks for the info, I know that those are all viable solutions for the current problem I have, but I would still like to learn something here, and know if there is any solution working WITH hibernate instead of HACKING around hibernate. – Ruben Garat Feb 08 '12 at 19:05

2 Answers2

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
Community
  • 1
  • 1
tmbrggmn
  • 8,680
  • 10
  • 35
  • 44
  • 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. – Ruben Garat Feb 08 '12 at 15:57
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.

Pere Villega
  • 16,429
  • 5
  • 63
  • 100
  • 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 Garat Feb 08 '12 at 15:57
  • @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... – Pere Villega Feb 08 '12 at 16:11