Home / Programming / Java / Hibernate / How to achieve Database Auto ID Generation Portability in Hibernate

How to achieve Database Auto ID Generation Portability in Hibernate

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.

Both generators, in addition to other specific parameters, share 3 useful configuration parameters:

  • optimizer
  • initial_value
  • increment_size

The role of the optimizer is to limit the number of times we actually need to hit the database in order to determine the next identifier value. The exact effect of initial_value and increment_size somewhat depend on the optimizer chosen. optimizer provides 3 choices:

  • none – says to hit the database on each and every request
  • hilo – says to use an in-memory pooling technique which is the same basic logic as the older Hibernate hilo or seqhilo generators. In terms of the database values, they are incremented one at a time; in other words, increment_size applies to the in-memory algorithm
  • pooled – says to use a stored pooling technique. Unlike hilo, where incremental values are stored and retrieved from the database sequence/table, pooled stores the actual current hi-value into the database. As an example, consider increment_size=10

Editor’s Pick;

You have to decide which generator you should use right from the application development. All such scenarios, flexibility & portability should taken care at the starting so your application architecture support these things without any extra work. So, I preferred to use   SequenceStyleGenerator which is hibernate’s own sequence generator just like oracle have & it works well on all major databases. Following is the simple plain example, how can you use it. It’s really simple to use , see the following code snippet and then below it complete pojo entity.

<pre>@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;
 }</pre>

See the full example of Hibernate entity.

import java.util.Date;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.persistence.Transient;
import org.hibernate.annotations.GenericGenerator;
import org.hibernate.annotations.Parameter;

@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;
 }

}
}

References:

About Faisal Basra

Check Also

Securing Spring Remoting by Spring Security

If you are exposing your some of services by Spring Remoting’s Http Invoker, most probably ...