openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel Weidele <>
Subject Extending MySQL Dictionary to support @SequenceGenerator
Date Sun, 22 Jul 2012 11:28:39 GMT

*Use case:*
I need a strict, no-gaped sequential id-column for sequential numbering

I could not make it together with MySQL out-of-the-box (OpenJPA 2.2.0) -
neither with AUTO, IDENTITY, SEQUENCE nor TABLE strategy.

*My approach:*
Extending the dictionary to make use of MySQL's AUTO_INCREMENT feature and
accessing its system table to read from there.

package my.package.;

import org.apache.openjpa.jdbc.schema.Sequence;
import org.apache.openjpa.jdbc.sql.MySQLDictionary;

public class CKMySQLDictionary extends MySQLDictionary
    public CKMySQLDictionary()
        this.nextSequenceQuery = "SELECT `auto_increment` FROM
`information_schema`.`tables` WHERE `table_name`=\"{0}\"";

    public String[] getCreateSequenceSQL(Sequence seq)
        StringBuffer buffer = new StringBuffer();
        buffer.append("ALTER TABLE  `" + seq.getFullIdentifier() + "`
AUTO_INCREMENT=" + seq.getInitialValue()
                      + ", CHANGE  `id`  `id` BIGINT( 20 ) NOT NULL
        return new String[] { buffer.toString() };

<property name="databasePlatform" value="my.package.CKMySQLDictionary" />

*Now here's the issue:*
As you can see, the next sequence query has a placeholder '{0}'  -- which
will later be replaced by the value of the @SequenceGenerator's attribute
value of 'sequenceName'.
I additionally need a second placeholder '{1}' to differenciate between
schemas, as the 'table_name' could be equal among different schemas -- such
that I could use e.g.:

        this.nextSequenceQuery = "SELECT `auto_increment` FROM
`information_schema`.`tables` WHERE `table_name`=\"{0}\" AND *

*Any suggestions?*
Best regards,

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message