Extending org.hibernate.dialect.MySQLDialect

gibbs_wesley · **Posted:** Sat May 29, 2010 10:01 pm

We are hoping to do local development against MySQL, our production environment is
running against Oracle. For local development, MySQL is installed on each developers
machine. Our Java application code uses annotations for sequences, because sequences are
handled in Oracle (our production environment). Initially when I was installing MySQL, I was
not aware that it did not support sequences like Oracle does. My WebLogic errors said something
to the effect that MySQL does not support sequences. After Googling a bit I saw a post that
someone had mentioned that extending the MySQLDialect class was a solution and that the
appropriate methods just had to be over-ridden.

I have extended the MySQLDialect class so that I can handle the use of sequences.
I've created a single database table with the columns sequence_name and sequence value
that will only be utilized by a local build. I was thinking that when the sequence name comes in
as a parameter in the class that is extending MySQLDialect that I could just add (or update) my new
MySQL sequence database table to track sequence names and their respective current number value.

My code is:

Code:

package com.bah.englink.database.dialect;

import org.jboss.seam.annotations.Name;
import org.jboss.seam.annotations.AutoCreate;
import org.jboss.seam.annotations.In;
import org.jboss.seam.annotations.Logger;
import org.jboss.seam.log.Log;
import org.hibernate.dialect.MySQLDialect;
import org.hibernate.MappingException;
import javax.persistence.EntityManager;

/**
 * Created by IntelliJ IDEA.
 * User: 524157
 * Date: May 28, 2010
 * Time: 11:53:48 PM
 * Package: com.bah.englink.database.dialect
 * Purpose:
 */
@Name("englinkMySqlDialect")
@AutoCreate
public class MySQLDialectEnhanced extends MySQLDialect {

    @In
    protected EntityManager entityManager;

    /**
     * Used to perform logging of information, debug error or general errors.
     */
    @Logger
    Log log;

    /***
     * Overvides the superclass method because the MySQL dialect does not support sequences.
     * We will have our own custom sequence generation that is saved within a single database
     * table.  This database table will contain 2 fields (sequence_name and sequence_value).
     * Sequence_name's that are passed in as a parameter that don't exist will be created, set
     * to zero, incremented by one, and the SQL to grab the current value will be returned to
     * the caller.  Sequence_name's that do exist will be incremented by one, and the SQL to
     * grab the current value will be returned to the caller.
     * @param sequenceName
     * @return
     * @throws org.hibernate.MappingException
     */
    @Override
    public String getSequenceNextValString(String sequenceName) throws MappingException {
        Integer sequenceCount = 0;
        int nextSequenceValue = 0;
        MySQLSequence emss;

        if (sequenceName == null)
            log.debug("sequenceName is null...");

        try {
            sequenceCount = (Integer)entityManager.createQuery("select count(*) from MySQLSequence sn where sn.sequenceName = :param1").setParameter("param1", sequenceName).getSingleResult();

            if (sequenceCount.intValue() > 0) {
                emss = (MySQLSequence)entityManager.createQuery("from MySQLSequence sn where sn.sequenceName = :param1").setParameter("param1", sequenceName).getSingleResult() ;
                nextSequenceValue = emss.getSequenceValue() + 1;
                emss.setSequenceValue(nextSequenceValue);
            }
            else {
                emss = new MySQLSequence();
                emss.setSequenceName(sequenceName);
                emss.setSequenceValue(1);
            }

            entityManager.persist(emss);
            entityManager.flush();
            return "select sequenceValue from MySqlSequence where sequenceName = '" + sequenceName + "'";
        }
        catch(MappingException me) {
            //log.debug("Encountering a MappingException in EnglinkMySQLDialect.getSequenceNextValString(String sequenceName class, the sequence name is: " + sequenceName);
        }
        catch(Exception e) {
            //log.debug("Encountering a Exception in EnglinkMySQLDialect.getSequenceNextValString(String sequenceName class, the sequence name is: " + sequenceName + ", exception description is: " + e.getMessage());
        }

        return null;
    }

    @Override
    protected String getDropSequenceString(String sequenceName) throws MappingException {
        try {
           //return "SELECT * FROM INFORMATION_SCHEMA.TABLES";
            log.debug("Attempting protected String MySQLDialectEnhanced.getDropSequenceString(String sequenceName) throws MappingException {");
        }
        catch(MappingException me) {
            log.debug("getDropSequenceString MappingException!");
        }
        catch(Exception e) {
            //log.debug("getDropSequenceString Exception: " + e.getMessage());
        }
        return "";
    }

    @Override
    public boolean supportsSequences() {
        return true;
    }
}

My current error in WebLogic is below:

Code:

<May 29, 2010 9:10:33 PM EDT> <Warning> <Deployer> <BEA-149004> <Failures were detected while initiating deploy task for application '_appsdir_wls-test_ear'.>
<May 29, 2010 9:10:33 PM EDT> <Warning> <Deployer> <BEA-149078> <Stack trace for message 149004
weblogic.application.ModuleException:
        at weblogic.servlet.internal.WebAppModule.prepare(WebAppModule.java:368)
        at weblogic.application.internal.flow.ModuleListenerInvoker.prepare(ModuleListenerInvoker.java:93)
        at weblogic.application.internal.flow.DeploymentCallbackFlow$1.next(DeploymentCallbackFlow.java:387)
        at weblogic.application.utils.StateMachineDriver.nextState(StateMachineDriver.java:37)
        at weblogic.application.internal.flow.DeploymentCallbackFlow.prepare(DeploymentCallbackFlow.java:58)
        Truncated. see log file for complete stacktrace
org.hibernate.MappingException: Dialect does not support sequences
        at org.hibernate.dialect.Dialect.getCreateSequenceString(Dialect.java:677)
        at org.hibernate.dialect.Dialect.getCreateSequenceStrings(Dialect.java:646)
        at org.hibernate.id.SequenceGenerator.sqlCreateStrings(SequenceGenerator.java:130)
        at org.hibernate.cfg.Configuration.generateSchemaCreationScript(Configuration.java:945)
        at org.hibernate.tool.hbm2ddl.SchemaExport.<init>(SchemaExport.java:105)
        Truncated. see log file for complete stacktrace

I'm not sure why the code still comes back with the error: "Dialect does not support sequences" if I over-ride
the supportsSequences method and the other sequence methods in the MySQLDialect. Any thoughts or ideas
would be appreciated.

Thank you in advance,
Wes

sanne.grinovero · **Posted:** Mon May 31, 2010 2:51 am

There's a simpler approach to your problem: don't force your mappings to use sequences, if you annotate your id like:

Code:

@Id @GeneratedValue
public Long getId()

hibernate will use Sequences on Oracle, and switch to auto-incremental values on MySQL.
Of course this means there are some differences between testing and production, so before going into production I'd recommend to have a staging system using Oracle.

About your approach:
1) did you register it as dialect in the hibernate configuration?
2) I might be wrong, but I doubt you can use annotation-based injection in the dialect, the dialect instance is loaded and managed by hibernate core. You might use the Seam static helpers to retrieve a reference to the objects you need.
3) your implementation is not considering race conditions; under stress imho it's likely you're going to assign the same sequence number to different objects.

semox78 · **Joined:** Fri Mar 20, 2015 2:44 am **Posts:** 1

Dear sanne.grinovero

Thank you, perfect! The simple approach is - 5 years later - my lifesaver.

Best regards,
Semox78

sanne.grinovero · **Posted:** Fri Mar 20, 2015 7:35 am

Nice :-)