Hello,
*Use case:*
I need a strict, no-gaped sequential id-column for sequential numbering
invoices.
*Problem:*
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.
*Implementation:*
package my.package.;
import org.apache.openjpa.jdbc.schema.Sequence;
import org.apache.openjpa.jdbc.sql.MySQLDictionary;
public class CKMySQLDictionary extends MySQLDictionary
{
public CKMySQLDictionary()
{
super();
this.nextSequenceQuery = "SELECT `auto_increment` FROM
`information_schema`.`tables` WHERE `table_name`=\"{0}\"";
}
@Override
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
AUTO_INCREMENT");
return new String[] { buffer.toString() };
}
}
*Configuration:*
<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 *
`table_schema`=\"{1}\"*";
*Any suggestions?*
*
*
Best regards,
Daniel
