Custom sequences

Compatible with Hibernate 3.

Written on June, 2005, by Pietro Polsinelli.

If you have updated information, please feed this page (not the remarks below) or send me (mailto:ppolsinelli (at) open-lab (dot) com) infos to put here.


Suppose that you are using native id generation, that you are unfortunate enough so that you have to use Oracle. Hibernate relieves you much of the pain by creating and using an Oracle sequence. What your customer will ask you next, is to have a distinct sequence for every table. From 3.0.5 documentation, you can name the sequence on the mapping:

<generator class="sequence">
    <param name="sequence">person_id_sequence</param>
</generator>

 

So what follows applies only for the case you are using the native id approach. In this case, proceed as follows. This solution will work for any db that works with sequences.

Extend the dialect:

public class PlatformOracleDialect extends OracleDialect {
  public Class getNativeIdentifierGeneratorClass() {
    return PlatformSequenceGenerator.class;
  }
}

 

Create a custom sequence generator of this sort:

public class PlatformSequenceGenerator  implements PersistentIdentifierGenerator, Configurable {
  /**
   * The sequence parameter
   */
  public static final String SEQUENCE = "sequence";

  /**
   * The parameters parameter, appended to the create sequence DDL.
   * For example (Oracle): <tt>INCREMENT BY 1 START WITH 1 MAXVALUE 100 NOCACHE</tt>.
   */
  public static final String PARAMETERS = "parameters";

  private String sequenceName;
  private String parameters;
  private Type identifierType;
  private String sql;

  private static Collection<Sequence> sequences;


  private static final Log log = LogFactory.getLog(PlatformSequenceGenerator.class);

  public void configure(Type type, Properties params, Dialect dialect) throws MappingException {

    this.sequenceName = PropertiesHelper.getString(SEQUENCE, params, "hibernate_sequence");
    this.parameters = params.getProperty(PARAMETERS);
    String schemaName = params.getProperty(SCHEMA);
    String catalogName = params.getProperty(CATALOG);

    if (!sequenceName.contains(dialect.getSchemaSeparator())) {
      sequenceName = Table.qualify(catalogName, schemaName, sequenceName, dialect.getSchemaSeparator());
    }

    this.identifierType = type;
    sql = dialect.getSequenceNextValString(sequenceName);
  }

  public static void inject(Collection<Sequence> sequences, Dialect dialect) {
    PlatformSequenceGenerator.sequences = sequences;
    for (Sequence sequence : sequences) {
      sequence.sql = dialect.getSequenceNextValString(sequence.name);
    }
  }


  public Serializable generate(SessionImplementor session, Object obj) throws HibernateException {

    PreparedStatement st = null;
    try {

      boolean found = false;
      if (sequences != null)
        for (Sequence sequence : sequences) {
          if (ReflectionUtilities.instanceOfPersistent(obj, sequence.superClass)) {
            st = session.getBatcher().prepareSelectStatement(sequence.sql);
            found = true;
            break;
          }
        }
      if (!found)
        st = session.getBatcher().prepareSelectStatement(sql);

      try {
        ResultSet rs = st.executeQuery();
        final Serializable result;
        try {
          rs.next();
          result = IdentifierGeneratorFactory.get(
                  rs, identifierType
          );
        }
        finally {
          rs.close();
        }
        if (log.isDebugEnabled())
          log.debug("Sequence identifier generated: " + result);
        return result;
      }
      finally {
        session.getBatcher().closeStatement(st);
      }

    }
    catch (SQLException sqle) {
      throw JDBCExceptionHelper.convert(
              session.getFactory().getSQLExceptionConverter(),
              sqle,
              "could not get next sequence value",
              sql
      );
    }

  }

  public String[] sqlCreateStrings(Dialect dialect) throws HibernateException {

    String[] mainDdl = dialect.getCreateSequenceStrings(sequenceName);
    if (parameters != null) mainDdl[mainDdl.length - 1] += ' ' + parameters;

    List<String> ddlSimple = new ArrayList<String>(asList(mainDdl));

    for (Sequence sequence : sequences) {
      String[] tmpDdl = dialect.getCreateSequenceStrings(sequence.name);
      if (sequence.parameters != null) tmpDdl[tmpDdl.length - 1] += ' ' + sequence.parameters;
      List<String> tmp = asList(tmpDdl);
      ddlSimple.addAll(tmp);
    }

    String[] ddl = ddlSimple.toArray(new String[ddlSimple.size()]);    return ddl;

  }

  public String[] sqlDropStrings(Dialect dialect) throws HibernateException {
    ...
  }

  public Object generatorKey() {
    ...
  }


You can even model the Sequence notion:

public class Sequence {

      public Class superClass;
      public String name;
      public String sql;
      public String parameters;

  public Sequence(Class superClass,String name) {
    this.superClass = superClass;
    this.name = name;
  }
}


 

 

And at startup you can "inject" your sequence as follows

  Set<Sequence> sequences = new HashSet<Sequence>();
  Sequence sequence = new Sequence(Operator.class,"operator_sequence");
  sequences.add(sequence);
  ...
  PlatformSequenceGenerator.inject(sequences,new PlatformOracleDialect());

This means that the Operator class (and its entire hierarchy) will have an id generated by the operator_sequence. :-)


 

Mr. Rob Hasselbaum sent me an even simpler solution if all you want to do is base the sequence name on the table name. Just extend the SequenceGenerator like so and override the configure method.

public class TableNameSequenceGenerator extends SequenceGenerator {

    /**
     * If the parameters do not contain a {@link SequenceGenerator#SEQUENCE} name, we
     * assign one based on the table name.
     */
    public void configure(Type type, Properties params, Dialect dialect) throws MappingException {
        if(params.getProperty(SEQUENCE) == null || params.getProperty(SEQUENCE).length() == 0) {
            String tableName = params.getProperty(PersistentIdentifierGenerator.TABLE);
            if(tableName != null) {
                String seqName = “seq_” + tableName;
                params.setProperty(SEQUENCE, seqName);               
            }
        }
        super.configure(type, params, dialect);
    }
}

 

For example, if you have a standard “tbl_” prefix on all tables. The production code replaces the “tbl_” prefix with the “seq_” prefix in the above method. This ensures we don’t run into name length issues.