Can I configure Hibernate to create separate sequence for each table by default?

Question

Hibernate by default creates a globel sequence which is used to generate ids for all tables, (in the case for PostgreSQL) which scales very bad IMHO. Though I can specify for each entity type which sequence to use, I don't like to do it. I don't like to explicit name the sequence and force to use sequence as the generator strategy, because I want hibernate generate DDL for databases which may not support sequence at all. The single global sequence also make it impossible to use 32-bit int as the primary key, which means I must convert all int id(s) to long type.

Answer 1

Hibernate meant to be Database independent ORM solution, but while migrating to another database vendor some key issues arrives. One of them is Auto ID generation of underlying database. MySQL, Oracle & MS SQL Server all uses different techniques to generate auto ID for primary keys. So, when we start migrating we face lot of issues, extra work which should not be the case.

Prior Hibernate 3.2.3 there was no proper solution by the Hibernate, but in version 3.2.3 Hibernate guys made it possible to offer such portable ID generator which works well on any database. The two are followings,

• org.hibernate.id.enhanced.SequenceStyleGenerator

“the approach it takes to portability is that really you dont care whether you are physically using a SEQUENCE in the database; really you just want a sequence-like generation of values. On databases which support SEQUENCES, SequenceStyleGenerator will in fact use a SEQUNCE as the value generator; for those database which do not support SEQUENCES, it will instead use a single-row table as the value generator, but with the same exact charecteristics as a SEQUENCE value generator (namely it deals with the sequence table in a separate transaction at all times)”.

• org.hibernate.id.enhanced.TableGenerator

while not specifically targetting portability, TableGenerator can certainly be used across all databases. It uses a multi-row table where the rows are keyed by a (configurable) sequence_name column; one approach would be to have each entity define a unique sequence_name value in the table to segment its identifier values. It grew out of the older org.hibernate.id.MultipleHiLoPerTableGenerator and uses basically the same table structure. However, while MultipleHiLoPerTableGenerator inherently applies a hi-lo algorithm to the value generation, this new TableGenerator was added to be able to take advantage of the pluggable optimizers.

Example Entity Which usage, Hibernate's Sequences across all databases.

@Entity
@Table(name = "author")
public class Author implements java.io.Serializable {

 // Fields

 private Integer id;
 private String name;
 private Date birthDate;
 private Date deathDate;
 private String bio;
 private String wikiUrl;
 private String imagePath;
 private Boolean isFeatured;
 private Long totalContent;
 private Set<Content> contents = new HashSet<Content>(0);

 // Constructors

 /** default constructor */
 public Author() {
 }

 // Property accessors
 @Id
 @GeneratedValue(generator = "Author_SequenceStyleGenerator")
 @GenericGenerator(name = "Author_SequenceStyleGenerator", strategy = "org.hibernate.id.enhanced.SequenceStyleGenerator",
 parameters = {
 @Parameter(name = "sequence_name", value = "Author_SEQ"),
 @Parameter(name = "optimizer", value = "hilo"),
 @Parameter(name = "initial_value", value = "1"),
 @Parameter(name = "increment_size", value = "1") }
 )
 @Column(name = "id", unique = true, nullable = false, length = 11)
 public Integer getId() {
 return this.id;
 }

 public void setId(Integer id) {
 this.id = id;
 }

 @Column(name = "name", length = 50)
 public String getName() {
 return this.name;
 }

 public void setName(String name) {
 this.name = name;
 }

 @Temporal(TemporalType.DATE)
 @Column(name = "birth_date", length = 10)
 public Date getBirthDate() {
 return this.birthDate;
 }

 public void setBirthDate(Date birthDate) {
 this.birthDate = birthDate;
 }

 @Temporal(TemporalType.DATE)
 @Column(name = "death_date", length = 10)
 public Date getDeathDate() {
 return this.deathDate;
 }

 public void setDeathDate(Date deathDate) {
 this.deathDate = deathDate;
 }

 @Column(name = "bio", length = 65535)
 public String getBio() {
 return this.bio;
 }

 public void setBio(String bio) {
 this.bio = bio;
 }

 @Column(name = "wiki_url", length = 128)
 public String getWikiUrl() {
 return this.wikiUrl;
 }

 public void setWikiUrl(String wikiUrl) {
 this.wikiUrl = wikiUrl;
 }

 @Column(name = "image_path", length = 50)
 public String getImagePath() {
 return this.imagePath;
 }

 public void setImagePath(String imagePath) {
 this.imagePath = imagePath;
 }

 @Column(name = "is_featured")
 public Boolean getIsFeatured() {
 return this.isFeatured;
 }

 public void setIsFeatured(Boolean isFeatured) {
 this.isFeatured = isFeatured;
 }

 @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "author")
 public Set<Content> getContents() {
 return this.contents;
 }

 public void setContents(Set<Content> contents) {
 this.contents = contents;
 }

 @Transient
 public Long getTotalContent() {
 return totalContent;
 }

 public void setTotalContent(Long totalContent) {
 this.totalContent = totalContent;
 }

}
}

Answer 2

If the only reason for not explicitly specifying the sequence for each entity is you wanting to use the DDL on databases not supporting sequences, this might be solution for you:

@Id
@SequenceGenerator(name = "your_table_id_seq", sequenceName = "your_table_id_seq")
@GeneratedValue(strategy = GenerationType.AUTO, generator = "your_table_id_seq")
@Column(name = "your_table_id")
public Long getId() {
    return id;
}

This is going to work for databases w/o sequences (the strategy AUTO).